当前位置：网站首页>数据库系统原理与应用教程（032）—— MySQL 的数据完整性（五）：定义自增列（AUTO_INCREMENT）

数据库系统原理与应用教程（032）—— MySQL 的数据完整性（五）：定义自增列（AUTO_INCREMENT）

2022-07-20 01:43:00 【睿思达DBA_WGX】

数据库系统原理与应用教程（032）—— MySQL 的数据完整性（五）：定义自增列（AUTO_INCREMENT）

目录

MySQL 可以把表中的某一列设置为自增列（AUTO_INCREMENT）。当设定某个列为自增列之后，如果插入记录时没有为该列提供数据，系统会根据之前已经存在的数据进行自动增加后，自动填充数据。

一、定义自增列的语法分析

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

（1）指定了 AUTO_INCREMENT 的列必须要建索引，不然会报错。

（2）一张表只能指定一个自增列。

（3）MySQL 允许为自增列指定数据（SQL Server 不允许）。

定义自增列的语法如下：

-- 创建表时同时创建自增列
-- 表定义选项中的 auto_increment=n 用于指定自增列的起始值
create table 表名(
    列名 类型 auto_increment,
    ....
) auto_increment=n;

-- 为已存在的表添加自增列
alter table 表名 add 列名 类型 auto_increment;

二、创建表同时定义自增列

语法如下：

create table 表名(
    列名 类型 auto_increment,
    ....
) auto_increment=n;

1、不定义自增列的起始值

/* create table t1( id int primary key auto_increment, name char(20) ); */

mysql> create table t1(
    ->     id int primary key auto_increment,
    ->     name char(20)
    -> );
Query OK, 0 rows affected (0.06 sec)

2、插入测试数据

mysql> insert into t1(name) values('Jack'),('Black'),('Tom');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  1 | Jack  |
|  2 | Black |
|  3 | Tom   |
+----+-------+
3 rows in set (0.00 sec)

-- 插入数据时指定自增列的值
mysql> insert into t1(id, name) values(101, 'Kate');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | Jack  |
|   2 | Black |
|   3 | Tom   |
| 101 | Kate  |
+-----+-------+
4 rows in set (0.00 sec)

-- 重新插入数据
mysql> insert into t1(name) values('Mark');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | Jack  |
|   2 | Black |
|   3 | Tom   |
| 101 | Kate  |
| 102 | Mark  |
+-----+-------+
5 rows in set (0.00 sec)

3、定义自增列并设置起始值

/* create table t2( id int primary key auto_increment, name char(20) ) auto_increment = 101; */
mysql> create table t2(
    ->     id int primary key auto_increment,
    ->     name char(20)
    -> ) auto_increment = 101;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into t2(name) values('Jack'),('Black'),('Tom');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+-----+-------+
| id  | name  |
+-----+-------+
| 101 | Jack  |
| 102 | Black |
| 103 | Tom   |
+-----+-------+
3 rows in set (0.00 sec)

三、为已存在的表添加自增列

语法格式如下：

alter table 表名 add 列名 类型 auto_increment;

例如：

（1）创建表并输入数据

/* create table t3( name char(20), salary int ); insert into t3(name,salary) values('张平',5200),('刘刚',4800),('刘涛',4200),('张强',5100); */
mysql> create table t3(
    ->     name char(20),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t3(name,salary) values('张平',5200),('刘刚',4800),('刘涛',4200),('张强',5100);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+--------+--------+
| name   | salary |
+--------+--------+
| 张平   |   5200 |
| 刘刚   |   4800 |
| 刘涛   |   4200 |
| 张强   |   5100 |
+--------+--------+
4 rows in set (0.00 sec)

（2）添加自增列

mysql> alter table t3 add id int primary key auto_increment first;
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+----+--------+--------+
| id | name   | salary |
+----+--------+--------+
|  1 | 张平   |   5200 |
|  2 | 刘刚   |   4800 |
|  3 | 刘涛   |   4200 |
|  4 | 张强   |   5100 |
+----+--------+--------+
4 rows in set (0.00 sec)

四、修改自增属性

1、删除自增属性

重新定义列的数据类型，并且去掉 auto_increment 关键词即可。

例如：

mysql> desc t3;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | YES  |     | NULL    |                |
| salary | int(11)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> alter table t3 modify id int;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc t3;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2、添加自增属性

重新定义列的数据类型，在需要定义自增列的列名后添加 auto_increment 关键词。

例如：

mysql> desc t3;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| id     | int(11)  | NO   | PRI | NULL    |       |
| name   | char(20) | YES  |     | NULL    |       |
| salary | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table t3 modify id int auto_increment;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc t3;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(20) | YES  |     | NULL    |                |
| salary | int(11)  | YES  |     | NULL    |                |
+--------+----------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

3、修改自增列的起始值

使用 alter table 命令可以修改自增列的起始值。格式如下：

alter table 表名 auto_increment = n;

例如：

mysql> select * from t3;
+----+--------+--------+
| id | name   | salary |
+----+--------+--------+
|  1 | 张平   |   5200 |
|  2 | 刘刚   |   4800 |
|  3 | 刘涛   |   4200 |
|  4 | 张强   |   5100 |
+----+--------+--------+
4 rows in set (0.00 sec)

mysql> alter table t3 auto_increment = 1001;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t3(name,salary) values('王刚',5000);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t3;
+------+--------+--------+
| id   | name   | salary |
+------+--------+--------+
|    1 | 张平   |   5200 |
|    2 | 刘刚   |   4800 |
|    3 | 刘涛   |   4200 |
|    4 | 张强   |   5100 |
| 1001 | 王刚   |   5000 |
+------+--------+--------+
5 rows in set (0.00 sec)

五、自增列的特性

1、为自增列插入 NULL 值

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

例如：

-- 自动生成编号
mysql> insert into t3(name,salary) values('刘明明',5500);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
+------+-----------+--------+
6 rows in set (0.00 sec)

-- 设置编号为 NULL
mysql> insert into t3 values(NULL, '张强', 6000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
| 1003 | 张强      |   6000 |
+------+-----------+--------+
7 rows in set (0.00 sec)

2、为自增列插入重复值

当插入记录时，如果为自增列明确指定数值，会有以下两种情况：

（1）如果插入的值与已有的编号重复，则会出现出 错信息，因为自增列的值必须唯一，不能重复；

（2）如果插入的值大于已经存在的所有值，则会插入该数据到自增列，下一个编号将从新值开始递增。

例如：

-- 编号 1002 已存在，插入失败
mysql> insert into t3 values(1002, '王涛', 4100);
ERROR 1062 (23000): Duplicate entry '1002' for key 'PRIMARY'

-- 插入的值大于所有已存在的编号
mysql> insert into t3 values(3001, '赵静', 4400);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
| 1003 | 张强      |   6000 |
| 3001 | 赵静      |   4400 |
+------+-----------+--------+
8 rows in set (0.00 sec)

mysql> insert into t3(name,salary) values('张燕', 4500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
| 1003 | 张强      |   6000 |
| 3001 | 赵静      |   4400 |
| 3002 | 张燕      |   4500 |
+------+-----------+--------+
9 rows in set (0.02 sec)

-- 插入一个较小的值，并且和已有的记录不相同
mysql> insert into t3 values(85, '赵刚', 4200);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
|   85 | 赵刚      |   4200 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
| 1003 | 张强      |   6000 |
| 3001 | 赵静      |   4400 |
| 3002 | 张燕      |   4500 |
+------+-----------+--------+
10 rows in set (0.00 sec)

mysql> insert into t3(name,salary) values('张静静', 7500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
|   85 | 赵刚      |   4200 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
| 1003 | 张强      |   6000 |
| 3001 | 赵静      |   4400 |
| 3002 | 张燕      |   4500 |
| 3003 | 张静静    |   7500 |
+------+-----------+--------+
11 rows in set (0.00 sec)

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

执行 delete 语句删除记录后，重新插入记录时自增列的值不会重复使用，除非手工指定自增列的值。使用 delete 命令即使删除所有记录，重复插入的新记录也是从上次插入的值继续编号。

使用 truncate table 命令删除记录，自增列的编号会被重置。

例如：

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
|   85 | 赵刚      |   4200 |
| 1001 | 王刚      |   5000 |
| 1002 | 刘明明    |   5500 |
| 1003 | 张强      |   6000 |
| 3001 | 赵静      |   4400 |
| 3002 | 张燕      |   4500 |
| 3003 | 张静静    |   7500 |
+------+-----------+--------+
11 rows in set (0.00 sec)

mysql> delete from t3 where id > 5;
Query OK, 7 rows affected (0.02 sec)

mysql> select * from t3;
+----+--------+--------+
| id | name   | salary |
+----+--------+--------+
|  1 | 张平   |   5200 |
|  2 | 刘刚   |   4800 |
|  3 | 刘涛   |   4200 |
|  4 | 张强   |   5100 |
+----+--------+--------+
4 rows in set (0.01 sec)

-- 重新插入新记录
mysql> insert into t3(name,salary) values('张静静', 7500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+-----------+--------+
| id   | name      | salary |
+------+-----------+--------+
|    1 | 张平      |   5200 |
|    2 | 刘刚      |   4800 |
|    3 | 刘涛      |   4200 |
|    4 | 张强      |   5100 |
| 3004 | 张静静    |   7500 |
+------+-----------+--------+
5 rows in set (0.00 sec)

-- 使用 truncate table 命令删除记录
mysql> truncate table t3;
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> insert into t3(name,salary) values('张静静', 7500);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+----+-----------+--------+
| id | name      | salary |
+----+-----------+--------+
|  1 | 张静静    |   7500 |
+----+-----------+--------+
1 row in set (0.00 sec)
原网站

版权声明
本文为[睿思达DBA_WGX]所创，转载请带上原文链接，感谢
https://wanggx.blog.csdn.net/article/details/125849900

边栏推荐

猜你喜欢

随机推荐