我正试图将我的表排序类型从unicode转换为通用,但mysql不允许。没有返回错误,但是当我查看表时,我可以看到没有进行任何更改。
mysql> show variables like '%coll%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)
mysql> show create table abc;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc_test | CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`somecolumn` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table abc convert to charset utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table abc;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc_test | CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`somecolumn` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
就像这样使用alter, ALTER TABLE table_name COLLATE='utf8_general_ci';
您的表已经有UTF8 charset
。所以,在我看来,你不需要再设置了。我可以看到,您正试图将其转换为字符集utf8再次与您的alter查询。
我尝试了mysql 5.7.18,一切看起来都很好。你用的是哪个版本?
我设置了与您相同的排序变量,并创建了表,并通过从上面复制的语句更改了排序。最后我得到了:
mysql> show create table abc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc | CREATE TABLE `abc` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`somecolumn` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.7.18-debug |
+--------------+
SHOW CREATE TABLE不打印排序规则信息,因为utf8_general_ci是utf8的默认排序规则。