mysql批量更新问题

Question

假设有一个表 table_a,有id，country,index 3个字段。id country index
1  China    0
2  China    1
3  China    2
4  USA      0
5  USA      1
6  USA      2
请问:当我删掉某些记录（比如id=2,id=5），如何让剩余记录的index重新排序，需要的结果如下id country index
1  China    0
3  China    1
4  USA      0
6  USA      1
谢谢！

Answer 1

mysql> select * from table_a;
+------+---------+-------+
| id   | country | index |
+------+---------+-------+
|    1 | China   |     0 |
|    2 | China   |     1 |
|    3 | China   |     2 |
|    4 | USA     |     0 |
|    5 | USA     |     1 |
|    6 | USA     |     2 |
+------+---------+-------+
6 rows in set (0.00 sec)mysql> delete from table_a where id=2 or id=5;
Query OK, 2 rows affected (0.00 sec)mysql> select * from table_a;
+------+---------+-------+
| id   | country | index |
+------+---------+-------+
|    1 | China   |     0 |
|    3 | China   |     2 |
|    4 | USA     |     0 |
|    6 | USA     |     2 |
+------+---------+-------+
4 rows in set (0.00 sec)mysql> update table_a a, (select t1.id,count(*)-1 as cnt from table_a t1,table_a t2 where t1.country=t2.country and t1.id<=t2.id group by t1.id) b
    -> set a.index =b.cnt
    -> where a.id=b.id;
Query OK, 4 rows affected (0.06 sec)
Rows matched: 4  Changed: 4  Warnings: 0mysql> select * from table_a;
+------+---------+-------+
| id   | country | index |
+------+---------+-------+
|    1 | China   |     1 |
|    3 | China   |     0 |
|    4 | USA     |     1 |
|    6 | USA     |     0 |
+------+---------+-------+
4 rows in set (0.00 sec)mysql>

Answer 2

update table_a a
inner join (select t1.id,count(*)-1 as cnt from table_a t1,table_a t2 where t1.country=t2.country and t1.id<=t2.id group by t1.id) b on a.id=b.id
set a.index =b.cnt

Answer 3

set @ind := 0;
set @last_country := '';update table_a as a, 
(
    select id, country, if(@last_country = country, @ind := @ind + 1, @ind := 0) as new_index, @last_country := country
    from table_a
)  as b
set a.`index` = new_index
where a.id = b.id;