Chapter 07

Tables, Data Types and Constraints

How to define tables that store the right values in the right shape and refuse the wrong ones, in MySQL 9.7 — with the older 5.7 and 8.0 behaviour you still meet.

11 topics

Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#CREATE TABLE and ALTER TABLE

Added

A table definition is a contract. Every column says what kind of value it accepts, whether it may be missing, and what to use when nothing is given; every constraint says which combinations of rows are allowed. Once the contract is in place, every application, script and person that writes to the table is held to it — the database refuses bad data instead of trusting each piece of code to check.

Think of CREATE TABLE as printing a paper form with labelled boxes ("date of birth: DD/MM/YYYY, required") and ALTER TABLE as reprinting the form after thousands of copies have already been filled in. Reprinting is easy when the change only adds an optional box; it's expensive when every existing copy has to be rewritten — that's what Online schema changes is about.

A CREATE TABLE has three kinds of parts: column definitions (name, type, NULL/NOT NULL, DEFAULT, AUTO_INCREMENT), indexes and keys (PRIMARY KEY, UNIQUE, INDEX) and constraints (FOREIGN KEY, CHECK). Here is a reviews table for the site's sample schema, using all three:

SQL

CREATE TABLE reviews (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id  INT          NOT NULL,
  customer_id INT          NOT NULL,
  rating      TINYINT      NOT NULL,
  body        TEXT,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_reviews_product  FOREIGN KEY (product_id)  REFERENCES products(id),
  CONSTRAINT fk_reviews_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT chk_reviews_rating  CHECK (rating BETWEEN 1 AND 5)
);
DESCRIBE reviews;
INSERT INTO reviews (product_id, customer_id, rating, body) VALUES (1, 1, 5, 'Light and fast'), (3, 2, 4, NULL);
SELECT id, product_id, customer_id, rating, body FROM reviews;

Result

+-------------+--------------+------+-----+-------------------+-------------------+
| Field       | Type         | Null | Key | Default           | Extra             |
+-------------+--------------+------+-----+-------------------+-------------------+
| id          | int unsigned | NO   | PRI | NULL              | auto_increment    |
| product_id  | int          | NO   | MUL | NULL              |                   |
| customer_id | int          | NO   | MUL | NULL              |                   |
| rating      | tinyint      | NO   |     | NULL              |                   |
| body        | text         | YES  |     | NULL              |                   |
| created_at  | datetime     | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+--------------+------+-----+-------------------+-------------------+
+----+------------+-------------+--------+----------------+
| id | product_id | customer_id | rating | body           |
+----+------------+-------------+--------+----------------+
|  1 |          1 |           1 |      5 | Light and fast |
|  2 |          3 |           2 |      4 | NULL           |
+----+------------+-------------+--------+----------------+

What’s happening

id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY makes id the primary key — in InnoDB that also makes it the key the rows are physically stored by — and lets MySQL hand out 1, 2, 3… when the insert leaves it out. DESCRIBE shows that as PRI and auto_increment.
NOT NULL on product_id, customer_id and rating means a review without them is rejected. body has no NOT NULL, so it's nullable — the second review stores NULL, which means "no text", not an empty string.
DEFAULT CURRENT_TIMESTAMP fills created_at when the insert doesn't mention it; DESCRIBE marks that as DEFAULT_GENERATED because the default is an expression, not a constant.
The two FOREIGN KEY constraints say every product_id must exist in products.id and every customer_id in customers.id. InnoDB needs an index on each to check them quickly, so it created one automatically — that's the MUL (non-unique index) in the Key column.
CHECK (rating BETWEEN 1 AND 5) is enforced on every insert and update (since MySQL 8.0.16). Naming constraints with CONSTRAINT name pays off later: error messages quote the name, and ALTER TABLE … DROP CONSTRAINT needs it.
Both inserts satisfy the contract, so both rows land and get ids 1 and 2.

AdvancedChanging the table later with ALTER TABLE

ALTER TABLE changes an existing definition. Several changes can go in one statement, which matters because each ALTER that rebuilds the table pays the cost once per statement:

SQL

ALTER TABLE reviews
  ADD COLUMN title VARCHAR(120) NULL AFTER rating,
  RENAME COLUMN body TO comment,
  MODIFY rating TINYINT UNSIGNED NOT NULL,
  ADD CONSTRAINT uq_reviews_product_customer UNIQUE (product_id, customer_id);
SHOW CREATE TABLE reviews;
SELECT * FROM reviews;

Result

*************************** 1. row ***************************
       Table: reviews
Create Table: CREATE TABLE `reviews` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `customer_id` int NOT NULL,
  `rating` tinyint unsigned NOT NULL,
  `title` varchar(120) DEFAULT NULL,
  `comment` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reviews_product_customer` (`product_id`,`customer_id`),
  KEY `fk_reviews_customer` (`customer_id`),
  CONSTRAINT `fk_reviews_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `fk_reviews_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `chk_reviews_rating` CHECK ((`rating` between 1 and 5))
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+----+------------+-------------+--------+-------+----------------+---------------------+
| id | product_id | customer_id | rating | title | comment        | created_at          |
+----+------------+-------------+--------+-------+----------------+---------------------+
|  1 |          1 |           1 |      5 | NULL  | Light and fast | 2026-10-16 15:23:17 |
|  2 |          3 |           2 |      4 | NULL  | NULL           | 2026-10-16 15:23:17 |
+----+------------+-------------+--------+-------+----------------+---------------------+

What’s happening

ADD COLUMN title … AFTER rating adds a nullable column in a chosen position; existing rows get NULL because there's no default.
RENAME COLUMN body TO comment (MySQL 8.0+) renames without restating the type. On 5.7 you had to write CHANGE body comment TEXT and repeat the whole definition — forget an attribute and it's silently dropped.
MODIFY rating TINYINT UNSIGNED NOT NULL changes the type. MODIFY replaces the whole column definition, so anything you don't repeat (a DEFAULT, a COMMENT, NOT NULL) is lost — the classic MODIFY accident.
ADD CONSTRAINT … UNIQUE (product_id, customer_id) means one review per customer per product.
Look at the keys in SHOW CREATE TABLE: the automatic fk_reviews_product index is gone. The new unique index starts with product_id, so it can serve the foreign key too, and InnoDB dropped the now-redundant one. The docs warn about exactly this: the automatic index "might be silently dropped later".
SHOW CREATE TABLE is the one to trust for "what is this table really?" — it shows the engine, the character set, the collation and the next AUTO_INCREMENT value (3), none of which DESCRIBE shows.

AdvancedProof: the index list before and after

Here are the indexes from information_schema.statistics, first right after CREATE TABLE, then after the ALTER:

SQL

SELECT index_name, column_name, seq_in_index, non_unique
FROM information_schema.statistics
WHERE table_schema = 'ch07' AND table_name = 'reviews'
ORDER BY index_name, seq_in_index;

Result

+---------------------+-------------+--------------+------------+
| INDEX_NAME          | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE |
+---------------------+-------------+--------------+------------+
| fk_reviews_customer | customer_id |            1 |          1 |
| fk_reviews_product  | product_id  |            1 |          1 |
| PRIMARY             | id          |            1 |          0 |
+---------------------+-------------+--------------+------------+
+-----------------------------+-------------+--------------+------------+
| INDEX_NAME                  | COLUMN_NAME | SEQ_IN_INDEX | NON_UNIQUE |
+-----------------------------+-------------+--------------+------------+
| fk_reviews_customer         | customer_id |            1 |          1 |
| PRIMARY                     | id          |            1 |          0 |
| uq_reviews_product_customer | product_id  |            1 |          0 |
| uq_reviews_product_customer | customer_id |            2 |          0 |
+-----------------------------+-------------+--------------+------------+

What’s happening

Before: one index per foreign key (fk_reviews_customer, fk_reviews_product) plus PRIMARY. The automatic indexes take the constraint's name.
After: fk_reviews_product has disappeared. uq_reviews_product_customer has product_id at position 1, which is all a foreign key check on product_id needs ("find child rows with this product_id" is a leftmost-prefix lookup).
fk_reviews_customer stays, because in the unique index customer_id is only the second column — useless for "find rows by customer_id" on its own (see Composite indexes and the leftmost-prefix rule).
The practical consequence: the unique index is now load-bearing. Try to drop it and MySQL refuses: ERROR 1553 (HY000): Cannot drop index 'uq_reviews_product_customer': needed in a foreign key constraint. Add a plain index on product_id first, then drop the unique one.

AdvancedVariants: IF NOT EXISTS, LIKE and AS SELECT

Three shortcuts behave differently in ways that bite:

SQL

CREATE TABLE IF NOT EXISTS reviews (id INT PRIMARY KEY);
CREATE TABLE reviews_copy LIKE reviews;
CREATE TABLE reviews_snapshot AS SELECT id, rating, created_at FROM reviews;
SELECT table_name, column_name, column_key, extra FROM information_schema.columns
WHERE table_schema = 'ch07' AND table_name IN ('reviews_copy', 'reviews_snapshot') AND column_name = 'id';
SELECT COUNT(*) AS rows_in_copy FROM reviews_copy;
SELECT COUNT(*) AS rows_in_snapshot FROM reviews_snapshot;
DROP TABLE reviews_copy, reviews_snapshot;
DROP TABLE reviews_nope;
DROP TABLE IF EXISTS reviews_nope;

Result

Note (Code 1050): Table 'reviews' already exists
+------------------+-------------+------------+----------------+
| TABLE_NAME       | COLUMN_NAME | COLUMN_KEY | EXTRA          |
+------------------+-------------+------------+----------------+
| reviews_copy     | id          | PRI        | auto_increment |
| reviews_snapshot | id          |            |                |
+------------------+-------------+------------+----------------+
+--------------+
| rows_in_copy |
+--------------+
|            0 |
+--------------+
+------------------+
| rows_in_snapshot |
+------------------+
|                2 |
+------------------+
ERROR 1051 (42S02): Unknown table 'ch07.reviews_nope'
Note (Code 1051): Unknown table 'ch07.reviews_nope'

What’s happening

CREATE TABLE IF NOT EXISTS with a different definition does nothing and only leaves a note. It never compares definitions — a migration that relies on it can leave an old table in place without complaint.
CREATE TABLE … LIKE copies the structure — columns, indexes, AUTO_INCREMENT, CHECK constraints — but no rows and no foreign keys.
CREATE TABLE … AS SELECT copies the rows but only plain column types: id lost its primary key and auto_increment. It's for snapshots and reports, not for cloning a table.
DROP TABLE on a missing table is an error; DROP TABLE IF EXISTS turns it into a note, which is why migration and test scripts use it.

AdvancedMaking a column NOT NULL on a live table

Adding NOT NULL to existing data has two very different outcomes:

SQL

ALTER TABLE reviews MODIFY comment TEXT NOT NULL;
ALTER TABLE reviews ADD COLUMN helpful_votes INT NOT NULL;
SELECT id, comment, helpful_votes FROM reviews;

Result

ERROR 1138 (22004): Invalid use of NULL value
+----+----------------+---------------+
| id | comment        | helpful_votes |
+----+----------------+---------------+
|  1 | Light and fast |             0 |
|  2 | NULL           |             0 |
+----+----------------+---------------+

What’s happening

Review 2 has a NULL comment, so changing comment to NOT NULL fails with ERROR 1138 — strict mode won't invent a value for existing data. Fix the data first (UPDATE reviews SET comment = '' WHERE comment IS NULL), then alter.
A new NOT NULL column without a DEFAULT is allowed: existing rows get the type's implicit default — 0 for numbers, '' for strings, the "zero" value for dates.
That implicit 0 may be a lie ("this review has 0 helpful votes" when really nobody counted). Give new columns an explicit DEFAULT that means what you intend.

AdvancedPractice: versioned migrations and DDL commits

#Numeric types: INT, DECIMAL and FLOAT

Added

MySQL has three families of numbers, and picking the wrong family is a bug, not a style choice:

Integers — TINYINT (1 byte), SMALLINT (2), MEDIUMINT (3), INT (4), BIGINT (8). Exact, fixed range. UNSIGNED shifts the range to start at zero: TINYINT is −128…127, TINYINT UNSIGNED is 0…255; INT is about ±2.1 billion, INT UNSIGNED up to about 4.29 billion; BIGINT about ±9.2 × 10¹⁸.
Fixed-point — DECIMAL(p, s): exactly p digits, s of them after the point. DECIMAL(10,2) holds −99,999,999.99…99,999,999.99 with no rounding surprises. It's stored as decimal digits, so 0.10 really is one tenth.
Floating-point — FLOAT (4 bytes) and DOUBLE (8 bytes): binary IEEE 754 approximations, huge range, but most decimal fractions (0.1, 0.2) can't be represented exactly.

The mental model: integers and DECIMAL are counting; FLOAT and DOUBLE are measuring. You count money, stock and votes — they must be exact. You measure temperatures, sensor readings and scientific values, where a tiny error in the 16th digit doesn't matter and range does.

SQL

CREATE TABLE money_test (
  id      INT PRIMARY KEY,
  as_dec  DECIMAL(10,2),
  as_flt  FLOAT,
  as_dbl  DOUBLE
);
INSERT INTO money_test VALUES (1, 0.10, 0.10, 0.10), (2, 0.20, 0.20, 0.20);
SELECT SUM(as_dec), SUM(as_flt), SUM(as_dbl) FROM money_test;
SELECT
  (SELECT COUNT(*) FROM money_test WHERE as_dec = 0.1) AS dec_matches,
  (SELECT COUNT(*) FROM money_test WHERE as_flt = 0.1) AS float_matches,
  (SELECT COUNT(*) FROM money_test WHERE as_dbl = 0.1) AS double_matches;
SELECT as_flt, CAST(as_flt AS DOUBLE) AS what_float_really_holds FROM money_test WHERE id = 1;

Result

+-------------+---------------------+---------------------+
| SUM(as_dec) | SUM(as_flt)         | SUM(as_dbl)         |
+-------------+---------------------+---------------------+
|        0.30 | 0.30000000447034836 | 0.30000000000000004 |
+-------------+---------------------+---------------------+
+-------------+---------------+----------------+
| dec_matches | float_matches | double_matches |
+-------------+---------------+----------------+
|           1 |             0 |              1 |
+-------------+---------------+----------------+
+--------+-------------------------+
| as_flt | what_float_really_holds |
+--------+-------------------------+
|    0.1 |     0.10000000149011612 |
+--------+-------------------------+

What’s happening

The same two values, 0.10 and 0.20, go into all three columns. DECIMAL(10,2) stores them as the exact digits 0.10 and 0.20.
SUM(as_dec) is exactly 0.30. SUM(as_dbl) is 0.30000000000000004 — the same error you get from 0.1 + 0.2 in JavaScript, because both are IEEE 754 doubles. SUM(as_flt) is worse, 0.30000000447034836, because FLOAT has only about 7 significant digits.
WHERE as_flt = 0.1 finds nothing. The column holds the nearest 32-bit float to 0.1, which is 0.10000000149011612, and the literal 0.1 is compared as a double — the two approximations differ.
The client displays the float as 0.1 because it rounds for display. That's what makes the bug sneaky: the value looks right in every query tool.
DOUBLE happens to match 0.1 here because both sides are the same 64-bit approximation — but sums and comparisons after arithmetic still drift. The lesson: money is DECIMAL (or an integer number of cents), never FLOAT/DOUBLE.

AdvancedOut-of-range values and DECIMAL rounding

In strict mode (the default since 5.7) MySQL refuses a value that doesn't fit. DECIMAL rounds extra decimal places instead:

SQL

CREATE TABLE int_test (
  tiny   TINYINT,
  utiny  TINYINT UNSIGNED,
  price  DECIMAL(5,2)
);
INSERT INTO int_test (tiny) VALUES (127);
INSERT INTO int_test (tiny) VALUES (128);
INSERT INTO int_test (utiny) VALUES (-1);
INSERT INTO int_test (price) VALUES (12.345);
INSERT INTO int_test (price) VALUES (999.995);
INSERT INTO int_test (price) VALUES (1234.5);
SELECT * FROM int_test;

Result

ERROR 1264 (22003): Out of range value for column 'tiny' at row 1
ERROR 1264 (22003): Out of range value for column 'utiny' at row 1
Note (Code 1265): Data truncated for column 'price' at row 1
ERROR 1264 (22003): Out of range value for column 'price' at row 1
ERROR 1264 (22003): Out of range value for column 'price' at row 1
+------+-------+-------+
| tiny | utiny | price |
+------+-------+-------+
|  127 |  NULL |  NULL |
| NULL |  NULL | 12.35 |
+------+-------+-------+

What’s happening

127 is the largest TINYINT, so it's stored. 128 fails with ERROR 1264 … Out of range; nothing is inserted.
-1 into TINYINT UNSIGNED fails the same way — unsigned columns can't hold negatives.
12.345 into DECIMAL(5,2) has one decimal too many. That's not an error: MySQL rounds half away from zero to 12.35 and leaves a Note 1265. Rounding silently is usually what you want for money, but be aware it happens.
999.995 looks like it fits five digits, but rounding makes it 1000.00, which needs six — so it's out of range. Rounding happens before the range check.
1234.5 needs four digits before the point and DECIMAL(5,2) allows three, so it fails.

AdvancedOld behaviour: non-strict mode clamps silently

On MySQL 5.6 and earlier — or any server where someone set sql_mode = '' — the same bad values are changed and stored, with only a warning:

SQL

SET SESSION sql_mode = '';
TRUNCATE int_test;
INSERT INTO int_test (tiny, utiny, price) VALUES (300, -1, 1234.5);
SELECT * FROM int_test;
SET SESSION sql_mode = DEFAULT;

Result

Warning (Code 1264): Out of range value for column 'tiny' at row 1
Warning (Code 1264): Out of range value for column 'utiny' at row 1
Warning (Code 1264): Out of range value for column 'price' at row 1
+------+-------+--------+
| tiny | utiny | price  |
+------+-------+--------+
|  127 |     0 | 999.99 |
+------+-------+--------+

What’s happening

Without STRICT_TRANS_TABLES, out-of-range values are clamped to the nearest end of the range: 300 → 127, -1 → 0, 1234.5 → 999.99.
The insert succeeds. Applications rarely read warnings, so the data is quietly wrong — a price of 1234.50 became 999.99.
This is why upgrade guides and interviewers ask about sql_mode. If you inherit an old server, check SELECT @@sql_mode before trusting the data. MySQL 9.7's default is ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION.

AdvancedInteger arithmetic: division and overflow

/ always returns a decimal; integer division is DIV. Overflow in expressions is an error, not a wrap-around:

SQL

SELECT 7 / 2 AS slash, 7 DIV 2 AS div_op, 7 % 2 AS modulo, 1 / 3 AS one_third, 1 / 0 AS by_zero;
SELECT 18446744073709551615 + 1;
SELECT CAST(9223372036854775807 AS SIGNED) + 1;

Result

+--------+--------+--------+-----------+---------+
| slash  | div_op | modulo | one_third | by_zero |
+--------+--------+--------+-----------+---------+
| 3.5000 |      3 |      1 |    0.3333 |    NULL |
+--------+--------+--------+-----------+---------+
Warning (Code 1365): Division by 0
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'
ERROR 1690 (22003): BIGINT value is out of range in '(cast(9223372036854775807 as signed) + 1)'

What’s happening

7 / 2 is 3.5000, not 3 — MySQL's / is never integer division. The four decimals come from div_precision_increment (default 4), which is also why 1 / 3 shows 0.3333.
7 DIV 2 is 3: integer division, truncating toward zero. PostgreSQL is the opposite — 7 / 2 is 3 there (see Two philosophies).
1 / 0 in a SELECT gives NULL plus a warning. In an INSERT/UPDATE under strict mode with ERROR_FOR_DIVISION_BY_ZERO, the same expression is an error.
Going past the largest BIGINT or BIGINT UNSIGNED raises ERROR 1690 instead of wrapping around like C integers do.

AdvancedDeprecated: display width and ZEROFILL

INT(11) in old schemas does not mean "11 digits" — it was only a display hint. MySQL 8.0.17 deprecated it:

SQL

CREATE TABLE width_test (a INT(11), b INT(5) ZEROFILL);
SHOW CREATE TABLE width_test;
INSERT INTO width_test VALUES (42, 42);
SELECT a, b FROM width_test;

Result

Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
Warning (Code 1681): The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
Warning (Code 1681): Integer display width is deprecated and will be removed in a future release.
*************************** 1. row ***************************
       Table: width_test
Create Table: CREATE TABLE `width_test` (
  `a` int DEFAULT NULL,
  `b` int(5) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+------+-------+
| a    | b     |
+------+-------+
|   42 | 00042 |
+------+-------+

What’s happening

INT(11) gets a deprecation warning and SHOW CREATE TABLE drops the (11) entirely — it's plain int, still 4 bytes, still ±2.1 billion.
The width only ever had an effect together with ZEROFILL, which pads the display to 5 digits: 00042. ZEROFILL also implies UNSIGNED.
Both are deprecated; format numbers in the application or with LPAD(b, 5, '0'). When you see INT(11) in a 5.7 dump, read it as INT.

AdvancedPractice: picking INT vs BIGINT

Predict the result

SQL

CREATE TABLE prices (p DECIMAL(4,1));
INSERT INTO prices VALUES (99.94), (99.95), (999.96);
SELECT * FROM prices;

Show answer

The insert fails and the table stays empty:

Result

ERROR 1264 (22003): Out of range value for column 'p' at row 3
Note (Code 1265): Data truncated for column 'p' at row 1
Note (Code 1265): Data truncated for column 'p' at row 2
Error (Code 1264): Out of range value for column 'p' at row 3

SELECT * FROM prices returns nothing (and SELECT COUNT(*) gives 0).

What’s happening

DECIMAL(4,1) allows four digits, one after the point: the largest value is 999.9.
Row 1, 99.94, rounds to 99.9 and row 2, 99.95, rounds half away from zero to 100.0. Both fit; each leaves a Note 1265 about the rounding.
Row 3, 999.96, rounds to 1000.0, which needs five digits, so it's out of range — and the error says at row 3.
A multi-row INSERT is one statement, and on InnoDB in strict mode a failed statement is rolled back as a unit: rows 1 and 2 aren't kept either. Insert the rows separately and the first two would be stored.

#Strings: CHAR, VARCHAR, TEXT and character sets

Added

A string column has two independent settings: how much it can hold (the type and length) and how characters are encoded and compared (the character set and collation).

CHAR(n) is fixed width: always n characters, padded with spaces; good for truly fixed codes (ISO country codes, currency codes).
VARCHAR(n) is variable width: stores the actual characters plus a 1–2 byte length. n counts characters, not bytes. This is the default choice.
TEXT (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT — up to 255 B, 64 KB, 16 MB, 4 GB) is for long text. It can be stored off-page, needs a prefix length to be indexed, and before 8.0.13 couldn't have a default.

The character set decides which characters are possible and how many bytes each takes; the collation decides how they compare and sort. Think of the character set as the alphabet and the collation as the dictionary's ordering rules — the same letters can be sorted "case doesn't matter, accents don't matter" or "exact bytes".

SQL

CREATE TABLE str_test (
  code  CHAR(5),
  name  VARCHAR(5)
);
INSERT INTO str_test VALUES ('ab  ', 'ab  ');
SELECT CONCAT('[', code, ']') AS char_value, CONCAT('[', name, ']') AS varchar_value,
       LENGTH(code) AS char_len, LENGTH(name) AS varchar_len FROM str_test;
INSERT INTO str_test VALUES ('abcdef', NULL);
INSERT INTO str_test VALUES (NULL, 'héllo');
INSERT INTO str_test VALUES (NULL, '😀😀😀😀😀');
SELECT name, CHAR_LENGTH(name) AS chars, LENGTH(name) AS bytes FROM str_test WHERE name IS NOT NULL;

Result

+------------+---------------+----------+-------------+
| char_value | varchar_value | char_len | varchar_len |
+------------+---------------+----------+-------------+
| [ab]       | [ab  ]        |        2 |           4 |
+------------+---------------+----------+-------------+
ERROR 1406 (22001): Data too long for column 'code' at row 1
+----------------------+-------+-------+
| name                 | chars | bytes |
+----------------------+-------+-------+
| ab                   |     4 |     4 |
| héllo                |     5 |     6 |
| 😀😀😀😀😀                     |     5 |    20 |
+----------------------+-------+-------+

What’s happening

'ab ' (two trailing spaces) goes into both columns. CHAR(5) pads it to five characters on disk, then strips all trailing spaces when you read it back — so you get [ab] and length 2. The trailing spaces you inserted are gone.
VARCHAR(5) stores exactly what you gave it, trailing spaces included: [ab ], length 4.
'abcdef' is six characters for a five-character column, so strict mode rejects it: ERROR 1406 … Data too long. Non-strict mode would have truncated it to 'abcde' with a warning.
'héllo' is 5 characters but 6 bytes (é is two bytes in UTF-8), and five emoji are 5 characters but 20 bytes (four bytes each). Both fit VARCHAR(5) because the limit is in characters. LENGTH() counts bytes and CHAR_LENGTH() counts characters — use the latter for "how long is this text?".
The ragged borders in the last table are real client output: the mysql client pads columns by display width and miscounts emoji. The stored data is fine.

Advancedutf8 is not UTF-8: utf8mb3 vs utf8mb4

In MySQL, utf8 historically meant utf8mb3 — at most three bytes per character, which can't store emoji or many CJK characters. Real UTF-8 is utf8mb4. MySQL 8.0 made utf8mb4 the default; MySQL 5.7's default was latin1, and lots of 5.7-era schemas say utf8:

SQL

CREATE TABLE legacy_utf8 (msg VARCHAR(20)) CHARACTER SET utf8;
SHOW CREATE TABLE legacy_utf8;
INSERT INTO legacy_utf8 VALUES ('héllo');
INSERT INTO legacy_utf8 VALUES ('thumbs 👍');
ALTER TABLE legacy_utf8 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
INSERT INTO legacy_utf8 VALUES ('thumbs 👍');
SELECT msg FROM legacy_utf8;

Result

Warning (Code 3719): 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
*************************** 1. row ***************************
       Table: legacy_utf8
Create Table: CREATE TABLE `legacy_utf8` (
  `msg` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x8D' for column 'msg' at row 1
+-------------+
| msg         |
+-------------+
| héllo       |
| thumbs 👍     |
+-------------+

What’s happening

MySQL 9.7 still treats utf8 as utf8mb3, warns that this will change, and SHOW CREATE TABLE spells it out as utf8mb3.
'héllo' is fine — é needs only two bytes.
👍 is the four-byte sequence F0 9F 91 8D, which utf8mb3 can't store: ERROR 1366 … Incorrect string value. In non-strict mode the emoji would have become ? — silent data loss.
CONVERT TO CHARACTER SET utf8mb4 rewrites the table (a full rebuild) and every text column in it; after that the emoji goes in.
Rule: always write utf8mb4 explicitly, in tables and in the client connection (drivers have a charset option; mysql2 3.24 defaults to UTF8MB4_UNICODE_CI).

AdvancedCollations: case, accents and unique keys

The default collation, utf8mb4_0900_ai_ci, is accent-insensitive (ai) and case-insensitive (ci). That affects equality, ORDER BY, GROUP BY and — the surprising part — UNIQUE indexes:

SQL

SELECT 'cafe' = 'Café' AS ai_ci, 'cafe' = 'Café' COLLATE utf8mb4_0900_as_cs AS as_cs, 'cafe' = 'Café' COLLATE utf8mb4_0900_bin AS bin;
SELECT 'Rohit' = 'rohit' AS ci, 'Rohit' = 'rohit' COLLATE utf8mb4_0900_as_cs AS cs;
INSERT INTO customers (name, email, city, created_at) VALUES ('Rohit 2', 'ROHIT@example.com', 'Toronto', NOW());
SELECT id, name FROM customers WHERE email = 'Rohit@Example.COM';

Result

+-------+-------+------+
| ai_ci | as_cs | bin  |
+-------+-------+------+
|     1 |     0 |    0 |
+-------+-------+------+
+----+------+
| ci | cs   |
+----+------+
|  1 |    0 |
+----+------+
ERROR 1062 (23000): Duplicate entry 'ROHIT@example.com' for key 'customers.email'
+----+-------+
| id | name  |
+----+-------+
|  1 | Rohit |
+----+-------+

What’s happening

Under utf8mb4_0900_ai_ci, 'cafe' = 'Café' is true (1): case and accents are ignored.
COLLATE utf8mb4_0900_as_cs (accent- and case-sensitive) and utf8mb4_0900_bin (compare code points) both say they differ.
Inserting ROHIT@example.com fails with ERROR 1062 because the unique index on email uses the column's case-insensitive collation, so it sees rohit@example.com as the same value. For emails that's exactly what you want.
Lookups are case-insensitive too: 'Rohit@Example.COM' finds customer 1. PostgreSQL compares case-sensitively by default, which is one of the first things that breaks when porting (see SQL differences you'll hit first).
If a column must be case-sensitive (API tokens, file paths, base64 ids), give it a _bin or _as_cs collation in its definition: token VARCHAR(64) COLLATE utf8mb4_0900_bin.

AdvancedTrailing spaces: PAD SPACE vs NO PAD

Older collations ignore trailing spaces when comparing; the 8.0 _0900_ collations don't:

SQL

SELECT 'a' = 'a   ' AS pad_space_varchar;
SELECT collation_name, pad_attribute FROM information_schema.collations
WHERE collation_name IN ('utf8mb4_0900_ai_ci', 'utf8mb4_general_ci', 'utf8mb4_bin', 'utf8mb4_0900_bin');

Result

+-------------------+
| pad_space_varchar |
+-------------------+
|                 0 |
+-------------------+
+--------------------+---------------+
| collation_name     | pad_attribute |
+--------------------+---------------+
| utf8mb4_0900_ai_ci | NO PAD        |
| utf8mb4_0900_bin   | NO PAD        |
| utf8mb4_bin        | PAD SPACE     |
| utf8mb4_general_ci | PAD SPACE     |
+--------------------+---------------+

What’s happening

With the default NO PAD collation, 'a' and 'a ' are different strings (0).
utf8mb4_general_ci and utf8mb4_bin — common in schemas created on 5.7 — are PAD SPACE: they treat trailing spaces as insignificant, so 'a' = 'a ' would be true there.
After a 5.7 → 8.0 upgrade, a column that changes collation can suddenly reject or accept "duplicates" that differ only in trailing spaces. Check collations as part of any upgrade.

AdvancedLimits: TEXT indexes and the 65,535-byte row

Two limits come straight from the storage format:

SQL

CREATE TABLE text_test (id INT PRIMARY KEY, body TEXT, INDEX (body));
CREATE TABLE text_test (id INT PRIMARY KEY, body TEXT, INDEX (body(100)));
CREATE TABLE wide_test (a VARCHAR(16384), b VARCHAR(1000));
CREATE TABLE wide_test (a VARCHAR(10000), b VARCHAR(10000));
CREATE TABLE wide_test (a VARCHAR(10000), b TEXT);

Result

ERROR 1170 (42000): BLOB/TEXT column 'body' used in key specification without a key length
ERROR 1074 (42000): Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

What’s happening

A TEXT column can't be indexed whole: ERROR 1170. A prefix index on the first 100 characters (body(100)) works — the second statement succeeds. For searching words inside text, use a full-text index instead (see Full-text search).
A single VARCHAR in utf8mb4 can't exceed 16,383 characters, because 16,384 × 4 bytes would pass the 65,535-byte limit: ERROR 1074.
The 65,535-byte limit applies to the sum of all non-TEXT columns, counted at their maximum size: two VARCHAR(10000) columns are up to 80,000 bytes, hence ERROR 1118.
TEXT and BLOB only count a few bytes toward that limit (their data lives elsewhere), so swapping one column to TEXT makes the last statement succeed.

AdvancedPractice: declaring meaningful lengths

#Dates and times: DATETIME vs TIMESTAMP

Added

MySQL has DATE (a calendar day), TIME (a time of day or a duration, up to ±838 hours), YEAR, and two types for a point in time: DATETIME and TIMESTAMP. The interview question is always the difference between the last two, and it comes down to what they store:

DATETIME stores the wall-clock reading you gave it — "2025-06-01 12:00:00" — with no time zone attached. It never changes on the way in or out. Range: year 1000 to 9999. 5 bytes (plus up to 3 for fractional seconds).
TIMESTAMP stores an instant — internally seconds since 1970-01-01 UTC. On the way in, MySQL converts from the session's time_zone to UTC; on the way out, from UTC back to the session's time zone. Range: 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC. 4 bytes (plus fractional seconds).

The analogy: a DATETIME is a photo of a wall clock — it says "12:00" but not which city's clock. A TIMESTAMP is an entry in a world-clock app — one moment, displayed in whatever city you're looking from.

SQL

CREATE TABLE tz_test (
  id      INT PRIMARY KEY,
  as_dt   DATETIME,
  as_ts   TIMESTAMP
);
SET time_zone = '+00:00';
INSERT INTO tz_test VALUES (1, '2025-06-01 12:00:00', '2025-06-01 12:00:00');
SELECT @@session.time_zone AS tz, as_dt, as_ts FROM tz_test;
SET time_zone = '+05:30';
SELECT @@session.time_zone AS tz, as_dt, as_ts FROM tz_test;
SET time_zone = '-04:00';
SELECT @@session.time_zone AS tz, as_dt, as_ts, UNIX_TIMESTAMP(as_ts) AS epoch FROM tz_test;

Result

+--------+---------------------+---------------------+
| tz     | as_dt               | as_ts               |
+--------+---------------------+---------------------+
| +00:00 | 2025-06-01 12:00:00 | 2025-06-01 12:00:00 |
+--------+---------------------+---------------------+
+--------+---------------------+---------------------+
| tz     | as_dt               | as_ts               |
+--------+---------------------+---------------------+
| +05:30 | 2025-06-01 12:00:00 | 2025-06-01 17:30:00 |
+--------+---------------------+---------------------+
+--------+---------------------+---------------------+------------+
| tz     | as_dt               | as_ts               | epoch      |
+--------+---------------------+---------------------+------------+
| -04:00 | 2025-06-01 12:00:00 | 2025-06-01 08:00:00 | 1748779200 |
+--------+---------------------+---------------------+------------+

What’s happening

With the session in UTC, the same text '2025-06-01 12:00:00' goes into both columns. DATETIME stores the text's digits; TIMESTAMP converts from the session zone (UTC) to UTC — no change — and stores the instant 1748779200 seconds since the epoch.
Switch the session to India (+05:30). as_dt comes back unchanged: it's just digits. as_ts is converted from UTC to the session zone and shows 17:30 — the same moment, seen from Mumbai.
Switch to -04:00 (Toronto in summer). as_ts shows 08:00; UNIX_TIMESTAMP(as_ts) is still 1748779200, proving the stored instant never changed.
So TIMESTAMP answers "when did this happen?" correctly for every viewer, while DATETIME is only correct if everyone agrees which zone the digits are in.
In practice most teams use DATETIME and always write UTC from the application (set the connection's time zone to UTC, or convert in code). That avoids the 2038 limit and makes the column independent of session settings. TIMESTAMP is fine for "row last changed" audit columns that will never reach 2038 — but see the gotchas below.

AdvancedEdge cases: 2038, zero dates and impossible dates

Four values that break a rule of the date types, and one literal with an offset:

SQL

SET time_zone = '+00:00';
INSERT INTO tz_test VALUES (2, '2038-01-19 03:14:07', '2038-01-19 03:14:07');
INSERT INTO tz_test VALUES (3, '2038-01-19 03:14:08', '2038-01-19 03:14:08');
INSERT INTO tz_test VALUES (4, '9999-12-31 23:59:59', NULL);
INSERT INTO tz_test VALUES (5, '0000-00-00 00:00:00', NULL);
INSERT INTO tz_test VALUES (6, '2025-02-30 10:00:00', NULL);
INSERT INTO tz_test VALUES (7, '2025-06-01 12:00:00+05:30', '2025-06-01 12:00:00+05:30');
SELECT * FROM tz_test ORDER BY id;

Result

ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'as_ts' at row 1
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'as_dt' at row 1
ERROR 1292 (22007): Incorrect datetime value: '2025-02-30 10:00:00' for column 'as_dt' at row 1
+----+---------------------+---------------------+
| id | as_dt               | as_ts               |
+----+---------------------+---------------------+
|  1 | 2025-06-01 12:00:00 | 2025-06-01 12:00:00 |
|  2 | 2038-01-19 03:14:07 | 2038-01-19 03:14:07 |
|  4 | 9999-12-31 23:59:59 | NULL                |
|  7 | 2025-06-01 06:30:00 | 2025-06-01 06:30:00 |
+----+---------------------+---------------------+

What’s happening

2038-01-19 03:14:07 UTC is the last second a signed 32-bit epoch can hold, so it's the last valid TIMESTAMP. One second later fails with ERROR 1292. A subscription that ends in 2040 can't be a TIMESTAMP.
DATETIME happily stores 9999-12-31 23:59:59.
'0000-00-00 00:00:00' — the "zero date" that 5.x schemas used as "no date" — is rejected by NO_ZERO_DATE in strict mode. Use NULL for "unknown". Dumps from old servers that contain zero dates fail to load until you fix them or relax sql_mode for the import.
February 30th doesn't exist, so it's rejected too (non-strict mode would store a zero date with a warning).
Row 7 uses a literal with an offset (supported since MySQL 8.0.19). MySQL converts 12:00+05:30 to the session zone (UTC) before storing, so both columns get 06:30 — the DATETIME loses the offset, it only keeps the converted digits.

AdvancedPitfall: TIMESTAMP and daylight saving time

With a named zone that has daylight saving time, some local times don't exist and some happen twice:

SQL

SET time_zone = 'America/Toronto';
INSERT INTO tz_test VALUES (8, '2025-03-09 02:30:00', '2025-03-09 02:30:00');
INSERT INTO tz_test VALUES (9, '2025-11-02 01:30:00', '2025-11-02 01:30:00');
SELECT id, as_dt, as_ts, UNIX_TIMESTAMP(as_ts) AS epoch FROM tz_test WHERE id IN (8, 9);
SET time_zone = '+00:00';
SELECT id, as_dt, as_ts FROM tz_test WHERE id IN (8, 9);

Result

ERROR 1292 (22007): Incorrect datetime value: '2025-03-09 02:30:00' for column 'as_ts' at row 1
+----+---------------------+---------------------+------------+
| id | as_dt               | as_ts               | epoch      |
+----+---------------------+---------------------+------------+
|  9 | 2025-11-02 01:30:00 | 2025-11-02 01:30:00 | 1762061400 |
+----+---------------------+---------------------+------------+
+----+---------------------+---------------------+
| id | as_dt               | as_ts               |
+----+---------------------+---------------------+
|  9 | 2025-11-02 01:30:00 | 2025-11-02 05:30:00 |
+----+---------------------+---------------------+

What’s happening

On 9 March 2025 Toronto clocks jumped from 02:00 to 03:00, so 02:30 never happened there. Converting it to UTC is impossible, and the TIMESTAMP insert fails with ERROR 1292 (the DATETIME column would have taken it — it doesn't care).
On 2 November 2025 clocks went back from 02:00 to 01:00, so 01:30 happened twice (once in EDT, once in EST). MySQL picked one: viewed from UTC it's 05:30, i.e. the first one, EDT (UTC−4).
Named zones like America/Toronto only work if the server's time zone tables are loaded (mysql_tzinfo_to_sql); the Docker image used here has them. Without them, SET time_zone = 'America/Toronto' fails and CONVERT_TZ returns NULL.
Storing UTC and converting only for display avoids both problems — UTC has no daylight saving time.

AdvancedFractional seconds and auto-updating columns

DATETIME(6) keeps microseconds. DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP work on both DATETIME and TIMESTAMP (on DATETIME since 5.6.5):

SQL

CREATE TABLE audit_test (
  id          INT PRIMARY KEY,
  note        VARCHAR(20),
  created_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at  DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);
INSERT INTO audit_test (id, note) VALUES (1, 'first');
SELECT * FROM audit_test;
DO SLEEP(1.5);
UPDATE audit_test SET note = 'edited' WHERE id = 1;
SELECT * FROM audit_test;
UPDATE audit_test SET note = 'edited' WHERE id = 1;
SELECT ROW_COUNT() AS changed;
SELECT * FROM audit_test;

Result

+----+-------+----------------------------+----------------------------+
| id | note  | created_at                 | updated_at                 |
+----+-------+----------------------------+----------------------------+
|  1 | first | 2026-10-16 15:25:26.061339 | 2026-10-16 15:25:26.061339 |
+----+-------+----------------------------+----------------------------+
+----+--------+----------------------------+----------------------------+
| id | note   | created_at                 | updated_at                 |
+----+--------+----------------------------+----------------------------+
|  1 | edited | 2026-10-16 15:25:26.061339 | 2026-10-16 15:25:27.567847 |
+----+--------+----------------------------+----------------------------+
+---------+
| changed |
+---------+
|       0 |
+---------+
+----+--------+----------------------------+----------------------------+
| id | note   | created_at                 | updated_at                 |
+----+--------+----------------------------+----------------------------+
|  1 | edited | 2026-10-16 15:25:26.061339 | 2026-10-16 15:25:27.567847 |
+----+--------+----------------------------+----------------------------+

What’s happening

The insert fills both columns from CURRENT_TIMESTAMP(6), with microseconds. The precision in the default must match the column's: DATETIME(6) with CURRENT_TIMESTAMP (no 6) is an error.
The first UPDATE changes note, so ON UPDATE sets updated_at to the new time, about 1.5 s later. created_at stays.
The second UPDATE sets note to the value it already has. ROW_COUNT() is 0 — no row actually changed — and updated_at is not touched. "Updated at" really means "last changed at".
Without (6), DATETIME rounds to whole seconds, and two events in the same second sort as ties.

AdvancedOld behaviour: TIMESTAMP magic in 5.7

On MySQL 5.7, explicit_defaults_for_timestamp was OFF by default, and the first TIMESTAMP column in a table silently became NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP even if you didn't ask. Tables that "mysteriously" changed a date column on every update usually date from then. MySQL 8.0.2+ defaults it to ON (9.7 shows explicit_defaults_for_timestamp = ON), so you get exactly what you declare. When you migrate an old schema, read its SHOW CREATE TABLE rather than the original DDL.

#JSON columns

Added

A JSON column (MySQL 5.7.8+) stores a JSON document per row. MySQL validates it on insert and stores it in a binary format, so reading one field doesn't mean re-parsing the whole text. You get it for data whose shape varies per row: product attributes (a laptop has RAM and ports, a phone has colours, a book has pages), API payloads, feature flags, settings.

The mental model is a drawer inside each row. The table's columns are the labelled shelves everybody shares; the JSON drawer holds whatever extra this row needs. It's convenient, but the database can't enforce much about what's in the drawer, and it can't index the inside of it unless you tell it exactly which field to index.

SQL

CREATE TABLE product_details (
  product_id INT PRIMARY KEY,
  attrs      JSON NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO product_details VALUES
  (1, '{"brand": "Lenovo", "ram_gb": 16, "ports": ["usb-c", "hdmi"], "screen": {"inches": 14, "touch": false}}'),
  (2, '{"brand": "Apple",  "ram_gb": 16, "ports": ["usb-c"],         "screen": {"inches": 13.6, "touch": false}}'),
  (3, '{"brand": "Google", "ram_gb": 12, "ports": ["usb-c"],         "colours": ["obsidian", "porcelain"]}'),
  (8, '{"brand": "Sony",   "ports": ["usb-c", "3.5mm"], "anc": true}');
SELECT p.name,
       d.attrs->'$.brand'          AS brand_json,
       d.attrs->>'$.brand'         AS brand_text,
       d.attrs->>'$.screen.inches' AS inches,
       JSON_LENGTH(d.attrs, '$.ports') AS port_count
FROM products p JOIN product_details d ON d.product_id = p.id;
SELECT p.name FROM products p JOIN product_details d ON d.product_id = p.id
WHERE 'hdmi' MEMBER OF (d.attrs->'$.ports') OR d.attrs->>'$.anc' = 'true';

Result

+-----------------------------+------------+------------+--------+------------+
| name                        | brand_json | brand_text | inches | port_count |
+-----------------------------+------------+------------+--------+------------+
| ThinkBook 14                | "Lenovo"   | Lenovo     | 14     |          2 |
| MacBook Air                 | "Apple"    | Apple      | 13.6   |          1 |
| Pixel 10                    | "Google"   | Google     | NULL   |          1 |
| Noise-cancelling Headphones | "Sony"     | Sony       | NULL   |          2 |
+-----------------------------+------------+------------+--------+------------+
+-----------------------------+
| name                        |
+-----------------------------+
| ThinkBook 14                |
| Noise-cancelling Headphones |
+-----------------------------+

What’s happening

Each product gets a different shape: laptops have screen, the phone has colours, the headphones have anc. A fixed column for every possible attribute would be mostly NULLs.
attrs->'$.brand' is shorthand for JSON_EXTRACT(attrs, '$.brand') and returns a JSON value — note the quotes in "Lenovo". $ is the document root and .brand a key.
attrs->>'$.brand' (5.7.13+) also unquotes: JSON_UNQUOTE(JSON_EXTRACT(…)), giving plain text Lenovo. Use ->> for display and comparisons with strings, -> when you want to keep JSON types.
Paths go deeper with dots ($.screen.inches); a missing path gives SQL NULL, so the phone and headphones show NULL inches. JSON_LENGTH(attrs, '$.ports') counts array elements.
'hdmi' MEMBER OF (array) (8.0.17+) tests array membership. ->>'$.anc' = 'true' works because unquoting the JSON true gives the text 'true'. Those two conditions find the ThinkBook and the headphones.

AdvancedValidation and how MySQL normalises documents

Two inserts: a broken document, then a valid but messy one.

SQL

INSERT INTO product_details VALUES (4, '{"brand": "Apple", "ram_gb": 8,}');
INSERT INTO product_details VALUES (4, '{"z": 1, "a": 2, "a": 3,   "mm": [1, 2]}');
SELECT attrs FROM product_details WHERE product_id = 4;

Result

ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 31 in value for column 'product_details.attrs'.
+--------------------------------+
| attrs                          |
+--------------------------------+
| {"a": 3, "z": 1, "mm": [1, 2]} |
+--------------------------------+

What’s happening

A trailing comma isn't valid JSON, so the insert fails with ERROR 3140 and the exact position. A TEXT column would have stored the broken string.
The second document is valid but messy: duplicate key a, odd spacing, keys in a random order.
MySQL keeps the last duplicate ("a": 3), drops the extra whitespace and reorders keys (shorter keys first, then alphabetically: a, z, mm). You get back an equivalent document, not your original text.
That's the same idea as PostgreSQL's jsonb; PostgreSQL's plain json type keeps the text exactly (see Data types: JSON vs JSONB, arrays and more). If you need the exact original bytes (signed webhooks), store them in a TEXT/BLOB column too.

AdvancedUpdating inside a document and JSON_TABLE

You don't have to rewrite a whole document to change one field, and JSON_TABLE (8.0.4+) turns arrays into rows you can join and group:

SQL

UPDATE product_details SET attrs = JSON_SET(attrs, '$.ram_gb', 32, '$.screen.touch', true) WHERE product_id = 1;
UPDATE product_details SET attrs = JSON_ARRAY_APPEND(attrs, '$.ports', 'thunderbolt') WHERE product_id = 2;
UPDATE product_details SET attrs = JSON_REMOVE(attrs, '$.anc') WHERE product_id = 8;
SELECT product_id, attrs FROM product_details WHERE product_id IN (1, 2, 8);
SELECT d.product_id, jt.port
FROM product_details d,
     JSON_TABLE(d.attrs, '$.ports[*]' COLUMNS (port VARCHAR(20) PATH '$')) AS jt
ORDER BY d.product_id, jt.port;

Result

+------------+-----------------------------------------------------------------------------------------------------------------+
| product_id | attrs                                                                                                           |
+------------+-----------------------------------------------------------------------------------------------------------------+
|          1 | {"brand": "Lenovo", "ports": ["usb-c", "hdmi"], "ram_gb": 32, "screen": {"touch": true, "inches": 14}}          |
|          2 | {"brand": "Apple", "ports": ["usb-c", "thunderbolt"], "ram_gb": 16, "screen": {"touch": false, "inches": 13.6}} |
|          8 | {"brand": "Sony", "ports": ["usb-c", "3.5mm"]}                                                                  |
+------------+-----------------------------------------------------------------------------------------------------------------+
+------------+-------------+
| product_id | port        |
+------------+-------------+
|          1 | hdmi        |
|          1 | usb-c       |
|          2 | thunderbolt |
|          2 | usb-c       |
|          3 | usb-c       |
|          8 | 3.5mm       |
|          8 | usb-c       |
+------------+-------------+

What’s happening

JSON_SET takes path/value pairs: ram_gb becomes 32 and the nested screen.touch becomes true. It replaces existing keys and adds missing ones (JSON_INSERT only adds, JSON_REPLACE only replaces).
JSON_ARRAY_APPEND pushes "thunderbolt" onto the MacBook's ports; JSON_REMOVE deletes the headphones' anc key.
Since 8.0, InnoDB can apply JSON_SET/JSON_REPLACE/JSON_REMOVE as partial in-place updates when the new value fits, and with binlog_row_value_options=PARTIAL_JSON the binary log records only the change — useful for big documents.
JSON_TABLE(attrs, '$.ports[*]' COLUMNS (…)) produces one row per array element, joined laterally to its product. Now "how many products have each port?" is an ordinary GROUP BY port.

AdvancedIndexing JSON: functional and multi-valued indexes

MySQL can't index a whole JSON column. You index an expression extracted from it — a functional index (8.0.13+) or a generated column — and for arrays a multi-valued index (8.0.17+):

SQL

ALTER TABLE product_details ADD INDEX idx_brand ((CAST(attrs->>'$.brand' AS CHAR(30)) COLLATE utf8mb4_bin));
ALTER TABLE product_details ADD INDEX idx_ports ((CAST(attrs->'$.ports' AS CHAR(20) ARRAY)));
EXPLAIN SELECT product_id FROM product_details WHERE attrs->>'$.brand' = 'Apple';
EXPLAIN SELECT product_id FROM product_details WHERE 'hdmi' MEMBER OF (attrs->'$.ports');
EXPLAIN SELECT product_id FROM product_details WHERE attrs->>'$.screen.inches' > 13;

Result

*************************** 1. row ***************************
EXPLAIN: -> Index lookup on product_details using idx_brand ((cast(json_unquote(json_extract(attrs,_utf8mb4'$.brand')) as char(30) charset utf8mb4) collate utf8mb4_bin) = 'Apple')  (cost=0.35 rows=1)

*************************** 1. row ***************************
EXPLAIN: -> Filter: json'"hdmi"' member of (cast(json_extract(attrs,_utf8mb4'$.ports') as char(20) array))  (cost=0.35 rows=1)
    -> Index lookup on product_details using idx_ports (cast(json_extract(attrs,_utf8mb4'$.ports') as char(20) array) = json'"hdmi"')  (cost=0.35 rows=1)

*************************** 1. row ***************************
EXPLAIN: -> Filter: (json_unquote(json_extract(product_details.attrs,'$.screen.inches')) > 13)  (cost=0.65 rows=4)
    -> Table scan on product_details  (cost=0.65 rows=4)

What’s happening

idx_brand indexes the expression CAST(attrs->>'$.brand' AS CHAR(30)) COLLATE utf8mb4_bin. The CAST is required because an index needs a fixed type; the COLLATE utf8mb4_bin matters because ->> returns utf8mb4_bin text, and the optimizer only uses a functional index when the query's expression matches the indexed one exactly, collation included.
With that, the plain query attrs->>'$.brand' = 'Apple' becomes an Index lookup on idx_brand, estimated at one row.
CAST(… AS CHAR(20) ARRAY) builds a multi-valued index: one index entry per array element, so MEMBER OF, JSON_CONTAINS and JSON_OVERLAPS can use it. The plan shows the index lookup with a filter on top.
$.screen.inches isn't indexed, so that query is a Table scan with a filter. Every JSON field you want to search fast needs its own index — the drawer only has labels where you add them.
MySQL 9.7 prints EXPLAIN in TREE format by default (@@explain_format). See Reading EXPLAIN for the details.

AdvancedEnforcing a shape with JSON_SCHEMA_VALID

If the drawer should follow a schema, a CHECK constraint with JSON_SCHEMA_VALID (8.0.17+) enforces it:

SQL

ALTER TABLE product_details ADD CONSTRAINT chk_attrs_shape CHECK (JSON_SCHEMA_VALID('{
  "type": "object",
  "required": ["brand"],
  "properties": {"brand": {"type": "string"}, "ram_gb": {"type": "integer", "minimum": 1}}
}', attrs));
INSERT INTO product_details VALUES (5, '{"ram_gb": 8}');
INSERT INTO product_details VALUES (5, '{"brand": "O''Reilly", "ram_gb": 0}');
INSERT INTO product_details VALUES (5, '{"brand": "O''Reilly", "pages": 352}');
SELECT product_id, attrs FROM product_details WHERE product_id = 5;

Result

ERROR 3819 (HY000): Check constraint 'chk_attrs_shape' is violated.
Error (Code 3934): The JSON document location '#' failed requirement 'required' at JSON Schema location '#'.
Error (Code 3819): Check constraint 'chk_attrs_shape' is violated.
ERROR 3819 (HY000): Check constraint 'chk_attrs_shape' is violated.
Error (Code 3934): The JSON document location '#/ram_gb' failed requirement 'minimum' at JSON Schema location '#/properties/ram_gb'.
Error (Code 3819): Check constraint 'chk_attrs_shape' is violated.
+------------+-------------------------------------+
| product_id | attrs                               |
+------------+-------------------------------------+
|          5 | {"brand": "O'Reilly", "pages": 352} |
+------------+-------------------------------------+

What’s happening

Adding the constraint checks the existing rows first; all four have a string brand, so it succeeds.
{"ram_gb": 8} has no brand: rejected. SHOW WARNINGS (shown here because the client ran with --show-warnings) gives the reason, failed requirement 'required'.
ram_gb: 0 breaks minimum: 1, and the detail points at #/ram_gb.
The book has a brand and an extra pages key; the schema doesn't forbid extra keys (additionalProperties isn't set), so it's accepted.

AdvancedPitfall: JSON instead of real columns

#ENUM, SET and BOOLEAN

Added

These three types look like conveniences and each has a twist:

ENUM('a','b','c') — one value from a fixed list. Stored as a 1–2 byte index (1 for the first value, 2 for the second…), shown as the string. Sorting and numeric context use the index.
SET('a','b','c') — zero or more values from a list of up to 64, stored as a bitmask.
BOOLEAN — not a real type in MySQL. It's an alias for TINYINT(1), and TRUE/FALSE are just 1/0.

Think of ENUM as a numbered dropdown: the database stores "option 3" and looks up the label when you read it. That's why reordering the options changes the meaning of stored data, and why sorting follows the dropdown order, not the alphabet.

SQL

SELECT id, status, status + 0 AS enum_index FROM orders ORDER BY status, id;
INSERT INTO orders (customer_id, status, ordered_at) VALUES (1, 'refunded', NOW());

Result

+----+-----------+------------+
| id | status    | enum_index |
+----+-----------+------------+
|  6 | pending   |          1 |
|  2 | paid      |          2 |
|  7 | paid      |          2 |
|  1 | shipped   |          3 |
|  3 | shipped   |          3 |
|  5 | shipped   |          3 |
|  8 | shipped   |          3 |
|  4 | cancelled |          4 |
+----+-----------+------------+
ERROR 1265 (01000): Data truncated for column 'status' at row 1

What’s happening

orders.status is ENUM('pending','paid','shipped','cancelled'). status + 0 forces numeric context and reveals the stored index: pending = 1 … cancelled = 4.
ORDER BY status sorts by that index, so pending, paid, shipped, cancelled — the declaration order, not alphabetical (alphabetically cancelled would be first). That's often what you want for a workflow, but it surprises people.
'refunded' isn't in the list. Strict mode rejects it with the oddly named ERROR 1265 … Data truncated for column 'status'. In non-strict mode it would be stored as the special empty-string value with index 0 — a silent bad value.
Adding refunded needs an ALTER TABLE. That's the real ENUM trade-off: a cheap, self-documenting constraint, but the list of values lives in the schema, so changing it is a deployment.

AdvancedPitfall: comparing an ENUM with a string

ORDER BY uses the index, but comparing with a string literal compares strings:

SQL

SELECT GROUP_CONCAT(id ORDER BY id) AS string_compare FROM orders WHERE status >= 'paid';
SELECT GROUP_CONCAT(id ORDER BY id) AS index_compare  FROM orders WHERE status + 0 >= 2;
SELECT GROUP_CONCAT(DISTINCT status ORDER BY status) AS sorted_by_index,
       GROUP_CONCAT(DISTINCT CAST(status AS CHAR) ORDER BY CAST(status AS CHAR)) AS sorted_alphabetically
FROM orders;

Result

+----------------+
| string_compare |
+----------------+
| 1,2,3,5,6,7,8  |
+----------------+
+---------------+
| index_compare |
+---------------+
| 1,2,3,4,5,7,8 |
+---------------+
+--------------------------------+--------------------------------+
| sorted_by_index                | sorted_alphabetically          |
+--------------------------------+--------------------------------+
| pending,paid,shipped,cancelled | cancelled,paid,pending,shipped |
+--------------------------------+--------------------------------+

What’s happening

status >= 'paid' compares text: 'pending', 'shipped' and 'paid' are >= 'paid' alphabetically, 'cancelled' isn't. So you get every order except 4 (cancelled) — including 6, which is still pending.
status + 0 >= 2 compares indexes: paid (2), shipped (3), cancelled (4). Now 4 is in and 6 (pending, index 1) is out.
Both happen to return seven rows, but different ones. If you meant "paid or later in the workflow", neither is right (cancelled isn't "later than paid"). Write the list you mean: status IN ('paid', 'shipped').
The last query shows the two orderings side by side.

AdvancedChanging an ENUM: append is instant, insert rebuilds

On a copy of orders, add a value at the end and then one in the middle:

SQL

CREATE TABLE orders_copy LIKE orders;
INSERT INTO orders_copy SELECT * FROM orders;
ALTER TABLE orders_copy MODIFY status ENUM('pending','paid','shipped','cancelled','refunded') NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE orders_copy MODIFY status ENUM('pending','paid','packed','shipped','cancelled','refunded') NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE orders_copy MODIFY status ENUM('pending','paid','packed','shipped','cancelled','refunded') NOT NULL;
SELECT id, status, status + 0 AS idx FROM orders_copy WHERE id IN (1, 4);

Result

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
+----+-----------+-----+
| id | status    | idx |
+----+-----------+-----+
|  1 | shipped   |   4 |
|  4 | cancelled |   5 |
+----+-----------+-----+

What’s happening

Appending 'refunded' at the end succeeds with ALGORITHM=INSTANT: existing rows' indexes (1–4) still mean the same thing, so only metadata changes.
Inserting 'packed' in the middle shifts every later value's index, so every stored row would need rewriting. ALGORITHM=INSTANT refuses with ERROR 1846 and the reason.
Without the ALGORITHM clause MySQL rebuilds the table. Afterwards shipped is index 4 and cancelled is 5 — every row was rewritten. On a big table that's a long ALTER (see Online schema changes).
So: add ENUM values only at the end, and never reorder or remove values on a big table without planning the rebuild.

AdvancedSET and the BOOLEAN that isn't

A table with a SET column and a BOOLEAN, filled with values that test both:

SQL

CREATE TABLE flags_test (
  id        INT PRIMARY KEY,
  handling  SET('gift', 'fragile', 'express'),
  is_active BOOLEAN NOT NULL DEFAULT TRUE
);
SHOW CREATE TABLE flags_test;
INSERT INTO flags_test VALUES (1, 'fragile,gift', TRUE), (2, 'express', FALSE), (3, '', 2), (4, 'gift,gift', -1);
SELECT id, handling, handling + 0 AS bits, is_active FROM flags_test;
SELECT GROUP_CONCAT(id) AS gift_ids FROM flags_test WHERE FIND_IN_SET('gift', handling);
SELECT
  (SELECT GROUP_CONCAT(id) FROM flags_test WHERE is_active = TRUE)  AS eq_true,
  (SELECT GROUP_CONCAT(id) FROM flags_test WHERE is_active)         AS truthy,
  (SELECT GROUP_CONCAT(id) FROM flags_test WHERE is_active IS TRUE) AS is_true;
INSERT INTO flags_test VALUES (5, 'oversized', TRUE);

Result

*************************** 1. row ***************************
       Table: flags_test
Create Table: CREATE TABLE `flags_test` (
  `id` int NOT NULL,
  `handling` set('gift','fragile','express') DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
+----+--------------+------+-----------+
| id | handling     | bits | is_active |
+----+--------------+------+-----------+
|  1 | gift,fragile |    3 |         1 |
|  2 | express      |    4 |         0 |
|  3 |              |    0 |         2 |
|  4 | gift         |    1 |        -1 |
+----+--------------+------+-----------+
+----------+
| gift_ids |
+----------+
| 1,4      |
+----------+
+---------+--------+---------+
| eq_true | truthy | is_true |
+---------+--------+---------+
| 1       | 1,3,4  | 1,3,4   |
+---------+--------+---------+
ERROR 1265 (01000): Data truncated for column 'handling' at row 1

What’s happening

SHOW CREATE TABLE gives the game away: BOOLEAN became tinyint(1) and TRUE became '1'.
A SET is a bitmask: gift = 1, fragile = 2, express = 4. 'fragile,gift' is stored as 3 and always displayed in declaration order (gift,fragile); the duplicate in 'gift,gift' collapses to gift (1).
FIND_IN_SET('gift', handling) finds rows 1 and 4. It can't use an index — searching a SET column is always a scan.
Because BOOLEAN is a TINYINT, 2 and -1 were accepted. Then is_active = TRUE means is_active = 1 and finds only row 1, while WHERE is_active and IS TRUE treat any non-zero as true and find 1, 3 and 4. Three "obvious" ways to ask the same question disagree.
'oversized' isn't a SET member, so it's rejected like a bad ENUM value.

AdvancedFixes: a CHECK for booleans, a lookup table for statuses

A CHECK makes a BOOLEAN column honest, and a lookup table replaces an ENUM when the list changes often or needs extra data:

SQL

CREATE TABLE strict_flags (
  id        INT PRIMARY KEY,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT chk_is_active_bool CHECK (is_active IN (0, 1))
);
INSERT INTO strict_flags VALUES (1, TRUE), (2, FALSE);
INSERT INTO strict_flags VALUES (3, 2);
CREATE TABLE order_statuses (code VARCHAR(20) PRIMARY KEY, sort_order TINYINT NOT NULL, is_final BOOLEAN NOT NULL);
INSERT INTO order_statuses VALUES ('pending', 1, FALSE), ('paid', 2, FALSE), ('shipped', 3, TRUE), ('cancelled', 4, TRUE), ('refunded', 5, TRUE);
SELECT s.code, s.is_final, COUNT(o.id) AS orders
FROM order_statuses s LEFT JOIN orders o ON o.status = s.code
GROUP BY s.code, s.is_final, s.sort_order ORDER BY s.sort_order;

Result

ERROR 3819 (HY000): Check constraint 'chk_is_active_bool' is violated.
+-----------+----------+--------+
| code      | is_final | orders |
+-----------+----------+--------+
| pending   |        0 |      1 |
| paid      |        0 |      2 |
| shipped   |        1 |      4 |
| cancelled |        1 |      1 |
| refunded  |        1 |      0 |
+-----------+----------+--------+

What’s happening

With CHECK (is_active IN (0, 1)), TRUE and FALSE go in and 2 is refused with ERROR 3819. Now = TRUE and IS TRUE always agree.
order_statuses holds the allowed values as rows: adding refunded is an INSERT, not an ALTER, and each status can carry data (sort_order, is_final) that an ENUM can't.
In a real schema orders.status would be a VARCHAR(20) with a foreign key to order_statuses(code); here the join on the ENUM's text shows the idea. The LEFT JOIN keeps refunded with zero orders.
PostgreSQL has a real boolean type and real enum types (CREATE TYPE … AS ENUM), so neither trick is needed there.

#Primary keys: AUTO_INCREMENT vs UUID

Added

Every InnoDB table is a clustered index: the rows themselves are stored in a B+tree, sorted by primary key, and every secondary index stores the primary key as its pointer back to the row. So the primary key decides two things at once — where each new row physically goes, and how big every other index is (see Clustered and secondary indexes in InnoDB).

AUTO_INCREMENT integers are small (4 or 8 bytes) and always increasing, so each insert appends to the right-hand edge of the tree — like adding pages to the end of a binder.
UUIDs are 128-bit values you can generate anywhere without asking the database — good for distributed systems, merging data, client-generated ids and not leaking "we have 1,200 customers". But a random UUID (version 4) lands at a random place in the tree each time — like filing every new page into a random spot in the binder, forcing page splits and half-empty pages.

SQL

CREATE TABLE tickets (
  id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject VARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO tickets (subject) VALUES ('Login broken'), ('Refund please');
SELECT LAST_INSERT_ID();
START TRANSACTION;
INSERT INTO tickets (subject) VALUES ('Will be rolled back');
ROLLBACK;
INSERT INTO tickets (subject) VALUES ('Login broken');
INSERT INTO tickets (subject) VALUES ('Order missing');
SELECT * FROM tickets ORDER BY id;
SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = 'ch07' AND table_name = 'tickets';

Result

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
ERROR 1062 (23000): Duplicate entry 'Login broken' for key 'tickets.subject'
+----+---------------+
| id | subject       |
+----+---------------+
|  1 | Login broken  |
|  2 | Refund please |
|  5 | Order missing |
+----+---------------+
+----------------+
| AUTO_INCREMENT |
+----------------+
|              6 |
+----------------+

What’s happening

The two-row insert gets ids 1 and 2. LAST_INSERT_ID() returns 1 — the first id of the last multi-row insert, per connection, so concurrent clients never see each other's ids.
The insert inside the transaction takes id 3, then ROLLBACK removes the row — but not the counter. Handing out ids isn't transactional, otherwise every insert would have to wait for earlier transactions to finish.
The duplicate 'Login broken' fails with ERROR 1062, and it still burned id 4: the id was assigned before the unique check failed.
'Order missing' gets 5. The gaps are normal and harmless; never use AUTO_INCREMENT ids as a gap-free sequence (invoice numbers that must be consecutive need their own mechanism).
The next value is 6. Since MySQL 8.0 the counter is persisted, so it survives a restart. On 5.7 it was recomputed as MAX(id) + 1 at startup, so deleting the newest rows and restarting could hand the same ids out again.

AdvancedBenchmark: what a random primary key costs

Four tables with the same 500,000 rows and a secondary index on created_at; only the primary key differs. RANDOM_BYTES(16) stands in for a random UUIDv4, and UUID_TO_BIN(UUID(), 1) stores MySQL's time-based UUIDv1 with the time fields swapped to the front so it sorts by time:

SQL

CREATE TABLE pk_auto         (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payload CHAR(100) NOT NULL, created_at DATETIME NOT NULL, INDEX (created_at));
CREATE TABLE pk_uuid_random  (id BINARY(16) PRIMARY KEY, payload CHAR(100) NOT NULL, created_at DATETIME NOT NULL, INDEX (created_at));
CREATE TABLE pk_uuid_ordered (id BINARY(16) PRIMARY KEY, payload CHAR(100) NOT NULL, created_at DATETIME NOT NULL, INDEX (created_at));
CREATE TABLE pk_uuid_char    (id CHAR(36)   PRIMARY KEY, payload CHAR(100) NOT NULL, created_at DATETIME NOT NULL, INDEX (created_at));
-- seq holds the numbers 1..500000
SET @t = NOW(6);
INSERT INTO pk_auto (payload, created_at) SELECT 'x', NOW() FROM seq;
SELECT 'pk_auto' AS tbl, TIMESTAMPDIFF(MICROSECOND, @t, NOW(6)) / 1e6 AS seconds;
-- … the same for the other three tables, with RANDOM_BYTES(16), UUID_TO_BIN(UUID(), 1) and UUID() as the id …
ANALYZE TABLE pk_auto, pk_uuid_random, pk_uuid_ordered, pk_uuid_char;
SET SESSION information_schema_stats_expiry = 0;
SELECT table_name, table_rows AS approx_rows,
       ROUND(data_length / 1048576, 1)  AS data_mb,
       ROUND(index_length / 1048576, 1) AS secondary_index_mb
FROM information_schema.tables
WHERE table_schema = 'ch07' AND table_name LIKE 'pk\_%'
ORDER BY data_length;

Result

+---------+---------+
| tbl     | seconds |
+---------+---------+
| pk_auto |  1.3516 |
+---------+---------+
+----------------+----------+
| tbl            | seconds  |
+----------------+----------+
| pk_uuid_random | 3.601987 |
+----------------+----------+
+-----------------+----------+
| tbl             | seconds  |
+-----------------+----------+
| pk_uuid_ordered | 1.451734 |
+-----------------+----------+
+--------------+----------+
| tbl          | seconds  |
+--------------+----------+
| pk_uuid_char | 1.795962 |
+--------------+----------+
+-----------------+-------------+---------+--------------------+
| TABLE_NAME      | approx_rows | data_mb | secondary_index_mb |
+-----------------+-------------+---------+--------------------+
| pk_auto         |      495731 |    69.6 |                9.5 |
| pk_uuid_ordered |      495517 |    72.6 |               13.6 |
| pk_uuid_char    |      494760 |    83.8 |               23.6 |
| pk_uuid_random  |      495734 |   117.8 |               19.6 |
+-----------------+-------------+---------+--------------------+

What’s happening

pk_auto is the baseline: 1.35 s to insert, 69.6 MB of clustered index (the rows), 9.5 MB for the created_at index.
pk_uuid_random took 2.7× longer and its clustered index is 70% bigger (117.8 MB) for the same rows. Each insert lands on a random leaf page; full pages split in half, and the tree ends up full of half-empty pages. Here the whole table fits in the 128 MB buffer pool; once it doesn't, random inserts also become random disk reads, and the gap grows much wider.
pk_uuid_ordered — same 16 bytes, but time-ordered — is nearly as fast as AUTO_INCREMENT (1.45 s, 72.6 MB). The size of the key matters much less than its order.
Its secondary index is still 13.6 MB vs 9.5 MB, because every secondary index entry carries the 16-byte primary key instead of an 8-byte one. pk_uuid_char stores the key as 36 characters of text, so its secondary index is 2.5× the baseline (23.6 MB).
pk_uuid_char was only moderately slow here because MySQL's UUID() is version 1: its first eight hex digits are the low bits of a timestamp, so within a short run the strings are nearly increasing. In a long-running system those bits wrap around every few minutes and the order is lost.
table_rows is an estimate from sampling (≈495,000, not 500,000) — normal for InnoDB.

AdvancedStoring UUIDs compactly with UUID_TO_BIN

The smallest keys in three of the benchmark tables, and a round trip through the swap flag:

SQL

SELECT HEX(id) FROM pk_uuid_ordered ORDER BY id LIMIT 3;
SELECT HEX(id) FROM pk_uuid_random ORDER BY id LIMIT 3;
SELECT id FROM pk_uuid_char ORDER BY id LIMIT 3;
SELECT UUID() AS v1_text, BIN_TO_UUID(UUID_TO_BIN(UUID(), 1), 1) AS round_trip;

Result

+----------------------------------+
| HEX(id)                          |
+----------------------------------+
| 11F1C976266B0789991EC22B9F20C416 |
| 11F1C976266B0B78991EC22B9F20C416 |
| 11F1C976266B0BBC991EC22B9F20C416 |
+----------------------------------+
+----------------------------------+
| HEX(id)                          |
+----------------------------------+
| 0000067091EEED64B9D1D825BFA60BC1 |
| 000027B567C46B52BD7B034576E96F4E |
| 00002C3E4E8B0DA1FA5AE3F5A2BF26BF |
+----------------------------------+
+--------------------------------------+
| id                                   |
+--------------------------------------+
| 27485da5-c976-11f1-991e-c22b9f20c416 |
| 27485fe0-c976-11f1-991e-c22b9f20c416 |
| 27486031-c976-11f1-991e-c22b9f20c416 |
+--------------------------------------+
+--------------------------------------+--------------------------------------+
| v1_text                              | round_trip                           |
+--------------------------------------+--------------------------------------+
| 31402026-c976-11f1-991e-c22b9f20c416 | 3140202c-c976-11f1-991e-c22b9f20c416 |
+--------------------------------------+--------------------------------------+

What’s happening

A version-1 UUID is time_low-time_mid-time_hi…: the fastest-changing part of the time comes first, which is why consecutive UUID() values (27485da5…, 27485fe0…) differ at the start.
UUID_TO_BIN(uuid, 1) packs the 36-character text into 16 bytes and, with the swap flag 1, moves time_hi and time_mid (11f1, c976) to the front. The stored keys now begin 11F1C976… and increase with time — ideal for a clustered index.
The random keys share no prefix at all: consecutive inserts are scattered across the whole tree.
BIN_TO_UUID(bin, 1) reverses the swap for display. Always pass the same flag both ways, or you'll print a different UUID. (The two columns differ only because each UUID() call makes a new value.)
MySQL 9.7 has no UUIDv7 function — SELECT UUID_V7() fails with ERROR 1305 (42000): FUNCTION ch07.UUID_V7 does not exist. UUIDv7 puts a millisecond timestamp first and is designed to sort; generate it in the application (or use PostgreSQL 18's uuidv7()) and store it as BINARY(16) without a swap.

AdvancedPractice: internal id plus public UUID

#Foreign keys and referential actions

Added

A foreign key says "every value in this column must exist as a key in that table". It protects you from orphans — orders for customers who don't exist, order items for deleted products. The table holding the foreign key is the child; the table it points at is the parent.

The second half is the referential action: what should happen to children when their parent is deleted or its key changes?

RESTRICT / NO ACTION (the default) — refuse to delete a parent that still has children. In InnoDB the two are identical and checked immediately.
CASCADE — delete (or update) the children too.
SET NULL — keep the children but set their foreign key to NULL (the column must be nullable).

A mental model: a foreign key is a name tag on a lanyard clipped to a parent row. RESTRICT means you can't throw the parent away while tags are clipped to it; CASCADE means the tagged children go with it; SET NULL means the tags are unclipped and the children stay, unattached.

SQL

CREATE TABLE wishlist_items (
  customer_id INT NOT NULL,
  product_id  INT NOT NULL,
  PRIMARY KEY (customer_id, product_id),
  CONSTRAINT fk_wish_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_wish_product  FOREIGN KEY (product_id)  REFERENCES products(id)  ON DELETE CASCADE
);
CREATE TABLE support_tickets (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NULL,
  subject     VARCHAR(80) NOT NULL,
  CONSTRAINT fk_ticket_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
);
INSERT INTO wishlist_items VALUES (6, 2), (6, 8), (1, 4);
INSERT INTO support_tickets (customer_id, subject) VALUES (6, 'Delete my account'), (1, 'Where is order 2?');
DELETE FROM customers WHERE id = 6;
SELECT * FROM wishlist_items;
SELECT * FROM support_tickets;
DELETE FROM customers WHERE id = 1;
SELECT COUNT(*) AS rohit_wishlist_still_there FROM wishlist_items WHERE customer_id = 1;

Result

+-------------+------------+
| customer_id | product_id |
+-------------+------------+
|           1 |          4 |
+-------------+------------+
+----+-------------+-------------------+
| id | customer_id | subject           |
+----+-------------+-------------------+
|  1 |        NULL | Delete my account |
|  2 |           1 | Where is order 2? |
+----+-------------+-------------------+
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch07`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
+----------------------------+
| rohit_wishlist_still_there |
+----------------------------+
|                          1 |
+----------------------------+

What’s happening

Zara (customer 6, no orders) has two wishlist items and a support ticket. Rohit (customer 1) has a wishlist item, a ticket and two orders.
DELETE FROM customers WHERE id = 6 succeeds. fk_wish_customer … ON DELETE CASCADE deletes her two wishlist rows; fk_ticket_customer … ON DELETE SET NULL keeps her ticket for the support history but sets its customer_id to NULL.
Deleting Rohit fails with ERROR 1451, naming the constraint that blocked it: orders_ibfk_1 (the sample schema's orders foreign key has no action, so it's RESTRICT). You don't want deleting a customer to silently delete their order history.
The failed DELETE is atomic: the cascade to Rohit's wishlist row was undone along with it, so his wishlist item is still there. Either the whole statement and all its cascades happen, or none of it does.
The choice of action is a business decision per relationship: cascade for things that only exist for the parent (wishlist items, cart lines), SET NULL for history you want to keep, RESTRICT for records you must never lose by accident (orders, payments).

AdvancedThe other direction: inserting an orphan

The child side is checked too:

SQL

INSERT INTO orders (customer_id, status, ordered_at) VALUES (99, 'pending', NOW());

Result

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ch07`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

What’s happening

There's no customer 99, so the insert fails with ERROR 1452 — 1451 is "parent row" (you're removing something referenced), 1452 is "child row" (you're pointing at something missing).
The same check runs when an UPDATE changes a child's foreign key to a value that doesn't exist.
Without the constraint, this order would sit in the table forever and quietly disappear from every INNER JOIN report.

AdvancedDefinition errors you'll meet

Three foreign keys that look reasonable at first glance:

SQL

CREATE TABLE fk_wrong_type (customer_id BIGINT, FOREIGN KEY (customer_id) REFERENCES customers(id));
CREATE TABLE fk_non_unique (city VARCHAR(50), FOREIGN KEY (city) REFERENCES customers(city));
CREATE TABLE fk_myisam (customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id)) ENGINE=MyISAM;
INSERT INTO fk_myisam VALUES (12345);
SELECT * FROM fk_myisam;

Result

ERROR 3780 (HY000): Referencing column 'customer_id' and referenced column 'id' in foreign key constraint 'fk_wrong_type_ibfk_1' are incompatible.
ERROR 6125 (HY000): Failed to add the foreign key constraint. Missing unique key for constraint 'fk_non_unique_ibfk_1' in the referenced table 'customers'
+-------------+
| customer_id |
+-------------+
|       12345 |
+-------------+

What’s happening

Child and parent columns must have the same type, size and sign: BIGINT → INT fails with ERROR 3780. This bites when you widen a parent id to BIGINT and forget the children.
Referencing a column that isn't a primary or unique key fails with ERROR 6125. Older InnoDB allowed foreign keys to any indexed column; MySQL 8.4 turned on restrict_fk_on_non_standard_key by default to stop that, and 9.7 keeps it ON.
On MyISAM the FOREIGN KEY clause is parsed and silently ignored — no error, no warning, and the orphan 12345 goes straight in. Old schemas that "have foreign keys" sometimes don't, for exactly this reason.

AdvancedPitfall: foreign_key_checks = 0

Bulk loads and dump restores often switch checks off. Rows written meanwhile are never re-checked:

SQL

SET foreign_key_checks = 0;
INSERT INTO support_tickets (customer_id, subject) VALUES (777, 'Orphan ticket');
SET foreign_key_checks = 1;
SELECT t.id, t.customer_id, c.id AS customer_exists
FROM support_tickets t LEFT JOIN customers c ON c.id = t.customer_id
WHERE t.customer_id IS NOT NULL AND c.id IS NULL;

Result

+----+-------------+-----------------+
| id | customer_id | customer_exists |
+----+-------------+-----------------+
|  3 |         777 |            NULL |
+----+-------------+-----------------+

What’s happening

With foreign_key_checks = 0, the insert of a ticket for non-existent customer 777 succeeds.
Turning checks back on does not scan existing data; the docs say so explicitly. The orphan stays.
The anti-join (LEFT JOIN … WHERE parent IS NULL) is how you find orphans after the fact. Run it after any load done with checks off.

AdvancedMySQL 9.6+: foreign keys moved to the SQL layer

Until MySQL 9.5, InnoDB enforced foreign keys inside the storage engine. That had two long-standing side effects: cascaded changes didn't fire triggers on the child table, and they weren't written to the binary log as separate row events. Since MySQL 9.6 the server's SQL layer handles them by default (innodb_native_foreign_keys = OFF). Two visible effects, run on 9.7:

SQL

INSERT INTO support_tickets (customer_id, subject) VALUES (99, 'No such customer');
INSERT INTO support_tickets (customer_id, subject) VALUES (2, 'Valid ticket');
SELECT id, customer_id, subject FROM support_tickets ORDER BY id;
CREATE TABLE wishlist_log (msg VARCHAR(100));
CREATE TRIGGER trg_wishlist_delete AFTER DELETE ON wishlist_items FOR EACH ROW
  INSERT INTO wishlist_log VALUES (CONCAT('removed product ', OLD.product_id, ' for customer ', OLD.customer_id));
INSERT INTO products (id, name, category_id, price, stock) VALUES (9, 'Discontinued Mouse', 5, 19.00, 0), (10, 'Discontinued Cable', 5, 9.00, 0);
INSERT INTO wishlist_items VALUES (1, 9), (2, 10);
DELETE FROM products WHERE id = 9;
SELECT COUNT(*) AS log_rows_after_default_cascade FROM wishlist_log;
SET SESSION enable_cascade_triggers = ON;
DELETE FROM products WHERE id = 10;
SELECT * FROM wishlist_log;

Result

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ch07`.`support_tickets`, CONSTRAINT `fk_ticket_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL)
+----+-------------+-------------------+
| id | customer_id | subject           |
+----+-------------+-------------------+
|  1 |        NULL | Delete my account |
|  2 |           1 | Where is order 2? |
|  3 |         777 | Orphan ticket     |
|  4 |           2 | Valid ticket      |
+----+-------------+-------------------+
+--------------------------------+
| log_rows_after_default_cascade |
+--------------------------------+
|                              0 |
+--------------------------------+
Warning (Code 1681): 'enable_cascade_triggers' is deprecated and will be removed in a future release.
+-----------------------------------+
| msg                               |
+-----------------------------------+
| removed product 10 for customer 2 |
+-----------------------------------+

What’s happening

The ticket for customer 99 is rejected, and the next valid ticket gets id 4 — no gap. The SQL layer checks the foreign key before InnoDB inserts the row, so no AUTO_INCREMENT value is used up (the docs list this as a difference; on 8.0 the failed insert would have burned an id).
Deleting product 9 cascades to its wishlist row, but the AFTER DELETE trigger on wishlist_items doesn't fire — wishlist_log is empty. That's still the default, for compatibility.
With SET SESSION enable_cascade_triggers = ON, the cascade from deleting product 10 does fire the trigger, and the log gets a row. Note the real warning: on 9.7 the variable is already marked deprecated, so treat it as transitional and check the release notes before depending on it.
Under the hood, the cascaded deletes are now logged as their own row events, so replicas and change-data-capture tools (Debezium and friends) finally see them — previously a CDC pipeline could miss cascaded deletes entirely.
On MySQL 8.0/8.4 none of this applies: cascades never fire triggers, and the only fix was to do the child deletes yourself.

AdvancedObserved: ON DELETE SET DEFAULT

The manual says InnoDB rejects SET DEFAULT. On this 9.7.2 server the definition was accepted, but the action behaved like RESTRICT:

SQL

INSERT INTO customers (id, name, email, city, created_at) VALUES (100, 'Deleted user', 'deleted@example.com', NULL, NOW()), (7, 'Temp', 'temp@example.com', NULL, NOW());
CREATE TABLE fk_set_default (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL DEFAULT 100,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET DEFAULT
);
INSERT INTO fk_set_default VALUES (1, 7);
DELETE FROM customers WHERE id = 7;
SELECT * FROM fk_set_default;

Result

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch07`.`fk_set_default`, CONSTRAINT `fk_set_default_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
+----+-------------+
| id | customer_id |
+----+-------------+
|  1 |           7 |
+----+-------------+

What’s happening

CREATE TABLE with ON DELETE SET DEFAULT raised no error, and SHOW CREATE TABLE keeps the clause.
Deleting customer 7 didn't move the child to the default 100; it failed with ERROR 1451, exactly like RESTRICT.
Whatever the version does, don't rely on SET DEFAULT in MySQL. If you want "reassign to a placeholder parent", do the UPDATE explicitly before the DELETE, in one transaction. PostgreSQL supports SET DEFAULT properly.

AdvancedPitfall: unindexed foreign keys in PostgreSQL

AdvancedEdge case: cascade depth and ON UPDATE

#UNIQUE, NOT NULL, DEFAULT and CHECK

Added

These four are the everyday rules of the contract:

NOT NULL — the value must be present. Most columns should be NOT NULL; NULL should mean "genuinely unknown or not applicable", not "we forgot".
DEFAULT — what to use when an insert doesn't mention the column. Since MySQL 8.0.13 it can be an expression in parentheses, like DEFAULT (CURRENT_DATE + INTERVAL 30 DAY).
UNIQUE — no two rows may have the same value (or combination of values). It's implemented as a unique index, so it also speeds up lookups.
CHECK — any condition on the row's own columns: ranges, cross-column rules. Enforced since MySQL 8.0.16.

Think of them as the validation on a sign-up form, but enforced at the last possible moment, by the one component every write must pass through. An application bug, a hurried admin with a SQL console or a forgotten batch job can't get around them.

SQL

CREATE TABLE coupons (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(20)   NOT NULL UNIQUE,
  partner_ref VARCHAR(20)   NULL UNIQUE,
  percent_off TINYINT       NOT NULL DEFAULT 10,
  min_order   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  starts_on   DATE          NOT NULL DEFAULT (CURRENT_DATE),
  ends_on     DATE          NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 30 DAY),
  CONSTRAINT chk_percent CHECK (percent_off BETWEEN 1 AND 90),
  CONSTRAINT chk_dates   CHECK (ends_on > starts_on)
);
INSERT INTO coupons (code) VALUES ('WELCOME');
INSERT INTO coupons (code, partner_ref) VALUES ('SPRING', NULL), ('SUMMER', NULL);
INSERT INTO coupons (code) VALUES ('WELCOME');
INSERT INTO coupons (code, percent_off) VALUES ('HALFOFF', 95);
INSERT INTO coupons (code, starts_on, ends_on) VALUES ('BACKWARDS', '2025-06-01', '2025-05-01');
INSERT INTO coupons (code, percent_off) VALUES (NULL, 20);
SELECT id, code, partner_ref, percent_off, min_order, starts_on, ends_on FROM coupons ORDER BY id;

Result

ERROR 1062 (23000): Duplicate entry 'WELCOME' for key 'coupons.code'
ERROR 3819 (HY000): Check constraint 'chk_percent' is violated.
ERROR 3819 (HY000): Check constraint 'chk_dates' is violated.
ERROR 1048 (23000): Column 'code' cannot be null
+----+---------+-------------+-------------+-----------+------------+------------+
| id | code    | partner_ref | percent_off | min_order | starts_on  | ends_on    |
+----+---------+-------------+-------------+-----------+------------+------------+
|  1 | WELCOME | NULL        |          10 |      0.00 | 2026-10-16 | 2026-11-15 |
|  2 | SPRING  | NULL        |          10 |      0.00 | 2026-10-16 | 2026-11-15 |
|  3 | SUMMER  | NULL        |          10 |      0.00 | 2026-10-16 | 2026-11-15 |
+----+---------+-------------+-------------+-----------+------------+------------+

What’s happening

INSERT INTO coupons (code) VALUES ('WELCOME') gives only the code; every other column comes from its DEFAULT: 10 percent, 0.00 minimum, today (2026-10-16, the day this was run) and today + 30 days. The date defaults are expressions, hence the parentheses.
SPRING and SUMMER both have partner_ref = NULL, and the UNIQUE index on partner_ref accepts both. In SQL, NULL isn't equal to anything — not even another NULL — so it never counts as a duplicate. "Optional but unique if present" works out of the box.
A second WELCOME breaks the unique key: ERROR 1062, naming the index coupons.code.
95 percent breaks chk_percent, and an end date before the start breaks chk_dates — a cross-column rule that no single column type could express. ERROR 3819 names the constraint, which is why naming them matters.
An explicit NULL for a NOT NULL column fails with ERROR 1048. Leaving a column out is different: then the DEFAULT applies.
Only the three valid rows exist. Every rule ran inside the database, whichever client sent the insert.

AdvancedCHECK edge cases: NULL, non-determinism, old rows

Four situations where a CHECK doesn't behave the way people expect:

SQL

CREATE TABLE check_null (qty INT NULL, CHECK (qty > 0));
INSERT INTO check_null VALUES (NULL);
INSERT INTO check_null VALUES (0);
SELECT * FROM check_null;
CREATE TABLE check_now (d DATE, CHECK (d >= CURDATE()));
ALTER TABLE products ADD CONSTRAINT chk_stock_positive CHECK (stock > 0);
ALTER TABLE products ADD CONSTRAINT chk_stock_positive CHECK (stock > 0) NOT ENFORCED;
INSERT INTO products (name, category_id, price, stock) VALUES ('Ghost item', 5, 1.00, -5);
SELECT constraint_name, check_clause, enforced
FROM information_schema.check_constraints
JOIN information_schema.table_constraints USING (constraint_schema, constraint_name)
WHERE constraint_schema = 'ch07' AND table_name = 'products';

Result

ERROR 3819 (HY000): Check constraint 'check_null_chk_1' is violated.
+------+
| qty  |
+------+
| NULL |
+------+
ERROR 3814 (HY000): An expression of a check constraint 'check_now_chk_1' contains disallowed function: curdate.
ERROR 3819 (HY000): Check constraint 'chk_stock_positive' is violated.
+--------------------+---------------+----------+
| CONSTRAINT_NAME    | CHECK_CLAUSE  | ENFORCED |
+--------------------+---------------+----------+
| chk_stock_positive | (`stock` > 0) | NO       |
+--------------------+---------------+----------+

What’s happening

NULL > 0 is unknown, not false, and a CHECK only rejects rows where the condition is false. So NULL passes and 0 fails. If NULL shouldn't be allowed, add NOT NULL as well. (This is three-valued logic again — see NULL and three-valued logic.)
A CHECK must give the same answer every time for the same row, so CURDATE(), NOW(), RAND(), user variables and subqueries are rejected with ERROR 3814. "Dates must be in the future" belongs in the application or a trigger.
Adding CHECK (stock > 0) to products fails because an existing row violates it — the iPhone 17 has stock = 0. MySQL checks all existing rows when you add an enforced constraint.
NOT ENFORCED stores the constraint as documentation only; then -5 stock goes straight in. It's useful as a migration step (add not-enforced, clean data, then ALTER TABLE … ALTER CHECK chk_stock_positive ENFORCED), not as a permanent state.

AdvancedOld behaviour: CHECK ignored before 8.0.16

MySQL 5.7 and 8.0 up to 8.0.15 parsed CHECK clauses and silently threw them away. A 5.7 schema can contain CHECK (price > 0) that has never been enforced, and the data may already break it. When you upgrade, MySQL won't re-add those old constraints for you (they were never stored); run the rule as a query first (SELECT … WHERE NOT (price > 0)), fix the data, then add the constraint.

SQL

CREATE TABLE no_default (id INT PRIMARY KEY, label VARCHAR(10) NOT NULL);
INSERT INTO no_default (id) VALUES (1);
SET SESSION sql_mode = '';
INSERT INTO no_default (id) VALUES (1);
SET SESSION sql_mode = DEFAULT;
SELECT id, CONCAT('[', label, ']') AS label FROM no_default;

Result

ERROR 1364 (HY000): Field 'label' doesn't have a default value
Warning (Code 1364): Field 'label' doesn't have a default value
+----+-------+
| id | label |
+----+-------+
|  1 | []    |
+----+-------+

What’s happening

label is NOT NULL with no DEFAULT, so an insert that leaves it out has no value to use: strict mode refuses with ERROR 1364.
In non-strict mode the same insert succeeds with a warning and stores the type's implicit default, the empty string ''.
Old applications that "worked" on a lenient server often break on a strict one for exactly this reason. Fixing the insert is better than relaxing sql_mode.

AdvancedPractice: business rules as composite keys

Spot the bug

A team wants "each customer can have at most one coupon redemption per coupon", and writes:

SQL

CREATE TABLE redemptions (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  coupon_id   INT NOT NULL,
  customer_id INT NULL,
  UNIQUE (coupon_id, customer_id)
);

Guest checkouts insert customer_id = NULL. What goes wrong?

Show answer

Nothing stops a guest from redeeming the same coupon any number of times:

SQL

INSERT INTO redemptions (coupon_id, customer_id) VALUES (5, NULL), (5, NULL), (5, NULL), (5, 1);
INSERT INTO redemptions (coupon_id, customer_id) VALUES (5, 1);
SELECT * FROM redemptions;

Result

ERROR 1062 (23000): Duplicate entry '5-1' for key 'redemptions.coupon_id'
+----+-----------+-------------+
| id | coupon_id | customer_id |
+----+-----------+-------------+
|  1 |         5 |        NULL |
|  2 |         5 |        NULL |
|  3 |         5 |        NULL |
|  4 |         5 |           1 |
+----+-----------+-------------+

What’s happening

A unique index compares the whole tuple. (5, NULL) vs (5, NULL): the second parts are NULL = NULL, which is unknown, so the tuples are not considered equal.
So the unique index treats every row with a NULL in it as distinct, and three identical guest redemptions pass.
For signed-in customers it works: a second (5, 1) fails with ERROR 1062 (the index is named after its first column, redemptions.coupon_id). The hole is exactly the rows where the rule mattered most.
Fixes: track guests with a non-null identifier (an email or a guest id) and make the column NOT NULL, or index a generated column such as COALESCE(customer_id, -1) if one redemption for "all guests together" is really the rule. PostgreSQL 15+ also has UNIQUE NULLS NOT DISTINCT; MySQL has no equivalent.

#Generated columns

Added

A generated column is computed from other columns in the same row by an expression you declare once. It comes in two kinds:

VIRTUAL (the default) — computed on read, takes no space in the row. You can still index it; then the index stores the computed value.
STORED — computed on write and saved in the row, like a normal column that nobody can write to.

The mental model is a spreadsheet formula column: you never type into it, it's always consistent with the cells it reads, and you can sort and filter by it. That consistency is the point — a denormalised "total" column that the application must remember to update eventually drifts; a generated one can't.

SQL

ALTER TABLE order_items
  ADD COLUMN line_total DECIMAL(12,2) AS (quantity * unit_price) STORED;
ALTER TABLE customers
  ADD COLUMN email_domain VARCHAR(100) AS (SUBSTRING_INDEX(email, '@', -1)) VIRTUAL,
  ADD INDEX idx_email_domain (email_domain);
SELECT order_id, product_id, quantity, unit_price, line_total FROM order_items WHERE order_id IN (1, 7);
UPDATE order_items SET quantity = 3 WHERE order_id = 1 AND product_id = 7;
SELECT order_id, product_id, quantity, unit_price, line_total FROM order_items WHERE order_id = 1;
UPDATE order_items SET line_total = 0 WHERE order_id = 1;

Result

+----------+------------+----------+------------+------------+
| order_id | product_id | quantity | unit_price | line_total |
+----------+------------+----------+------------+------------+
|        1 |          1 |        1 |     899.00 |     899.00 |
|        1 |          7 |        2 |      29.00 |      58.00 |
|        7 |          1 |        1 |     899.00 |     899.00 |
|        7 |          5 |        3 |      39.00 |     117.00 |
+----------+------------+----------+------------+------------+
+----------+------------+----------+------------+------------+
| order_id | product_id | quantity | unit_price | line_total |
+----------+------------+----------+------------+------------+
|        1 |          1 |        1 |     899.00 |     899.00 |
|        1 |          7 |        3 |      29.00 |      87.00 |
+----------+------------+----------+------------+------------+
ERROR 3105 (HY000): The value specified for generated column 'line_total' in table 'order_items' is not allowed.

What’s happening

line_total … AS (quantity * unit_price) STORED is computed for every existing row while the ALTER rebuilds the table: 2 × 29.00 = 58.00, 3 × 39.00 = 117.00.
email_domain is VIRTUAL: nothing is added to the rows, but the index idx_email_domain stores the computed domain for each customer, so lookups by domain can use it.
Changing quantity from 2 to 3 recomputes line_total to 87.00 in the same write. Nobody can forget to update it.
Writing to the generated column directly fails with ERROR 3105. The only value it accepts is DEFAULT.
STORED costs space and makes writes slightly heavier but reads free; VIRTUAL costs nothing to store but recomputes on every read — cheap for simple expressions like these.

AdvancedThe optimizer matches the expression too

Query the generated column by name, then by the expression behind it:

SQL

EXPLAIN SELECT name FROM customers WHERE email_domain = 'example.com';
EXPLAIN SELECT name FROM customers WHERE SUBSTRING_INDEX(email, '@', -1) = 'example.com';

Result

*************************** 1. row ***************************
EXPLAIN: -> Index lookup on customers using idx_email_domain (email_domain = 'example.com')  (cost=1 rows=5)

*************************** 1. row ***************************
EXPLAIN: -> Index lookup on customers using idx_email_domain (email_domain = 'example.com')  (cost=1 rows=5)

What’s happening

Querying the generated column by name uses its index, as expected.
Querying the expression — SUBSTRING_INDEX(email, '@', -1) — uses it too: the optimizer recognises the exact expression of an indexed generated column and rewrites the condition to email_domain = 'example.com'.
The match has to be exact (same function, same arguments, same types and collation). LOWER(SUBSTRING_INDEX(…)) wouldn't match.
This is the mechanism behind functional indexes (8.0.13+): CREATE INDEX … ((expr)) creates a hidden virtual generated column and indexes it. See Functional, prefix and invisible indexes.

AdvancedRestrictions and DDL cost

Three generated columns that MySQL treats very differently:

SQL

ALTER TABLE orders ADD COLUMN age_days INT AS (DATEDIFF(NOW(), ordered_at));
ALTER TABLE orders ADD COLUMN order_year SMALLINT AS (YEAR(ordered_at)) VIRTUAL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD COLUMN order_month TINYINT AS (MONTH(ordered_at)) STORED, ALGORITHM=INSTANT;
ALTER TABLE orders ADD COLUMN order_month TINYINT AS (MONTH(ordered_at)) STORED, ALGORITHM=INPLACE;
SELECT table_name, column_name, generation_expression, extra FROM information_schema.columns
WHERE table_schema = 'ch07' AND extra LIKE '%GENERATED' AND generation_expression <> '';

Result

ERROR 3763 (HY000): Expression of generated column 'age_days' contains a disallowed function: now.
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY.
+-------------+--------------+---------------------------------------------+-------------------+
| TABLE_NAME  | COLUMN_NAME  | GENERATION_EXPRESSION                       | EXTRA             |
+-------------+--------------+---------------------------------------------+-------------------+
| customers   | email_domain | substring_index(`email`,_utf8mb4\'@\',-(1)) | VIRTUAL GENERATED |
| order_items | line_total   | (`quantity` * `unit_price`)                 | STORED GENERATED  |
+-------------+--------------+---------------------------------------------+-------------------+

What’s happening

The expression must be deterministic: "age in days" depends on NOW(), which changes, so ERROR 3763. Compute it in the query instead.
Adding a VIRTUAL column is instant — it's only metadata. The order_year statement succeeded.
Adding a STORED column must compute and write a value into every row, so it's neither INSTANT nor INPLACE — only COPY, a full rebuild with writes blocked. On a large table, prefer VIRTUAL + index, or plan the rebuild.
information_schema.columns lists generated columns with their expression and kind. (The starts_on/ends_on defaults on coupons are DEFAULT_GENERATED, which is a different thing — an expression default — and the generation_expression <> '' filter leaves them out.)

#Online schema changes

Added

Changing a table people are using is the hard part of schema design. Some changes only touch the table's metadata ("there's now a column called referrer"), and some need every row rewritten ("customer_id is now 8 bytes, not 4"). On a table with a billion rows, a rewrite can take hours, and how much it blocks writers decides whether you have an outage.

InnoDB has three algorithms, and you can ask for one explicitly so MySQL fails fast instead of surprising you:

ALGORITHM=INSTANT — metadata only; finishes in milliseconds whatever the table size. Adding a column (8.0.12+ at the end, 8.0.29+ anywhere), dropping a column (8.0.29+), renaming, changing a default, extending an ENUM at the end.
ALGORITHM=INPLACE — InnoDB does the work inside the engine, usually allowing concurrent reads and writes (LOCK=NONE). Adding a secondary index, widening a VARCHAR within the same length-byte size.
ALGORITHM=COPY — create a new table, copy every row, swap. Writes are blocked for the whole copy. Changing a column's type, adding a STORED column, converting a character set.

Think of renovating a shop. INSTANT is changing the sign over the door. INPLACE is rebuilding the shelves one aisle at a time while customers keep shopping. COPY is building a whole new shop next door, moving every item across and keeping the door locked until you're done.

SQL

-- page_views has 1,000,000 rows
ALTER TABLE page_views ADD COLUMN referrer VARCHAR(200) NULL, ALGORITHM=INSTANT;
ALTER TABLE page_views ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL, ALGORITHM=INPLACE;
ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL, ALGORITHM=COPY, LOCK=NONE;
ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL;
ALTER TABLE page_views DROP COLUMN referrer, ALGORITHM=INSTANT;

Result

ALTER TABLE page_views ADD COLUMN referrer VARCHAR(200) NULL, ALGORITHM=INSTANT
Query OK, 0 rows affected (0.022 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE page_views ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE
Query OK, 0 rows affected (2.011 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL, ALGORITHM=INSTANT
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL, ALGORITHM=INPLACE
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL, ALGORITHM=COPY, LOCK=NONE
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

ALTER TABLE page_views MODIFY customer_id BIGINT NOT NULL
Query OK, 1000000 rows affected (4.571 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

ALTER TABLE page_views DROP COLUMN referrer, ALGORITHM=INSTANT
Query OK, 0 rows affected (0.023 sec)
Records: 0  Duplicates: 0  Warnings: 0

What’s happening

Adding referrer with INSTANT takes 22 ms on a million rows, and 0 rows affected: no row was touched. Existing rows don't physically have the column; InnoDB records a new row version in the metadata and fills in NULL (or the default) when it reads an old row.
Adding an index with INPLACE, LOCK=NONE takes 2 s, during which reads and writes continue. InnoDB scans the table, sorts the keys, builds the index, and replays the changes that happened meanwhile from an online log.
Changing customer_id from INT to BIGINT changes the size of every row. MySQL refuses INSTANT and INPLACE with ERROR 1846 and says why — and refuses COPY with LOCK=NONE, because a copy can't allow writes.
With no clause, MySQL picks the best algorithm itself — here COPY — and rewrites all 1,000,000 rows in 4.6 s with writes blocked. On a 500 GB table that's hours of blocked writes. That's the reason to always state ALGORITHM and LOCK in production migrations: the statement fails in a millisecond instead of locking the table for hours.
Dropping the column instantly (8.0.29+) is again metadata only.

AdvancedThe real outage: waiting for the metadata lock

Even an INSTANT change needs an exclusive metadata lock for a moment. If any open transaction has touched the table, the ALTER waits — and every new query on the table queues behind the waiting ALTER. Three sessions, run concurrently:

SQL

-- Session A: an open transaction that read the table, then went idle
START TRANSACTION;
SELECT url FROM page_views WHERE id = 1;
DO SLEEP(12);
COMMIT;

-- Session B, 2 seconds later
ALTER TABLE page_views ADD COLUMN session_id INT NULL, ALGORITHM=INSTANT;

-- Session C, 2 seconds after that: a trivial primary-key read
SELECT url FROM page_views WHERE id = 2;

-- Session D, while they wait
SELECT id, time, state, info FROM performance_schema.processlist
WHERE db = 'ch07' AND info IS NOT NULL AND info NOT LIKE '%processlist%' ORDER BY id;

Result

+-----+------+---------------------------------+--------------------------------------------------------------------------+
| id  | time | state                           | info                                                                     |
+-----+------+---------------------------------+--------------------------------------------------------------------------+
| 396 |    6 | User sleep                      | DO SLEEP(12)                                                             |
| 398 |    4 | Waiting for table metadata lock | ALTER TABLE page_views ADD COLUMN session_id INT NULL, ALGORITHM=INSTANT |
| 400 |    2 | Waiting for table metadata lock | SELECT url FROM page_views WHERE id = 2                                  |
+-----+------+---------------------------------+--------------------------------------------------------------------------+

Session B: ALTER TABLE … ALGORITHM=INSTANT
Query OK, 0 rows affected (10.041 sec)

Session C: SELECT url FROM page_views WHERE id = 2
1 row in set (7.959 sec)

What’s happening

Session A's SELECT took a shared metadata lock on page_views, and it's held until the transaction ends — not until the SELECT ends. Then A sits idle (here SLEEP; in real life an app that forgot to commit, or a long report).
Session B's ALTER needs an exclusive metadata lock, so it waits: Waiting for table metadata lock.
Session C's single-row SELECT would normally take microseconds, but metadata lock requests are queued fairly, and C is behind B's pending exclusive request. C waits too. On a busy table, every request piles up behind the ALTER within seconds — that's the outage, caused by an instant change.
When A commits after 12 s, B gets its lock, finishes in milliseconds (10.04 s total, almost all of it waiting) and releases; C finally returns after 7.96 s.
The INSTANT part was never the problem; the lock queue was. The defences are on the next card.

AdvancedDefence: a short lock_wait_timeout

Make the ALTER give up quickly instead of blocking everyone, then retry later:

SQL

-- Session A again holds a transaction open on page_views
SET SESSION lock_wait_timeout = 2;
ALTER TABLE page_views ADD COLUMN session_id INT NULL, ALGORITHM=INSTANT;

Result

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

What’s happening

lock_wait_timeout (default 31,536,000 seconds — a year) is how long a statement waits for a metadata lock.
With 2 seconds, the ALTER fails with ERROR 1205 instead of queueing traffic behind it for as long as session A stays open; migration tools retry in a loop.
Before a migration, look for long-running transactions (information_schema.innodb_trx, ordered by trx_started) and deal with them first.

AdvancedWidening a VARCHAR: when it's cheap

Growing and shrinking VARCHAR columns on the million-row page_views table:

SQL

-- page_views.url is VARCHAR(200) again (after a rebuild)
ALTER TABLE page_views MODIFY url VARCHAR(500) NOT NULL, ALGORITHM=INSTANT;
ALTER TABLE page_views MODIFY url VARCHAR(500) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE page_views MODIFY url VARCHAR(100) NOT NULL, ALGORITHM=INPLACE;
ALTER TABLE page_views ADD COLUMN country CHAR(2) NULL, ALGORITHM=INSTANT;
ALTER TABLE page_views ADD COLUMN device VARCHAR(60) NULL, ALGORITHM=INSTANT;
ALTER TABLE page_views MODIFY device VARCHAR(63) NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE page_views MODIFY device VARCHAR(70) NULL, ALGORITHM=INPLACE, LOCK=NONE;
SELECT name, total_row_versions FROM information_schema.innodb_tables WHERE name = 'ch07/page_views';

Result

ALTER TABLE page_views MODIFY url VARCHAR(500) NOT NULL, ALGORITHM=INSTANT
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
ALTER TABLE page_views MODIFY url VARCHAR(500) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE
Query OK, 0 rows affected (0.005 sec)
ALTER TABLE page_views MODIFY url VARCHAR(100) NOT NULL, ALGORITHM=INPLACE
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
ALTER TABLE page_views ADD COLUMN country CHAR(2) NULL, ALGORITHM=INSTANT
Query OK, 0 rows affected (0.006 sec)
ALTER TABLE page_views ADD COLUMN device VARCHAR(60) NULL, ALGORITHM=INSTANT
Query OK, 0 rows affected (0.006 sec)
ALTER TABLE page_views MODIFY device VARCHAR(63) NULL, ALGORITHM=INPLACE, LOCK=NONE
Query OK, 0 rows affected (0.003 sec)
ALTER TABLE page_views MODIFY device VARCHAR(70) NULL, ALGORITHM=INPLACE, LOCK=NONE
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
+-----------------+--------------------+
| name            | total_row_versions |
+-----------------+--------------------+
| ch07/page_views |                  2 |
+-----------------+--------------------+

What’s happening

Widening url from VARCHAR(200) to VARCHAR(500) is never INSTANT, but it is INPLACE and touches no rows (5 ms). In utf8mb4 both sizes are over 255 bytes (800 and 2,000), so both use a 2-byte length prefix and every existing row is already valid.
Shrinking to VARCHAR(100) could cut existing values, so it's never in place — only COPY, which checks every row.
device VARCHAR(60) is 240 bytes (1-byte prefix). Growing it to VARCHAR(63) (252 bytes) stays under 256 and is in place. VARCHAR(70) is 280 bytes, which needs a 2-byte prefix, so every row would change: ERROR 1846. The rule from the docs: in-place widening works only if the column stays on the same side of the 256-byte line — and the byte size depends on the character set (×4 for utf8mb4).
total_row_versions is 2: one per instant ADD COLUMN since the table was last rebuilt. MySQL 9.7 allows 255 row versions; after that, instant add/drop is refused with ERROR 4092 … Maximum row versions reached until a rebuild (ALTER TABLE … FORCE or OPTIMIZE TABLE) resets the counter. The 8.0 manual gives a limit of 64.

AdvancedTools: gh-ost and pt-online-schema-change

For changes MySQL can't do in place on huge tables, teams use external tools. They weren't run here (they need a real server topology and hours of table to be worth it), but the idea is worth knowing:

pt-online-schema-change (Percona Toolkit) creates an empty copy of the table with the new definition, adds triggers on the original that mirror every insert/update/delete into the copy, copies the existing rows in small chunks, then atomically swaps the two with RENAME TABLE.
gh-ost (GitHub) does the same copy-in-chunks and swap, but instead of triggers it reads the changes from the binary log, so the original table carries no extra write load, and it can be paused and throttled when replicas lag.

Both still need a brief metadata lock for the final swap, so the advice above about long transactions applies. Many teams now use MySQL's own INSTANT/INPLACE first and reach for these tools only for genuine rewrites.

AdvancedPractice: expand, migrate, contract

Predict what happens

A developer runs this on a busy 200 GB orders table during the day, with no ALGORITHM clause:

SQL

ALTER TABLE orders MODIFY status VARCHAR(20) NOT NULL;

status is currently ENUM('pending','paid','shipped','cancelled'). What does MySQL do?

Show answer

It quietly picks ALGORITHM=COPY: it rebuilds the whole 200 GB table row by row, converting each ENUM index to text, and blocks all writes to orders until it's done — likely hours. Reads continue (the copy takes a shared lock), but nobody can place an order.

What’s happening

Changing an ENUM to a VARCHAR changes the stored format of every row (a 1-byte index becomes a length-prefixed string), so neither INSTANT nor INPLACE is possible.
Without an ALGORITHM clause MySQL doesn't fail; it falls back to the cheapest algorithm that works — COPY — with the lock that requires, LOCK=SHARED.
Had the developer written ALGORITHM=INPLACE, LOCK=NONE, MySQL would have refused immediately with ERROR 1846, and they'd have known to use gh-ost or pt-online-schema-change, or an expand/migrate/contract plan, instead.
Even before the copy starts, the ALTER needs the metadata lock — so one forgotten open transaction would first queue all traffic behind it.