MySQL → emoji, MySQL и кодировка UTF-8

Давно уже, многие годы, знал о проблеме, что комментарии в этом блоге с эмоджами не сохраняются, как и статьи, потому что MySQL ругался чем-то вроде Incorrect string value: '\xF0\x9F\x87\xB2...' for column 'text' at row 1 и записи не сохранял. Но исправить эту штуку было просто недосуг, комментируют здесь не то, чтобы сильно часто, да и то чаще боты со спамом. А вот в новой версии движка блога, которая уже фактически запущена, это было запланировано к исправлению 😁

Когда переносил блог на новый сервер, то установил там восьмую версию мускула и проверил, сохраняет или нет. Вдруг разработчики базы данных давно уже исправили этот incorrect string value, я ведь не один такой на планете с эмоджами, нас десятки и сотни тысяч, уверен, если не миллионы. Но результат не поменялся, та же ошибка. Не прокатило, штош...

Собственно, давно уже придумал как это сделать. Точно так же я давно уже размещал эмоджи в статьях, а именно: смотрел в emojipedia, пусть будет зелёное сердце, например, 💚. В эмоджипедии вижу, что это сердце имеет в юникоде кодовую позицию U+1F49A, далее вставляю HTML-спецсимвол 💚 и готово. Оставалось только автоматизировать этот процесс. Решил только загуглить диапазон проблемных для MySQL символов, чтобы не конвертировать все подряд многобайтное, включая кириллицу.

Разочарованию не было предела, я уже настроился на программирование, а проблема с сохранением решается просто изменением кодировки. Так же становится и очевидным, почему вообще такое происходит. utf8 в MySQL является псевдонимом utf8mb3, т.е. корректными символами являются мультибайтные символы UTF-8 до 3-х байт включительно. А стандарт кодирования UTF-8 предусматривает использование от одного до четырёх байт.

1
2
3
4
5
6

# для начала меняем кодировку и метод сравнения для базы данных
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

# потом меняем кодировку для каждой таблицы, где это необходимо
ALTER TABLE db_name.table_name_1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE db_name.table_name_2 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Выше запросы к БД для изменения кодировки, заодно изменяется и метод сравнения текстовых строк, поскольку utf8_general_ci не учитывает порядок букв и для него Ä = A, Ü = U, ß = s и т.д. (Хотя если тексты у вас только на английском или русском, то разницы не будет никакой, а по слухам utf8_general_ci работает быстрее utf8_unicode_ci, вдруг у вас бигдата и эта разница будет даже заметна на глаз)

На этом можно было бы и закончить данную заметку, но раз уже упомянул в заголовке и кодировку UTF-8, то напишу пару слов и за неё. Вот таблица, показывающая как кодируются символы:

Вместо точек, которые видно в таблице выше, подставляются биты кодовой позиции юникод-символа, а старшие биты октетов (байт) UTF-8 символа позволяют определить начало многобайтной последовательности и её длину, по числу повторяющихся единиц в старших битах первого октета. У однобайтных символов получается полное соответствие с кодировкой ASCII

Продемонстрирую на примере зелёного сердца из начала статьи.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

💚 → U+1F49A

# В двоичном виде
0x1F49A (hex) → 11111010010011010 (binary)

# В виде байт закодированного в UTF-8 символа
11110000 10011111 10010010 10011010

# Или в более привычном виде
0xF0 0x9F 0x92 0x9A

Так что ничего магического в кодировке UTF-8 нет и если бы в MySQL не оказалось utf8mb4, то можно бы было выбирать из текста 4-хбайтные символы и преобразовывать их в HTML-сущности. Но не пришлось 🙂