我表中有个index,一开始是正常加一,index是连续的,后面由于删除数据,index就不连续了。
为了整理出一个连续的index,我需要创建一个新表。
====================================================================================
copy mytable 到 mytable2,产生一个顺序id。他们结构相同,id是自动增长的。mytable2要先建好。
mysql> insert into mytable2(name,age) select name,age from mytable;Query OK, 9 rows affected (0.11 sec)
Records: 9 Duplicates: 0 Warnings: 0
可以看到mytable2的id是连续的了。
mysql> select * from mytable2;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | aaa | 60 |
| 2 | bbb | 50 |
| 3 | ccc | 50 |
| 4 | ddd | 40 |
| 5 | fff | 20 |
| 6 | ggg | 10 |
| 7 | hhh | 30 |
| 8 | xxx | 30 |
| 9 | yyy | 30 |
+----+------+------+
9 rows in set (0.00 sec)
原来的mytable的id是不连续的。
mysql> select * from mytable;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | aaa | 60 |
| 2 | bbb | 50 |
| 3 | ccc | 50 |
| 4 | ddd | 40 |
| 6 | fff | 20 |
| 7 | ggg | 10 |
| 8 | hhh | 30 |
| 20 | xxx | 30 |
| 21 | yyy | 30 |
+----+------+------+
9 rows in set (0.00 sec)
====================================================================================
用mytable2,生产mytable3,结构基本一样,内容一样,结构中没有Key,Extra(auto_increment)等。没有index。
mysql> create table mytable3 as select * from mytable2;
Query OK, 9 rows affected (0.13 sec)
Records: 9 Duplicates: 0 Warnings: 0
====================================================================================
copy表结构,结构完全一样,包括Key,Extra。
mysql> create table mytable4 like mytable2;
Query OK, 0 rows affected (0.11 sec)
mysql> select * from mytable4;
Empty set (0.00 sec)
mysql> desc mytable4;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | MUL | NULL | |
| age | int(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
====================================================================================