高效更新多条记录
我的网站上有一个表，用户可以在其中对行重新排序。这部分很容易使用 jQuery UI 完成，并且使用 $('#sortable').sortable("serialize") 方法取回新订单也很简单。我现在的问题是，由于更改一项的顺序会更改表中的所有记录，因此将所有这些记录保存在数据库中的最有效方法是什么？我知道我可以根据每一行的 ID 创建一个新的 UPDATE 语句，但我觉得必须有更好的方法。有没有？
我正在使用 MySQL 和 ASP.NET。
I have a table on my site where the user has the ability to re-order the rows. This part is easy to do with jQuery UI, and getting the new order back is simple using the $('#sortable').sortable("serialize") method. My question is now, what's the most efficient way to save all these records in the database, since changing the order of one item changes all the records in the table? I know I could create a new UPDATE statement for each row based on their ID, but I feel like there has to be a better way. Is there?
I'm using MySQL and ASP.NET.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
使用
START TRANSACTION命令。这要快得多。Use the
START TRANSACTIONcommand. This is much faster.鉴于可能存在任意大量的行，我会将排序存储为用户首选项表中的文本字符串条目（假设您有这样的表）。基本上，默认值是一个空条目（以节省磁盘空间），这意味着顺序与数据库中的顺序保持不变，如果用户更改它，您只需序列化行的顺序（即，如果他们先有第 15 行，然后是第 19 行，然后是第 3 行，那么您可以得到
15-19-3），然后将其读出。这是一次更新，并且可以无限扩展（嗯，直到文本字符串的长度，但那是 对于mediumtext和longtext类型来说非常大）。我保留上述内容只是为了防止它适用于其他人，但对于下面描述的特定情况，我想我会考虑将首选项存储在文本文件中。鉴于无论如何都会向用户显示顺序，因此明文文件中的首选项不存在安全风险，并且您可以非常快速地重写整个内容。鉴于您在下面的描述，我将使用两列 CSV 文件，但显然您可以设置它，但最有意义。
对文件读取速度的快速搜索导致了此评论 在
file_get_contents()函数上，这表明您在考虑用于访问文件中数据的方法时可能需要注意文件有多大。我不知道写作。Given that there is potentially an arbitrarily large number of rows, I would store the ordering as a text string entry in the user preferences table (assuming you have such a table). Basically, the default would be a null entry (to save on disc space), which would mean that the ordering is unchanged from the ordering in the database, and if the user changes it you could just serialize the order of the rows (i.e., if they had row 15 first, then 19, then 3, you could have
15-19-3) and then just read it out. It's a single update and it scales indefinitely (well, until the length of the text string, but that's pretty doggone big for themediumtextandlongtexttypes).I'm leaving the above just in case it's applicable to other people, but for the specific case described below, I think I would consider storing the prefs in a textfile. Given that the ordering is shown to the user anyway, there's no security risk in the prefs being in a plaintext file, and you can rewrite the whole thing very quickly. Given how you described it below, I would use a two-column CSV file, but obviously you could set it up however makes the most sense.
A quick search on file reading speeds led to this comment on the
file_get_contents()function, which suggests you may want to pay attention to how large the file will be when considering the method you'll use to access the data in the file. I don't know about writing.至少对我而言，您的问题缺乏清晰度。我想您会展示一些条目，我将通过 ID~rank 来引用它们。例如：
现在你说改变“一项的顺序改变了表中的所有记录”。这在你的设计中真的有必要吗？如果用户切换 id-s 1 和 24 的元素，那么您将得到以下序列化列表：
现在，通过迭代此列表，您可以轻松确定哪些行应该真正更新（这一行与排名的自然顺序不匹配）。
在我的示例中，您应该进行两次更新查询，而不是六个：
干杯！
Your question is lacking some clarity at least for me. I imagine you show a couple of entries to which I will refer by ID~rank. For example:
Now you say changing that "the order of one item changes all the records in the table". Is this really necessary in your design? If the user switches the elements with id-s 1 and 24 then you will have the following serialized list:
Now by iterating this list you can easily determine which rows should be really updated (this one mismatching the natural order of rank).
In my example you should make two update queries instead of six:
Cheers!