Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.