How to solve the problem of auto_increment in mysql
This article will explain in detail how to solve the problem of auto_increment in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.
The problem of auto_increment in mysql
When I was shopping today, I found a seemingly simple problem, but it aroused widespread concern:
This is an early interview question:
A table with ID self-increasing primary key. When insert has 17 records, delete 15, 16 and 17 records, and restart Mysql.
Insert another record, whether the ID of this record is 18 or 15. Www.2cto.com
Answer:
If the type of the table is MyISAM, it is 18.
Because the MyISAM table records the maximum ID of the self-incrementing primary key in the data file, the maximum ID of restarting the MySQL self-incrementing primary key will not be lost.
If the type of the table is InnoDB, it is 15.
The InnoDB table simply records the maximum ID of the self-increasing primary key into memory, so restart or OPTIMIZE the table
Will cause the maximum ID to be lost.
I have also done an experiment, and the results confirm the above statement. I'm really ashamed that I have dialed the wrong number for a seemingly simple question.
[html]
> select * from test1
+-+ +
| | id | name |
+-+ +
| 1 | Chen Binghui |
| | 2 | chen |
| | 3 | chen |
| | 4 | chen |
| | 5 | chen |
| | 6 | chen |
| | 7 | chen |
| | 8 | chen |
| | 9 | chen |
| | 10 | chen |
| | 11 | chen |
+-+ +
11 rows in set (0.00 sec)
Www.2cto.com
Mysql > delete from test1 where id in (10, 11, 10, 9)
Query OK, 3 rows affected (0.03 sec)
Mysql > show create table test1
CREATE TABLE `test1` (
`id`int (11) NOT NULL auto_increment
`name` varchar (10) default NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
Mysql > exit
Bye
[root@fsailing1 ~] # service mysqld restart
Stop MySQL: [OK]
Start MySQL: [OK]
[root@fsailing1] # mysql-uroot-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 2
Server version: 5.0.95 Source distribution
Copyright (c) 2000, 2011, and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
Affiliates. Other names may be trademarks of their respective
Owners.
Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.
Mysql > use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-A
Database changed
Mysql > show create table test1
| | CREATE TABLE `test1` (
`id`int (11) NOT NULL auto_increment
`name` varchar (10) default NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 | www.2cto.com
2, and another is to get the number of self-incrementing fields of the current database table.
[html]
Mysql > select last_insert_id ()
+-+
| | last_insert_id () |
+-+
| | 0 |
+-+
1 row in set (0.00 sec)
This is the end of this article on "how to solve the problem of auto_increment in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.
