如何删除数据后，让主键自动减少？

Question

mysql>  select  *   from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date       | open  | high  | low   | close | vol      | adj   | id      | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO   | 2010-07-14 | 15.32 | 15.42 |  15.2 | 15.37 | 12255700 | 15.37 | 7976402 |    1 |
| YHOO   | 2010-07-13 | 15.06 |  15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 |    2 |
| YHOO   | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 |    3 |
| YHOO   | 2010-07-09 |  14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 |    4 |
| YHOO   | 2010-07-08 | 14.43 | 14.77 |  14.4 |  14.6 | 17088700 |  14.6 | 7976406 |    5 |
| YHOO   | 2010-07-07 | 14.18 | 14.42 | 14.12 |  14.4 | 17417900 |  14.4 | 7976407 |    6 |
| YHOO   | 2010-07-06 | 14.23 | 14.46 |    14 | 14.13 | 17334100 | 14.13 | 7976408 |    7 |
| YHOO   | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 |    8 |
| YHOO   | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 |    9 |
| YHOO   | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 |   10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)mysql> delete from t2 where (date='2010-07-14');
Query OK, 1 row affected (0.00 sec)mysql>  select  *   from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date       | open  | high  | low   | close | vol      | adj   | id      | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO   | 2010-07-13 | 15.06 |  15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 |    2 |
| YHOO   | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 |    3 |
| YHOO   | 2010-07-09 |  14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 |    4 |
| YHOO   | 2010-07-08 | 14.43 | 14.77 |  14.4 |  14.6 | 17088700 |  14.6 | 7976406 |    5 |
| YHOO   | 2010-07-07 | 14.18 | 14.42 | 14.12 |  14.4 | 17417900 |  14.4 | 7976407 |    6 |
| YHOO   | 2010-07-06 | 14.23 | 14.46 |    14 | 14.13 | 17334100 | 14.13 | 7976408 |    7 |
| YHOO   | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 |    8 |
| YHOO   | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 |    9 |
| YHOO   | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 |   10 |
| YHOO   | 2010-06-29 | 14.53 | 14.54 | 13.88 | 14.04 | 31825900 | 14.04 | 7976412 |   11 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)我希望得到
mysql>  select  *   from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date       | open  | high  | low   | close | vol      | adj   | id      | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO   | 2010-07-14 | 15.32 | 15.42 |  15.2 | 15.37 | 12255700 | 15.37 | 7976402 |    1 |
| YHOO   | 2010-07-13 | 15.06 |  15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 |    2 |
| YHOO   | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 |    3 |
| YHOO   | 2010-07-09 |  14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 |    4 |
| YHOO   | 2010-07-08 | 14.43 | 14.77 |  14.4 |  14.6 | 17088700 |  14.6 | 7976406 |    5 |
| YHOO   | 2010-07-07 | 14.18 | 14.42 | 14.12 |  14.4 | 17417900 |  14.4 | 7976407 |    6 |
| YHOO   | 2010-07-06 | 14.23 | 14.46 |    14 | 14.13 | 17334100 | 14.13 | 7976408 |    7 |
| YHOO   | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 |    8 |
| YHOO   | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 |    9 |
| YHOO   | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 |   10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)mysql> delte from t2 where (date='2010-07-14');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delte from t2 where (date='2010-07-14')' at line 1
mysql> delete from t2 where (date='2010-07-14');
Query OK, 1 row affected (0.00 sec)mysql>  select  *   from t2 limit 10;
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| symbol | date       | open  | high  | low   | close | vol      | adj   | id      | myid |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
| YHOO   | 2010-07-13 | 15.06 |  15.6 | 14.99 | 15.52 | 22328800 | 15.52 | 7976403 |    1 |
| YHOO   | 2010-07-12 | 14.93 | 15.21 | 14.78 | 14.94 | 15585900 | 14.94 | 7976404 |    2 |
| YHOO   | 2010-07-09 |  14.6 | 14.93 | 14.59 | 14.89 | 12682000 | 14.89 | 7976405 |    3|
| YHOO   | 2010-07-08 | 14.43 | 14.77 |  14.4 |  14.6 | 17088700 |  14.6 | 7976406 |    4|
| YHOO   | 2010-07-07 | 14.18 | 14.42 | 14.12 |  14.4 | 17417900 |  14.4 | 7976407 |    5 |
| YHOO   | 2010-07-06 | 14.23 | 14.46 |    14 | 14.13 | 17334100 | 14.13 | 7976408 |    6 |
| YHOO   | 2010-07-02 | 14.08 | 14.24 | 14.03 | 14.07 | 18564400 | 14.07 | 7976409 |    7 |
| YHOO   | 2010-07-01 | 13.99 | 14.15 | 13.75 | 14.09 | 33222500 | 14.09 | 7976410 |    8 |
| YHOO   | 2010-06-30 | 13.95 | 14.22 | 13.79 | 13.84 | 23912900 | 13.84 | 7976411 |   9 |
| YHOO   | 2010-06-29 | 14.53 | 14.54 | 13.88 | 14.04 | 31825900 | 14.04 | 7976412 |   10 |
+--------+------------+-------+-------+-------+-------+----------+-------+---------+------+
10 rows in set (0.00 sec)
myid已经是主键

Answer 1

这就是auto_increment的性质。。你想你这样 
那就不要用 自增。可以考虑触发器试试

Answer 2

http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx
MySQL中的ROWNUM的实现 

Answer 3

这么做的效率很差,很少有人这么做的!
drop table if exists ttt;
CREATE TABLE `ttt` (
  `name` varchar(64) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ttt` (`name`,`id`) VALUES ('a',1);
INSERT INTO `ttt` (`name`,`id`) VALUES ('b',2);
INSERT INTO `ttt` (`name`,`id`) VALUES ('c',3);
INSERT INTO `ttt` (`name`,`id`) VALUES ('d',4);
INSERT INTO `ttt` (`name`,`id`) VALUES ('e',5);
INSERT INTO `ttt` (`name`,`id`) VALUES ('f',6);drop table if exists ttt_tmp;
CREATE TABLE `ttt_tmp` (
  `name` varchar(64) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;# 上面是测试数据
mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| a    |  1 |
| b    |  2 |
| c    |  3 |
| d    |  4 |
| e    |  5 |
| f    |  6 |
+------+----+
6 rows in set (0.01 sec)mysql> truncate ttt_tmp;
Query OK, 0 rows affected (0.06 sec)mysql> delete from ttt where name='a';
Query OK, 1 row affected (0.02 sec)mysql> insert into ttt_tmp(name) select name from ttt order by id;
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0mysql> rename table ttt_tmp to ttt_tmp1,ttt to ttt_tmp,ttt_tmp1 to ttt;
Query OK, 0 rows affected (0.11 sec)mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| b    |  1 |
| c    |  2 |
| d    |  3 |
| e    |  4 |
| f    |  5 |
+------+----+
5 rows in set (0.00 sec)

Answer 4

mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| a    |  1 |
| b    |  2 |
| c    |  3 |
| d    |  4 |
| e    |  5 |
| f    |  6 |
+------+----+
6 rows in set (0.00 sec)mysql> delete from ttt where name='a';
Query OK, 1 row affected (0.03 sec)mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| b    |  2 |
| c    |  3 |
| d    |  4 |
| e    |  5 |
| f    |  6 |
+------+----+
5 rows in set (0.00 sec)mysql> set @rownum:=0;
Query OK, 0 rows affected (0.00 sec)mysql> update ttt,(select id,@rownum:=@rownum+1 as rownum from ttt) as t2 set ttt.id=t2.rownum where ttt.id=t2.id;
Query OK, 5 rows affected (0.03 sec)
Rows matched: 5  Changed: 5  Warnings: 0mysql> select * from ttt;
+------+----+
| name | id |
+------+----+
| b    |  1 |
| c    |  2 |
| d    |  3 |
| e    |  4 |
| f    |  5 |
+------+----+
5 rows in set (0.00 sec)

Answer 5

上面的表如果不是innodb类型的话,需要添加一个order by id哈
update ttt,(select id,@rownum:=@rownum+1 as rownum from ttt order by id) as t2 set ttt.id=t2.rownum where ttt.id=t2.id;