威尼斯网站分析mysql中的auto_increment的标题_Mysql

2019-12-19 00:02栏目:计算机资讯

前日在逛论坛时,开采一个像样很简短的标题,却引起了权族的广大关切:这是大器晚成道很早的面试题:一张表,里面有ID自增主键,当insert了17条记下之后,删除了第15,16,17条记下,再把Mysql重启,再insert一条记下,那条记下的ID是18仍然15 。答案:

比方表的等级次序是MyISAM,那么是18。 因为MyISAM表会把自增主键的最大ID记录到数据文件里,重启MySQL自增主键的最大ID也不会遗弃。 假诺表的品类是InnoDB,那么是15。 InnoDB表只是把自增主键的最大ID记录到内部存款和储蓄器中,所以重启数据库也许是对表进行OPTIMIZE操作,都会招致最大ID错过。

仁慈也做了个试验,结果表明了地点的说法。真是可耻啊,看似不难的标题本人也打错了。复制代码 代码如下:mysql> select * from test1; ---- ----------- | id | name | ---- ----------- | 1 | 陈兵辉 || 2 | chen || 3 | chen || 4 | chen || 5 | chen || 6 | chen || 7 | chen || 8 | chen || 9 | chen || 10 | chen || 11 | chen | ---- ----------- 11 rows in set

mysql> delete from test1 where id in ;Query OK, 3 rows affected

mysql> show create table test1;CREATE TABLE `test1` NOT NULL auto_increment, `name` varchar default NULL, PRIMARY KEY ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |mysql> exit;Bye[root@fsailing1 ~]# service mysqld restart停止 MySQL: [确定]启动 MySQL: [确定][root@fsailing1 ~]# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 2Server version: 5.0.95 Source distributionCopyright 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.

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 namesYou can turn off this feature to get a quicker startup with -A

Database changedmysql> show create table test1;| CREATE TABLE `test1` NOT NULL auto_increment, `name` varchar default NULL, PRIMARY KEY ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHA帕杰罗SET=utf8 |2,别的还应该有八个正是收获当前数据库表的自增字段数。 复制代码 代码如下:mysql> select last_insert_id(); ------------------ | last_insert_id() | ------------------ | 0 | ------------------ 1 row in set

版权声明:本文由威尼斯网站发布于计算机资讯,转载请注明出处:威尼斯网站分析mysql中的auto_increment的标题_Mysql