如何更改数据库的默认排序规则？

Question

我们以前的程序员在表（Mysql）中设置了错误的排序规则。他使用拉丁文归类（应为UTF8）进行设置，现在我遇到了问题。每个带有中文和日文字符的记录都变成??? 字符。是否可以更改排序规则并找回角色的细节？

Answer 1

更改数据库排序规则：

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;

Answer 2

以下是如何更改所有数据库/表/列的方法。运行这些查询，它们将输出将整个模式转换为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';

-更改表排序规则/字符集

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';

-更改列排序规则/字符集

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';

Answer 3

除了David Whittaker发布的内容之外，我还创建了一个查询，该查询生成完整的表和将更改每个表的列alter语句。跑步可能是一个好主意

SET SESSION group_concat_max_len = 100000;

首先要确保您的小组比赛不会超过这里看到的很小的限制。

     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并使用t1.data_type和t1.CHARACTER_MAXIMUM_LENGTH对于枚举无效。另外，我的查询排除了视图，因为这些视图必须分别更改。

我只是使用Perl脚本将所有这些更改作为数组返回并遍历它们，修复了过长的列（通常为varchar（256），当数据中通常只有20个字符时，因此很容易解决）。

从latin1-> utf8mb4进行更改时，我发现某些数据已损坏。看来是utf8编码的latin1字符在列中会在转换中出错。我只是简单地保存了更改前后的列中的数据，这些列我知道这将是内存中的问题，并对其进行比较并生成用于修复数据的更新语句。