侧边栏壁纸
博主头像
孔子说JAVA博主等级

成功只是一只沦落在鸡窝里的鹰,成功永远属于自信且有毅力的人!

  • 累计撰写 285 篇文章
  • 累计创建 125 个标签
  • 累计收到 4 条评论

目 录CONTENT

文章目录

MySql设置自增字段的几种方式

孔子说JAVA
2022-08-19 / 0 评论 / 0 点赞 / 152 阅读 / 2,846 字 / 正在检测是否收录...

在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');

image-1660782795600

执行上述语句然后刷新表,看到id的值默认从1开始自增。

1.2 自增属性不指定值时

插入数据时不指定自增属性的值。

INSERT INTO student(name, sex) VALUES("王五", 'f');

image-1660782906725

可以看到不指定自增属性的值和指定自增属性值为null的结果一致,mysql属性均保持自增状态。

1.3 自增属性指定具体的值

如果指定具体的值,倘若这个值小于目前的自增字段的最大值,则输入失败,相反,如果大于则插入成功,但下一次自增则是在自增值的最大值基础上自增。

执行下述语句:

INSERT INTO student VALUES(8, "小三", 'f');

image-1660783160352

再次插入一条数据,不指定自增属性的值。

INSERT INTO student(name, sex) VALUES("小四", 'm');

image-1660783224543

1.4 last_insert_id()查看自增值

-- 该函数返回最近一个insert语句生成的第一个自动增长列的值(最大的),值为bigint unsigned类型。
select last_insert_id();

image-1660783414075

1.5 更改auto_increment的值

当你更改了auto_increment的最大值的时候,如果你插入新的数据的时候,它会在你更改后的最大值基础上加1。

-- 修改当前auto_increment值, 22表示你要更新字段的最大值为22
alter table student auto_increment= 22;

这时候根据last_insert_id()查看自增值,可以看到还是之前的值。

image-1660783414075

再次插入一条数据

INSERT INTO student(name, sex) VALUES("小五", 'f');

image-1660783792638

从结果可以看到,自增值为我们设置的最大值22。多次插入数据,会发现数据自增依次为23,24…

2、已有表上创建自增字段

2.1 去除原表自增属性的自增功能

还是以上面的表为例,因为一个数据库表中只能有一个字段使用自增长,所以我们先去除原id的自增功能。

ALTER TABLE student modify id int;

image-1660784829803

执行完上述语句后查看表结构可以看到id已经没有自增功能,执行下述语句会报错(因为id为主键不能自增,必须指定值)。

INSERT INTO student(name, sex) VALUES("小五", 'f');

image-1660784921083

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;

image-1660785241168

再查看数据可以发现,auto_id的值已经赋值为从1开始的自增值。

image-1660786198220

如果原表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');

image-1660785267213

新增的属性auto_id数据自增,历史数据会从1开始自增。

2.4 指定自增开始值

通过设置auto_increment指定自增属性的开始值,如下指定开始值为100,则后续新增的数据从100开始自增。

ALTER TABLE student auto_increment=100;

执行完上述语句后我们再插入一条数据:

INSERT INTO student(id, name, sex) VALUES(26, "小六", 'm');

image-1660785727985

可以看到新插入的数据的auto_id值是100。

2.5 查看下一条自增属性的值

set @maxId=1;

select max(auto_id) into @maxId from student; 

select @maxId+1 from dual;

image-1660787254894

0

评论区