如何改变字符集(和排序)在整个数据库?

我们之前的程序员在一个表(Mysql)中设置了错误的排序规则。他把它设置成拉丁排序，而它应该是UTF8，现在我有问题了。每一个有中国和日本特色的记录都变成了??的性格。

是否有可能改变排序和得到字符的细节?

197197 次浏览
小开

在这里很好地描述了这个过程。然而，一些不适合拉丁语空间的字符已经永远消失了。UTF-8是latin1的超集。而不是相反。大多数将适合单字节空间，但任何未定义的将不(检查latin1列表-不是所有256个字符都被定义，这取决于mysql的latin1定义)

小开
最佳答案

更改数据库排序规则:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

更改表排序:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

更改列排序规则:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

utf8mb4_0900_ai_ci的部分是什么意思?

3 bytes -- utf8
4 bytes -- utf8mb4 (new)

v4.0 --   _unicode_
v5.20 --  _unicode_520_
v9.0 --   _0900_ (new)

_bin      -- just compare the bits; don't consider case folding, accents, etc
_ci       -- explicitly case insensitive (A=a) and implicitly accent insensitive (a=á)
_ai_ci    -- explicitly case insensitive and accent insensitive
_as (etc) -- accent-sensitive (etc)

_bin         -- simple, fast
_general_ci  -- fails to compare multiletters; eg ss=ß, somewhat fast
...          -- slower
_0900_       -- (8.0) much faster because of a rewrite

更多信息:

小开

注意，在Mysql中，utf8字符集只是真正UTF8字符集的一个子集。为了节省一个字节的存储空间，Mysql团队决定只存储3个字节的UTF8字符，而不是完整的4个字节。这意味着一些东亚语言和表情符号并不完全支持。为了确保你可以存储所有UTF8字符，在Mysql中使用utf8mb4数据类型和utf8mb4_bin或utf8mb4_general_ci。

小开

下面是如何更改所有数据库/表/列。运行这些查询，它们将输出所有必要的后续查询，以将整个数据库转换为字符编码utf8mb4，并将排序规则转换为MySQL 8默认的utf8mb4_0900_ai_ci。希望这能有所帮助!

——更改数据库默认排序规则

SELECT
CONCAT('ALTER DATABASE `', SCHEMA_NAME,'` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME
NOT IN ('sys','mysql','information_schema','performance_schema','innodb')
AND SCHEMA_NAME LIKE 'database_name';

注意，改变默认模式会改变新表(及其列)的默认的。它不修改现有表的现有列。

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

SELECT
CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA
NOT IN ('sys','mysql','information_schema','performance_schema','innodb')
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA LIKE 'database_name';

注意，更改表默认值会更改新列的默认的。它不修改现有表上的现有列。

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

SELECT
CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` ', COLUMN_TYPE,
IF(COLUMN_DEFAULT IS NOT NULL, CONCAT(' DEFAULT \'', COLUMN_DEFAULT, '\''), ''),
IF(IS_NULLABLE = 'YES', ' NULL ', ' NOT NULL '),
' COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA
NOT IN ('sys','mysql','information_schema','performance_schema','innodb')
AND COLLATION_NAME IS NOT NULL
AND TABLE_SCHEMA LIKE 'database_name'
AND COLLATION_NAME = 'old_collation_name';

这将改变实际的列和查询时的数据库行为。然而，如果数据不在一个兼容的排序规则/字符集中，它不会转换数据。有关从旧排序规则迁移的详细信息，请参见https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/。我们还假设你的默认值不包括单引号——它需要转义——并且我们确保COLLATION_NAME不是NULL，以排除包含整数、时间戳等的列。

在这三种情况下，我们过滤掉了内置的系统模式，如sys和mysql，因为除非有明确的理由，否则不应该修改这些模式。

小开

添加到David Whittaker发布的内容，我创建了一个查询，生成完整的表和列alter语句，将转换每个表。跑步也许是个好主意

SET SESSION 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和使用t1。带t1的Data_type。CHARACTER_MAXIMUM_LENGTH对枚举无效。此外，我的查询排除了视图，因为这些视图必须单独更改。

我简单地使用Perl脚本将所有这些alter作为一个数组返回，并对它们进行迭代，修复了太长的列(通常它们是varchar(256)，而数据通常只有20个字符，所以这是一个简单的修复)。

我发现一些数据损坏时，从latin1 -> utf8mb4。似乎utf8编码的列中的latin1字符会在转换中出错。我只是从alter前后的内存中保存我知道将会出现问题的列中的数据，并对它们进行比较，生成更新语句来修复数据。


提交答案