SQL子句来优化UPDATE查询？
在我的虚构数据库中，我有几列数据。在设计脚本的 PHP 前端时，有必要允许用户在必要时修改元组的所有属性。
如果最终用户最终仅修改一个属性而不是所有属性，则以下语句：
UPDATE foo
SET name='bar' location='YYZ' drink='ale'
where user='smithj'`
将更新元组“smithj”的所有三个属性，即使其中两个属性相同。
有没有办法让 SQL（MySQL，如果有影响的话）自动过滤掉冗余更新，类似于 CREATE TABLE IF EXISTS 中的 IF EXISTS 子句？
谢谢！
In my fictional database, I have several columns of data. In designing a PHP front-end to the script, it was necessary to allow the user to modify all attributes of a tuple if necessary.
If the end-user only ends up modifying one attribute instead of all of them, the following statement:
UPDATE foo
SET name='bar' location='YYZ' drink='ale'
where user='smithj'`
Would update all three attributes of the tuple "smithj", even if two of the attributes are the same.
Is there a way to have SQL (MySQL, if it makes a difference) automatically filter out the redundant updates, similar to the IF EXISTS clause in CREATE TABLE IF EXISTS?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
“如果你将一列设置为当前的值，MySQL 会注意到这一点并且不会更新它。”
http://dev.mysql.com/doc/refman/5.0/ zh/update.html
"If you set a column to the value it currently has, MySQL notices this and does not update it."
http://dev.mysql.com/doc/refman/5.0/en/update.html
就个人而言，此时您很可能会尝试进行不成熟的优化。更新这三个值以使其成为所需的值就可以了。
检查要更新的内容很可能会花费更多费用。
Personally you are most likely trying to do a pre-mature optimization at this point. Updating the three values to make them what they need to be is going to be just fine.
Checking to see what to update will most likely cost more to do.
首先要回答的问题是为什么？
我看到的唯一原因是性能。但性能挑战可能有两个原因：
在应用程序和数据库之间移动数据。您可以通过仅在更新语句中包含实际需要更新的列来减少这种情况。但这是一项繁重的工作，而且当我们谈论三列时可能不值得。另请注意，这会创建大量不同的 sql 语句，可能会破坏某些数据库内部缓存。我不知道MySQL是否是这样，但oracle是这样。 （假设您使用绑定变量（您应该这样做））
并实际操作数据库中的数据。但在那个阶段，查找要更新的记录（where 子句）比实际更新它的成本要高得多。我想这就是为什么没有任何功能可以在旧值和新值实际上相同时进行更新
The first question to answer would be why?
The only reason I see is performance. But performance challenges might come for two reasons:
moving data between your application and the database. You can reduce that by only including the columns in the update statement that actually need updating. But that is a lot of work and probably not worth it when we are talking about three columns. Note also that the large amount of different sql statement this creates might kill some database internal caching. I don't know if this is true for MySQL, but it is for oracle. (assuming you use bind variables (which you should))
and actually manipulating data in the database. But at that stage, finding the record to update (the where clause) is much more costly then actually updating it. And I guess that is the reason why there isn't any feature, that turns the update of, when old and new values are actually the same
您需要在代码中执行此操作。无论如何，您提供的“示例”查询都需要 SQL 注入 :)
使用参数化查询，动态构建它们，并仅添加您想要更新的项目。
You need to do this in your code. In any case, the "example" query you provide cries for SQL injection :)
Use parametrized queries, build them on the fly, and add only the items you want to update.
按照 Sunny 的建议，您必须参数化您的查询。
一种选择可能是这样的（在高级伪代码中）：
当用户编辑某个项目时（比如按下按键），您可以标记该单元格。
您可以从每个标记的单元格中获取值，并且仅将它们的值传递到参数化查询中。
然而，令人遗憾的是，除非您有大量行，否则它不会对您的性能产生太大影响。
Going along with what Sunny suggested, you have to parametrize your query.
One option could be this (in high-level pseudocode):
When a user edits an item (lets say on keyPress), you can flag that cell.
You can get the value from every flagged cell, and only pass their values into your parametrized query.
However, this being sad, unless you have a huge number of rows, it shouldn't make that much of a difference to your performance.