INCREMENT用法实例详解(3)

测试：
在MySQL当���，INT类型的最大值为 -2147483648~2147483647

(root@localhost) [test] > select * from t1; +----+------+ | c1 | c2 | +----+------+ | -1 | ddd | | 2 | aaa | | 3 | bbb | | 4 | ccc | +----+------+ 4 rows in set (0.00 sec) (root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 5 | +----------------+ 1 row in set (0.00 sec) (root@localhost) [test] > insert into t1 values(2147483647,'eee'); Query OK, 1 row affected (0.02 sec) (root@localhost) [test] > select * from t1; +------------+------+ | c1 | c2 | +------------+------+ | -1 | ddd | | 2 | aaa | | 3 | bbb | | 4 | ccc | | 2147483647 | eee | +------------+------+ 5 rows in set (0.00 sec) (root@localhost) [test] > SELECT auto_increment FROM information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 2147483647 | +----------------+ 1 row in set (0.00 sec)

可以看出自增列 AUTO_INCREMENT 并不会分配。

大量插入导致的间隙

当参数值 innodb_autoinc_lock_mode 设置为 0 或 1 时，每条语句生成的自增列值都是连续的，不会产生间隙。因为表级 AUTO-INC 锁会一直持有直到语句结束，并且同一时间只有一条语句在执行；

当参数值 innodb_autoinc_lock_mode 设置为 2 时，在大量插入时有可能会产生间隙，但是只有当并发执行 INSERT 语句时。

对于设置成 1 或者 2 情形下，在连续的语句之间可能产生间隙，因为对于大量插入InnoDB并不清楚每条语句所需自增量值数量。

混合模式插入自增列值分配

测试表：

-- t1表：表中无数据，但自增列下一个分配值从101开始 (root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost) [test] > select count(*) from t1; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) -- t2表：表中有100万行数据，并且自增列值是连续的 (root@localhost) [test] > show create table t2\G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost) [test] > select count(*) from t2; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.22 sec) (root@localhost) [test] > select min(c1),max(c1) from t2; +---------+---------+ | min(c1) | max(c1) | +---------+---------+ | 1 | 1000000 | +---------+---------+ 1 row in set (0.01 sec) innodb_autoinc_lock_mode = 0 (root@localhost) [test] > show global variables like 'innodb_autoinc_lock_mode'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | innodb_autoinc_lock_mode | 0 | +--------------------------+-------+ 1 row in set (0.02 sec) -- 1、开启session 1执行TRX1 TRX1: insert into t1(c2) select c2 from t2; (root@localhost) [test] > insert into t1(c2) select c2 from t2; Query OK, 1000000 rows affected (6.37 sec) Records: 1000000 Duplicates: 0 Warnings: 0 -- 2、在TRX1执行期间开启session 2执行TRX2 TRX2: insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); (root@localhost) [test] > insert into t1 (c1,c2) values (1,'test_inc_a'), (NULL,'test_inc_b'), (5,'test_inc_c'), (0,'test_inc_d'); Query OK, 4 rows affected (5.01 sec) Records: 4 Duplicates: 0 Warnings: 0 -- 3、查看TRX2插入值的记录 (root@localhost) [test] > select * from t1 where c2 in ('test_inc_a','test_inc_b','test_inc_c','test_inc_d'); +---------+------------+ | c1 | c2 | +---------+------------+ | 1 | test_inc_a | | 5 | test_inc_c | | 1000101 | test_inc_b | | 1000102 | test_inc_d | +---------+------------+ 4 rows in set (0.34 sec) -- 4、查看当前AUTO_INCREMENT值 (root@localhost) [test] > select auto_increment from information_schema.tables where table_schema='test' and table_name='t1'; +----------------+ | auto_increment | +----------------+ | 1000103 | +----------------+ 1 row in set (0.00 sec)

TRX1：持有了 AUTO-INC 表锁，自动分配了101~1000100的自增值，并保证是连续的；

TRX2：需等待 AUTO-INC 锁的释放，自动分配了1000101~1000102的自增值。