Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.