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屋！