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