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

наш предыдущий программист установил неправильные параметры сортировки в таблице (Mysql). Он установил его с помощью Латинской сортировки, когда это должно быть UTF8, и теперь у меня есть проблемы. Каждая запись с китайским и японским характером поворачивается ??? характер.

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

5 136
sql mysql collation

5 ответов:

изменить параметры сортировки базы данных:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

изменить параметры сортировки таблицы:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

изменить параметры сортировки столбца:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Подробнее:

308

вот как изменить все базы данных / таблицы / столбцы. Запустите эти запросы, и они выведут все последующие запросы, необходимые для преобразования всей схемы в utf8. Надеюсь, это поможет!

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

SELECT DISTINCT concat('ALTER DATABASE `', TABLE_SCHEMA, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like  'database_name';

-- изменить параметры сортировки таблицы / Char Set

SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.tables
where TABLE_SCHEMA like 'database_name';

-- изменить параметры сортировки столбцов / Char Set

SELECT concat('ALTER TABLE `', t1.TABLE_SCHEMA, '`.`', t1.table_name, '` MODIFY `', t1.column_name, '` ', t1.data_type , '(' , t1.CHARACTER_MAXIMUM_LENGTH , ')' , ' CHARACTER SET utf8 COLLATE utf8_unicode_ci;')
from information_schema.columns t1
where t1.TABLE_SCHEMA like 'database_name' and t1.COLLATION_NAME = 'old_charset_name';
34

будьте осторожны, что в Mysql,utf8 набор символов - это только подмножество реального набора символов UTF8. Чтобы сохранить один байт памяти, команда Mysql решила хранить только три байта символов UTF8 вместо полных четырех байтов. Это означает, что некоторые Восточноазиатский язык и смайлики не полностью поддерживаются. Чтобы убедиться, что вы можете хранить все символы utf8, используйте utf8mb4 тип данных, и utf8mb4_bin или utf8mb4_general_ci в Mysql.

20

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

установить сеанс group_concat_max_len = 100000;

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

     SELECT a.table_name, concat('ALTER TABLE ', a.table_schema, '.', a.table_name, ' DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci, ',
        group_concat(distinct(concat(' MODIFY ',  column_name, ' ', column_type, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ', if (is_nullable = 'NO', ' NOT', ''), ' NULL ',
        if (COLUMN_DEFAULT is not null, CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\''), ''), if (EXTRA != '', CONCAT(' ', EXTRA), '')))), ';') as alter_statement
    FROM information_schema.columns a
    INNER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG
        AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
        AND a.TABLE_NAME = b.TABLE_NAME
        AND b.table_type != 'view'
    WHERE a.table_schema = ? and (collation_name = 'latin1_swedish_ci' or collation_name = 'utf8mb4_general_ci')
    GROUP BY table_name;

разница здесь между предыдущим ответом - это использование utf8 вместо ut8mb4 и использование Т1.это с Т1.CHARACTER_MAXIMUM_LENGTH не работает для перечислений. Кроме того, мой запрос исключает представления, так как они должны будут изменяться отдельно.

Я просто использовал скрипт Perl для возврата всех этих изменений в виде массива и перебирал их, исправлял столбцы, которые были слишком длинными (обычно они были varchar(256), когда данные обычно имели только 20 символов в них, так что это было легко исправить).

Я обнаружил, что некоторые данные были повреждены при изменении с latin1 - > utf8mb4. Оно оказалось, что символы latin1 в кодировке utf8 в Столбцах будут искажены при преобразовании. Я просто держал данные из столбцов, которые, как я знал, будут проблемой в памяти до и после alter, и сравнивал их и создавал инструкции update для исправления данных.

6

здесь хорошо описывает процесс. Однако некоторые из символов, которые не вписывались в латинское пространство, исчезли навсегда. UTF-8 является надмножеством latin1. Не наоборот. Большинство будет помещаться в однобайтовое пространство, но любые неопределенные не будут (проверьте список latin1 - не все 256 символов определены, в зависимости от определения latin1 mysql)

4