在系统开发过程中,我们经常要用到唯一编号。使用过mysql的人都应该知道,mysql有一个定义列为自增的属性:AUTO_INCREMENT。 指定了AUTO_INCREMENT的列必须要建索引,不然会报错,索引可以为主键索引,当然也可以为非主键索引。(不一定要做主键) mysql> create table t4 (id int auto_increment); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> 下面的定义把t5表的主键定义为了name,而非自增的id字段 mysql> mysql> create table t5 (id int auto_increment,name varchar(20) primary key,key(id)); Query OK, 0 rows affected (0.01 sec) 指定了auto_increment的列,在插入时:
修改AUTO_INCREMENT字段的起始值可用alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值。 但是如果设置的n比目前的数值小的话,执行的sql不会报错,但是不会生效!MyISAM和Innodb均是如此。 mysql> show create table t2; +-------+----------------------- CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> alter table t2 auto_increment=2; Query OK, 6 rows affected (0.04 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> show create table t2; +-------+-------------------- CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 auto_increment_increment & auto_increment_offset 两个变量的介绍这两个参数作用:控制自增列AUTO_INCREMENT的行为,用于MASTER-MASTER之间的复制,防止出现重复值。 两个变量均可以设置为全局或局部变量,并且假定每个值都可以为1到65,535之间的整数值。将其中一个变量设置为0会使该变量为1。如果试图将这些变量设置为大于65,535或小于0的值,则会将该值设置为65,535。如果向将auto_increment_increment或auto_increment_offset设置为非整数值,则会给出错误,并且变量的实际值在这种情况下保持不变。 两个值的含义: 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 | +--------------------------+-------+ 配置auto-increment-increment&auto-increment-offset的值: (1):修改配置文件,重启mysqld vi my.cnf 将 auto-increment-increment = 2 auto-increment-offset = 2 加入到mysqld相关的配置中 (2):通过set命令修改,不需要重启mysqld,一般需要用set global来设置 set global auto_increment_increment=2; set global auto_increment_offset=2; 注意:在一个会话中,如果用set global 修改了mysql的某个变量值,如果不退出session,重新连接,你用show variables 看到的还是修改之前的值,因为show variables 默认返回的是当前session的值,最好用show session variables 和 show global variables 来查看对应的变量值。 下面是个例子: mysql> set global auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%auto_increment%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> show session variables like '%auto_increment%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) mysql> show global variables like '%auto_increment%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 2 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec) 当然也可以只设定当前session有效 set session auto_increment_increment=2; set session auto_increment_offset=2; 具体的例子: auto_increment_increment=2 auto_increment_offset=1 mysql> truncate t2; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> set session auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_offset=1; Query OK, 0 rows affected (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.00 sec) mysql> insert into t2 values (null),(null),(null),(null),(null),(null); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+ | id | +----+ | 1 | | 3 | | 5 | | 7 | | 9 | | 11 | +----+ 6 rows in set (0.00 sec) auto_increment_increment=2 auto_increment_offset=2 mysql> truncate t2; Query OK, 0 rows affected (0.00 sec) mysql> mysql> set session auto_increment_increment=2; Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_offset=2; Query OK, 0 rows affected (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.00 sec) mysql> insert into t2 values (null),(null),(null),(null),(null),(null); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+ | id | +----+ | 2 | | 4 | | 6 | | 8 | | 10 | | 12 | +----+ 6 rows in set (0.00 sec) auto_increment_increment=10 auto_increment_offset=5 mysql> truncate t2; Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_increment=10; Query OK, 0 rows affected (0.00 sec) mysql> set session auto_increment_offset=5; Query OK, 0 rows affected (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.00 sec) mysql> insert into t2 values (null),(null),(null),(null),(null),(null); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+ | id | +----+ | 5 | | 15 | | 25 | | 35 | | 45 | | 55 | +----+ 6 rows in set (0.00 sec) 一个很重要的问题:如果在原有的序列中强制插入一个值,比如上面的例子,下一个数据我插入57,那再往后生成的值会受前面插入数据的影响吗? 答案是: 不会的!! mysql> insert into t2 values (57),(58); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+ | id | +----+ | 5 | | 15 | | 25 | | 35 | | 45 | | 55 | | 57 | | 58 | +----+ 8 rows in set (0.00 sec) mysql> insert into t2 values (null),(null),(null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+ | id | +----+ | 5 | | 15 | | 25 | | 35 | | 45 | | 55 | | 57 | | 58 | | 65 | | 75 | | 85 | +----+ 11 rows in set (0.00 sec) |
|