Using AUTO_INCREMENT CASE
发表于：2025-01-25 作者：千家信息网编辑
千家信息网最后更新 2025年01月25日，Using AUTO_INCREMENT CASEAUTO_INCREMENT CASE EXPLAINhttp://dev.mysql.com/doc/refman/5.6/en/example-a
千家信息网最后更新 2025年01月25日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安全错误
数据库的锁怎样保障安全
学软件开发好赚钱吗
世界神奇宝贝手机版服务器推荐
如何跟数据库商要下载量数据
王者如何查看有账号的服务器
女生适合做网络安全
2018年网络安全标准培训
上海助力智慧校园软件开发
全网电商软件开发
华为 lte软件开发
csgo 有哪些社区服务器
2018网络安全总结报告
黑河软件开发平台
软件开发活
宏系统数据库引数据库
腾讯的小霸王服务器
数据库疫情最新
服务器8t怎么配
北京知名软件开发公司
个人搭建服务器管理软件
中国联通网络技术研究学院
污染源普查数据库报价
数据库筛选偶数条件语句
sql数据库如何使用
网络安全竞赛赛事
网络安全关于应急响应的目标
服务器怎么解决文章的存储
河北系统软件开发报价
西安坐标软件开发有限公司
ccs软件开发板仿真步骤
如何连接别人电脑数据库
