欢迎光临
Corasql

MySQL字符集排序规则导致主键冲突

一、知识点
字符集排序gbk_bin与gbk_chinese_ci(utf8_general_ci与utf8_general_cs)区别
1、gbk_bin是二进制存储.区分大小写的 gbk_chinese_ci不区分大小写
2、utf8_general_ci 不区分大小写 utf8_general_cs 区分大小写

如果my.cnf设置成collation_server=gbk_chinese_ci,在创建库是不指定collate,默认将是my.cnf中的collation_server值
ci是case insensitive, 即大小写不敏感

二、测试
操作方式一
(root@192.168.0.94) [(none)]> CREATE DATABASE /!32312 IF NOT EXISTS/ atest /*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_bin */;
Query OK, 1 row affected (0.01 sec)

(root@192.168.0.94) [(none)]> use atest;
Database changed
(root@192.168.0.94) [atest]> CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

(root@192.168.0.94) [atest]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’a’,’香港居民身份证’,1,0);
Query OK, 1 row affected (0.00 sec)

(root@192.168.0.94) [atest]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’A’,’营业执照’,1,0);
Query OK, 1 row affected (0.00 sec)

查看所创建的表结构
(root@192.168.0.94) [atest]> show create table test;
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| Table | Create Table |
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| test | CREATE TABLE test (
c1 varchar(12) COLLATE gbk_bin NOT NULL,
c2 varchar(20) COLLATE gbk_bin NOT NULL,
c3 varchar(20) COLLATE gbk_bin NOT NULL,
c4 varchar(60) COLLATE gbk_bin NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB DEFAULT CHARSET=gbk COLLATE=gbk_bin |
+——-+——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

操作方式二
(root@192.168.0.94) [atest]> CREATE DATABASE /!32312 IF NOT EXISTS/ atest2 /*!40100 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci */;
Query OK, 1 row affected (0.03 sec)

(root@192.168.0.94) [atest]> use atest2;
Database changed
(root@192.168.0.94) [atest2]> CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

(root@192.168.0.94) [atest2]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’a’,’香港居民身份证’,1,0);
Query OK, 1 row affected (0.01 sec)

(root@192.168.0.94) [atest2]> insert into test (c1,c2,c3,c4,c5,c6) values(‘test’,’证件类别’,’A’,’营业执照’,1,0);
ERROR 1062 (23000): Duplicate entry ‘test-A’ for key ‘PRIMARY’

主键冲突了

查看所创建的表结构
(root@192.168.0.94) [atest2]> show create table test;
+——-+———————————————————————————————————————————————————————————————————————————————————————————–+
| Table | Create Table |
+——-+———————————————————————————————————————————————————————————————————————————————————————————–+
| test | CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+——-+———————————————————————————————————————————————————————————————————————————————————————————–+
1 row in set (0.00 sec)

两者创建出来的表结构不一样(字符排序不一样)导致了主键冲突。

备注:
创建表时,也不能添加DEFAULT CHARSET=gbk,否则也会出问题
CREATE TABLE test (
c1 varchar(12) NOT NULL,
c2 varchar(20) NOT NULL,
c3 varchar(20) NOT NULL,
c4 varchar(60) NOT NULL,
c5 int(11) NOT NULL DEFAULT ‘1’,
c6 int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (c1,c3)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

分享到:更多 ()

评论 抢沙发

评论前必须登录!

DBA是一种生活方式,优化是一种生活态度

OracleMySQL