完整性(五):定义自增列(AUTO"/>
数据库系统原理与应用教程(032)—— MySQL 的数据完整性(五):定义自增列(AUTO
数据库系统原理与应用教程(032)—— MySQL 的数据完整性(五):定义自增列(AUTO_INCREMENT)
目录
- 数据库系统原理与应用教程(032)—— MySQL 的数据完整性(五):定义自增列(AUTO_INCREMENT)
- 一、定义自增列的语法分析
- 二、创建表同时定义自增列
- 1、不定义自增列的起始值
- 2、插入测试数据
- 3、定义自增列并设置起始值
- 三、为已存在的表添加自增列
- 四、修改自增属性
- 1、删除自增属性
- 2、添加自增属性
- 3、修改自增列的起始值
- 五、自增列的特性
- 1、为自增列插入 NULL 值
- 2、为自增列插入重复值
- 3、删除记录后自增列的变化情况
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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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: 0mysql> 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)
更多推荐
数据库系统原理与应用教程(032)—— MySQL 的数据完整性(五):定义自增列(AUTO
发布评论