About AUTO-INC
Posted by:
five small
Date: April 20, 2021 05:59AM
Hello, In 《15.6.1.6 AUTO_INCREMENT Handling in InnoDB》 about “Mixed-mode inserts” like
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
It say "four auto-increment values are allocated at the time the statement is processed" With innodb_autoinc_lock_mode set to 1 (“consecutive”)
But I test with my way,threr are something i can't understand:
------------------------------------------------------------------------------
mysql> select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
| 1 |
+----------------------------+
------------------------------------------------------------------------------
My table 'aa' follows:
mysql> create table aa(id int primary key auto_increment,name varchar(10));
------------------------------------------------------------------------------
And i use "Mixed-mode inserts",and insert 5 rows:
mysql> insert into aa values(null,"b"),(null,"c"),(null,"d"),(5,"e"),(null,"f");
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
------------------------------------------------------------------------------
mysql> show create table aa\G
*************************** 1. row ***************************
Table: aa
Create Table: CREATE TABLE `aa` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
------------------------------------------------------------------------------
In my opinoon with define,i will get 5 numbers "immediately",so the next available auto-increment value is 6,But actually, is 7.So I'm confused now,Please help me out. Thank you very much.