Research and analysis of auto-increment field auto_commit

MySQL auto-increment field, auto-increment field counter is in the main storage, not on the hard disk (This counter is stored only in main memory, not on disk).
1, add a table, set up an auto-increment primary key field

create table t(id int primary key auto_increment, name varchar(3000)) engine=innodb;

2, you can let the system auto-increment, or you can manually set the input auto-increment.

insert into t select 4, 'a44';
insert into t(name) select 'a8';

3, query the auto-increment value of the current table

SELECT MAX(id) FROM t FOR UPDATE;

4, if the auto-increment value you set exceeds the current The maximum self-increment value will be based on the self-increment value you set, and it will start to increase automatically. For example:
SELECT MAX(id) FROM t FOR UPDATE;The resulting value is 4, and then you insert into t select 9, 'a44';then at this time, MAX(id) is 9,
Then the next auto-increment value is 16 instead of 5. Of course, if you execute the statement insert into t select 5, 'a44';, it will pass.


5. If there is a transaction and rollback is performed after insert, the counter in autocommit will not be rolled back and will still be +1.


6, Parameter

innodb_autoinc_lock_made

Add a line directly in my.cnf

[mysqld]
innodb_autoinc_lock_mode = 0

Then restart the mysql database, execute show variables like '%innodb_autoinc_lock_mode%'; and find that the value is 0, Successfully modified.


7. How to enter a table with only auto-increment fields

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7.1 Insert your own value

insert into t1 select 1;
mysql> select * from t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

7.2 Call the mysql function LAST_INSERT_ID();

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.01 sec)

ps: Here is 0, because the last insert was the entered value and this function was not called, so the initial value is from 0, but it does not affect the normal insert value to the table.

mysql> insert into t1 select LAST_INSERT_ID();
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)
mysql>

The above is the content of the research and analysis of the auto-increment field auto_commit. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!