如何在 MySQL 中设置计数器列值? [英] How can I set a counter column value in MySQL?

问题描述

我有一个带有SortID"列的表格，该列使用连续数字编号.每当删除一行时，就会留下一个空白.有没有办法使用纯 SQL 用行号更新行?像这样:

I have a table with a "SortID" column that is numbered using consecutive numbers. Whenever a row is deleted, it leaves a gap. Is there a way using pure SQL to update the rows with their row number? Something like this:

UPDATE tbl SET SortID={rowindex} ORDER BY SortID

(我意识到这不是有效的 SQL，这就是我寻求帮助的原因)

(I realize this isn't valid SQL, that's why I'm asking for help)

这应该将第一行设置为#1，第二行设置为#2...等等.这可以使用SQL吗?请原谅措辞不佳的问题，我不确定问这个问题的最佳方式.:)

This should set the first row to #1, the second row to #2... etc. Is this possible using SQL? Please forgive the poorly worded question, I'm not really sure the best way to ask this. :)

推荐答案

MySQL 变量 可用于此.

SET @a:=0;
UPDATE tbl SET sortId=@a:=@a+1 ORDER BY sortId;

这篇关于如何在 MySQL 中设置计数器列值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！