1. 使用index的Sub_part用法
mysql> show index from base_idfa;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| base_idfa | 0 | PRIMARY | 1 | id | A | 6151434 | NULL | NULL | | BTREE | |
| base_idfa | 0 | idfa_index | 1 | idfa | A | 6151434 | 30 | NULL | | BTREE | |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------
注意上面的idfa_index的sub_part部分,取得是列idfa中的前30个字符,而该列的长度是36个字符。
如果使用load data local infile 'path' into base_idfa ( idfa);时并不会报错,且数据正常。而如果使用insert语句时,会遇到这样的error:
ERROR 1062 (23000): Duplicate entry '7B558C70-2729-48E0-94F1-3492BADF' for key 2
显然将要insert的具有36个字符的idfa在insert时,取前30个字符取计算index值了,而该index已经存在了,从而插入失败。
修改方式: 取消unique限制,或者把该unique key修改为整个的36个字符,而非sub_part。
2. 表中已有数据了,把列定义从varchar(64)变为char(32),而实际上的列中数据全部都是36个字符。
mysql> alter table base_idfa modify idfa char(32) not null;
Query OK, 6151434 rows affected, 65535 warnings (25.99 sec)
Records: 6151434 Duplicates: 0 Warnings: 6151431
mysql> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'idfa' at row 1 |
| Warning | 1265 | Data truncated for column 'idfa' at row 2 |
| Warning | 1265 | Data truncated for column 'idfa' at row 3 |
| Warning | 1265 | Data truncated for column 'idfa' at row 4 |
3. 使用insert具有unique属性的列的重复值时,如何忽略错误:
使用insert IGNORE into table的语法。
4. 在#3中情况,除了忽略错误,还可以将insert转变为update的动作。
mysql> insert into base_idfa_test (id, idfa) values (1, 2);
Query OK, 1 row affected (0.00 sec)
mysql> select * from base_idfa_test;
+----+------+
| id | idfa |
+----+------+
| 0 | 1 |
| 1 | 2 |
+----+------+
2 rows in set (0.00 sec)
mysql> insert into base_idfa_test (id, idfa) values (1, 2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into base_idfa_test (id, idfa) values (1, 2)
-> on duplicate key update idfa = 'this is idfa';
Query OK, 2 rows affected (0.01 sec)
mysql> select * from base_idfa_test;
+----+--------------+
| id | idfa |
+----+--------------+
| 0 | 1 |
| 1 | this is idfa |
+----+--------------+
2 rows in set (0.00 sec)