Ошибка «ERROR 1366 (22007): Incorrect string value» в MySQL / MariaDB (РЕШЕНО)

Как вставить эмодзи (emoji) в базу данных MySQL / MariaDB

С одной стороны, вставка эмодзи в таблицу базы данных MySQL / MariaDB не требует каких-то специальных подготовительных действий — достаточно просто вставить один или несколько символов эмодзи, которые могут содержать другой текст. Например:

INSERT INTO TestTABLE (`test_column`) VALUES ("🎫⏭️✈️ and hi 🌅");

Но при выполнении предыдущего запроса вы можете столкнуться с ошибкой:

ERROR 1366 (22007): Incorrect string value: '\xF0\x9F\x8E\xAB\xE2\x8F...' for column `TestDB1`.`TestTABLE`.`test_column` at row 1

Почему возникает ошибка «ERROR 1366 (22007): Incorrect string value»

Эмодзи — это символы, которые используют четыре байта кодировки UTF-8. Напомню, что UTF-8 это кодировка переменной длины, и MySQL / MariaDB позволяют использовать наборы символов utf8mb3 и utf8mb4, которые, соответственно, используют 3 и 4 байта на кодовую точку.

Сейчас utf8mb3 считается скорее устаревшим набором символов и при создании баз данных, а также таблиц, рекомендуется использовать только набор символов utf8mb4.

Смотрите подробности в статье: В чём разница между кодировками utf8_general_ci, utf8_unicode_ci, utf8mb4_general_ci, utf8mb4_unicode_ci. Какую кодировку выбрать для базы данных MySQL

Как исправить ошибку «ERROR 1366 (22007): Incorrect string value»

1. Для хранения текстов, в которых встречаются эмодзи, необходимо не только выбрать кодировку UTF-8, но и также выбрать набор символов utf8mb4. Это необходимо сделать для базы данных в целом, а также таблиц и столбцов.
2. Если база данных использует utf8mb4, то даже в этом случае необходимо указать правильный набор символов в свойствах подключения к серверу MySQL / MariaDB.

Ниже все эти вопросы рассмотрены подробнее.

Как проверить, что база данных и таблица используют набор символов utf8mb4

Чтобы проверить, какой набор символов использует база данных, выполните запрос вида (замените _TestDB_ на имя базы данных):

SHOW CREATE DATABASE _TestDB_;

Пример вывода:

CREATE DATABASE `TestDB` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */

Как можно убедиться, набор символов установлен на utf8mb4, а параметры сравнения (collation) установлены на utf8mb4_unicode_ci. Это хорошо. Но, на самом деле, таблицы могут использовать свой собственный набор символов. Более того, даже отдельные колонки одной таблицы могут иметь свои собственные значения используемого набора символов.

В моей практике я столкнулся с тем, что база данных была создана с указанием набора символов utf8 и параметрами сортировки utf8_unicode_ci, но это не помешало импортировать таблицы с набором символов utf8mb4 и параметрами сортировки utf8mb4_unicode_ci. На что тогда влияет выбор набора символов базы данных и таблиц, если эти значения можно переопределить для отдельных столбцов? Мне это не до конца понятно, возможно, эти данные используются в качестве значений по умолчанию при создании новых таблиц.

Чтобы проверить, какой набор символов использует таблица, выполните запрос вида (замените _TestTABLE_ на имя таблицы:):

SHOW CREATE TABLE _TestTABLE_;

Пример вывода:

CREATE TABLE `TestTABLE` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_column` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

В качестве набора символов по умолчанию указано значение utf8mb4. Это означает следующее:

• колонки также должны использовать набор символов utf8mb4, если это не изменялось после создания таблицы
• такая таблица может хранить эмодзи

Как поменять набор символов таблицы и полей таблицы на utf8mb4

Если набор символов отличен от utf8mb4 (например, используется utf8 или что-то ещё), то для начала вы можете установить набор символов utf8mb4 для базы данных в целом, это делается запросом вида:

ALTER DATABASE _TestDB_ CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Хотя, как уже было сказано выше, это не особо влияет на содержимое уже созданных таблиц.

Чтобы изменить набор символов таблицы по умолчанию, а также используемый набор символов всех текстовых столбцов (CHAR, VARCHAR, TEXT) на новый набор символов, используйте выражение вида (замените _TestTABLE_ на имя таблицы):

ALTER TABLE _TestTABLE_ CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Этот оператор также изменяет параметры сортировки (collation) всех символьных столбцов.

Итак, изменение набора символов отдельных столбцов на utf8mb4 не требуется. Но вы можете это сделать, в том числе для избежания автоматического преобразования типа данных столбца.

Для столбца с типом данных VARCHAR или одного из типов TEXT команда CONVERT TO CHARACTER SET изменяет тип данных по мере необходимости, чтобы гарантировать, что новый столбец имеет достаточную длину для хранения такого же количества символов, как и исходный столбец. Например, столбец TEXT имеет два байта длины, в которых хранится длина значений в столбце, максимум до 65 535. Для столбца Latin1 TEXT каждому символу требуется один байт, поэтому столбец может хранить до 65 535 символов. Если столбец преобразован в utf8mb4, для каждого символа может потребоваться до 4 байтов, при максимально возможной длине 4 × 65 535 = 262 140 байт. Эта длина не соответствует длине столбца TEXT в байтах, поэтому MySQL преобразует тип данных в MEDIUMTEXT, который является наименьшим строковым типом, для которого длина в байтах может записывать значение 262 140. Аналогичным образом столбец VARCHAR может быть преобразован в MEDIUMTEXT.

Чтобы избежать только что описанных изменений типа данных, не используйте CONVERT TO CHARACTER SET. Вместо этого используйте MODIFY для изменения отдельных столбцов. Например:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8mb4;

ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8mb4;

Как создать таблицу, использующую набор символов utf8mb4

Чтобы таблица и её символьные колонки использовали utf8mb4, добавьте к запросу создания таблицы следующую строку:

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Например:

CREATE TABLE IF NOT EXISTS `TestDB`.`test_table` (`id` int(11) NOT NULL AUTO_INCREMENT, `test_column` text COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

База данных и таблица использует utf8mb4, но всё равно возникает ошибка «ERROR 1366 (22007): Incorrect string value»

Если с базой данной и хранящимися в них данными всё в порядке, то перед выполнением запросов, включающих эмодзи, выполните следующий запрос:

SET NAMES utf8mb4;

Этот оператор устанавливает три системные переменные сеанса: character_set_client, character_set_connection и character_set_results в заданный набор символов.

Как можно убедиться, теперь ошибка исчезла.

Мы можем убедиться, что новые данные действительно вставлены в таблицу:

SELECT * FROM TestTABLE;

Примечание: эмодзи в консоли могут отображаться не совсем корректно — видимо, это связано с особенностями терминала, либо отсутствием необходимых шрифтов. Тем не менее несмотря на то, что на скриншоте эмодзи как бы наползают друг на друга, а некоторые просто отображаются неправильно, в веб-брауезере или текстовом редакторе они выглядят корректно.

Как установить в настройках набор символов по умолчанию

Вы можете указать набор символов по умолчанию в троке команды с помощью опции --default-character-set. «Набор символов по умолчанию» означает, что будет использоваться указанная кодировка, если она не была переопределена другими запросами.

Пример команд для подключения к серверу MySQL и MariaDB, при этом подключение будет использовать набор символов utf8mb4:

mariadb -u root --default-character-set=utf8mb4
mysql -u root --default-character-set=utf8mb4

Если вы хотите всегда использовать набор символов utf8mb4, но не хотите постоянно выполнять запрос на его выбор или указывать в строке команды, то вы можете выбрать набор символов по умолчанию в конфигурационном файле.

Для этого откройте файл /etc/my.cnf:

sudo gedit /etc/my.cnf

И добавьте туда следующие строки:

[client]
default-character-set=utf8mb4

Смотрите также: Как определить расположение и имя конфигурационного файла MySQL (MariaDB). Как узнать название групп для конфигурационных файлов MySQL и MariaDB

Вы можете убедиться, что данная настройка действительно имеет действие с помощью следующей команды:

mariadb --help | grep -i ^default-character-set

В результате будет выведено значение переменной окружения default-character-set:

default-character-set             utf8mb4

Как узнать, какой символ вызывает ошибку «ERROR 1366 (22007): Incorrect string value»

Возможно, вы не хотите ничего менять ни в базах данных, ни в подключении к СУБД, то, как альтернативный вариант, вы можете удалить проблемный символ из текста.

Рассмотрим ошибку:

ERROR 1366 (22007): Incorrect string value: '\xF0\x9F\x8E\xAB\xE2\x8F...' for column `TestDB1`.`TestTABLE`.`test_column` at row 1

Часть строки со значениями начинающимися с «\x» это шестнадцатеричное представление эмодзи (или другого символа в четёрыхбайтной кодировке). То, что байт более четырёх, говорит о том, что символов эмодзи несколько. Чтобы увидеть первый символ, скопируйте первые четыре байта и запустите команду вида:

printf '_4 BYTES HERE_'

Например:

printf '\xF0\x9F\x8E\xAB'
🎫