Shulou(Shulou.com)05/31 Report--

This article is about how to understand the mysql self-growth column, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The self-growing column must be an index column, otherwise the successful table cannot be created, the same for both myisma and innodb

(localhost@testdb) [root] > create table test5 (id int auto_increment,name varchar (10)) engine=innodb

ERROR 1075 (42000):

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] > create table test5 (id int auto_increment,name varchar (10), index (id)) engine=innodb

Query OK, 0 rows affected (0.01 sec)

(localhost@testdb) [root] > create table test5 (id int auto_increment,name varchar (10)) engine=myisam

ERROR 1075 (42000):

(localhost@testdb) [root] > create table test5 (id int auto_increment,name varchar (10), index (id)) engine=myisam

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] >

After successful creation, the id column does not insert data, but can grow automatically.

(localhost@testdb) [root] > insert into test5 (name) values ('aa'), (' bb'), ('cc')

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb) [root] > select * from test5

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

+-+ +

3 rows in set (0.00 sec)

Indexes

(localhost@testdb) [root] > (localhost@testdb) [root] > show index from test5

+- -+

| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+- -+

| | test5 | 1 | id | 1 | id | A | NULL | NULL | NULL | | BTREE |

+- -+

1 row in set (0.00 sec)

Delete the data in the table, and the id column will continue to grow according to the original value when inserting the data.

(localhost@testdb) [root] > delete from test5

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] > select * from test5

Empty set (0.00 sec)

(localhost@testdb) [root] > insert into test5 (name) values ('aa'), (' bb'), ('cc')

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb) [root] > select * from test5

+-+ +

| | id | name |

+-+ +

| | 4 | aa |

| | 5 | bb |

| | 6 | cc |

+-+ +

3 rows in set (0.00 sec)

After the data in the truncate table is inserted, the id column will grow from 1.

(localhost@testdb) [root] > truncate table test5

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb) [root] > select * from test5

Empty set (0.00 sec)

(localhost@testdb) [root] > insert into test5 (name) values ('aa'), (' bb'), ('cc')

Query OK, 3 rows affected (0.00 sec)

(localhost@testdb) [root] > select * from test5

+-+ +

| | id | name |

+-+ +

| | 1 | aa |

| | 2 | bb |

| | 3 | cc |

+-+ +

3 rows in set (0.00 sec)

(localhost@testdb) [root] >

For self-growing columns of composite indexes

The self-growing column of the myisam engine, which is a non-leading column in the index, can be created successfully

The self-growing column of the innodb engine, which must be a leading column in the index for the table to be created successfully

(localhost@testdb) [root] > create table test4 (id1 int auto_increment,id2 int,name varchar (10), index (id2,id1)) engine=myisam

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] > drop table test4

Query OK, 0 rows affected (0.00 sec)

(localhost@testdb) [root] > create table test4 (id1 int auto_increment,id2 int,name varchar (10), index (id2,id1)) engine=innodb

ERROR 1075 (42000):

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] > create table test4 (id1 int auto_increment,id2 int,name varchar (10), index (id1,id2)) engine=innodb

Query OK, 0 rows affected (0.01 sec)

(localhost@testdb) [root] >

(localhost@testdb) [root] >

(localhost@testdb) [root] >

The above is how to understand the mysql self-growth column, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.