自增锁预分配ID

mysql> show create table pp;

CREATE TABLE `pp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk 

mysql>  insert into pp(name) values('xx');
Query OK, 1 row affected (0.18 sec)

mysql> show create table pp;

CREATE TABLE `pp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk 

mysql> insert into pp(name) values('xx'),('xx'),('xx'),('xx');    
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> show create table pp;

CREATE TABLE `pp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gbk

mysql> insert into pp(name) select name from pp;   预分配ID值
Query OK, 5 rows affected (0.20 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show create table pp;                    
CREATE TABLE `pp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=gbk 

mysql> select * from pp;
+----+------+
| id | name |
+----+------+
|  1 | xx   |
|  2 | xx   |
|  3 | xx   |
|  4 | xx   |
|  5 | xx   |
|  6 | xx   |
|  7 | xx   |
|  8 | xx   |
|  9 | xx   |
| 10 | xx   |
+----+------+
10 rows in set (0.00 sec)

mysql>  insert into pp(name) values('xx');
Query OK, 1 row affected (0.18 sec)

mysql> select * from pp;
+----+------+
| id | name |
+----+------+
|  1 | xx   |
|  2 | xx   |
|  3 | xx   |
|  4 | xx   |
|  5 | xx   |
|  6 | xx   |
|  7 | xx   |
|  8 | xx   |
|  9 | xx   |
| 10 | xx   |
| 13 | xx   |
+----+------+
11 rows in set (0.00 sec)

 

mysql> insert into pp(name) select name from pp;   
Query OK, 11 rows affected (0.16 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> show create table pp;   
CREATE TABLE `pp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=gbk |
+

mysql> select * from pp;                        
+----+------+
| id | name |
+----+------+
|  1 | xx   |
|  2 | xx   |
|  3 | xx   |
|  4 | xx   |
|  5 | xx   |
|  6 | xx   |
|  7 | xx   |
|  8 | xx   |
|  9 | xx   |
| 10 | xx   |
| 13 | xx   |
| 14 | xx   |
| 15 | xx   |
| 16 | xx   |
| 17 | xx   |
| 18 | xx   |
| 19 | xx   |
| 20 | xx   |
| 21 | xx   |
| 22 | xx   |
| 23 | xx   |
| 24 | xx   |
+----+------+
22 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

模拟崩溃

kill -9 `pidof mysqld`

 

MYSQLD再起动，再测ID

mysql> show create table pp;

CREATE TABLE `pp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=gbk

 

并没有持久化29,

select max(id) from pp   取初值 25    回退了