如何知道 MySQL UPDATE 查询是否因为提供的信息与数据库中已有的数据匹配而失败? [英] How to know if a MySQL UPDATE query fails because information being supplied matches data already in the database?

问题描述

我有一个查询，用户在 textarea 字段中输入了一段文本.他们能够将这些信息保存在数据库中.问题是，如果他们没有更改信息，或者如果信息与数据库中已有的数据匹配，我会收到受影响行的0".通常我会显示一个错误，说明当没有受影响的行时查询失败.我如何知道" 0 个受影响的行是因为数据已经存在，以便我可以显示更具体的错误?

I have a query where the user types a block of text in a textarea field. They are able to save this information in a database. The problem is that if they have not changed the information or if the information matches the data already in the database, I recieve a '0' for affected rows. Usually I display an error that says the query failed when there are no affected rows. How would I 'know' that the 0 affected rows is because the data already exists, so that I can display a more specific error?

推荐答案

获得 0 个受影响行的另一个原因是 UPDATE 语句没有匹配任何行.例如:

Another reason you'd get 0 affected rows is if the UPDATE statement matches no rows. For instance:

UPDATE MyTable SET field = 'content' WHERE id = 1234;

如果不存在 id = 1234 的行，则给出 0 个受影响的行.这也不是错误，只是 UPDATE 碰巧没有匹配任何行.

Gives 0 affected rows if no row exists with id = 1234. This is not an error either, it's just an UPDATE that happened to match no rows.

检测这种情况的方法是使用SELECT来验证是否有这样的一行.如果您可以确认该行存在，但 UPDATE 表示它影响了 0 行，那么您就知道您尝试更改的值实际上是数据库中已经存在的行.

The way to detect this case is to use SELECT to verify that there is such a row. If you can confirm the row exists, but the UPDATE said it affected 0 rows, then you know that the values you tried to change were in fact the rows already in the database.

SELECT COUNT(*) FROM MyTable WHERE id = 1234;

但区别可能并不重要.如果 mysql_error() 如@BoltClock 所建议的那样说有一个错误，则可以报告错误.*如果没有错误，您可以向用户报告没有变化".

But the distinction may not be important. You could report an error if mysql_error() says there is one, as @BoltClock suggests.* If there is no error you could just report "no change" to the user.

* 注意:你不必报告mysql_error()返回的文字信息.这些消息可能会让用户感到困惑，因此最好报告一些对他们更友好的内容.

* Note: you don't have to report the literal message returned by mysql_error(). The messages can be confusing to users, so it's a good idea to report something more friendly to them.

这篇关于如何知道 MySQL UPDATE 查询是否因为提供的信息与数据库中已有的数据匹配而失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！