在MySQL中如何正确修改已有字段的字符集与校对集？-mysql教程-PHP中文网

ALTER TABLE … MODIFY COLUMN 会丢失 NOT NULL、DEFAULT、COMMENT、AUTO_INCREMENT 等字段属性；MySQL 8.0+ 虽部分保留但行为不一致，跨版本风险高；应优先用 CHANGE COLUMN 显式重申全部属性，并结合 SHOW CREATE TABLE 核对定义。

在mysql中如何正确修改已有字段的字符集与校对集？

ALTER TABLE … MODIFY COLUMN 会丢失字段属性

直接用 MODIFY COLUMN 改字符集，看似简单，但容易把 NOT NULL、DEFAULT、COMMENT、自增（AUTO_INCREMENT）甚至生成列（GENERATED ALWAYS）等元信息悄悄清掉。MySQL 8.0+ 虽然对部分属性做了保留优化，但行为不一致，尤其跨版本迁移时风险更高。

实操建议：

优先用 CHANGE COLUMN，显式重申所有关键属性，例如：

ALTER TABLE users CHANGE COLUMN name name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户昵称';

执行前先查原始定义：SHOW CREATE TABLE users;，复制完整字段声明再修改，避免遗漏
若字段有索引，CHANGE 或 MODIFY 会隐式重建索引，大表需评估锁表现和耗时

utf8mb4_unicode_ci 和 utf8mb4_0900_ai_ci 的选择陷阱

MySQL 8.0 默认校对集是 utf8mb4_0900_ai_ci，它比 utf8mb4_unicode_ci 更准（比如正确处理德语 ß 和 ss 的等价性），但也更严格——某些旧应用依赖 unicode_ci 的宽松排序逻辑，改完后可能发现 ORDER BY 或 GROUP BY 结果异常。

实操建议：

新项目直接用 utf8mb4_0900_ai_ci；老系统升级前，先在测试库跑对比：SELECT * FROM t ORDER BY col COLLATE utf8mb4_unicode_ci; vs COLLATE utf8mb4_0900_ai_ci
若字段已建索引，校对集变更后索引仍可用，但比较行为变了——不是“失效”，而是“语义变了”
不要混用：同一张表不同字段用不同校对集，JOIN 或 UNION 时可能报 Illegal mix of collations

批量修改多个字段时别用一条 ALTER TABLE 命令硬扛

有人习惯写成：ALTER TABLE t MODIFY c1 ... , MODIFY c2 ... , MODIFY c3 ...;，看起来省事，但 MySQL 会逐个重建表（即使引擎是 InnoDB），不是原子性优化。尤其当字段类型没变、只调校对集时，这种写法反而更慢。

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

实操建议：

单字段改校对集，用 ALTER TABLE t MODIFY COLUMN x x TYPE CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; 即可
真要批量改，拆成多条独立 ALTER TABLE，配合 pt-online-schema-change 或 MySQL 8.0+ 的 ALGORITHM=INSTANT（仅限部分变更，如加列、改默认值；改字符集不支持 INSTANT）
确认当前 MySQL 版本是否支持在线 DDL：SELECT VERSION();，然后查对应版本的 ALTER TABLE 支持矩阵

修改后 SELECT COLLATION() 返回 NULL？

执行完 ALTER TABLE ... COLLATE 后，对字段用 SELECT COLLATION(col) 却返回 NULL，不是失败，而是函数用错了——COLLATION() 只接受字符串字面量或表达式，不能直接传列名。正确查字段校对集得看 INFORMATION_SCHEMA.COLUMNS。

实操建议：

查字段实际校对集：

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 't' AND COLUMN_NAME = 'col';