Intro

-- ===== The values =====
-- - Battle-tested at huge scale (Facebook, YouTube, Wikipedia at points in history)
-- - Excellent performance for typical web workloads
-- - InnoDB engine: transactions, FK constraints, MVCC
-- - Strong replication story; widely managed by every cloud

-- ===== Hello, table =====
CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(254) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO users (email, name) VALUES ('a@x.io', 'Alex');

SELECT id, name FROM users WHERE email = 'a@x.io';

-- ===== Joins + group =====
CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

SELECT u.name, COUNT(*) AS orders, SUM(o.total) AS spent
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

-- ===== JSON =====
CREATE TABLE events (id BIGINT AUTO_INCREMENT PRIMARY KEY, data JSON NOT NULL);
SELECT data->>'$.kind' AS kind, COUNT(*)
FROM events
GROUP BY data->>'$.kind';

-- ===== Transactions =====
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- ===== When MySQL wins =====
-- - LAMP-style web apps + their descendants
-- - WordPress, Drupal, Magento, countless SaaS
-- - When the team is already deep in MySQL operations
-- - Managed everywhere: RDS, Cloud SQL, Azure DB for MySQL, PlanetScale

-- ===== When MySQL hurts =====
-- - You want stricter type enforcement and standards conformance (lean Postgres)
-- - You need rich extensions (PostGIS, etc) without bolt-ons
-- - Heavy JSON workloads (Postgres JSONB is more pleasant)

-- ===== Patterns to internalise =====
-- - utf8mb4 always; never plain utf8 (3-byte broken)
-- - DECIMAL for money
-- - InnoDB by default; not MyISAM
-- - Use the right timestamp type for the use case (TIMESTAMP vs DATETIME)

-- ===== Pitfalls =====
-- - VARCHAR(255) on utf8mb4 -> index key length limits
-- - Implicit zero dates ('0000-00-00') in legacy modes
-- - FLOAT/DOUBLE for money -> rounding bugs
-- - Forgetting innodb_buffer_pool_size tuning on big instances

-- MySQL: open-source RDBMS, owned by Oracle.
-- MariaDB is a community-maintained fork.