Using AUTO_INCREMENT CASE
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)
# # 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.01sec)
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)
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 (2pm doudou1')
ERROR 1062 (23000): Duplicate entry'2' for key 'PRIMARY'
Mysql > INSERT INTO animals (id,name) VALUES (- 2pm dou1')
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.01sec)
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.01sec)
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)
#
All rights reserved, the article is allowed to be reprinted, but the source address must be indicated by link, otherwise legal liability will be investigated! [QQ Exchange Group: 53993419]
QQ:14040928 E-mail:dbadoudou@163.com
Link to this article: 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)
SELECT * FROM animals
INSERT INTO animals (id,name) VALUES (2meme double 1')
INSERT INTO animals (id,name) VALUES (- 2pm 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')
