MySQL讲义第13讲——完整性约束之auto

科技2024-04-03 98

MySQL讲义第13讲——完整性约束之auto_increment

在 MySQL 中可以利用 auto_increment 参数设置某一列为自增列。当设定某个字段为该属性之后，在没有为该列提供数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据。

一、自增列的定义

定义为自增的列数据类型必须是整数类型，当用户插入数据的时候，如果没有给定自增列的值，系统在原始值的基础上加上步长生成自增列的数据。

注意事项：（1）指定了 AUTO_INCREMENT 的列必须要建索引，不然会报错，索引可以为主键索引，当然也可以为非主键索引。（2）一张表只能指定一个自增列。（3）MySQL 允许为自增列指定数据（SQL Server 不允许）。

定义自增列的语法如下：

-- 1、创建表时同时创建自增列 create table 表名( 列名类型 auto_increment, .... ) auto_increment=n; 说明：表定义选项中的 auto_increment=n 用于指定自增列的起始值。 -- 2、为一个表新增一个自增列 alter table 表名 add 列名类型 auto_increment;

举例：

1、创建一张包含自增列的表

创建表 t1，指定自增列的起始值为 1001，命令如下：

插入数据：插入数据时如果手工指定自增列的数据，则自增列的起始值变为新插入的数据，下次插入数据时从当前插入的数据值递增。

mysql> insert into t1(name) values('Jack'),('Tom'),('Rose'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1001 | Jack | | 1002 | Tom | | 1003 | Rose | +------+------+ mysql> insert into t1 values(3001,'John'); Query OK, 1 row affected (0.01 sec) mysql> insert into t1(name) values('Mary'); Query OK, 1 row affected (0.03 sec) mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1001 | Jack | | 1002 | Tom | | 1003 | Rose | | 3001 | John | | 3002 | Mary | +------+------+ 5 rows in set (0.00 sec)

2、为一个已经存在的表添加自增列

可以使用 alter table 名为一张添加一个自增列，命令如下：

mysql> create table t2(name char(20)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t2 values('Jack'),('Tom'),('Rose'); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t2; +------+ | name | +------+ | Jack | | Tom | | Rose | +------+ 3 rows in set (0.00 sec) -- 添加自增列 id mysql> alter table t2 add id int primary key auto_increment first; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t2; +----+------+ | id | name | +----+------+ | 1 | Jack | | 2 | Tom | | 3 | Rose | +----+------+ 3 rows in set (0.00 sec)

二、修改自增属性

1、删除自增属性

修改表结构重新定义字段类型，并且去掉 auto_increment 关键词即可，例如：

2、添加自增属性

举例：

mysql> alter table t2 modify id int auto_increment; Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) -- 由于表中已经存在数据，且 id 列的值最大为3，因此自增列的起始值为4

3、修改自增列的起始值

使用 alter table 命令可以修改自增列的起始值，例如：

mysql> alter table t2 auto_increment=1001; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)

三、为自增列插入数据时的一些注意事项

1、为自增列插入 NULL 值

如果把 NULL 插入到 AUTO_INCREMENT 数据列，MySQL 将自动生成下一个序列编号。当插入记录时，如果没有为自增列明确指定值，则等同插入 NULL 值。例如：

mysql> insert into t2 values(NULL,'Mark'); Query OK, 1 row affected (0.01 sec) mysql> insert into t2(name) values('Jerry'); Query OK, 1 row affected (0.04 sec) mysql> select * from t2; +------+-------+ | id | name | +------+-------+ | 1 | Jack | | 2 | Tom | | 3 | Rose | | 1001 | Mark | | 1002 | Jerry | +------+-------+ 5 rows in set (0.00 sec)

2、为自增列插入已经存在的值

当插入记录时，如果为自增列明确指定数值，会有以下两种情况：（1）如果插入的值与已有的编号重复，则会出现出错信息，因为自增列的值必须唯一，不能重复；（2）如果插入的值大于已经存在的所有值，则会插入该数据到自增列，下一个编号将从新值开始递增。

举例：

mysql> insert into t2 values(1002,'Black'); ---插入重复值，出现错误 ERROR 1062 (23000): Duplicate entry '1002' for key 'PRIMARY' mysql> insert into t2 values(8002,'Black'); Query OK, 1 row affected (0.00 sec) mysql> insert into t2(name) values('Sharp'); Query OK, 1 row affected (0.03 sec) mysql> select * from t2; +------+-------+ | id | name | +------+-------+ | 1 | Jack | | 2 | Tom | | 3 | Rose | | 1001 | Mark | | 1002 | Jerry | | 8002 | Black | | 8003 | Sharp | +------+-------+ 7 rows in set (0.00 sec)

3、删除记录之后自增列的情况

被 delete 语句删除的自增列的值不会重复使用，除非手工指定。即使使用 delete 命令删除所有记录，重复插入的新记录也是从上次插入的值继续编号。而使用 truncate table 命令删除记录，自增列的编号会被重置。例如：

mysql> delete from t2; Query OK, 9 rows affected (0.03 sec) mysql> insert into t2(name) values('Tom'); Query OK, 1 row affected (0.02 sec) mysql> select * from t2; +------+------+ | id | name | +------+------+ | 8006 | Tom | +------+------+ 1 row in set (0.00 sec) mysql> truncate table t2; Query OK, 0 rows affected (0.01 sec) mysql> insert into t2(name) values('Tom'); Query OK, 1 row affected (0.01 sec) mysql> select * from t2; +----+------+ | id | name | +----+------+ | 1 | Tom | +----+------+ 1 row in set (0.00 sec)

Processed: 0.010, SQL: 8