Storage engines and character sets

Two settings that shape every table

When you create a table, MySQL attaches two settings you may never have noticed: the storage engine (how data is physically stored) and the character set and collation (how text is encoded, compared and sorted). The defaults are good, but understanding them prevents some nasty surprises.

Storage engines

The part of MySQL that reads and writes data on disk is pluggable. Different engines make different trade-offs. See which are available and what they support:

SELECT ENGINE, SUPPORT, TRANSACTIONS, XA, SAVEPOINTS
FROM information_schema.ENGINES
WHERE ENGINE IN ('InnoDB', 'MyISAM', 'MEMORY')
ORDER BY ENGINE;

+--------+---------+--------------+------+------------+
| ENGINE | SUPPORT | TRANSACTIONS | XA   | SAVEPOINTS |
+--------+---------+--------------+------+------------+
| InnoDB | DEFAULT | YES          | YES  | YES        |
| MEMORY | YES     | NO           | NO   | NO         |
| MyISAM | YES     | NO           | NO   | NO         |
+--------+---------+--------------+------+------------+

Check which engine your tables use:

SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shop' ORDER BY TABLE_NAME;

+-------------+--------+
| TABLE_NAME  | ENGINE |
+-------------+--------+
| categories  | InnoDB |
| customers   | InnoDB |
| departments | InnoDB |
| employees   | InnoDB |
| order_items | InnoDB |
| orders      | InnoDB |
| products    | InnoDB |
+-------------+--------+

What InnoDB gives you

• Transactions with commit and rollback (see the previous lesson).
• Foreign keys to protect relationships.
• Row-level locking, so many sessions can write to different rows of one table at once.
• Crash recovery: after a power cut, committed data is restored from the redo log.
• MVCC (multi-version concurrency control), so readers do not block writers.

You can choose an engine per table with ENGINE=..., but unless you have an unusual reason, leave it as InnoDB.

Seeing the difference

A rollback shows it clearly. Make one table of each kind and try to undo an insert:

CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS t_innodb, t_myisam;
CREATE TABLE t_innodb (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=InnoDB;
CREATE TABLE t_myisam (id INT PRIMARY KEY, note VARCHAR(20)) ENGINE=MyISAM;

START TRANSACTION;
INSERT INTO t_innodb VALUES (1, 'innodb row');
INSERT INTO t_myisam VALUES (1, 'myisam row');
ROLLBACK;

SELECT 'innodb' AS engine, COUNT(*) AS rows_left FROM t_innodb
UNION ALL
SELECT 'myisam', COUNT(*) FROM t_myisam;

+--------+-----------+
| engine | rows_left |
+--------+-----------+
| innodb |         0 |
| myisam |         1 |
+--------+-----------+

The InnoDB insert vanished with the rollback. The MyISAM row is still there, because that engine ignores transactions. If the server had crashed halfway through a multi-statement update on MyISAM, you could have been left with half-finished data.

To change an existing table: ALTER TABLE t_myisam ENGINE = InnoDB;. It rebuilds the table, so on a big table plan the time.

Character sets

A character set is the list of characters a column may hold and how each is encoded in bytes. Old defaults caused years of pain with accented letters and emoji. The modern answer:

• utf8mb4: real UTF-8 that stores up to 4 bytes per character. It covers every language and emoji. MySQL 8 uses it by default.
• utf8 (really utf8mb3): an older, 3-byte subset that cannot store emoji or some rare characters. Avoid it.
• latin1: Western European only.

SELECT @@character_set_server AS server_charset, @@collation_server AS server_collation;

+----------------+--------------------+
| server_charset | server_collation   |
+----------------+--------------------+
| utf8mb4        | utf8mb4_0900_ai_ci |
+----------------+--------------------+

Compare the characters and the bytes of an emoji:

SELECT CHAR_LENGTH('😀') AS characters, LENGTH('😀') AS bytes;

+------------+-------+
| characters | bytes |
+------------+-------+
|          1 |     4 |
+------------+-------+

The older three-byte set cannot hold it. Inserting into a utf8mb3 column fails in strict mode:

DROP TABLE IF EXISTS t_old, t_new;
CREATE TABLE t_old (txt VARCHAR(10)) CHARACTER SET utf8mb3;
CREATE TABLE t_new (txt VARCHAR(10)) CHARACTER SET utf8mb4;
INSERT INTO t_new VALUES ('hello 😀');
SELECT txt FROM t_new;

+------------+
| txt        |
+------------+
| hello 😀     |
+------------+

INSERT INTO t_old VALUES ('hello 😀');

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x80' for column 'txt' at row 1

Remember that the connection also has a character set. Clients should connect with utf8mb4 (most modern drivers do; PHP PDO needs charset=utf8mb4 in the DSN), otherwise text can be corrupted on the way in even if the column is fine.

Collations

A collation is a set of rules for comparing and sorting text in a character set. The name tells you the rules:

SELECT 'a' = 'A' AS same_case_ai_ci,
       'cafe' = 'café' AS same_accent_ai_ci,
       ('a' COLLATE utf8mb4_0900_as_cs) = 'A' AS same_case_cs,
       ('cafe' COLLATE utf8mb4_0900_as_cs) = 'café' AS same_accent_as;

+-----------------+-------------------+--------------+----------------+
| same_case_ai_ci | same_accent_ai_ci | same_case_cs | same_accent_as |
+-----------------+-------------------+--------------+----------------+
|               1 |                 1 |            0 |              0 |
+-----------------+-------------------+--------------+----------------+

With the default collation, 'a' equals 'A' and 'cafe' equals 'café'. That is why a search for asha finds Asha. If you need exact matching, for example for case-sensitive codes, use a _bin or _cs collation on that column, or override it in a query:

SELECT first_name FROM shop.customers WHERE first_name = 'asha' COLLATE utf8mb4_0900_as_cs;
SELECT first_name FROM shop.customers WHERE first_name = 'asha';

+------------+
| first_name |
+------------+
| Asha       |
+------------+

The first query finds nobody, because asha and Asha differ under a case-sensitive collation. The second finds Asha. Collations also decide sorting:

SELECT x FROM (SELECT 'b' AS x UNION ALL SELECT 'A' UNION ALL SELECT 'a' UNION ALL SELECT 'B') AS letters ORDER BY x COLLATE utf8mb4_bin;

+---+
| x |
+---+
| A |
| B |
| a |
| b |
+---+

With the binary collation all capitals sort before lowercase letters, which is what the byte values say. The default collation treats a and A as equal and orders them next to each other.

Setting them on databases, tables and columns

The settings cascade: server, then database, then table, then column. Set them explicitly when you create things:

DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  body TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

To convert an existing table, for example from latin1:

ALTER TABLE old_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Conversion rewrites the table, and a column that is a foreign key must match its partner exactly, including the collation. Mismatched collations are also the reason joins on text columns can give "Illegal mix of collations" errors.

Common mistakes

• Using utf8 (utf8mb3) and losing emoji or other characters.
• Connecting with the wrong client character set, which garbles text.
• Mixing collations between tables and then failing to join or compare them.
• Using MyISAM for new tables.
• Expecting case-sensitive matching under the default collation.

Exercise

Create a table codes_demo with a column code VARCHAR(10) using a case-sensitive collation (utf8mb4_0900_as_cs), insert 'ab12' and 'AB12', and show that a search for 'ab12' finds only one of them. Then show that the same data in the default collation finds both.

DROP TABLE IF EXISTS codes_demo;
CREATE TABLE codes_demo (code VARCHAR(10) COLLATE utf8mb4_0900_as_cs);
INSERT INTO codes_demo VALUES ('ab12'), ('AB12');
SELECT code FROM codes_demo WHERE code = 'ab12';
SELECT code FROM codes_demo WHERE code = 'ab12' COLLATE utf8mb4_0900_ai_ci;

+------+
| code |
+------+
| ab12 |
+------+
+------+
| code |
+------+
| ab12 |
| AB12 |
+------+