UTF-8 in MySQL
Tags: unicode, utf, mysql
Published: 2023-09-24 21:10:06

This series is supposed to be focused on technical aspects of Unicode and I do not plan to analyze UTF support in various technologies. However for MySQL I want to make an exception, because I've seen countless examples of misunderstanding its concepts and falling into traps.

## Character Set vs Collation

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32)
) Engine=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
```

* Character set tells <u>how data will be stored</u> in binary form.
* Collation tells <u>how data will be compared</u>. It was explained in [this post of the series](https://dev.to/bbkr/utf-8-sorting-and-collation-p35).

Later I will explain what those cryptic names mean.

## Property inheritance

Character set and/or collation can be specified on 7 (yes, seven!) different levels.

* Server

```
MySQL > SELECT @@character_set_server, @@collation_server;
+------------------------+--------------------+
| @@character_set_server | @@collation_server |
+------------------------+--------------------+
| utf8mb4                | utf8mb4_0900_ai_ci |
+------------------------+--------------------+
```

Those are your global settings that will be used when creating databases. So:

```sql
CREATE DATABASE `test`;
```

Is the same as:

```sql
CREATE DATABASE `test` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

Those server settings are **copied** when database is created, so changing server settings later **will not** affect your databases.

* Database

```sql
CREATE DATABASE `test` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```

It is just another level of default, this time applied to created tables. So:

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32)
) Engine=InnoDB;
```

Is the same as:

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32)
) Engine=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
```

And just like server settings those are also **copied** when tables are created. Altering database with `ALTER DATABASE test CHARACTER SET xxx COLLATE yyy` **will not** alter tables in this database.

You can check currently used database character set and collation either from variables:

```
MySQL [test]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_ai_ci   |
+--------------------------+----------------------+
```

Or from information schema:

```
MySQL [test]> SELECT `default_character_set_name`, `default_collation_name`
    FROM `information_schema`.`schemata`
    WHERE `schema_name` = 'test';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_0900_ai_ci     |
+----------------------------+------------------------+
```

Note the inconsistency - `DEFAULT_CHARACTER_SET_NAME` in information schema, but `character_set_database` in variable and `CHARACTER SET` in create.

* Table

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32)
) Engine=InnoDB
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
```

It is - you guessed it - another level of defaults applied to columns. So:

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32)
) Engine = InnoDB;
```

Is the same as:

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
) Engine = InnoDB;
```

And just like database settings those are also **copied** when columns are created. Altering table with `ALTER TABLE foo CHARACTER SET xxx COLLATE yyy` **will not** alter columns in this table.

However this time tool is available for convenient conversion - `ALTER TABLE foo CONVERT TO CHARACTER SET xxx COLLATE yyy` **will** alter both table defaults and columns in this table.

You can check table collation in information schema:

```
MySQL [test]> SELECT `table_collation`
    FROM `information_schema`.`tables`
    WHERE `table_schema` = 'test'
        AND `table_name` = 'foo';
+--------------------+
| TABLE_COLLATION    |
+--------------------+
| utf8mb4_0900_ai_ci |
+--------------------+
```

Note another inconsistency - this time `TABLE_COLLATION` implies character set, which is not given explicitly. Also it inconsistent with database level naming, being a default but missing `DEFAULT_` prefix.

If you want to retrieve implied character set there is another information schema resource to do so:

```
MySQL > SELECT `character_set_name`
    FROM `information_schema`.`character_sets`
    WHERE `default_collate_name` = 'utf8mb4_0900_ai_ci';
+--------------------+
| CHARACTER_SET_NAME |
+--------------------+
| utf8mb4            |
+--------------------+
```

* Column

Finally, this is the "true" thing. That is how data will be stored and sorted. Server, database and table levels were <u>only the defaults</u> used for column creation.

You can check column character set and collation from information schema:

```
MySQL [test]> SELECT `character_set_name`, `collation_name`
    FROM `information_schema`.`columns`
    WHERE `table_schema` = 'test'
        AND `table_name` = 'foo'
        AND `column_name` = 'bar';
+--------------------+--------------------+
| CHARACTER_SET_NAME | COLLATION_NAME     |
+--------------------+--------------------+
| utf8mb4            | utf8mb4_0900_ai_ci |
+--------------------+--------------------+
```

Yes, you can have different character sets and collations within single table:

```sql
CREATE TABLE `foo` (
    `bar` VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    `baz` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_general_ci
) Engine = InnoDB;
```

I will give examples when it may be useful once all those cryptic names are explained.

My advice is: always provide character set and collation when creating databases, tables and columns. I've seen this too many times - developers adding tables without checking which character set and collation will be inherited from database. Or adding columns without checking which character set and collation will be inherited from table. Being more explicit = having less headache later.

* System

```
MySQL > SELECT @@character_set_system;
+------------------------+
| @@character_set_system |
+------------------------+
| utf8mb3                |
+------------------------+
```

This is global character set for metadata. It tells what characters you can use in schema names:

```sql
CREATE TABLE `łąka` (
    `bąki` int unsigned
) Engine = InnoDB
```

It is **not** part of inheritance chain Server -> Database -> Table -> Column.

* Connection

```
MySQL > SELECT @@character_set_connection, @@collation_connection;
+----------------------------+------------------------+
| @@character_set_connection | @@collation_connection |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_general_ci     |
+----------------------------+------------------------+
```

Those are wire protocol information. Character set tells meaning of transferred data, for example `0xF0 0x9F 0x98 0x8A` sent or received means 😊. Collation will be used for comparing/sorting data not derived from any column, for example bare `SELECT 'A' = 'a'`.

Connection and Column character set may not be aligned, but it will fail if Connection wire protocol cannot transfer code points encoded in Columns. Best practice is to always use `utf8mb4`.

* Query

```sql
SELECT *
FROM `foo`
ORDER BY `bar` COLLATE utf8mb4_estonian_ci;
```

You can override default column collation for ordering / grouping within SELECT query. This is useful when different alphabets sorts the same characters differently.

```
MySQL > CREATE TABLE `collation_test` (`data` text) Engine = InnoDB;

MySQL > INSERT INTO `collation_test` (`data`)
    VALUES ("A"), ("Ä"), ("Z");

MySQL > SELECT *
    FROM `collation_test`
    ORDER BY `data` COLLATE utf8mb4_sv_0900_as_cs;
+------+
| data |
+------+
| A    |
| Z    |
| Ä    |
+------+

MySQL > SELECT *
    FROM `collation_test`
    ORDER BY `data` COLLATE utf8mb4_es_0900_as_cs;
+------+
| data |
+------+
| A    |
| Ä    |
| Z    |
+------+
```

## Character set `utf8` vs `utf8mb4`

MySQL cheated in the past. They added character set `utf8` but it was capable only of handling up to 3 byte code points.

```
MySQL [test]> CREATE TABLE `foo` ( `bar` CHAR(1) )
    Engine = InnoDB
    CHARACTER SET = utf8;

MySQL [test]> INSERT INTO `foo` (`bar`) VALUES ('😊');
ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x98\x8A' for column 'bar' at row 1
```

They did it however in good faith - back then 4 byte code points were not used. Indexes are constructed in such a way, that they must assume maximum byte length of a string. Maximum supported index byte length was 767 bytes, which allowed to index columns up to `CHAR(255)` - because `255*3=765` was fitting into index. For 4 byte code points maximum indexable column would be only `CHAR(191)`.

Later MySQL added `utf8mb4` character set capable of storing proper 4 byte code points. Legacy `utf8` was aliased as `utf8mb3`. Default maximum supported index byte length was also extended in MySQL 8 to 3072 bytes, allowing to index columns up to `VARCHAR(768)`.

Today MySQL tries to fix this technical debt, and if you specify character set as `utf8` you will get following warning: `'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.`

But how to index longer UTF-8 columns? Common trick is to use hash indexing:

```sql
CREATE TABLE `foo` (
    `bar` varchar(1000),
    `bar_hash` CHAR(32),
    KEY (`bar_hash`)
) ENGINE = InnoDB
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_0900_ai_ci;

CREATE TRIGGER `foo_insert`
BEFORE INSERT ON `foo`
FOR EACH ROW SET NEW.`bar_hash` = MD5( WEIGHT_STRING( NEW.`bar` ) );

CREATE TRIGGER `foo_update`
BEFORE UPDATE ON `foo`
FOR EACH ROW SET NEW.`bar_hash` = MD5( WEIGHT_STRING( NEW.`bar` ) );
```

Function `WEIGHT_STRING` is super useful, because it converts text to format used by collation.  Function `MD5` reduces too long texts always to 32 bytes HEX representation.

Now you can for example create `UNIQUE KEY` on column `bar_hash` or use it in query:

```
SELECT *
FROM `foo`
WHERE `bar_hash` = MD5( WEIGHT_STRING( 'looked up text' ) );
```

## Collation `utf8mb4_0900_ai_ci`

MySQL 8 did huge cleanup in collation naming. `utf8mb4_0900_ai_ci` means that it is collation of 4 byte UTF-8 done by Unicode 9.0 standard in accent (diacritic) insensitive and case insensitive manner.

It **does not** mean that database cannot store characters from Unicode version 10 onward. As I [explained previously](https://dev.to/bbkr/utf-8-internal-design-5c8b) UTF-8 is designed in such a way, that storage is independent from versioning. Just comparison rules from Unicode version 9.0 will be used. That pretty much means recent ones, because almost nothing new was declared in this aspect later.

Accent / case insensitivity is up to you to decide. Basically you have 3 options:

- `utf8mb4_0900_ai_ci` - Accent and case insensitive, `'a' = 'A' = 'ą' = 'Ą'`.
- `utf8mb4_0900_as_ci` - Accent sensitive but case insensitive, `'a' <> 'ą'` but still `'a' = 'A'` and `'ą' = 'Ą'`.
- `utf8mb4_0900_as_cs` - Accent and case sensitive, `'a' <> 'A' <> 'ą' <> 'Ą'`.

Remember that you can mix them. For example unique column for `login` may have collation `utf8mb4_0900_ai_ci` so `Józef`, `józef` and `jozef` are treated as the same user. While column `hobby` may have collation `utf8mb4_0900_as_ci` because `baki` (fuel tanks) and `bąki` (bumble bees) are not the same.

You can list all `utf8mb4` related collations by following query:

```sql
SHOW COLLATION WHERE Charset = 'utf8mb4';
```

Best practice is to stick with `utf8mb4_0900_*` set and avoid alphabet specific collations in columns. For example if you know your user is from Poland you can always use more friendly collation in query, ignoring column one:

```sql
SELECT `name`
FROM `products`
ORDER BY `name` COLLATE utf8mb4_pl_0900_ai_ci
```

Also avoid legacy collations like `utf8mb4_general_ci`, use only those with `*_0900_*` within name.

## Triggers, Procedures, Functions

Things are weird for triggers, because they inherit character set and collation from... definer's connection. I won't go much into details here because it rarely bites the developer. Just remember to also drop / create them if you are migrating from old databases to new character set and collation. For full description of consequences read MySQL 5.1.21 change log.

**Coming up next:** Series wrap up.