有趣的大小写问题-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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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|维西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;
+—————-+———-+————————-+
| count(user_id) | count(*) | count(distinct user_id) |
+—————-+———-+————————-+
| 1700241 | 1700241 | 1700240 |
+—————-+———-+————————-+

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

| taobao|维西V | -1 |
| taobao|维西v | -1 |

可以看到一个是“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则可以区分大小写。