如何知道由于提供的信息与数据库中已存在的数据匹配而导致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屋！