【MySql】2.2 mysql中自增字段 auto_increment 的一些常识

theoforce

　　1. 在系统开发过程中，我们经常要用到唯一编号，mysql有一个列的属性:AUTO_INCREMENT，它定义为自增，指定了AUTO_INCREMENT的列必须要建索引，不然会报错，索引可以为主键索引，当然也可以为非主键索引。（不一定要做主键）
　　create table tb (
　　id int auto_increment,
　　name varchar(20) primary key,
　　key(id)
　　);
　　2. 指定了auto_increment的列，在插入时：
　　（1）如果把一个NULL插入到一个AUTO_INCREMENT数据列里去，MySQL将自动生成下一个序列编号。编号从1开始，并将1作为基数递增。当插入记录时，如果没有为AUTO_INCREMENT明确指定值，则等同插入NULL值。
　　insert into tb values(null,'a');
　　insert into tb(name) values('b');

　　（2）当插入记录时，如果为AUTO_INCREMENT字段明确指定了一个数值，则会出现两种情况：
　　情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO_INCREMENT数据列的值必须是唯一的；
　　情况二，如果插入的值大于已编号的值，则会把该值插入到数据列中，并且下一个编号将从这个新值开始递增。
　　show create table tb;

　　insert into tb values(null,'c'),(null,'d'),(null,'e');

　　insert into tb values(10,'f');

　　show create table tb;

　　换句话说，就是自增字段可以跳过一些编号。
　　3. 对于MyISAM表，如果用UPDATE命令更新自增列，如果列值与已有的值重复，则会出错。如果大于已有值，则下一个编号从该值开始递增。但是对于innodb表，update auto_increment字段，会导致发生报错
　　（1）MyISAM表的 update 如下所示：
        mysql> show create table test_import\G        *************************** 1. row ***************************        Table: test_import        CreateTable: CREATE TABLE `test_import` (            `user_id` int(11) NOT NULL AUTO_INCREMENT,            `user_name` varchar(20) DEFAULT NULL,            PRIMARY KEY(`user_id`)        ) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=utf8        1 row inset(0.00 sec)

　　mysql> update test_import set user_id=20 where user_id=15;
　　Query OK, 1 row affected (0.04 sec)
　　Rows matched: 1  Changed: 1  Warnings: 0

　　mysql> show create table test_import\G
　　*************************** 1. row ***************************
　　Table: test_import
　　Create Table: CREATE TABLE `test_import` (
　　`user_id` int(11) NOT NULL AUTO_INCREMENT,
　　`user_name` varchar(20) DEFAULT NULL,
　　PRIMARY KEY (`user_id`)
　　) ENGINE=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=utf8
　　1 row in set (0.00 sec)
　　（2）Innodb表的update操作如下所示
　　（可以看到在update前后，表定义语句没有变化），接着执行insert会导致主键错误！
　　mysql> show create table users\G
　　*************************** 1. row ***************************
　　Table: users
　　Create Table: CREATE TABLE `users` (
　　`id` int(10) NOT NULL AUTO_INCREMENT,
　　`name` varchar(50) NOT NULL DEFAULT 'N/A',
　　`sex` char(1) DEFAULT NULL,
　　PRIMARY KEY (`id`)
　　) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
　　1 row in set (0.00 sec)

　　mysql> update users set>　　Query OK, 1 row affected (0.05 sec)
　　Rows matched: 1  Changed: 1  Warnings: 0

　　Create Table: CREATE TABLE `users` (
　　`id` int(10) NOT NULL AUTO_INCREMENT,
　　`name` varchar(50) NOT NULL DEFAULT 'N/A',
　　`sex` char(1) DEFAULT NULL,
　　PRIMARY KEY (`id`)
　　) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
　　1 row in set (0.00 sec)
　　Innodb表继续插入会导致报错，但是只会报错一次，跳过5之后会正常插入
　　mysql> insert into users values(null,'a','F');
　　Query OK, 1 rowaffected (0.47 sec)
　　mysql> insert into users values(null,'a','F');
　　ERROR 1062 (23000):Duplicate entry '5' for key 'PRIMARY'
　　mysql> insert into users values(null,'a','F');
　　Query OK, 1 row affected (0.03 sec)

　　4. 被delete语句删除的id值，除非sql中将id重新插入，否则前面空余的id不会复用。

　　(1) delete from tableName，该语句不会引起auto_increment的变化（以上表users为例）：
　　mysql> delete from users;
　　Query OK, 7 rowsaffected (0.03 sec)
　　mysql> show create table users\G
　　***************************1. row ***************************
　　Table: users
　　Create Table: CREATETABLE `users` (
　　`id` int(10) NOTNULL AUTO_INCREMENT,
　　`name` varchar(50)NOT NULL DEFAULT 'N/A',
　　`sex` char(1)DEFAULT NULL,
　　PRIMARY KEY (`id`)
　　) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
　　1 row in set (0.00sec)
　　(2) truncate table tableName，该语句会引起auto_increment的变化，从头开始:
　　mysql> truncate table users;
　　Query OK, 0 rowsaffected (0.12 sec)
　　mysql> show create table users\G
　　***************************1. row ***************************
　　Table: users
　　Create Table: CREATETABLE `users` (
　　`id` int(10) NOT NULL AUTO_INCREMENT,
　　`name` varchar(50) NOT NULL DEFAULT 'N/A',
　　`sex` char(1) DEFAULT NULL,
　　PRIMARY KEY (`id`)
　　) ENGINE=InnoDB DEFAULT CHARSET=utf8
　　1 row in set (0.00sec)
　　5. last_insert_id()函数自动返回最后一个INSERT或 UPDATE 查询中 AUTO_INCREMENT列设置的第一个表发生的值。但该函数只与服务器的本次会话过程中生成的值有关。如果在与服务器的本次会话中尚未生成AUTO_INCREMENT值，则该函数返回0。
　　mysql> insert into users values(null,'a','F');
　　Query OK, 1 rowaffected (0.07 sec)
　　mysql> select last_insert_id();
　　+------------------+
　　| last_insert_id() |
　　+------------------+
　　|2 |
　　+------------------+
　　1 row in set (0.00sec)
　　mysql> insert into users values(null,'a','F'),(null,'a','F'),(null,'a','F');
　　Query OK, 3 rowaffected (0.05 sec)
　　mysql> select last_insert_id();
　　+------------------+
　　| last_insert_id() |
　　+------------------+
　　|3 |
　　+------------------+

　　虽然将3个新行插入 users, 但对这些行的第一行产生的>　　返回的值。
　　6.修改AUTO_INCREMENT字段的起始值
　　MySQL支持多种数据表，每种数据表的自增属性都有差异，可以在创建数据表时设置数据列的自增属性，也可以过后修改。
　　mysql> CREATE TABLE test2
　　-> (

　　->>　　-> username VARCHAR(15) NOT NULL
　　-> )AUTO_INCREMENT = 100;
　　Query OK, 0 rowsaffected (0.10 sec)
　　mysql> insert into test2 values(null,'a');
　　Query OK, 1 rowaffected (0.07 sec)
　　mysql> select * from test2;
　　+-----+----------+

　　|>　　+-----+----------+
　　| 100 | a|
　　+-----+----------+
　　1 row in set (0.00sec)
　　也可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。
　　但是如果设置的n比目前的数值小的话，执行的sql不会报错，但是不会生效！MyISAM和Innodb均是如此。
　　mysql> show create table test_import;
　　+-------------+-------------------------------------------------------
　　| Table       | Create Table
　　+-------------+-------------------------------------------------------
　　| test_import | CREATE TABLE `test_import` (
　　`user_id` int(11) NOT NULL AUTO_INCREMENT,
　　`user_name` varchar(20) DEFAULT NULL,
　　PRIMARY KEY (`user_id`)
　　) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |
　　+-------------+-------------------------------------------------------
　　1 row in set (0.00 sec)

　　mysql>>　　Query OK, 20 rows affected (0.25 sec)
　　Records: 20  Duplicates: 0  Warnings: 0
　　mysql> show create table test_import;
　　+-------------+-------------------------------------------------------
　　| Table       | Create Table
　　+-------------+-------------------------------------------------------
　　| test_import | CREATE TABLE `test_import` (
　　`user_id` int(11) NOT NULL AUTO_INCREMENT,
　　`user_name` varchar(20) DEFAULT NULL,
　　PRIMARY KEY (`user_id`)
　　) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 |
　　+-------------+-------------------------------------------------------
　　1 row in set (0.00 sec)
　　7.auto_increment_increment & auto_increment_offset 两个变量的介绍
　　这两个参数作用：控制自增列AUTO_INCREMENT的行为
　　两个值的含义：
　　auto_increment_increment：自增值的自增量
　　auto_increment_offset： 自增值的偏移量
　　设置了两个值之后，改服务器的自增字段值限定为：
　　auto_increment_offset + auto_increment_increment*N  的值，其中N>=0，但是上限还是要受定义字段的类型限制。
　　比如：
　　auto_increment_offset=1
　　auto_increment_increment=2
　　那么ID则是所有的奇数[1,3,5,7，.....]
　　如果：
　　auto_increment_offset=5
　　auto_increment_increment=10
　　那么ID则是所有的奇数[5,15,25,35，.....]
　　查看当前值：
　　mysql> show variables like '%auto_increment%';
　　+--------------------------+-------+
　　| Variable_name| Value |
　　+--------------------------+-------+
　　|auto_increment_increment | 1|
　　|auto_increment_offset| 1|
　　+--------------------------+-------+
　　2 rows in set (0.00sec)
　　配置auto-increment-increment&auto-increment-offset的值：
　　例1：
　　mysql> truncate users;
　　Query OK, 0 rows affected (0.03 sec)
　　mysql> set session auto_increment_increment=2;
　　Query OK, 0 rowsaffected (0.00 sec)
　　mysql> set session auto_increment_offset=1;
　　Query OK, 0 rowsaffected (0.00 sec)
　　mysql> show session variables like '%auto_incre%';
　　+--------------------------+-------+
　　| Variable_name| Value |
　　+--------------------------+-------+
　　| auto_increment_increment| 2|
　　|auto_increment_offset| 1|
　　+--------------------------+-------+
　　2 rows in set (0.00sec)
　　mysql> insert into users values(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F');
　　Query OK, 6 rowsaffected (0.04 sec)
　　Records: 6Duplicates: 0Warnings: 0
　　mysql> select * from users;
　　+----+------+------+

　　|>　　+----+------+------+
　　|1 | a| F|
　　|3 | a| F|
　　|5 | a| F|
　　|7 | a| F|
　　|9 | a| F|
　　|11 | a| F|
　　+----+------+------+
　　6 rows in set (0.00sec)
　　例2：
　　mysql> truncate users;
　　Query OK, 0 rowsaffected (0.07 sec)
　　mysql> set session auto_increment_increment=2;
　　Query OK, 0 rowsaffected (0.00 sec)
　　mysql> set session auto_increment_offset=2;
　　Query OK, 0 rowsaffected (0.00 sec)
　　mysql> show session variables like '%auto_incre%';
　　+--------------------------+-------+
　　| Variable_name| Value |
　　+--------------------------+-------+
　　|auto_increment_increment | 2|
　　|auto_increment_offset| 2|
　　+--------------------------+-------+
　　2 rows in set (0.00sec)
　　mysql> insert into users values(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F');
　　Query OK, 6 rowsaffected (0.07 sec)
　　Records: 6Duplicates: 0Warnings: 0
　　mysql> select * from users;
　　+----+------+------+

　　|>　　+----+------+------+
　　|2 | a| F|
　　|4 | a| F|
　　|6 | a| F|
　　|8 | a| F|
　　|10 | a| F|
　　|12 | a| F|
　　+----+------+------+
　　6 rows in set (0.00sec)
　　例3：
　　mysql> truncate users;
　　Query OK, 0 rowsaffected (0.07 sec)
　　mysql> set session auto_increment_increment=10;
　　Query OK, 0 rowsaffected (0.00 sec)
　　mysql> set session auto_increment_offset=5;
　　Query OK, 0 rowsaffected (0.00 sec)
　　mysql> show session variables like '%auto_incre%';
　　+--------------------------+-------+
　　| Variable_name | Value |
　　+--------------------------+-------+
　　|auto_increment_increment | 10|
　　|auto_increment_offset| 5|
　　+--------------------------+-------+
　　2 rows in set (0.00sec)
　　mysql> insert into users values(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F'),(null,'a','F');
　　Query OK, 6 rowsaffected (0.05 sec)
　　Records: 6Duplicates: 0Warnings: 0
　　mysql> select * from users;
　　+----+------+------+

　　|>　　+----+------+------+
　　|  5 | a| F|
　　| 15 | a| F|
　　| 25 | a| F|
　　| 35 | a| F|
　　| 45 | a| F|
　　| 55 | a| F|
　　+----+------+------+
　　6 rows in set (0.00sec)
　　一个很重要的问题：如果在原有的序列中强制插入一个值，比如上面的例子，下一个数据我插入57，58,59,那再往后生成的值会受前面插入数据的影响吗？
　　答案是： 不会的！！
　　mysql> insert into users values(57,'a','F'),(58,'a','F'),(59,'a','F');
　　Query OK, 3 rowsaffected (0.04 sec)
　　Records: 3Duplicates: 0Warnings: 0
　　mysql> select * from users;
　　+----+------+------+

　　|>　　+----+------+------+
　　|5 | a| F|
　　| 15 | a| F|
　　| 25 | a| F|
　　| 35 | a| F|
　　| 45 | a| F|
　　| 55 | a| F|
　　| 57 | a| F|
　　| 58 | a| F|
　　| 59 | a| F|
　　+----+------+------+
　　9 rows in set (0.00sec)
　　mysql> insert into users values(null,'a','F'),(null,'a','F'),(null,'a','F');
　　Query OK, 3 rowsaffected (0.05 sec)
　　Records: 3Duplicates: 0Warnings: 0
　　mysql> select * from users;
　　+----+------+------+

　　|>　　+----+------+------+
　　|5 | a| F|
　　| 15 | a| F|
　　| 25 | a| F|
　　| 35 | a| F|
　　| 45 | a|F|
　　| 55 | a| F|
　　| 57 | a| F|
　　| 58 | a| F|
　　| 59 | a| F|
　　| 65 | a| F|
　　| 75 | a| F|
　　| 85 | a| F|
　　+----+------+------+
　　12 rows in set (0.00sec)