python - 通过 MySQL 更新增加的值

Question

我在 MySQL 中有一个表，数据如下:

+-----------+--------------------------------------+-------+
| address   | subnet_id                            | major |
+-----------+--------------------------------------+-------+
| 2.2.2.2   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
| 7.7.7.7   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
| 1.1.1.1   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
| 3.3.3.3   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
| 4.4.4.4   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
| 9.99.9.10 | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
+-----------+--------------------------------------+-------+

我需要将 major 列更新为递增的数字(从 0 到 1、2、3...)。例如，更新subnet_id等于“7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9”。结果应该是这样的:

+-----------+--------------------------------------+-------+
| address   | subnet_id                            | major |
+-----------+--------------------------------------+-------+
| 2.2.2.2   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     0 |
| 7.7.7.7   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     1 |
| 1.1.1.1   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     2 |
| 3.3.3.3   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     3 |
| 4.4.4.4   | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     4 |
| 9.99.9.10 | 7ec1f191-476d-46cd-8fc9-0a8d24dfb8e9 |     5 |
+-----------+--------------------------------------+-------+

我应该使用MYSQL做什么？

Answer 1

要获得所需的输出，您可以使用用户定义的变量在更新中使用排名查询，为了获得正确的排序，我假设地址、子网ID的每个组合都是唯一的

update table1 t1 
join (
select address,subnet_id,
@r:= case when @g = subnet_id then @r +1 else 0 end rownum,
@g:= subnet_id
from table1
cross join (select @r:= 0 , @g:=null) vars
order by subnet_id
 ) t2 using(address,subnet_id)
 set major = t2.rownum

DEMO