innodb对于update,insert,delete关于auto_incremant的影响
mysql> create table inc_innodb (id int auto_increment primary key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into inc_innodb (name) values ('name1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into inc_innodb (name) values ('name22');
Query OK, 1 row affected (0.00 sec)
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into inc_innodb values (4,'name4');
Query OK, 1 row affected (0.01 sec)
mysql> select * from inc_innodb;
+----+--------+
| id | name |
+----+--------+
| 1 | name1 |
| 2 | name22 |
| 4 | name4 |
+----+--------+
3 rows in set (0.00 sec)
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update inc_innodb set id=10 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from inc_innodb where id=10;
Query OK, 1 row affected (0.00 sec)
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
对于innodb来说,insert来说 都会把 AUTO_INCREMENT的值升上去,
update不会把AUTO_INCREMENT值升上去
但是delete不会把AUTO_INCREMENT的值降下去。
转载请注明源出处
QQ 273002188 欢迎一起学习
QQ 群 236941212
oracle,mysql,mongo 相互交流
相关:
http://blog.itpub.net/25099483/viewspace-1869360/
mysql> create table inc_innodb (id int auto_increment primary key,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into inc_innodb (name) values ('name1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into inc_innodb (name) values ('name22');
Query OK, 1 row affected (0.00 sec)
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into inc_innodb values (4,'name4');
Query OK, 1 row affected (0.01 sec)
mysql> select * from inc_innodb;
+----+--------+
| id | name |
+----+--------+
| 1 | name1 |
| 2 | name22 |
| 4 | name4 |
+----+--------+
3 rows in set (0.00 sec)
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update inc_innodb set id=10 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> delete from inc_innodb where id=10;
Query OK, 1 row affected (0.00 sec)
mysql> show create table inc_innodb;
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| inc_innodb | CREATE TABLE `inc_innodb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
对于innodb来说,insert来说 都会把 AUTO_INCREMENT的值升上去,
update不会把AUTO_INCREMENT值升上去
但是delete不会把AUTO_INCREMENT的值降下去。
转载请注明源出处
QQ 273002188 欢迎一起学习
QQ 群 236941212
oracle,mysql,mongo 相互交流
相关:
http://blog.itpub.net/25099483/viewspace-1869360/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25099483/viewspace-1869361/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25099483/viewspace-1869361/