在mysql中,可以通过给字段添加“AUTO_INCREMENT”属性来给字段增加自增属性,默认情况下,AUTO_INCREMENT 的初始值是 1,每新增一条记录,字段值自动加 1。一个表中只能有一个字段使用 AUTO_INCREMENT 约束,且该字段必须有唯一索引,以避免序号重复。该属性可以为主键,非主键时必须有唯一索引。
- AUTO_INCREMENT 约束的字段必须有唯一索引,以避免序号重复(即为主键或有唯一索引)。
- AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性。
- AUTO_INCREMENT 约束的字段只能是整数类型(TINYINT、SMALLINT、INT、BIGINT 等)。
- AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束,如果达到上限,AUTO_INCREMENT 就会失效。
- 当某个属性定义为自增长后,这个属性的值就不再需要用户输入数据了,而由数据库系统根据定义自动赋值。每增加一条记录,自增属性会自动以相同的步长进行增长。
1、新建表自增字段/属性
在创建表时,可以将某个字段/属性设置为auto_increment,则该字段为自增属性。auto_increment是MySQL唯一扩展的完整性约束,当向数据库表中插入新纪录时,字段上的值会自动生成唯一的id。在设置具体的auto_increment约束时,一个数据库表中只能有一个字段使用该约束,并且该字段必须是整数类型。
- InnoDB引擎的自增值在每次重启后,第一次打开表的时候,都会去找自增值的最大值max,然后将max+步长作为这个表当前的自增值,默认步长为1。
CREATE TABLE student(
id int NOT NULL auto_increment primary key,
name VARCHAR(8),
sex CHAR(1)
)
1.1 自增属性为null值时
插入数据时指定自增属性的值为null时。
-- 插入数据
INSERT INTO student VALUES(NULL, "张三", 'm');
INSERT INTO student VALUES(NULL, "李四", 'm');
执行上述语句然后刷新表,看到id的值默认从1开始自增。
1.2 自增属性不指定值时
插入数据时不指定自增属性的值。
INSERT INTO student(name, sex) VALUES("王五", 'f');
可以看到不指定自增属性的值和指定自增属性值为null的结果一致,mysql属性均保持自增状态。
1.3 自增属性指定具体的值
如果指定具体的值,倘若这个值小于目前的自增字段的最大值,则输入失败,相反,如果大于则插入成功,但下一次自增则是在自增值的最大值基础上自增。
执行下述语句:
INSERT INTO student VALUES(8, "小三", 'f');
再次插入一条数据,不指定自增属性的值。
INSERT INTO student(name, sex) VALUES("小四", 'm');
1.4 last_insert_id()查看自增值
-- 该函数返回最近一个insert语句生成的第一个自动增长列的值(最大的),值为bigint unsigned类型。
select last_insert_id();
1.5 更改auto_increment的值
当你更改了auto_increment的最大值的时候,如果你插入新的数据的时候,它会在你更改后的最大值基础上加1。
-- 修改当前auto_increment值, 22表示你要更新字段的最大值为22
alter table student auto_increment= 22;
这时候根据last_insert_id()查看自增值,可以看到还是之前的值。
再次插入一条数据
INSERT INTO student(name, sex) VALUES("小五", 'f');
从结果可以看到,自增值为我们设置的最大值22。多次插入数据,会发现数据自增依次为23,24…
2、已有表上创建自增字段
2.1 去除原表自增属性的自增功能
还是以上面的表为例,因为一个数据库表中只能有一个字段使用自增长,所以我们先去除原id的自增功能。
ALTER TABLE student modify id int;
执行完上述语句后查看表结构可以看到id已经没有自增功能,执行下述语句会报错(因为id为主键不能自增,必须指定值)。
INSERT INTO student(name, sex) VALUES("小五", 'f');
2.2 新增自增属性
通过下述语句新增自增属性,其中auto_id为自增属性名称,auto_increment指定该属性自增,UNIQUE key约束该属性为唯一键。表中已有数据auto_id的值默认从1开始自增。
ALTER TABLE student add column auto_id int NOT NULL auto_increment UNIQUE key;
上述语句也可以拆分一下(作用和上面的一条语句完全一致):
-- 新增auto_id属性,并设置唯一索引,这里无法设置为主键,因为主键不能为null
ALTER TABLE student add column auto_id int unique key;
-- 给auto_id属性设置为自增
ALTER TABLE student MODIFY auto_id int auto_increment;
再查看数据可以发现,auto_id的值已经赋值为从1开始的自增值。
如果原表id非主键,可以指定新增的自增属性为主键,这时候就不需要指定唯一约束了(UNIQUE key)。
ALTER TABLE student add column auto_id int NOT NULL auto_increment primary key;
2.3 插入数据
INSERT INTO student(id, name, sex) VALUES(25, "小五", 'f');
新增的属性auto_id数据自增,历史数据会从1开始自增。
2.4 指定自增开始值
通过设置auto_increment指定自增属性的开始值,如下指定开始值为100,则后续新增的数据从100开始自增。
ALTER TABLE student auto_increment=100;
执行完上述语句后我们再插入一条数据:
INSERT INTO student(id, name, sex) VALUES(26, "小六", 'm');
可以看到新插入的数据的auto_id值是100。
2.5 查看下一条自增属性的值
set @maxId=1;
select max(auto_id) into @maxId from student;
select @maxId+1 from dual;
评论区