如何重新计算主索引? [英] How to recalculate primary index?

问题描述

我在mysql数据库中具有自动递增的PRIMARY KEY表.定期删除此表中的行.因此，结果是最新行的PK值增长非常快，但是此表中没有太多行.

I have table in mysql database with autoincrement PRIMARY KEY. On regular basis rows in this table are being deleted an added. So the result is that value of PK of the latest row is growing very fast, but there is not so much rows in this table.

我想做的就是以这种方式重新计算" PK，第一行的PK = 1，第二行的PK = 2，依此类推.该表的PK没有外部依赖关系，因此它是安全的".

What I want to do is to "recalculate" PK in this way, that the first row has PK = 1, second PK = 2 and so on. There are no external dependencies on PK of this table so it would be "safe".

有没有可以仅使用mysql查询/工具来完成?还是我必须从我的代码中做到这一点?

Is there anyway it can be done using only mysql queries/tools? Or I have to do it from my code?

推荐答案

set @pk:=0;

update 
  your_table
  set pk=@pk:=@pk+1
  order by pk;       <-- order by original pk

在我看来，拥有较大的代理密钥很好.您可能不太可能会用完所有允许的最大整数.考虑您可以使用unsigned将其加倍.

In my opinion, having a big surrogate key is fine. You probably unlikely to use up all the max allowed integer. Consider you can double it up using unsigned.

这篇关于如何重新计算主索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！