Keys and column types

The decision

InnoDB stores every table as a B-tree ordered by its primary key, and every secondary index entry carries a copy of that key. The key you pick in CREATE TABLE therefore sets the size and write pattern of the whole table and of every index on it. Column types are just as hard to undo: changing a column's data type is ALGORITHM=COPY only, which rebuilds the table and blocks writes while it runs. Get them wrong and the cost shows up months later: an INT key that stops at 2147483647, a UUID key that makes every index larger than it needs to be, emoji rejected by utf8mb3, a TIMESTAMP that cannot hold 2039, totals that do not add up, and rows that nothing stopped. This skill fixes the default for keys, character sets, times, money, SQL mode, constraints and JSON.

The rule

1. Give every InnoDB table a short, insert-ordered primary key, by default BIGINT UNSIGNED AUTO_INCREMENT, never INT, CHAR(36) or a random UUID.
2. When the key must be a UUID, store it as BINARY(16): either UUID() written with UUID_TO_BIN(u, 1) and read with BIN_TO_UUID(id, 1) everywhere, or a time-ordered UUIDv7 generated by the application and stored with UUID_TO_BIN(u).
3. Declare DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci on every table (a _bin collation for case-sensitive tokens), never utf8mb3 or its alias utf8.
4. Store points in time as DATETIME(6) in UTC (DEFAULT (UTC_TIMESTAMP(6)), or every connection on time_zone = '+00:00'), never TIMESTAMP and never local wall-clock time.
5. Store money as DECIMAL(p, s) (or BIGINT minor units), never FLOAT or DOUBLE.
6. Keep strict SQL mode on, declare every column NOT NULL unless "unknown" is a real state, and enforce value rules with named CHECK constraints.
7. Declare a FOREIGN KEY for every reference, pointing at a PRIMARY KEY or UNIQUE key of the parent.
8. Use JSON only for attributes with no fixed shape, and promote any key you filter, join, sort or constrain to a typed column, or to a generated column with an index.

Why

Rule 1. "If there is no logical unique and non-null column or set of columns to use a the primary key, add an auto-increment column", and "each record in a secondary index contains the primary key columns for the row … If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key" (clustered and secondary indexes). Page fill depends on insert order: "If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full" (physical structure of an InnoDB index). Measured in this skill's examples with 200000 rows on 8.4 and 9.7:

The CHAR(36) key is inserted in order here and is still 60% larger, from length alone, and every secondary index pays it again. Random keys also spread inserts over the whole tree, so the working set is the whole index rather than its right edge. INT stops at 2147483647. The bad example sets the counter there and the next insert fails with a duplicate key (23000). Widening to BIGINT later is a data type change, which "is only supported with ALGORITHM=COPY" (online DDL operations), and every child column must be widened with it. Changing the primary key itself "always requires copying of table data" (same page).

Rule 2. UUID() "returns a value that conforms to UUID version 1", as a 36-character utf8mb3 string. With UUID_TO_BIN(u, 1), "the time-low and time-high parts … are swapped. This moves the more rapidly varying part to the right and can improve indexing efficiency", and you "should also pass a second argument of 1 to BIN_TO_UUID()" to get the original back. For UUIDs "that do not follow version 1 format, time-part swapping provides no benefit" (miscellaneous functions). The 8.4 manual documents no UUIDv7 function, so a v7 key comes from the application, and it is already time-ordered, so you store it without the swap. Without the swap, a v1 key's first 4 bytes wrap every 429 seconds. A burst of UUID() calls in a test never wraps, so the bad example builds v1 values for one row per second over 2.3 days. Unswapped, they need 971 leaf pages; swapped, 751. In the good example, 200000 swapped keys need 619 leaf pages against 513 for BIGINT. That is only the 8 extra bytes per row. A key stored with one flag and read with the other decodes to a different UUID, so put the flag in one data-access helper.

Rule 3. utf8mb4 "supports BMP and supplementary characters", while for a supplementary character such as an emoji "utf8mb3 cannot store the character at all" (utf8mb4). utf8mb3 "is deprecated" and you should "expect utf8mb3 to be removed in a future major release" (utf8mb3). In strict mode the bad example's emoji fails with error 1366. The server default is "utf8mb4 and utf8mb4_0900_ai_ci" (server character set). Declare it on the table anyway, so a database or server with another default cannot change your columns. _ai_ci is accent- and case-insensitive, so the good example's unique key refuses ANA@example.com when ana@example.com exists. For API tokens and hashes, where case matters, use a _bin collation or BINARY/VARBINARY.

Rule 4. "TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC", while DATETIME runs to '9999-12-31 23:59:59'. "MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)" (DATE, DATETIME, and TIMESTAMP). The bad example's trial ending in 2039 fails with 22007, and with strict mode off it is stored as 0000-00-00 00:00:00. DATETIME does no conversion, so whoever writes it must write UTC. DEFAULT CURRENT_TIMESTAMP writes the session's local time: one instant from -03:00 and +09:00 is stored 12 hours apart. DEFAULT (UTC_TIMESTAMP(6)) stores both as 12:00. Expression defaults may use nondeterministic built-in functions (data type defaults).

Rule 5. DECIMAL types "are used when it is important to preserve exact precision, for example with monetary data" (fixed-point types). FLOAT and DOUBLE "represent approximate numeric data values", and "attempts to treat them as exact in comparisons may lead to problems" (floating-point types). Ten orders of 0.10 sum to exactly 1.00 as DECIMAL(12, 2) and to 0.9999999999999999 as DOUBLE.

Rule 6. The 8.4 default sql_mode includes STRICT_TRANS_TABLES. With it, "an error occurs for invalid or missing values … The statement is aborted and rolled back". Without it, "MySQL inserts adjusted values for invalid or missing values and produces warnings" (server SQL modes). With sql_mode = '', the bad example cuts an email to 20 characters, turns a 2039 TIMESTAMP into zeros and stores a total of 'abc' as 0, all with only a warning. Strict mode refuses each one (22001, 22007, error 1366). CHECK constraints are enforced "as of MySQL 8.0.16"; before that they were "parsed and ignored" (CHECK constraints, 8.0). A violation is error 3819 (SQLSTATE HY000). The expression "must evaluate to TRUE or UNKNOWN (for NULL values)" (CHECK constraints), so the bad example's nullable total passes CHECK (total >= 0) with NULL and then drops out of both total >= 0 and total < 0 in reports. NOT NULL refuses it (23000, error 1048).

Rule 7. Without a foreign key, the bad example stores an order for customer 999, who does not exist. With one, the insert fails with 23000 (error 1452). InnoDB needs an index on the referencing columns, and "such an index is created on the referencing table automatically if it does not exist", though it "might be silently dropped later if you create another index that can be used to enforce the foreign key constraint" (FOREIGN KEY constraints). The good example checks that orders.customer_id leads an index. Referencing a non-unique or partial key is "deprecated", and restrict_fk_on_non_standard_key, ON by default in 8.4, "prevents the use of non-unique keys or partial keys as foreign keys" (system variables). A schema copied from 8.0 fails with error 6125, and turning the variable off only trades that error for a deprecation warning.

Rule 8. JSON gives "automatic validation" and a binary format that "need not be parsed from a text representation", but JSON columns "are not indexed directly; instead, you can create an index on a generated column that extracts a scalar value" (JSON type). A secondary index on a VIRTUAL generated column materializes values only in the index. It "may be preferable to generated stored columns, which are materialized in the clustered index" (secondary indexes and generated columns). In the good example, both WHERE channel = 'web' and WHERE attributes->>'$.channel' = 'web' use orders_channel_idx. The bad example scans the table for the same filter and accepts "total": "abc" inside the document, which the typed DECIMAL column refuses.

Good example

See examples/good/test.sql.

CREATE TABLE orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(16) NOT NULL,
  total DECIMAL(12, 2) NOT NULL,
  placed_at DATETIME(6) NOT NULL DEFAULT (UTC_TIMESTAMP(6)),
  attributes JSON NOT NULL DEFAULT (JSON_OBJECT()),
  -- The one JSON key that queries filter on, promoted to an indexed column.
  channel VARCHAR(20) GENERATED ALWAYS AS (attributes->>'$.channel') VIRTUAL,
  CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id),
  CONSTRAINT orders_status_chk CHECK (status IN ('pending', 'paid', 'shipped')),
  CONSTRAINT orders_total_chk CHECK (total >= 0),
  KEY orders_channel_idx (channel)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE events_uuid (
  id BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
  payload VARCHAR(40) NOT NULL
);
CALL assert_true((SELECT BIN_TO_UUID(id, 1) FROM events_uuid WHERE payload = 'round trip') = @u,
  'BIN_TO_UUID(id, 1) returns the UUID that UUID_TO_BIN(u, 1) stored');

The test asserts that:

• an emoji is stored as one 4-byte character, the column collation is utf8mb4_0900_ai_ci, and the unique key refuses an email that differs only in case;
• 09:00 at -03:00 and 21:00 at +09:00 are stored as one value, 12:00 UTC, and a date in 2040 is accepted;
• ten totals of 0.10 sum to exactly 1.00;
• strict mode is on, and a NULL total (23000), a negative total, a misspelled status, an empty email (HY000, error 3819), a total of 'abc' (error 1366), a 300-character email (22001), an unknown customer (23000) and invalid JSON (22032) are all refused;
• orders.customer_id leads an index that InnoDB created for the foreign key;
• the JSON key filter, written either way, uses orders_channel_idx;
• 200000 swapped UUID keys need less than 1.3 times the leaf pages of 200000 BIGINT keys, and BIN_TO_UUID(id, 1) gives back the original UUID.

Bad example

See examples/bad/test.sql.

-- MISTAKE: INT AUTO_INCREMENT stops at 2147483647, and widening it later copies the table.
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  -- MISTAKE: utf8mb3 (the old "utf8") cannot store 4-byte characters such as emoji.
  name VARCHAR(100) CHARACTER SET utf8mb3,
  email VARCHAR(20),
  -- MISTAKE: DATETIME DEFAULT CURRENT_TIMESTAMP records the session's local wall-clock time.
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  -- MISTAKE: TIMESTAMP ends at 2038-01-19 03:14:07 UTC.
  trial_ends_at TIMESTAMP NULL
);
-- MISTAKE: UUID_TO_BIN(UUID()) without the swap flag puts the fast-changing time-low first.
CREATE TABLE ev_v1_unswapped (id BINARY(16) PRIMARY KEY, email VARCHAR(40) NOT NULL);

The orders table in the same file has a CHAR(36) key, a DOUBLE total with a CHECK but no NOT NULL, no foreign key, and business fields only in JSON. The assertions show the costs:

• the INT key fails with a duplicate key after 2147483647;
• utf8mb3 refuses an emoji, and TIMESTAMP refuses 2039 (22007);
• DATETIME DEFAULT CURRENT_TIMESTAMP stores one instant 12 hours apart;
• with sql_mode = '', an email is cut to 20 characters, a 2039 TIMESTAMP becomes 0000-00-00 00:00:00, and 'abc' becomes a total of 0;
• DOUBLE sums ten 0.10 to less than 1, a NULL total passes the CHECK and drops out of reports, and an orphan order is stored;
• JSON accepts "total": "abc", and filtering on a JSON key scans the whole table;
• a foreign key to a non-unique key fails on 8.4+ (error 6125), and switching the check off gives a deprecation warning;
• with 200000 rows, a CHAR(36) key makes the primary key and the email index over 50% larger than with BIGINT, random 16-byte keys need over 30% more leaf pages than ordered ones, and unswapped v1 UUIDs over 20% more than swapped ones.

Checklist

When reviewing schema or SQL under this skill, confirm:

• Every table has an explicit primary key, and every surrogate key is BIGINT UNSIGNED AUTO_INCREMENT (with BIGINT UNSIGNED referencing columns), with no INT, SMALLINT or CHAR(36) keys.
• Every UUID key is BINARY(16) and is either UUID_TO_BIN(UUID(), 1) with BIN_TO_UUID(id, 1) on every read and lookup, or an application-generated UUIDv7, never a random version 4 value.
• Every table declares utf8mb4 with an explicit collation, and no column uses utf8mb3 or utf8.
• No column is TIMESTAMP, and every DATETIME instant is written in UTC (UTC_TIMESTAMP(6) or a +00:00 session), not with DEFAULT CURRENT_TIMESTAMP on a local-time connection.
• Every money column is DECIMAL(p, s) or an integer of minor units, and none is FLOAT or DOUBLE.
• sql_mode keeps STRICT_TRANS_TABLES in the server config, the parameter group and every connection string or ORM setting.
• Every nullable column has a written reason why "unknown" is a valid state, and every enumerated or ranged value has a named CHECK.
• Every column that holds another table's id has a FOREIGN KEY to that table's PRIMARY KEY or a UNIQUE key, and no migration sets restrict_fk_on_non_standard_key = OFF.
• No JSON key is used in WHERE, JOIN, ORDER BY or a uniqueness rule without a typed column or an indexed generated column.

When not to apply

• Identifiers created outside the database. When clients, other services or offline devices create ids before insert, use an application-generated UUIDv7 in BINARY(16). If the key must not reveal creation time, keep BIGINT as the primary key and expose a random token in a separate UNIQUE column, because a v1 or v7 UUID contains its creation time.
• Natural keys that are short and stable. A lookup table keyed by an ISO 4217 currency code can use CHAR(3) with a _bin collation or a CHECK. It is shorter than a surrogate key and never changes.
• Wall-clock values that are not instants. A store's opening time or a "09:00 local" reminder is a TIME or DATETIME plus a separate time zone column, because the instant changes with the zone's rules.
• Pre-2038 audit columns on an existing schema. A legacy TIMESTAMP column holding only past events works until 2038. Migrate it on your next planned rebuild rather than with an urgent ALGORITHM=COPY.
• High-volume measurements. Sensor readings or metrics where small rounding errors are acceptable may use DOUBLE. Never use it for amounts that are billed or reconciled.
• Staging tables. A raw import table can be loose, as long as the copy into the real table applies every constraint.
• Payloads stored as received. A webhook body kept for audit can be JSON with no generated columns, as long as no business logic queries inside it.