Разрешить MySQL поддерживать хранение выражений эмодзи

MySQL

Сначала воспроизведите проблему, создайте базу данных test_db

create database test_db default charset utf8 default collate utf8_general_ci;

Создать таблицу данных

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(120) NOT NULL DEFAULT '' COMMENT '标题',
  `abstract` varchar(600) NOT NULL DEFAULT '' COMMENT '摘要',
  `created_at` int(11) NOT NULL DEFAULT '0',
  `updated_at` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='文章表'

Как набор символов и сопоставление базы данныхutf8иutf8_general_ciКогда вы создаете лист данных, хотя вы не указываете набор символов и соответствующее правило сортировки, сделана конфигурация базы данных.

использоватьPyMySQLПодключиться к базе данных, вставитьemojiвыражение

# coding=utf-8

import pymysql

connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        sql = "INSERT INTO article (title) VALUES ('😄')"
        cursor.execute(sql)

    connection.commit()
finally:
    connection.close()

После выполнения сообщили о следующей ошибке

pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x84' for column 'title' at row 1")

Эта ошибка возникает из-за того, что набор символов MySQL utf8 не может хранить выражения эмодзи.

Эй, нет, разве utf8 не является кодировкой юникода, разве она не должна поддерживать большинство символов в мире? Обычно мы говорим, что utf8 действительно такой, но utf8 в MySQL на самом деле является кастрированной версией utf8.Он использует только 3 байта для хранения символов, поэтому не может хранить выражения.Этот utf8 на самом деле является псевдонимом utf8mb3.

Если мы хотим поддерживать хранение выражений, нам нужен полный набор символов utf8, который может использовать до 4 байтов для хранения символов, имя набора символов — utf8mb4.

Поэтому для поддержки хранения выражений мы можем изменить набор символов базы данных на utf8mb4. Как это изменить?

Сначала измените столбец типа string в таблице article.

ALTER TABLE article MODIFY title varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '标题';
ALTER TABLE article MODIFY abstract varchar(600) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '摘要';

Чтобы сделать строку нового столбца также набором символов utf8mb4, мы можем изменить набор символов таблицы

ALTER TABLE article DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

ALTER DATABASE test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Вам решать, изменять ли столбцы, таблицы или библиотеки в соответствии с вашими потребностями.

Измените набор символов подключения к базе данных в сценарии и установите

charset='utf8'

изменить на

charset='utf8mb4'

Повторно запустите скрипт, вы найдете успешное.

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

При использовании клиента для подключения к базе данных мы можем указать кодировку по умолчанию

$ mysql -u root -p --default-character-set=utf8mb4

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

[client]
default-character-set=utf8mb4

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

Использование различных наборов символов для подключения к базе данных повлияет на следующие три переменные.

character_set_client
character_set_connection
character_set_results

Например, если при подключении кодировка не указана, конфигурация будет utf8mb4, а значения этих трех переменных все utf8mb4, если кодировка указана как utf8, значения этих трех переменных все утф8. Эти 3 переменные могут быть динамически изменены

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_results = utf8mb4;

Вместо этого вы также можете использовать следующую команду, функцию и три оператора выше.

SET NAMES utf8mb4;

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

Если вы выберете базу данных test_db и установите ее следующим образом

use test_db;
SET NAMES utf8;

воплощать в жизнь

INSERT INTO article (title) VALUES ('😄');

Ошибка

Incorrect string value: '\xF0\x9F\x98\x84' for column 'title' at row 1

Если вы выберете базу данных test_db и установите ее следующим образом

use test_db;
SET character_set_client = ascii;
SET character_set_connection = utf8mb4;
SET character_set_results = ascii;

воплощать в жизнь

INSERT INTO article (title) VALUES ('😄');

Мы обнаружим, что сохраненные данные искажены.

Итак, в клиенте PymySQL нам нужно указать код в UTF8MB4, чтобы скрипт работал нормально.

Как работают эти 3 переменные? Я кратко объясню.

character_set_clientОтносится к набору символов содержимого, запрошенного клиентом,character_set_connectionОтносится к набору символов, в котором сервер обрабатывает содержимое,character_set_resultsОтносится к набору символов ответа, возвращаемого сервером.

Все мы знаем, что компьютеры распознают только 0 и 1, поэтому полученный запрос или ответ, возвращенный сервером базы данных, представляет собой строку байтов. Когда сервер получает запрос, онcharacter_set_clientНабор символов декодируется, затемcharacter_set_connectionНабор символов кодируется, а затем отправляется на сервер для обработки. После завершения обработки результат снова используетсяcharacter_set_connectionкодировку для декодирования, а затем использоватьcharacter_set_resultsВозвращается набор символов для кодирования.

PS: Кодирование можно просто понять как из строки в строку байтов, декодирование наоборот.

Поэтому, когдаcharacter_set_connectionДиапазон набора символов превышаетcharacter_set_clientиcharacter_set_resultsдиапазона, можно нормально работать. Напримерcharacter_set_connectionзаutf8mb4,иcharacter_set_clientиcharacter_set_resultsобаutf8, мы выполняем

INSERT INTO article (title) VALUES ('你好');

Но эта предпосылка заключается в том, что мы просимSQLне может превышать диапазон символовcharacter_set_client, например, мы выполняем

INSERT INTO article (title) VALUES ('😄');

Данные будут искажены.

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

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

Категории