DB LIFE

MySQL AUTO_INCREMENT

Leave a comment

AUTO_INCREMENT属性
1.每张表只能有一个数据列为自动增长列，且必须是索引(常使用PRIMARY KEY或UNIQUE索引，但非唯一索引也ok)。
2.必须有NOT NULL约束条件(即使没有明确声明，MySQL会自动把该数据列设置为NOT NULL)。
3.最近生成的序号值可以通过调用LAST_INSERT_ID()函数获得，可以在后续的语句里引用AUTO_INCREMENT值。
4.可以通过插入一个序列号值大于当前计数器值的数据行的方法让计数器跳过一个区间。

MyISAM中的AUTO_INCREMENT
1.MyISAM序列默认从1开始编号，或者通过在create table语句中的AUTO_INCREMENT=n来设置初始值。最多只能有一个AUTO_INCREMENT数据列。

CREATE TABLE mytbl
(
     seq INT UNSIGNED NOT NULL AUTO_INCREMENT,
     PRIMARY KEY(seq)
)ENGINE=MYISAM AUTO_INCREMENT=1000;

2.以下SQL语句将使得下一个编号从2000开始

ALTER TABLE mytbl AUTO_INCREMENT=2000;

3.MyISAM支持组合索引，为表创建有多个数据列构成的键，并将AUTO_INCREMENT设置为这个组合索引的最后一列。则奇妙的现象，对于左边数据列构成的不同组合键排序后，AUTO_INCREMENT生成一组彼此不干扰的序列值。

mysql> create table myisam_test
-> (
-> name VARCHAR(20) NOT NULL,
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> other VARCHAR(100),
-> PRIMARY KEY(name,id)
-> )ENGINE=MYISAM;
Query OK, 0 rows affected (0.10 sec)

mysql> select * from myisam_test;
+------+----+-------+
| name | id | other |
+------+----+-------+
| one | 1 | 1 |
| one | 2 | 2 |
| two | 1 | 3 |
| two | 2 | 4 |
| one | 3 | 5 |
| one | 4 | 6 |
| two | 3 | 7 |
+------+----+-------+
7 rows in set (0.00 sec)

mysql> select * from myisam_test order by name,id;
+------+----+-------+
| name | id | other |
+------+----+-------+
| one | 1 | 1 |
| one | 2 | 2 |
| one | 3 | 5 |
| one | 4 | 6 |
| two | 1 | 3 |
| two | 2 | 4 |
| two | 3 | 7 |
+------+----+-------+
7 rows in set (0.00 sec)

ps.other列表示元组插入的顺序，可以发现自增列是在分组的情况下进行序号自增的。

4.自增序列单调，当某数据行删除后，该序列不会再被使用。特列为组合索引中，从序列顶端删除的值将被重复使用;或者使用TRUNCATE TABLE命令清空了一个数据表时，计数器将被重置为从1开始。

mysql>  create table myisam_id
    ->  (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   name VARCHAR(20),
    ->   PRIMARY KEY(id)
    ->  )ENGINE=MYISAM;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO myisam_id(name) values('1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myisam_id(name) values('2');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myisam_id(name) values('3');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myisam_id(name) values('4');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myisam_id(name) values('5');
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 5    |
+----+------+
5 rows in set (0.00 sec)

mysql> delete from myisam_id where name='5';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO myisam_id(name) values('6');
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  6 | 6    |
+----+------+
5 rows in set (0.00 sec)

但是在组合索引中，接着3中的例子

mysql> select * from myisam_test order by name,id;
+------+----+-------+
| name | id | other |
+------+----+-------+
| one  |  1 | 1     |
| one  |  2 | 2     |
| one  |  3 | 5     |
| one  |  4 | 6     |
| two  |  1 | 3     |
| two  |  2 | 4     |
| two  |  3 | 7     |
+------+----+-------+
7 rows in set (0.00 sec)

mysql> delete from myisam_test where other="7";
Query OK, 1 row affected (0.02 sec)

mysql> select * from myisam_test order by name,id;
+------+----+-------+
| name | id | other |
+------+----+-------+
| one  |  1 | 1     |
| one  |  2 | 2     |
| one  |  3 | 5     |
| one  |  4 | 6     |
| two  |  1 | 3     |
| two  |  2 | 4     |
+------+----+-------+
6 rows in set (0.00 sec)

mysql> insert into myisam_test(name,other) values('two','8');
Query OK, 1 row affected (0.00 sec)

mysql> select * from myisam_test order by name,id;
+------+----+-------+
| name | id | other |
+------+----+-------+
| one  |  1 | 1     |
| one  |  2 | 2     |
| one  |  3 | 5     |
| one  |  4 | 6     |
| two  |  1 | 3     |
| two  |  2 | 4     |
| two  |  3 | 8     |
+------+----+-------+
7 rows in set (0.00 sec)

 

 

InnoDB中的AUTO_INCREMENT
1.同MyISAM的1和2
2.从序列顶端删除的值通常不再使用，除TRUNCATE TABLE(同MyISAM)，还有一种情况：InnoDB在内存中维护计数器以生成后续的序号值，该计数器并未存储在数据表本身的内部，那么如果在序列的顶端删除了一些值以后重启服务器，删除过的那些值将被重复使用。

mysql> create table innodb_id
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> PRIMARY KEY(id)
    -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO innodb_id(name) values('1');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO innodb_id(name) values('2');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO innodb_id(name) values('3');
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO innodb_id(name) values('4');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO innodb_id(name) values('5');
Query OK, 1 row affected (0.07 sec)

mysql> select * from innodb_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 5    |
+----+------+
5 rows in set (0.00 sec)

mysql> delete from innodb_id where name='5';
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO innodb_id(name) values('6');
Query OK, 1 row affected (0.02 sec)

mysql> select * from innodb_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  6 | 6    |
+----+------+
5 rows in set (0.00 sec)

mysql> delete from innodb_id where name='6';
Query OK, 1 row affected (0.06 sec)

mysql> select * from innodb_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
+----+------+
4 rows in set (0.00 sec)

服务器重启后

mysql> use test
Database changed
mysql> select * from innodb_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
+----+------+
4 rows in set (0.00 sec)

mysql> INSERT INTO innodb_id(name) values('7');
Query OK, 1 row affected (0.14 sec)

mysql> select * from innodb_id;
+----+------+
| id | name |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
|  4 | 4    |
|  5 | 7    |
+----+------+
5 rows in set (0.00 sec)

ps.MyISAM经试验表明重启服务器依旧不使用重复值。

3.在组合索引中，自增列必须为第一列。

mysql> create table innodb_test
    -> (
    -> name VARCHAR(20) NOT NULL,
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> other VARCHAR(100),
    -> PRIMARY KEY(name,id)
    -> )ENGINE=INNODB;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum
n and it must be defined as a key
mysql> create table innodb_test
 -> (
 -> name VARCHAR(20) NOT NULL,
 -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 -> other VARCHAR(100),
 -> PRIMARY KEY(id,name)
 -> )ENGINE=INNODB;
Query OK, 0 rows affected (0.10 sec)

 

Leave a Reply

Your email address will not be published. Required fields are marked *