【 TiDB 使用环境】测试
【 TiDB 版本】v7.1.0
【复现路径】
发现7.1版本中对于主键存在空格时的行为比较怪异：
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select @@global.tidb_enable_clustered_index ;
+--------------------------------------+
| @@global.tidb_enable_clustered_index |
+--------------------------------------+
| ON |
+--------------------------------------+
1 row in set (0.00 sec)
聚簇表行为：
mysql> create table tt (id varchar(10) not null primary key);
Query OK, 0 rows affected (0.12 sec)
mysql> show create table tt;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`id` varchar(10) NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into tt values('a ');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tt values('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'tt.PRIMARY'
mysql> select *,length(id),char_length(id) from tt;
+----+------------+-----------------+
| id | length(id) | char_length(id) |
+----+------------+-----------------+
| a | 2 | 2 |
+----+------------+-----------------+
1 row in set (0.00 sec)
mysql> select length('a'),char_length('a');
+-------------+------------------+
| length('a') | char_length('a') |
+-------------+------------------+
| 1 | 1 |
+-------------+------------------+
1 row in set (0.00 sec)
mysql> select length('a '),char_length('a ');
+--------------+-------------------+
| length('a ') | char_length('a ') |
+--------------+-------------------+
| 2 | 2 |
+--------------+-------------------+
1 row in set (0.00 sec)
‘a’ 和 'a '对于id字段来说是不一的，写入时报主键冲突。
非聚簇表情况也一样：
mysql> create table tt2 (id varchar(10) not null primary key NONCLUSTERED);
Query OK, 0 rows affected (0.13 sec)
mysql> show create table tt2;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt2 | CREATE TABLE `tt2` (
`id` varchar(10) NOT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into tt2 values('a ');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tt2 values('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 'tt2.PRIMARY'
mysql> select *,length(id),char_length(id) from tt2;
+----+------------+-----------------+
| id | length(id) | char_length(id) |
+----+------------+-----------------+
| a | 2 | 2 |
+----+------------+-----------------+
1 row in set (0.01 sec)
【遇到的问题：问题现象及影响】
相同的用例在4.0.14版本中是可以写入的：
my:root@127.0.0.1:4000=> select tidb_version();
tidb_version()
-------------------------------------------------------------------
Release Version: v4.0.14 +
Edition: Community +
Git Commit Hash: 4f919b07f8f013e48521894c70cb69004f43c622 +
Git Branch: heads/refs/tags/v4.0.14 +
UTC Build Time: 2021-07-26 10:53:03 +
GoVersion: go1.13 +
Race Enabled: false +
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306+
Check Table Before Drop: false
(1 row)
my:root@127.0.0.1:4000=>
my:root@127.0.0.1:4000=> use test;
USE
my:root@127.0.0.1:4000=> create table tt2 (id varchar(10) not null primary key);
CREATE TABLE
my:root@127.0.0.1:4000=> show create table tt2;
Table | Create Table
-------+-------------------------------------------------------------
tt2 | CREATE TABLE "tt2" ( +
| "id" varchar(10) NOT NULL, +
| PRIMARY KEY ("id") +
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
(1 row)
my:root@127.0.0.1:4000=> insert into tt2 values('a ');
INSERT 1
my:root@127.0.0.1:4000=> insert into tt2 values('a');
INSERT 1
my:root@127.0.0.1:4000=>
my:root@127.0.0.1:4000=> select *,length(id),char_length(id) from tt;
error: mysql: 1146: Table 'test.tt' doesn't exist
my:root@127.0.0.1:4000=> select *,length(id),char_length(id) from tt2;
id | length(id) | char_length(id)
----+------------+-----------------
a | 1 | 1
a | 2 | 2
(2 rows)
不知道哪个版本的有问题。