Using 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)
## TypeStorageMinimum ValueMaximum Value
## MEDIUMINT3-83886088388607
## INT4-21474836482147483647


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');