Home > database > 有趣的大小写问题-utf8_bin

有趣的大小写问题-utf8_bin

October 15th, 2013

问题:

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则可以区分大小写。

Categories: database Tags:
Comments are closed.