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