如何在整个数据库中更改字符集（和排序规则）？

15 浏览
0 Comments

如何在整个数据库中更改字符集（和排序规则）？

我们之前的程序员在一个表（Mysql）中设置了错误的排序规则。他将它设置成了拉丁排序规则，而应该是UTF8，现在我有了问题。每个带有中文和日语字符的记录都会变成???字符。

是否有可能更改排序规则并恢复字符的详细信息？

admin 更改状态以发布 2023年5月24日
0

2 答案

0 Comments

以下是如何更改所有数据库/表/列的方法。运行这些查询，它们将输出所有必要的后续查询，以将整个数据库转换为字符编码 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 ，因为这些应该不会被修改，除非您有明确的理由这样做。

0
0 Comments

更改数据库排序方式：

ALTER DATABASE  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

更改表格排序方式：

ALTER TABLE  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

更改列排序方式：

ALTER TABLE  MODIFY  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

更多信息：

0