sql - 对列表中的项目进行编号 - 最佳方法？

Question

我正在创建一组简单的表来存储多个列表。第一个表描述了我拥有的列表，第二个描述了这些列表中的项目 - 表的定义如下:

CREATE TABLE `listman_list` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(100) NOT NULL,
    `description` varchar(1000) NOT NULL,
    `id_counter` integer UNSIGNED NOT NULL
)

CREATE TABLE `listman_listitem` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `number` integer UNSIGNED NOT NULL,
    `title` varchar(100) NOT NULL,
    `description` varchar(10000) NOT NULL,
    `list_id` integer NOT NULL,
)

每个 listitem 都有一个 number 字段，它描述了它相对于列表的唯一编号。这是为了使列表项能够为列表唯一编号(但不是全局编号，此编号与其主键不同)。例如。所以我可以列出第一个项目 1、2、3，然后列出第二个项目 1、2、3 等。

现在，什么是最佳在第二个表中对项目进行编号的方法？目前我有一对触发器:

CREATE TRIGGER set_listitem_number
BEFORE INSERT ON listman_listitem
FOR EACH ROW
    SET NEW.number = (SELECT DISTINCT id_counter FROM listman_list id=NEW.list_id);


CREATE TRIGGER inc_listcounter
AFTER INSERT ON listman_listitem 
FOR EACH ROW
    UPDATE listman_list SET id_counter = id_counter+1 WHERE id=NEW.list_id;

第一个在插入之前从列表中设置列表项编号，而第二个在成功插入后增加列表中的计数器(我听说在插入之前修改其他表如果事务失败会对 MySQL 产生一些不良后果——因为之前的触发器不会被撤销)。

这是实现此目的的好方法 - 还是我应该在应用程序代码中的事务下手动执行此操作，还是我应该完全以其他方式执行此操作？

感谢您的帮助 - 我可能在这里对琐事大惊小怪，但我在数据库方面没有太多经验。

Answer 1

我不明白你在触发器中的查询。您不想找到最大的现有数字(在列表中)并将其递增 1 吗？我不知道 MySQL 语法的来龙去脉，但这不会出现你在做什么。我希望在此查询中看到 Max() 或 Count()。

除此之外，我认为将需要在最后一个位置以外的位置插入新项目，这意味着所有项目都需要重新排序。触发器无法做到这一点，因此我建议您在应用程序中处理它。

之前讨论过维护排序顺序的问题，例如here .