Using AUTO_INCREMENT CASE
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，Using AUTO_INCREMENT CASEAUTO_INCREMENT CASE EXPLAINhttp://dev.mysql.com/doc/refman/5.6/en/example-a
千家信息网最后更新 2026年11月01日Using AUTO_INCREMENT CASEUsing AUTO_INCREMENT CASE
AUTO_INCREMENT CASE EXPLAIN
http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html
1.create case table and insert into data
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.04 sec)
## Type Storage Minimum Value Maximum Value
## MEDIUMINT 3 -8388608 8388607
## INT 4 -2147483648 2147483647
1.1 AUTO_INCREMENT column (i) are not values specified ,so MYSQL assigned sequence numbers automatically
mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
1.2 insert into NULL ,so i column sequence numbers automatically
mysql> INSERT INTO animals (id,name) VALUES(NULL,'doudou');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
+----+---------+
7 rows in set (0.00 sec)
mysql> INSERT INTO animals (id,name) VALUES(11111,'doudou1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+-------+---------+
| id | name |
+-------+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
+-------+---------+
8 rows in set (0.00 sec)
## manual specified value 1111 to AUTO_INCREMENT (i)，and 1111 is inserted into i column.SO AUTO_INCREMENT column is Manualed insert number.
mysql> INSERT INTO animals (id,name) VALUES(2,'doudou1');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> INSERT INTO animals (id,name) VALUES(-2,'doudou1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+-------+---------+
| id | name |
+-------+---------+
| -2 | doudou1 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
+-------+---------+
9 rows in set (0.00 sec)
## manual specified value -2 to AUTO_INCREMENT (id)，and -2 is inserted into id column.Order by AUTO_INCREMENT (id) column.
2.id set 1111 and next AUTO_INCREMENT value is 1112 automatically
mysql> INSERT INTO animals (name) VALUES ('xiaoyu');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+-------+---------+
| id | name |
+-------+---------+
| -2 | doudou1 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
+-------+---------+
10 rows in set (0.00 sec)
## insert into NULL id column ,and next automatically generated value follows sequentially from the largest column value.
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 11112 |
+------------------+
1 row in set (0.00 sec)
## You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function.
3.ALTER TABLE animals AUTO_INCREMENT = 8388607 and next AUTO_INCREMENT values is 8388607
mysql> ALTER TABLE animals AUTO_INCREMENT = 8388607;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8388607
Create_time: 2016-03-25 10:23:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('large number');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+---------+--------------+
| id | name |
+---------+--------------+
| -2 | doudou1 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
+---------+--------------+
11 rows in set (0.00 sec)
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 8388607 |
+------------------+
1 row in set (0.00 sec)
4.AUTO_INCREMENT values is largest 8388607 and using 'UNSIGNED' solve this problem
mysql> INSERT INTO animals (name) VALUES('largest number');
ERROR 1062 (23000): Duplicate entry '8388607' for key 'PRIMARY'
## ERROR 1062 (23000) http://blog.itpub.net/26442936/viewspace-2063150/
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
Query OK, 10 rows affected (0.08 sec)
mysql> show table status like ' animals' \G
Empty set (0.00 sec)
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10
Avg_row_length: 1638
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8388608
Create_time: 2016-03-25 17:37:00
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec
5.restart mysql server AUTO_INCREMENT values is not change
[root@dbdou02 ~]# service mysqld start
Starting mysqld: [ OK ]
[root@dbdou02 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM animals;
+---------+----------------+
| id | name |
+---------+----------------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
| 8388608 | largest number |
+---------+----------------+
11 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('xiaoyu');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM animals;
+---------+--------------+
| id | name |
+---------+--------------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
| 8388608 | xiaoyu |
| 8388609 | xiaoyu |
+---------+--------------+
12 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('xiaoyu');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+---------+--------------+
| id | name |
+---------+--------------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
| 8388608 | xiaoyu |
| 8388609 | xiaoyu |
| 8388610 | xiaoyu |
+---------+--------------+
13 rows in set (0.00 sec)
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 12
Avg_row_length: 1365
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8388611
Create_time: 2016-03-25 14:07:46
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文链接： http://blog.itpub.net/blog/post/id/2063871/
########################################################################################
CASE scripts
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
INSERT INTO animals (id,name) VALUES(NULL,'doudou');
SELECT * FROM animals;
INSERT INTO animals (id,name) VALUES(11111,'doudou1');
SELECT * FROM animals;
INSERT INTO animals (id,name) VALUES(2,'doudou1');
INSERT INTO animals (id,name) VALUES(-2,'doudou1');
SELECT * FROM animals;
INSERT INTO animals (name) VALUES ('xiaoyu');
SELECT * FROM animals;
ALTER TABLE animals AUTO_INCREMENT = 8388607;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('large number');
select LAST_INSERT_ID() ;
INSERT INTO animals (name) VALUES('largest number');
select LAST_INSERT_ID() ;
service mysqld stop
service mysqld start
select LAST_INSERT_ID() ;
SELECT * FROM animals;
INSERT INTO animals (name) VALUES ('xiaoyu');
AUTO_INCREMENT CASE EXPLAIN
http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html
1.create case table and insert into data
mysql> CREATE TABLE animals (
-> id MEDIUMINT NOT NULL AUTO_INCREMENT,
-> name CHAR(30) NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.04 sec)
## Type Storage Minimum Value Maximum Value
## MEDIUMINT 3 -8388608 8388607
## INT 4 -2147483648 2147483647
1.1 AUTO_INCREMENT column (i) are not values specified ,so MYSQL assigned sequence numbers automatically
mysql> INSERT INTO animals (name) VALUES
-> ('dog'),('cat'),('penguin'),
-> ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)
1.2 insert into NULL ,so i column sequence numbers automatically
mysql> INSERT INTO animals (id,name) VALUES(NULL,'doudou');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
+----+---------+
7 rows in set (0.00 sec)
mysql> INSERT INTO animals (id,name) VALUES(11111,'doudou1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+-------+---------+
| id | name |
+-------+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
+-------+---------+
8 rows in set (0.00 sec)
## manual specified value 1111 to AUTO_INCREMENT (i)，and 1111 is inserted into i column.SO AUTO_INCREMENT column is Manualed insert number.
mysql> INSERT INTO animals (id,name) VALUES(2,'doudou1');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> INSERT INTO animals (id,name) VALUES(-2,'doudou1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+-------+---------+
| id | name |
+-------+---------+
| -2 | doudou1 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
+-------+---------+
9 rows in set (0.00 sec)
## manual specified value -2 to AUTO_INCREMENT (id)，and -2 is inserted into id column.Order by AUTO_INCREMENT (id) column.
2.id set 1111 and next AUTO_INCREMENT value is 1112 automatically
mysql> INSERT INTO animals (name) VALUES ('xiaoyu');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+-------+---------+
| id | name |
+-------+---------+
| -2 | doudou1 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
+-------+---------+
10 rows in set (0.00 sec)
## insert into NULL id column ,and next automatically generated value follows sequentially from the largest column value.
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 11112 |
+------------------+
1 row in set (0.00 sec)
## You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function.
3.ALTER TABLE animals AUTO_INCREMENT = 8388607 and next AUTO_INCREMENT values is 8388607
mysql> ALTER TABLE animals AUTO_INCREMENT = 8388607;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8388607
Create_time: 2016-03-25 10:23:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES('large number');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM animals;
+---------+--------------+
| id | name |
+---------+--------------+
| -2 | doudou1 |
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
+---------+--------------+
11 rows in set (0.00 sec)
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 8388607 |
+------------------+
1 row in set (0.00 sec)
4.AUTO_INCREMENT values is largest 8388607 and using 'UNSIGNED' solve this problem
mysql> INSERT INTO animals (name) VALUES('largest number');
ERROR 1062 (23000): Duplicate entry '8388607' for key 'PRIMARY'
## ERROR 1062 (23000) http://blog.itpub.net/26442936/viewspace-2063150/
mysql> ALTER TABLE `animals` CHANGE `id` `id` MEDIUMINT UNSIGNED auto_increment;
Query OK, 10 rows affected (0.08 sec)
mysql> show table status like ' animals' \G
Empty set (0.00 sec)
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 10
Avg_row_length: 1638
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8388608
Create_time: 2016-03-25 17:37:00
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec
5.restart mysql server AUTO_INCREMENT values is not change
[root@dbdou02 ~]# service mysqld start
Starting mysqld: [ OK ]
[root@dbdou02 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select LAST_INSERT_ID() ;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM animals;
+---------+----------------+
| id | name |
+---------+----------------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
| 8388608 | largest number |
+---------+----------------+
11 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('xiaoyu');
Query OK, 1 row affected (0.02 sec)
mysql> SELECT * FROM animals;
+---------+--------------+
| id | name |
+---------+--------------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
| 8388608 | xiaoyu |
| 8388609 | xiaoyu |
+---------+--------------+
12 rows in set (0.00 sec)
mysql> INSERT INTO animals (name) VALUES ('xiaoyu');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM animals;
+---------+--------------+
| id | name |
+---------+--------------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
| 7 | doudou |
| 11111 | doudou1 |
| 11112 | xiaoyu |
| 8388607 | large number |
| 8388608 | xiaoyu |
| 8388609 | xiaoyu |
| 8388610 | xiaoyu |
+---------+--------------+
13 rows in set (0.00 sec)
mysql> show table status like 'animals' \G
*************************** 1. row ***************************
Name: animals
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 12
Avg_row_length: 1365
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8388611
Create_time: 2016-03-25 14:07:46
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文链接： http://blog.itpub.net/blog/post/id/2063871/
########################################################################################
CASE scripts
CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
INSERT INTO animals (id,name) VALUES(NULL,'doudou');
SELECT * FROM animals;
INSERT INTO animals (id,name) VALUES(11111,'doudou1');
SELECT * FROM animals;
INSERT INTO animals (id,name) VALUES(2,'doudou1');
INSERT INTO animals (id,name) VALUES(-2,'doudou1');
SELECT * FROM animals;
INSERT INTO animals (name) VALUES ('xiaoyu');
SELECT * FROM animals;
ALTER TABLE animals AUTO_INCREMENT = 8388607;
show table status like 'animals' \G
INSERT INTO animals (name) VALUES('large number');
select LAST_INSERT_ID() ;
INSERT INTO animals (name) VALUES('largest number');
select LAST_INSERT_ID() ;
service mysqld stop
service mysqld start
select LAST_INSERT_ID() ;
SELECT * FROM animals;
INSERT INTO animals (name) VALUES ('xiaoyu');
链接
地址
文章
方式
法律
法律责任
版权
版权所有
责任
交流
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
小米移动互联网科技园
互联网科技工作委员会毛军
互联网科技公司华为
深圳恒合互联网络科技有限公司
金融科技创新监管互联网金融
奇酷互联网络科技
亿策互联网科技
狮子座互联网科技培训
深圳前海聚择互联网科技有限公司
广州慧亚互联网科技怎么样
互联网科技有哪些公司
武汉健翔互联网络科技有限公司
赶超互联网科技 微信多开
中国科技创新与世界互联网大会
华夏恒生科技与互联网科技
互联网数据科技专业
山东有哪些互联网科技公司
互联网科技手机排行榜
唯美互联网科技
互联网科技创新方面的资料
互联网科技公司起名
港股互联网科技股
网络科技有限互联网
广州互联网络科技有限公司
唐信互联网科技 大连
成都仁贤领冠互联网科技有限公司
彤创互联网科技有限公司
岳阳华臻互联网科技有限公司
晓花互联网科技有限公司网址
10位科技互联网大佬
