Mysql中的auto_increment关键字详解
问题一：如何使用auto_increment关系字【MySQL】
create table mydatabase.test
(id int not null auto_increment PRIMARY key,
name varchar(20))auto_increment = 3;
#auto_increment = 3 指定一个自增的初始值
insert into mydatabase.test(name) VALUES
('littlelawson'),
('shakespere');
select * from mydatabase.test
drop table mydatabase.test;
问题二：如何使用关键字identity？【Not MySQL】
2.1表示该字段被标识，也就是自增长，在插入数据的时候该字段不用赋值，系统会自己为其赋值。
create table a
(id int identity(1,2),
name varchar(10)
);
报错如下：
mysql> create table a
-> (id int identity(1,2),
-> name varchar(10));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identity(1,2),
name varchar(10))' at line 2
错误原因：MySQL不支持identity关键字。但是mysql具有相同的特性，使用的关键字是auto_increment。
当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，则会出现两种情况
- 情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为
AUTO_INCREMENT数据列的值必须是唯一的； - 情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。
2.2 注意
-
AUTO_INCREMENT是数据列的一种属性，只适用于整数类型数据列 -
AUTO_INCREMENT数据列必须具备NOT NULL属性。 - 可在建表时可用
AUTO_INCREMENT=n选项来指定一个自增的初始值 - 可用
alter table table_name AUTO_INCREMENT=n命令来重设自增的起始值，默认的起始值是1
问题三：对于mysql中自增长的主键，比如存在主键id，其值自增【1,2,3…】但是如果手动删除了3，再插入一条数据，id会是3么？
3.1实验
- 创建自增性质表
mysql> create table testAutoIncre(id int auto_increment primary key,name varchar(20));
Query OK, 0 rows affected (0.19 sec)
- 往表中插入数据
mysql> insert into testAutoIncre values(1,'Lawson'),(2,'Ting');
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from testAutoIncre;
+----+--------+
| id | name |
+----+--------+
| 1 | Lawson |
| 2 | Ting |
+----+--------+
2 rows in set (0.00 sec)
- 删除数据
mysql> delete from testAutoIncre where id = 2;
Query OK, 1 row affected (0.04 sec)
- 插入一条数据
mysql> insert into testAutoIncre(name) values('hadoop');
Query OK, 1 row affected (0.06 sec)- 展示数据
mysql> select * from testAutoIncre;
+----+--------+
| id | name |
+----+--------+
| 1 | Lawson |
| 3 | hadoop |
+----+--------+
2 rows in set (0.00 sec)
可以看到这条name= hadoop记录的id值为3，而不是2。
问题四 ：auto_increment字段须是主键的一部分
4.1 auto_increment字段需要成为主键的一部分
在mysql 5.7中，将字段设置成auto_increment之后，是需要将其设置成主键/或者是主键的一部分，否则是不会通过的。
如下sql:
mysql> alter table orders modify id int auto_increment;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
应该将其修改成如下sql:
mysql> alter table orders modify id int auto_increment primary key;
Query OK, 0 rows affected (0.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
问题五：查询某表的自增主键的起始值【Mysql】
- 建表语句
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`address` varchar(20) DEFAULT NULL,
`score` varchar(10) DEFAULT NULL,
`rank` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
- 查看表结构
mysql> desc student;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| score | varchar(10) | YES | | NULL | |
| rank | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
- 查看表数据
mysql> select * from student;
+----+--------+---------------+-------+------+
| id | name | address | score | rank |
+----+--------+---------------+-------+------+
| 1 | lawson | anhui_jinzhai | 98.2 | 1% |
| 2 | ting | anhui_suzhou | NULL | NULL |
| 3 | spark | anhui_hefei | NULL | NULL |
| 4 | hadoop | anhui_sanyuan | NULL | NULL |
+----+--------+---------------+-------+------+
4 rows in set (0.00 sec)
- 查看student表自增键起始值
mysql> select auto_increment from information_schema.tables where table_name = 'student' and table_schema = 'insidemysql';
+----------------+
| auto_increment |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)
