有趣的大小寫問題-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則可以區分大小寫。