В MySQL 5.7 невозможно использовать или сконвертировать данные в utf8mb4_0900_ai_ci, потому что этот коллейшн (сравнение) появился только начиная с MySQL 8.0.

Вот что важно знать:

❌ utf8mb4_0900_ai_ci в MySQL 5.7

• Отсутствует в MySQL 5.7.

• Был добавлен в MySQL 8.0 как коллейшн, основанный на Unicode 9.0, с улучшенной сортировкой и поддержкой современных языков.

✅ Что есть в MySQL 5.7

В MySQL 5.7 вы можете использовать:

• utf8mb4_general_ci — более старая и быстрая сортировка, но не точная.

• utf8mb4_unicode_ci — более точная, основанная на Unicode, но не такая свежая, как 0900.

• utf8mb4_bin — бинарное сравнение, чувствительное к регистру и акцентам.

🔁 Альтернатива

Если вам нужно использовать utf8mb4_0900_ai_ci, то:

1. Нужно обновиться до MySQL 8.0.

2. После обновления можно выполнить:

   sql
   КопироватьРедактировать
   ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
   

✅ Проверка доступных collation в MySQL 5.7

Вы можете посмотреть доступные collation через:

sql
КопироватьРедактировать
SHOW COLLATION WHERE Charset = 'utf8mb4';

Если вы не можете обновиться до 8.0, но хотите имитировать 0900_ai_ci (AI = accent insensitive, CI = case insensitive), можно использовать utf8mb4_unicode_ci, но имейте в виду, что точность сортировки может отличаться.

Если тебе нужно сравнение этих collation или помощь в миграции, могу помочь с этим.

Отличный вопрос. Чтобы перенести базу данных из MySQL 5.7 с utf8mb4_unicode_ci в MySQL 8.0 и использовать utf8mb4_0900_ai_ci, нужно выполнить четкий пошаговый план:

✅ Пошаговая инструкция по миграции на MySQL 8.0 с переходом на utf8mb4_0900_ai_ci

1. 📦 Подготовка к миграции

a. Узнайте текущие настройки:

sql
КопироватьРедактировать
SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'collation%';
SHOW TABLE STATUS;

b. Убедитесь, что все таблицы и поля используют utf8mb4 (если еще нет):

sql
КопироватьРедактировать
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE CHARACTER_SET_NAME IS NOT NULL AND COLLATION_NAME NOT LIKE 'utf8mb4%';

c. Сделайте бэкап базы:

bash
КопироватьРедактировать
mysqldump -u root -p --all-databases --single-transaction --routines --triggers --default-character-set=utf8mb4 > backup.sql

2. ⬆️ Обновление MySQL 5.7 → 8.0

a. Установите MySQL 8.0 (на тестовую машину или staging-сервер, если боитесь делать сразу в продакшене).

b. Проверьте совместимость:

bash
КопироватьРедактировать
mysqlsh -- util checkForServerUpgrade root@localhost:3306 --target-version=8.0.36 --output-format=TEXT

Установить mysql-shell, если не установлен.

3. 🔄 Импорт базы в MySQL 8.0

a. Импортируйте дамп:

bash
КопироватьРедактировать
mysql -u root -p < backup.sql

b. После импорта можно изменить collation на utf8mb4_0900_ai_ci:

sql
КопироватьРедактировать
ALTER DATABASE your_database CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Для всех таблиц и колонок:

sql
КопироватьРедактировать
-- Пример для одной таблицы
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Чтобы сгенерировать команды автоматически:

sql
КопироватьРедактировать
SELECT CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_TYPE = 'BASE TABLE';

4. 🧪 Тестирование

• Проверьте, как работает сортировка и поиск (например, ORDER BY, LIKE, =), особенно для разных языков, спецсимволов, и акцентов.

5. ⚙️ Настройка по умолчанию (опционально)

Чтобы по умолчанию использовать utf8mb4_0900_ai_ci в новой базе, добавьте в my.cnf или my.ini (в MySQL 8.0):

ini
КопироватьРедактировать
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

6. 🚀 Перевод в продакшн

После всех тестов можно повторить миграцию с актуальным дампом с продакшена.

❗️ Важно

• utf8mb4_unicode_ci и utf8mb4_0900_ai_ci могут по-разному сортировать строки — это важно, если у вас есть зависимости от сортировки (например, индексы, ORDER BY, UNIQ ключи на текст).

• Используйте staging-сервер, чтобы избежать сюрпризов.

Если хочешь, я могу помочь с генерацией полного SQL-скрипта на миграцию или с анализом конкретной базы.

Ты на правильном пути! У тебя сейчас:

• MySQL 5.7 — база tinode, все таблицы используют utf8mb4_unicode_ci.

• MySQL 8.0.35 — настроен с collation по умолчанию utf8mb4_0900_ai_ci.

Теперь нужно грамотно перенести базу tinode, чтобы:

• все таблицы и колонки перешли на utf8mb4_0900_ai_ci,

• избежать проблем с совместимостью и кодировкой,

• убедиться, что всё работает корректно после импорта.

✅ Пошаговый план переноса базы tinode на MySQL 8.0.35

🔹 Шаг 1: Экспорт базы из MySQL 5.7

Выполни дамп всей базы, указав utf8mb4 как кодировку по умолчанию:

bash
КопироватьРедактировать
mysqldump -u root -p \
  --default-character-set=utf8mb4 \
  --single-transaction --routines --triggers \
  --set-charset \
  tinode > tinode_dump.sql

🔹 Шаг 2: Просмотри дамп (опционально)

Убедись, что в дампе используются корректные команды, например:

sql
КопироватьРедактировать
CREATE TABLE ... CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Мы позже их заменим на utf8mb4_0900_ai_ci.

🔹 Шаг 3: (опционально) Автоматическая замена collation

Если хочешь, можешь заменить collation в самом дампе, перед импортом:

bash
КопироватьРедактировать
sed -i 's/utf8mb4_unicode_ci/utf8mb4_0900_ai_ci/g' tinode_dump.sql

🔹 Шаг 4: Импорт дампа в MySQL 8.0.35

На сервере MySQL 8.0:

bash
КопироватьРедактировать
mysql -u root -p --default-character-set=utf8mb4 tinode < tinode_dump.sql

Убедись, что база tinode уже создана с нужной кодировкой (можно создать вручную или перед импортом):

sql
КопироватьРедактировать
CREATE DATABASE tinode CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

🔹 Шаг 5: Конвертация всех таблиц (если не менял дамп)

Если не редактировал дамп, нужно обновить collation для таблиц:

sql
КопироватьРедактировать
SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME,
  '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'tinode'
  AND TABLE_COLLATION != 'utf8mb4_0900_ai_ci';

Скопируй результат и выполни в MySQL 8.0.

🔹 Шаг 6: (рекомендуется) Обновить системные настройки

Убедись, что в my.cnf или my.ini у тебя прописано:

ini
КопироватьРедактировать
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

Перезапусти сервер после изменения.

🔹 Шаг 7: Проверка

Проверь:

sql
КопироватьРедактировать
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'tinode';

Также можно проверить колонки:

sql
КопироватьРедактировать
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'tinode';

⚠️ Важное замечание

• utf8mb4_unicode_ci и utf8mb4_0900_ai_ci не идентичны — возможны отличия в сортировке, сравнениях, индексации (например, ß, æ, и др.).

• Если у тебя были уникальные индексы на текстовые поля — проверь, что они не конфликтуют после смены collation.

Если хочешь, могу сгенерировать полный SQL-скрипт для конвертации всех таблиц и колонок базы tinode на 8.0.

Отлично, у тебя все таблицы в базе tinode используют utf8mb4_unicode_ci, и теперь ты хочешь перевести их на utf8mb4_0900_ai_ci в MySQL 8.0.35.

🔁 Готовый SQL-скрипт для смены COLLATE всех таблиц на utf8mb4_0900_ai_ci

Вот SQL-команды, которые можно выполнить на сервере MySQL 8.0 после импорта базы:

sql
КопироватьРедактировать
ALTER TABLE auth CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE credentials CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE dellog CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE devices CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE filemsglinks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE fileuploads CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE kvmeta CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE subscriptions CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE topictags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE usertags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Эти команды:

• Применяют utf8mb4 как кодировку,

• Устанавливают utf8mb4_0900_ai_ci как новый collation для всех строковых колонок в таблице.

✅ Как это работает

Команда ALTER TABLE ... CONVERT TO CHARACTER SET ... COLLATE ...:

• Автоматически обновляет все CHAR, VARCHAR, TEXT-поля в таблице на новый collation.

• Не влияет на BLOB, INT, DATE и прочие типы.

⚠️ Важно перед выполнением

1. Сделай резервную копию базы на MySQL 8.0 перед выполнением скрипта.

2. Проверь, нет ли уникальных индексов на строковые поля, где изменение collation может вызвать конфликт (дубликаты).

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

Отлично, ты уже на полпути!

🔍 Что произошло

Ты успешно выполнил 8 из 13 запросов, но 5 завершились ошибкой 3780:

Error 3780: Referencing column and referenced column in foreign key constraint are incompatible.

Это типичная ошибка при смене CHARSET/COLLATE, если в таблицах есть внешние ключи, ссылающиеся на поля с несовпадающими collation.

✅ Как исправить: обход ошибки 3780

🔧 Решение — временно удалить внешние ключи, обновить кодировку, затем вернуть обратно.

🔁 Пошагово

Допустим, ошибка у тебя при попытке изменить:

• dellog.topic → внешний ключ на topics.name

• messages.topic → внешний ключ на topics.name

• filemsglinks.topic → внешний ключ на topics.name

• и т.д.

🧩 Шаг 1: Получить список внешних ключей

Выполни этот запрос, чтобы найти все внешние ключи на topics.name:

sql
КопироватьРедактировать
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'topics'
  AND REFERENCED_COLUMN_NAME = 'name'
  AND TABLE_SCHEMA = 'tinode';

🧨 Шаг 2: Удалить внешние ключи

Пример:

sql
КопироватьРедактировать
ALTER TABLE dellog DROP FOREIGN KEY dellog_ibfk_1;
ALTER TABLE filemsglinks DROP FOREIGN KEY filemsglinks_ibfk_3;
ALTER TABLE messages DROP FOREIGN KEY messages_ibfk_1;
ALTER TABLE topictags DROP FOREIGN KEY topictags_ibfk_1;

Если нужно, могу сгенерировать их тебе автоматически.

🔄 Шаг 3: Выполнить ALTER TABLE заново

Теперь повтори ALTER TABLE для проблемных таблиц:

sql
КопироватьРедактировать
ALTER TABLE topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE dellog CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE filemsglinks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE topictags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

🔁 Шаг 4: Восстановить внешние ключи

Пример:

sql
КопироватьРедактировать
ALTER TABLE dellog
  ADD CONSTRAINT dellog_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE filemsglinks
  ADD CONSTRAINT filemsglinks_ibfk_3
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE messages
  ADD CONSTRAINT messages_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE topictags
  ADD CONSTRAINT topictags_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

🧠 Подсказка: генерация скриптов

Если хочешь, я могу:

• сгенерировать точные DROP FOREIGN KEY и ADD CONSTRAINT команды,

• проверить колонки на совместимость,

• сгенерировать универсальный скрипт восстановления связей.

Просто скажи — могу всё собрать тебе под ключ.

Готов продолжать?

Отлично. У тебя есть 4 внешних ключа, которые блокируют смену COLLATE из-за несовместимости:

✅ Готовый скрипт для исправления

🔹 1. Удалить внешние ключи (временно)

sql
КопироватьРедактировать
ALTER TABLE dellog DROP FOREIGN KEY dellog_ibfk_1;
ALTER TABLE filemsglinks DROP FOREIGN KEY filemsglinks_ibfk_3;
ALTER TABLE messages DROP FOREIGN KEY messages_ibfk_1;
ALTER TABLE topictags DROP FOREIGN KEY topictags_ibfk_1;

🔹 2. Обновить кодировку и collation

sql
КопироватьРедактировать
ALTER TABLE topics CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE dellog CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE filemsglinks CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE topictags CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

🔹 3. Восстановить внешние ключи

Убедись, что типы и длина полей совпадают (topic → topics.name, скорее всего VARCHAR).

sql
КопироватьРедактировать
ALTER TABLE dellog
  ADD CONSTRAINT dellog_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE filemsglinks
  ADD CONSTRAINT filemsglinks_ibfk_3
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE messages
  ADD CONSTRAINT messages_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE topictags
  ADD CONSTRAINT topictags_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

⚠️ На всякий случай: проверь длину и типы колонок

Поля topic и name должны быть одинакового типа и длины, например:

sql
КопироватьРедактировать
-- пример
topic   VARCHAR(128)
name    VARCHAR(128)

Можно проверить:

sql
КопироватьРедактировать
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_NAME IN ('topics', 'dellog', 'messages', 'filemsglinks', 'topictags')
  AND COLUMN_NAME IN ('name', 'topic');

Если типы отличаются — надо будет подогнать вручную перед восстановлением ключей.

Хочешь — могу автоматически сгенерировать ALTER для выравнивания типов, если дашь вывод этого запроса.

Готов двигаться дальше?

Отлично! 👍 У тебя:

• Все поля topic и name имеют одинаковый тип: CHAR(25)

• И уже используют utf8mb4_0900_ai_ci

Значит, никаких дополнительных правок не требуется — можно восстанавливать внешние ключи прямо сейчас ✅

✅ Готовый скрипт восстановления внешних ключей

sql
КопироватьРедактировать
ALTER TABLE dellog
  ADD CONSTRAINT dellog_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE filemsglinks
  ADD CONSTRAINT filemsglinks_ibfk_3
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE messages
  ADD CONSTRAINT messages_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

ALTER TABLE topictags
  ADD CONSTRAINT topictags_ibfk_1
  FOREIGN KEY (topic) REFERENCES topics(name);

После выполнения:

1. Все таблицы будут использовать utf8mb4_0900_ai_ci

2. Внешние ключи восстановлены без конфликтов

3. База полностью адаптирована под MySQL 8.0

Если хочешь, могу также помочь проверить:

• Все ли колонки теперь в нужной кодировке

• Какие индексы могут быть чувствительны к смене collation

• Или сгенерировать дамп для окончательного бэкапа

Готово, поздравляю! 🎉