7. MySQL¶

Intermediate · 14 min read

MySQL is the most widely deployed open-source database: WordPress, Shopify, Uber, GitHub and countless startups run on it, and every cloud offers it (Amazon RDS / Aurora, Google Cloud SQL, Azure Database for MySQL). If your company's orders and users live in MySQL, your RAG app and text-to-SQL agent will be talking to it.

This topic covers what is different about MySQL compared with the standard SQL from topics 1–4: the traps, the syntax and the features worth knowing for interviews.

Version and how to run these examples

Written for MySQL 8.4 LTS (most of it works on 8.0). Unlike topics 1–6, these examples aren't run on this site's SQLite: start a free local server with Docker and run them yourself.

docker run --name mysql -e MYSQL_ROOT_PASSWORD=pass -p 3306:3306 -d mysql:8.4
docker exec -it mysql mysql -uroot -ppass        # interactive shell

7.1 Connect from Python¶

# pip install pymysql
import pymysql

con = pymysql.connect(host="localhost", user="root", password="pass",
                      database="shop", charset="utf8mb4",
                      cursorclass=pymysql.cursors.DictCursor)   # rows as dicts
with con.cursor() as cur:
    cur.execute("SELECT id, name FROM customers WHERE city = %s", ("Pune",))   # %s placeholders
    rows = cur.fetchall()
con.commit()      # pymysql does NOT autocommit by default: forget this and your writes vanish

Driver	Install	Placeholder	Notes
PyMySQL	`pip install pymysql`	`%s`	pure Python, easy to install
mysql-connector-python	`pip install mysql-connector-python`	`%s`	Oracle's official driver
SQLAlchemy URL	—	—	`mysql+pymysql://root:pass@localhost:3306/shop`

Always pass values as parameters

%s here is a placeholder, not Python string formatting. cur.execute(f"... WHERE city = '{city}'") is SQL injection waiting to happen.

7.2 Data types that matter¶

CREATE TABLE customers (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(100) NOT NULL,
    city        VARCHAR(60),
    plan        ENUM('free', 'pro', 'team') NOT NULL DEFAULT 'free',
    balance     DECIMAL(12, 2) NOT NULL DEFAULT 0.00,   -- money: never FLOAT
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,          -- really TINYINT(1)
    profile     JSON,
    created_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at  DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

SELECT LAST_INSERT_ID();      -- the id just generated in this connection

Type	Use it for	Trap
`INT` / `BIGINT`	ids, counts	`INT` tops out at ~2.1 billion: use `BIGINT` for ids that grow fast (events, logs)
`DECIMAL(p, s)`	money	`FLOAT` / `DOUBLE` are approximate
`VARCHAR(n)` / `TEXT`	strings	`TEXT` can't have a default and is only indexable by prefix
`DATETIME`	timestamps	stored as written, no time zone. Store UTC
`TIMESTAMP`	timestamps	converted to UTC and back using the session time zone; ends in 2038
`JSON`	flexible attributes	validated on insert; index via generated columns (7.6)
`ENUM`	small fixed lists	adding a value means `ALTER TABLE`

utf8 is not UTF-8

In MySQL, utf8 is an alias for utf8mb3: at most 3 bytes per character, so emoji and some Indic and CJK characters fail or get mangled. Always use utf8mb4 (the default since 8.0) for the table and the connection (charset="utf8mb4"). LLM output is full of characters that need 4 bytes.

Collations: why `'a' = 'A'` is true¶

The default collation utf8mb4_0900_ai_ci is accent-insensitive and case-insensitive:

SELECT 'Priya' = 'PRIYA', 'resume' = 'résumé';          -- 1, 1
SELECT 'Priya' = 'PRIYA' COLLATE utf8mb4_0900_as_cs;    -- 0  (accent- and case-sensitive)

That's convenient for user search, and surprising for unique constraints: alice@x.com and ALICE@x.com count as duplicates. Choose a _bin or _as_cs collation for columns where case matters (tokens, hashes).

7.3 Dialect differences you will hit¶

Need	MySQL	Standard / PostgreSQL
Quote an identifier	`order` (backticks)	`"order"`
Concatenate	`CONCAT(a, ' ', b)` · `CONCAT_WS(', ', a, b)`	`a \|\| ' ' \|\| b`
Integer division	`7 DIV 2` → 3; `7 / 2` → 3.5000	`7 / 2` → 3 (Postgres)
Limit with offset	`LIMIT 20 OFFSET 40` or `LIMIT 40, 20`	`LIMIT 20 OFFSET 40`
FULL OUTER JOIN	not supported: `LEFT JOIN … UNION … RIGHT JOIN`	`FULL JOIN`
INTERSECT / EXCEPT	8.0.31+	yes
String list per group	`GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')`	`STRING_AGG(name, ', ')`
Days between dates	`DATEDIFF(d2, d1)`	`d2 - d1`
Add an interval	`DATE_ADD(d, INTERVAL 7 DAY)` or `d + INTERVAL 7 DAY`	`d + INTERVAL '7 days'`
Difference in minutes	`TIMESTAMPDIFF(MINUTE, t1, t2)`	`EXTRACT(EPOCH FROM t2 - t1) / 60`
Month bucket	`DATE_FORMAT(d, '%Y-%m-01')`	`DATE_TRUNC('month', d)`
Booleans	`TRUE` is just `1`	real `boolean` type
Regex	`REGEXP_LIKE(s, 'pat')`, `s REGEXP 'pat'`	`s ~ 'pat'`

Emulating FULL OUTER JOIN:

SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;

GROUP_CONCAT silently truncates

The result is cut at group_concat_max_len bytes (default 1024) with only a warning. Building a long context string for an LLM? Raise it first: SET SESSION group_concat_max_len = 1000000;

`ONLY_FULL_GROUP_BY` and strict mode¶

MySQL 8 enables ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES by default. The first rejects selecting a column that is neither grouped nor aggregated (old MySQL silently picked a random row). The second makes bad data an error rather than a silently truncated or zeroed value. If a legacy app turns them off, expect quiet data bugs.

7.4 Upserts: `INSERT … ON DUPLICATE KEY UPDATE`¶

When a PRIMARY KEY or UNIQUE index would be violated, update the existing row instead:

INSERT INTO daily_usage (user_id, day, calls, tokens)
VALUES (42, '2026-10-20', 1, 380) AS new                       -- row alias (8.0.19+)
ON DUPLICATE KEY UPDATE
    calls  = daily_usage.calls + new.calls,                    -- increment, don't overwrite
    tokens = daily_usage.tokens + new.tokens;

The older form tokens = tokens + VALUES(tokens) still works but VALUES() here is deprecated since 8.0.20.

Statement	Behaviour on a duplicate key
`INSERT … ON DUPLICATE KEY UPDATE`	updates the existing row (the usual choice)
`INSERT IGNORE`	skips the row and turns other errors into warnings: use with care
`REPLACE INTO`	deletes the old row and inserts a new one: new auto-id, fires delete triggers, cascades

7.5 CTEs and window functions (8.0+)¶

MySQL 8 supports everything from Advanced SQL: WITH, WITH RECURSIVE, ROW_NUMBER, RANK, LAG/LEAD, running totals and named windows.

-- latest ticket per customer
WITH ranked AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM tickets t
)
SELECT * FROM ranked WHERE rn = 1;

Recursive CTEs stop at cte_max_recursion_depth (default 1000). Generate a calendar of dates:

WITH RECURSIVE days AS (
    SELECT DATE('2026-10-01') AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-10-31'
)
SELECT d FROM days;

Still on MySQL 5.7?

No CTEs or window functions. 5.7 reached end of life in October 2023: if you meet it in an interview, "upgrade to 8.x" is part of the answer.

7.6 JSON columns, and how to index them¶

SELECT id,
       meta -> '$.feature'          AS feature_json,   -- JSON value: "chat" (with quotes)
       meta ->> '$.feature'         AS feature,        -- unquoted text: chat
       meta ->> '$.tokens.in'       AS tokens_in
FROM llm_calls
WHERE meta ->> '$.feature' = 'chat';

A function on a column can't use a normal index. Expose the JSON field as a generated column and index that:

ALTER TABLE llm_calls
    ADD COLUMN feature VARCHAR(30) AS (meta ->> '$.feature') STORED,
    ADD INDEX idx_llm_calls_feature (feature);

-- or a functional index directly (8.0.13+; note the double parentheses)
CREATE INDEX idx_email_lower ON customers ((LOWER(email)));

JSON arrays get a multi-valued index (8.0.17+), used by MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS:

CREATE INDEX idx_tags ON tickets ((CAST(meta -> '$.tags' AS CHAR(30) ARRAY)));
SELECT id FROM tickets WHERE 'refund' MEMBER OF (meta -> '$.tags');

Turn a JSON array into rows with JSON_TABLE:

SELECT t.id, j.tag
FROM tickets t,
     JSON_TABLE(t.meta, '$.tags[*]' COLUMNS (tag VARCHAR(30) PATH '$')) AS j;

7.7 Full-text search (keyword retrieval for RAG)¶

ALTER TABLE docs ADD FULLTEXT INDEX ft_docs (title, body);

-- natural language mode: ranked by relevance
SELECT id, title, MATCH(title, body) AGAINST ('refund policy') AS score
FROM docs
WHERE MATCH(title, body) AGAINST ('refund policy')
ORDER BY score DESC
LIMIT 5;

-- boolean mode: + must, - must not, * prefix
SELECT id, title FROM docs
WHERE MATCH(title, body) AGAINST ('+refund* -shipping' IN BOOLEAN MODE);

The MATCH column list must match a FULLTEXT index exactly. InnoDB ignores words shorter than innodb_ft_min_token_size (default 3) and common stopwords, so searching for "AI" finds nothing by default. For serious hybrid search, many teams pair MySQL with a search engine (OpenSearch) or a vector store.

Vectors in MySQL

MySQL 9.0 added a VECTOR data type (STRING_TO_VECTOR('[0.1, 0.2]')), but in Community Edition you can store vectors, not run indexed similarity search. Similarity functions arrived in Oracle's HeatWave service. If you need vector search next to relational data today, PostgreSQL + pgvector (topic 8) is the common choice.

7.8 Indexes and `EXPLAIN`¶

InnoDB stores each table clustered by its primary key: the rows are the leaves of the primary-key B-tree. Every secondary index stores the primary key value to find the row. Consequences:

A short, increasing primary key (BIGINT AUTO_INCREMENT) keeps inserts fast and secondary indexes small.
Random UUIDs as the primary key scatter inserts across the tree. Prefer UUID_TO_BIN(UUID(), 1) (time-ordered) stored in BINARY(16), or keep an integer key and a separate UUID column.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND created_at >= '2026-10-01';
EXPLAIN ANALYZE SELECT ...;      -- 8.0.18+: actually runs it, shows real time and rows
EXPLAIN FORMAT=TREE SELECT ...;  -- readable plan tree

The type column of EXPLAIN, from worst to best:

`type`	Meaning
`ALL`	full table scan: the one to fix
`index`	full scan of an index
`range`	index range (`BETWEEN`, `>`, `IN`)
`ref`	index lookup on a non-unique value
`eq_ref`	one row per join via a unique key
`const`	at most one row, by primary key or unique key

Also check key (the index actually used), rows (the estimate) and Extra: Using filesort and Using temporary mean extra sort or temp-table work; Using index means a covering index.

7.9 Transactions, isolation and locking¶

InnoDB's default isolation level is REPEATABLE READ. Plain SELECTs read a consistent snapshot (MVCC), so readers don't block writers. Locking reads (FOR UPDATE, FOR SHARE) and writes use next-key locks, which also lock the gaps between index entries, so other transactions can't insert phantom rows into the range.

START TRANSACTION;
SELECT balance FROM accounts WHERE id = 7 FOR UPDATE;      -- lock the row
UPDATE accounts SET balance = balance - 100 WHERE id = 7;
COMMIT;

A job queue that many workers can poll without blocking each other (8.0+):

START TRANSACTION;
SELECT id, payload FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 10
FOR UPDATE SKIP LOCKED;          -- each worker gets different rows
UPDATE jobs SET status = 'running' WHERE id IN (...);
COMMIT;

Deadlocks are normal: retry them

InnoDB detects deadlocks and rolls back one transaction with error 1213. Your code should retry the whole transaction. Reduce them by touching rows in a consistent order and keeping transactions short. Also watch out for UPDATE … WHERE on an unindexed column: InnoDB then locks every row it scans.

7.10 MySQL interview checklist¶

Explain why utf8mb4 and not utf8.
Explain the clustered primary key and why random UUID keys hurt.
Write an upsert with ON DUPLICATE KEY UPDATE; compare it with REPLACE and INSERT IGNORE.
Index a JSON field with a generated column.
Read the type and Extra columns of EXPLAIN.
Explain REPEATABLE READ, MVCC and gap locks.
Build a worker queue with FOR UPDATE SKIP LOCKED.

Interview questions¶

What's the difference between DATETIME and TIMESTAMP in MySQL?

DATETIME stores the value exactly as given, with no time zone, from year 1000 to 9999. TIMESTAMP converts from the session time zone to UTC on write and back on read, and only covers 1970 to 2038. Store UTC in DATETIME (or keep the server and session in UTC) to avoid both the 2038 limit and time-zone surprises.

Why can't MySQL use my index for WHERE YEAR(created_at) = 2026?

The index is sorted by created_at, not by YEAR(created_at), so the database must compute the function for every row. Rewrite it as a range: created_at >= '2026-01-01' AND created_at < '2027-01-01'. Or add a functional index if you really need that expression.

InnoDB vs MyISAM?

InnoDB (the default since 5.5) has transactions, row-level locking, foreign keys and crash recovery. MyISAM has none of these: it uses table-level locks and can lose data after a crash. Use InnoDB; MyISAM only appears in legacy systems.

How would you scale reads on MySQL for a GenAI dashboard?

Send the heavy analytical queries to read replicas (asynchronous replication, so accept some lag; read your own writes from the primary), add covering indexes for the dashboard queries, cache the results, and move true analytics to a warehouse (BigQuery, Snowflake, ClickHouse) fed by CDC rather than running them on the primary.

Practice¶

Create llm_calls with a JSON meta column, add a generated feature column, index it, and confirm with EXPLAIN that WHERE feature = 'chat' uses the index.
Write a daily usage upsert that increments calls and tokens, then run it twice and check the totals.
Open two mysql shells and make them deadlock on two rows. Read the error, then fix the lock order.

Next: PostgreSQL — the database most GenAI stacks pick, and why.