在 MySQL 中更新表的多个唯一行的最佳方法是什么？
我有一个 MySQL 表中的项目列表。 用户可以通过在 HTML 列表中上下拖动这些项目来对这些项目进行排序。 然后我需要存储列表中每个项目的位置。
是否可以在一次 MySQL 调用中完成此操作，还是必须为每个产品单独调用才能设置自己的订单 ID？
一次调用看起来像这样：
UPDATE table_name SET `order` = order_number AND `product_id` = 'X';
I have a list of items in a MySQL table.
The user is able to order these items by dragging them up and down in a HTML list.
I then need to store each items position in the list.
Is it possible to do this in one MySQL call or does it have to be a seperate call for each product to set its own order ID?
A single call would look something like this:
UPDATE table_name SET `order` = order_number AND `product_id` = 'X';
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
那么，您可以删除整个表，然后插入所有产品的新订单号。这只是两个声明。只要该表仅包含“订单”信息而没有其他关键数据，这就可以工作。
Well, you could drop the whole table and then insert the new order numbers for all products. That's only two statements. This would work as long as this table only contains the "order" information and no other critical data.
您可以删除所有有问题的项目，如果没有其他数据受此影响（CASCADE 和类似数据），则可以将它们重新插入。这将是两个查询；使用“WHERE Product_id IN ('X', 'Y', ...)”进行删除，并使用每个项目的更新值进行插入。
一个干净的解决方案是在事务中执行 UPDATE，但由于 MySQL 不支持延迟约束检查，因此如果“order”属性是 UNIQUE，则会出现问题。
You could delete all items in question, and insert them back again if no other data would be affected by that (CASCADE and similar). That would be two queries; DELETE with "WHERE product_id IN ('X', 'Y', ...)", and INSERT with updated values for each item.
A clean solution would be to do an UPDATE within a transaction, but as MySQL doesn't support deferred constraint checking, you would have a problem there if the 'order' attribute is UNIQUE.