mysql+去除列的自增长_mysql自增长列

自增长列必须是索引列，否则无法创建成功表，对myisma和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]>

创建成功后id列没有插入数据，但是可以自动增长

(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]> (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)

删除表里的数据，在插入数据id列会依据原来的值继续增长

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

truncate 表里的数据后在插入数据，id列会从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]>

对于复合索引的自增长列

myisam引擎的自增长列，在索引中是非前导列可以创建成功

innodb引擎的自增长列，在索引中必须是前导列，表才能创建成功

(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]>