MySQL订购字段 [英] MySQL ordering a field
问题描述
我有一个称为章的MySQL表,如下所示:
I have a MySQL table called chapters as shown below:
chapter_number
字段用于订购故事的章节.
The field chapter_number
is used to order chapters of a story.
我面临的问题是,如果用户删除一章,则需要以某种方式更新顺序.
The problem I am facing is if a user deletes a chapter then the ordering would somehow need to be updated.
尽管我要用前一行的值加1填充chapter_number
字段.
I though about populating the chapter_number
field with the value of the previous row plus 1.
因此,如果我删除一行,那仍将保持我的行的线性顺序.
So if I deleted a row then that would still keep linear ordering of my rows.
这是个好方法吗?
如果是这样,我将如何在PHP中访问上一行的值?
If so, how would I access the value of the previous row in PHP?
推荐答案
我知道,chapter_number
用于订购.
在MySQL中,您可以使用order by
和变量来更新表.如果您已订购并删除一行,则以下内容将连续对章节重新编号:
In MySQL, you can update a table using order by
and variables. If you have an ordering in place and delete a row, then the following will re-number the chapters consecutively:
update chapters
set chapter_number = (@o := coalesce(@o, 0) + 1)
order by chapter_number;
这篇关于MySQL订购字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!