我正在将数据库转换为utf8字符集和utf8_unicode_ci排序规则。将表的字符集更改为utf8时，MySQL会自动将表的列转换为utf8的默认排序规则：utf_general_ci。我不想运行数百个alter column命令来将每一列都转换为utf8_unicode_ci，所以我可以将utf8的默认排序规则设置为utf8_unicode_ci，如information_schema中所示吗？：
SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8';
+---------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN |
+---------------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_mysql561_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8_croatian_ci | utf8 | 576 | | Yes | 8 |
| utf8_myanmar_ci | utf8 | 577 | | Yes | 8 |
+---------------------------+--------------------+-----+------------+-------------+---------+
请注意
IS_DEFAULT列。另请注意，我并不是在问如何使用
ALTER转换数据库，表或列！另外，将
collation_server = utf8_unicode_ci添加到my.cnf无效。 最佳答案
每个表而不是每列（Reference）需要一个ALTER：
ALTER TABLE foo CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
您可以生成所有变更，然后手动复制它们以执行它们。就像是
SELECT CONCAT("ALTER TABLE ", table_schema, ".", table_name,
" CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
")
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema',
'performance_schema', 'sys_schema');
但是我建议您
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci，以便您可以处理所有中文以及表情符号。我希望您做到了
CONVERT TO，而不仅仅是MODIFY COLUMN。前者转换字符；前者转换字符。后者会使表中已有的任何8位字符弄乱。如果您在
VARCHAR(255)上有索引，则会发生utf8mb4错误。如果可行，请将尺寸缩小到191或更小。例
mysql> SHOW CREATE TABLE iidr\G
*************************** 1. row ***************************
Table: iidr
Create Table: CREATE TABLE `iidr` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`key2` int(10) unsigned NOT NULL,
`vc` varchar(99) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `key2` (`key2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> SHOW FULL COLUMNS FROM iidr;
+-------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| key2 | int(10) unsigned | NULL | NO | UNI | NULL | | select,insert,update,references | |
| vc | varchar(99) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+-------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE iidr CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;
Query OK, 2 rows affected (0.14 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW FULL COLUMNS FROM iidr;
+-------+------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+
| id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| key2 | int(10) unsigned | NULL | NO | UNI | NULL | | select,insert,update,references | |
| vc | varchar(99) | utf8mb4_unicode_520_ci | YES | | NULL | | select,insert,update,references | |
+-------+------------------+------------------------+------+-----+---------+----------------+---------------------------------+---------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE iidr\G
*************************** 1. row ***************************
Table: iidr
Create Table: CREATE TABLE `iidr` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`key2` int(10) unsigned NOT NULL,
`vc` varchar(99) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `key2` (`key2`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
1 row in set (0.00 sec)
关于mysql - 如何将utf8的MySQL默认排序规则设置为utf8_unicode_ci？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/36628283/