有趣的大小写问题-utf8_bin

问题：

xxx@3023 14:51:26>insert into test_tmp_log_node_10445__01 select * from test;

error 1062 (23000): duplicate entry ‘taobao|维西v’ for key ‘idx_nodetemp_10445_01’

查看表结构：

create table `test` (

`user_id` varchar(64) character set utf8 collate utf8_bin not null comment ‘主键。客户全局统一id，由客户统一id生成规则生成’,

`control_group_type` int(2) not null default ‘0’

) engine=innodb default charset=utf8;

create table `test_tmp_log_node_10445__01` (

`user_id` varchar(64) default null,

`control_group_type` tinyint(4) default null,

unique key `idx_nodetemp_10445_01` (`user_id`)

bakdb_jwswg888@3023 15:49:38>select count(user_id),count(*),count(distinct user_id) from test;

+—————-+———-+————————-+

| count(user_id) | count(*) | count(distinct user_id) |

| 1700241 | 1700241 | 1700241 |

可以看到对user_id做统计，user_id的数据是唯一，但是为什么在插入到test_tmp_log_node_10445__01中报主键冲突喃？

我们来看看test表中类似‘维西v’的数据：

bakdb_jwswg888@3023 15:31:28>select * from t1 where user_id like ‘%维西%’;

+———————+——————–+

| user_id | control_group_type |

| taobao|vici维西 | -1 |

| taobao|化雨维西 | -1 |

| taobao|维西v | -1 |

| taobao|胡维西 | -1 |

可以看到test表确实有两条’taobao|维西v’的数据，所以插入到test_tmp_log_node_10445__01中报主键冲突了,那为什么user_id distinct值和sum是相同的？对test表添加唯一索引验证一下：

bakdb_jwswg888@3023 15:56:22>alter table test add unique key uk_userid(user_id);

query ok, 0 rows affected (20.38 sec)

records: 0 duplicates: 0 warnings: 0

可以看到test表示是可以在user_id上面添加唯一索引的，则证明在test中user_id的确是唯一的，但在插入到test_tmp_log_node_10445__01中就冲突，那里出了问题？

这里看到test中user_id字段的字符集有些异常：character set utf8 collate utf8_bin,而test_tmp_log_node_10445__01中user_id字段的字符集则没有明显指定，难道是这里有问题？

bakdb_jwswg888@3023 15:29:48>create table t1 (user_id varchar(64),control_group_type int);

query ok, 0 rows affected (0.05 sec)

bakdb_jwswg888@3023 15:30:35>insert into t1 select * from test;

query ok, 1700241 rows affected (17.39 sec)

records: 1700241 duplicates: 0 warnings: 0

bakdb_jwswg888@3023 15:31:02>select count(user_id),count(*),count(distinct user_id) from t1;

| 1700241 | 1700241 | 1700240 |

哇哦，可以看到新创建的t1表中user_id已经出现了重复数据了，在仔细发现：

可以看到一个是“v”，一个是小“v”，原来是大小写的问题，在test表中指定了utf8_bin字符集 ，该字符集是区分大小写的：

utf8_general_ci 不区分大小写

utf8_general_cs 区分大小写

utf8_bin: 将字符串每个字符串用二进制数据编译存储， 区分大小写，而且可以存二进制的内容

所以在创建test_tmp_log_node_10445__01表的时候指定一下user_id列的属性为：user_id varchar(64) binary则可以区分大小写。