Как легко преобразовать таблицы utf8 в utf8mb4 в MySQL 5.5

У меня есть база данных, которая теперь должна поддерживать 4 байтовых символа (китайский). К счастью, у меня уже есть MySQL 5.5.

Итак, я просто хотел бы сделать все сопоставления, которые являются utf8_bin для utf8mb4_bin.

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

40 голосов | спросил geoaxis 22 22011vEurope/Moscow11bEurope/MoscowTue, 22 Nov 2011 16:18:30 +0400 2011, 16:18:30

7 ответов


58

Из моего руководства Как поддерживать полный Юникод в базах данных MySQL , вот те запросы, которые вы можете запустить для обновления кодировки и сортировки базы данных, таблицы или столбца:

Для каждой базы данных:

ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

Для каждой таблицы:

ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Для каждого столбца:

ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

(Don't ™ t слепо копировать-вставить это! Точный оператор зависит от типа столбца, максимальной длины и других свойств. Вышеприведенная строка является всего лишь примером для VARCHAR столбец).

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

Раздел 10.1.11 Справочного руководства по MySQL 5.5 содержит дополнительную информацию об этом.

ответил Mathias Bynens 30 J000000Monday12 2012, 18:32:19
17

У меня есть решение, которое будет конвертировать базы данных и таблицы, запустив несколько команд. Он также преобразует все столбцы типа varchar, text, tinytext, mediumtext, longtext код>. Вы можете добавлять другие типы, такие как char, просто расширяя запросы в следующем коде. Вы также должны создать резервную копию своей базы данных , если что-то сломается.

Скопируйте следующий код в файл с именем preAlterTables.sql:

use information_schema;
SELECT concat("ALTER DATABASE `",table_schema,"` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql 
FROM `TABLES` where table_schema like "yourDbName" group by table_schema;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql  
FROM `TABLES` where table_schema like "yourDbName" group by table_schema, table_name;
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type,"(",character_maximum_length,") CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('varchar');
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name, "` CHANGE `",column_name,"` `",column_name,"` ",data_type," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql 
FROM `COLUMNS` where table_schema like "yourDbName" and data_type in ('text','tinytext','mediumtext','longtext');

Замените все вхождения «yourDbName» на базу данных, которую вы хотите преобразовать. Затем запустите:

mysql -uroot < preAlterTables.sql | egrep '^ALTER' > alterTables.sql

Это создаст новый файл alterTables.sql со всеми запросами, необходимыми для преобразования базы данных. Для запуска преобразования выполните следующую команду:

mysql -uroot < alterTables.sql

Вы также можете адаптировать его для работы через несколько баз данных, изменив условие для table_schema. Например, table_schema like "wiki_%" преобразует все базы данных с префиксом имени wiki_. Чтобы преобразовать все базы данных, замените условие на table_type!='SYSTEM VIEW'.

Проблема, которая может возникнуть. У меня были несколько столбцов varchar (255) в ключах mysql. Это вызывает ошибку:

ERROR 1071 (42000) at line 2229: Specified key was too long; max key length is 767 bytes

Если это произойдет, вы можете просто изменить столбец меньшим, например varchar (150), и выполнить повторную команду.

Обратите внимание . Этот ответ преобразует базу данных в utf8mb4_unicode_ci вместо utf8mb4_bin, заданный в вопросе. Но вы можете просто заменить это.

ответил MrJingles87 23 J0000006Europe/Moscow 2015, 15:49:01
2

Я бы написал сценарий (в Perl или что-то еще), чтобы использовать information_schema (TABLES и COLUMNS), чтобы пройти через все таблицы, и делать MODIFY COLUMN в каждом поле CHAR /VARCHAR /TEXT. Я бы собрал все MODIFY в один ALTER для каждой таблицы; это будет более эффективным.

Я думаю (но не уверен), что предложение Raihan меняет default для таблицы.

ответил Rick James 21 J0000006Europe/Moscow 2012, 03:15:52
1

Встает в эту ситуацию; вот подход, который я использовал для преобразования моей базы данных:

  1. Во-первых, вам нужно отредактировать my.cnf, чтобы сделать подключение по умолчанию к базе данных (между приложениями и MYSQL) совместимым с utf8mb4_unicode_ci. Без этих символов, подобных emojis и аналогичным, представленным вашими приложениями, вы не попадете в ваши таблицы в правильных байтах /кодировании (если только параметры вашего CNN-кода вашего приложения не указывают соединение utf8mb4).

    Инструкции приведены здесь .

  2. Выполните следующий SQL (нет необходимости готовить SQL для изменения отдельных столбцов, инструкции ALTER TABLE сделают это).

    Перед выполнением ниже кода замените «DbName» на свое фактическое имя БД.

    USE information_schema;
    
    SELECT concat("ALTER DATABASE `",table_schema,
                  "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;") as _sql
      FROM `TABLES`
     WHERE table_schema like "DbName"
     GROUP BY table_schema;
    
    SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,
                  "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as _sql
      FROM `TABLES`
     WHERE table_schema like "DbName"
     GROUP BY table_schema, table_name;
    
  3. Соберите и сохраните вывод вышеуказанного SQL в файле dot sql и выполните его.

  4. Если вы получили ошибку, например #1071 - Specified key was too long; max key length is 1000 bytes. вместе с именем проблемной таблицы, это означает, что индексный ключ в некотором столбце этой таблицы (который должен был быть преобразован в MB5 charstring) будет очень большим, поэтому столбец Varchar должен быть & lt ; = 250, чтобы его индексный ключ был максимальным 1000 байт. Проверьте столбцы, на которых у вас есть индексы, и если один из них является varchar> 250 (скорее всего 255), то

    • Шаг 1: проверьте данные в этом столбце, чтобы убедиться, что максимальный размер строки в этом столбце равен <= 250.

      Пример запроса:

      select `id`,`username`, `email`,
             length(`username`) as l1,
             char_length(`username`) as l2,
             length(`email`) as l3,
             char_length(`email`) as l4
        from jos_users
       order by l4 Desc;
      
    • Шаг 2: если max charlength индексированных данных столбца <= 250, то измените длину столбца на 250. Если это невозможно, удалите индекс в этом столбце

    • Шаг 3: затем снова запустите запрос таблицы изменений для этой таблицы, и таблица теперь должна быть успешно преобразована в utf8mb4.

Ура!

ответил Nav44 9 AM00000010000001631 2017, 01:12:16
1

Я написал это руководство: http://hanoian.com/content/index.php/24-automate-the-converting-a-mysql-database-character-set-to-utf8mb4

Из моей работы я увидел, что ALTER базы данных и таблиц недостаточно. Мне приходилось входить в каждую таблицу и ALTER каждый из столбцов text /mediumtext /varchar.

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

Длинный индекс для MySQL 5.6:

Есть одна вещь, которой вы должны обладать привилегиями DBA /SUPER USER: Настройка параметров базы данных:

innodb_large_prefix: ON
innodb_file_format: Барракуда
innodb_file_format_max: Barracuda

В ответах на этот вопрос есть инструкция, как установить эти параметры выше: https://stackoverflow.com/questions/35847015/mysql-change-innodb-large-prefix

Конечно, в моей статье есть инструкции для этого.

Для MySQL версии 5.7 или более поздней , innodb_large_prefix по умолчанию включен, а innodb_file_format также является Barracuda по умолчанию.

ответил Châu Hồng Lĩnh 27 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowWed, 27 Sep 2017 21:51:21 +0300 2017, 21:51:21
0

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

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE $DB; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)
ответил Petr Stastny 11 +03002017-10-11T14:00:20+03:00312017bEurope/MoscowWed, 11 Oct 2017 14:00:20 +0300 2017, 14:00:20
0

Для людей, которые могут иметь эту проблему, лучшим решением является изменение первых столбцов на двоичный тип, в соответствии с этой таблицей:

  1. CHAR => BINARY
  2. TEXT => BLOB
  3. TINYTEXT => TINYBLOB
  4. MEDIUMTEXT => MEDIUMBLOB
  5. LONGTEXT => LONGBLOB
  6. VARCHAR => VARBINARY

И после этого измените столбец на прежний тип и на нужную кодировку.

Eg .: ALTER TABLE [TABLE_SCHEMA]. [TABLE_NAME] ИЗМЕНИТЬ [COLUMN_NAME] LONGBLOB; ALTER TABLE [TABLE_SCHEMA]. [TABLE_NAME] ИЗМЕНИТЬ [COLUMN_NAME] VARCHAR (140) CHARACTER SET utf8mb4;

Я попробовал в нескольких таблицах latin1 и сохранил все диакритики.

Вы можете извлечь этот запрос для всех столбцов, которые делают это:

SELECT CONCAT ('ALTER TABLE', TABLE_SCHEMA, '.', TABLE_NAME, 'MODIFY', COLUMN_NAME, 'VARBINARY;'), CONCAT ('ALTER TABLE', TABLE_SCHEMA, '.', TABLE_NAME, 'MODIFY', COLUMN_NAME, '', COLUMN_TYPE, 'CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') FROM information_schema.columns WHERE TABLE_SCHEMA IN ('[TABLE_SCHEMA]') AND COLUMN_TYPE LIKE 'varchar%' И (COLLATION_NAME не является NULL AND COLLATION_NAME НЕ НРАВИТСЯ 'utf%');

ответил MalachiteBR 6 J0000006Europe/Moscow 2018, 16:58:55

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132