Изменение COLLATION и восстановление символов в MySQL

Быстрый ответ

Для того чтобы в короткий срок изменить набор символов и сортировочные правила всей базы данных, можно применить следующий скрипт с инструкциями ALTER TABLE. Он выполнит конвертацию в utf8mb4 и utf8mb4_unicode_ci:

SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
FROM information_schema.TABLES 
WHERE table_schema = 'your_db_name';

Скопируйте результат данного запроса и выполните его в SQL-клиенте, не забыв заменить 'your_db_name' на название вашей базы данных. Помните о безопасности: обязательно создайте бэкап базы данных перед началом процесса.

Введение в набор символов и сортировочные правила

utf8mb4 представляет собой полностью поддерживаемый набор возможностей UTF-8, включая эмодзи и разнообразные азиатские языки. Этот набор символов представляет существенное улучшение по сравнению с ограниченным тремя байтами набором символов utf8.

Сортировка utf8mb4_0900_ai_ci обеспечивает регистронезависимое и игнорирующее акценты сравнение текста, устанавливая правила сопоставления для текстовых данных. Эта сортировка отличается от своих предшественников, таких как utf8_general_ci и utf8_unicode_ci.

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

Подготовка к переходу

Массовое изменение таблиц и столбцов

Для того чтобы изменить каждый столбец, потребуется набор команд ALTER TABLE MODIFY. Продемонстрируйте свои навыки скриптования, используя Perl или batch SQL-скрипт для генерации необходимых команд. Перед началом процесса увеличьте значение переменной сессии group_concat_max_len, для обработки длинных запросов на изменение.

Подробнее об этом расскажет наш спикер на видео

Системные базы данных и значения по умолчанию

Не забудьте о системных базах данных, например, 'sys' и 'mysql', которые играют ключевую роль в функционировании MySQL и не должны подвергаться изменениям. Также убедитесь, что значения по умолчанию правильно экранированы, чтобы избежать их непреднамеренного изменения.

Определение и установка типов данных

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

Визуализация

Можно представить изменение набора символов и сортировочных правил как обновление алфавита и правил библиотеки:

До обновления (📚):
- Полки: НАБОР СИМВОЛОВ (🔤 Старый_Алфавит)
- Книги: ПРАВИЛА СОРТИРОВКИ (📜 Старые_Правила)

После обновления (📚):
- Полки: НАБОР СИМВОЛОВ (🔤 Новый_Алфавит)
- Книги: ПРАВИЛА СОРТИРОВКИ (📜 Новые_Правила)

План действий выглядит следующим образом:

🔄 Процесс обновления:
1. Оценить каждую полку (🔤) и книгу (📜).
2. Заменить Старый_Алфавит (🔤) и Старые_Правила (📜)...
3. ...Новым_Алфавитом (🔤) и Новыми_Правилами (📜).

Так, вся коллекция будет соответствовать новым требованиям.

Исключение представлений и предотвращение повреждений

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

Переход с Latin1 к UTF-8

Переход от latin1 к utf8mb4 позволит улучшить поддержку символов. Однако обязательно проверяйте данные на отсутствие потерь и делайте резервные копии.

Полезные материалы

1. MySQL 8.0 Руководство по справочнику :: 13.1.2 ALTER DATABASE — официальное руководство MySQL по ALTER DATABASE.
2. DigitalOcean Tutorial — мануал по изменению набора символов MySQL на UTF8MB4 от DigitalOcean.
3. Stack Overflow questions — обсуждения на тему конвертации баз данных MySQL в UTF-8.
4. MySQL 8.0 Руководство по справочнику :: 10 Наборы символов, сортировки, Unicode — официальное руководство по наборам символов, сортировкам и поддержке Unicode в MySQL.
5. Руководства для веб-разработчиков — полезные материалы для понимания наборов символов и сортировок баз данных.