本文共 1367 字,大约阅读时间需要 4 分钟。
之前一直有个小误区,我以为mysql的唯一索引肯定是区分大小写的,然而实际上utf8字符集下,默认排序规则utf8_general_ci 情况下,是不区分大小写的。而在排序规则utf8_bin下是区分大小写的,这就有可能出现以下情况:之前字段是varchar binary类型,即排序规则为utf8_bin,后来将该字段改回varchar的话,就会导致唯一键冲突错误
mysql> show create table xx\G *************************** 1. row *************************** Table: xx Create Table: CREATE TABLE `xx` ( `id` int(11) DEFAULT NULL, `name` varchar(100) NOT NULL, `name1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`name`), UNIQUE KEY `name1` (`name1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
mysql> select * from xx; Empty set (0.00 sec) mysql> insert into xx(id,name,name1) values(1,'jiang','jiang'); Query OK, 1 row affected (0.00 sec) mysql> insert into xx(id,name,name1) values(1,'Jiang','jason'); ERROR 1062 (23000): Duplicate entry 'Jiang' for key 'PRIMARY' mysql> insert into xx(id,name,name1) values(1,'Jiang1','jason'); Query OK, 1 row affected (0.00 sec) mysql> insert into xx(id,name,name1) values(1,'Jiang2','Jason'); Query OK, 1 row affected (0.00 sec)
mysql> select * from xx; +------+--------+-------+ | id | name | name1 | +------+--------+-------+ | 1 | jiang | jiang | | 1 | Jiang1 | jason | | 1 | Jiang2 | Jason | +------+--------+-------+ 3 rows in set (0.00 sec) mysql> alter table xx modify name1 varchar(100); ERROR 1062 (23000): Duplicate entry 'Jason' for key 'name1'
转载地址:http://fcbgb.baihongyu.com/