自增字段写入数据表现不一致的情况

🪐 TiDB 技术问题
17

神奇的表现来了，tidb 里自增竟然没变

test case:

drop table t8;

create table t8 (
a int(1) primary key, -- diff
id int(1) not null auto_increment,
name varchar(64),
key idx_id(`id`)
) engine=innodb;

insert into t8 values(1,1,'chen'),(2147483646,2147483646,'wu');

show create table t8\G

insert into t8 (`a`,`name`) values(2147483647,'a47');

show create table t8\G

mariadb

AUTO_INCREMENT 增 1

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> insert into t8 (`a`,`name`) values(2147483647,'a47');
Query OK, 1 row affected (0.01 sec)

mysql> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `idx_id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> select * from t8;
+------------+------------+------+
| a          | id         | name |
+------------+------------+------+
|          1 |          1 | chen |
| 2147483646 | 2147483646 | wu   |
| 2147483647 | 2147483647 | a47  |
+------------+------------+------+
3 rows in set (0.00 sec)

tidb

AUTO_INCREMENT 不变？

tidb> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513647
1 row in set (0.00 sec)

tidb> insert into t8 (`a`,`name`) values(2147483647,'a47');
Query OK, 1 row affected (0.01 sec)

tidb> show create table t8\G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `a` int(1) NOT NULL,
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2147513647
1 row in set (0.00 sec)

tidb> select * from t8;
+------------+------------+------+
| a          | id         | name |
+------------+------------+------+
|          1 |          1 | chen |
| 2147483646 | 2147483646 | wu   |
| 2147483647 | 2147483647 | a47  |
+------------+------------+------+
3 rows in set (0.00 sec)