如果未删除任何行，则强制mysql抛出错误(尝试删除不存在的行) [英] Force mysql to throw error if no rows deleted (trying to delete non-existent row)

问题描述

我正在使用Perl DBI(execute_array)，但是我认为这更多是mysql问题.

I am using Perl DBI (execute_array), but I think that this is more of a mysql issue.

我尝试添加DELETE行，并遇到以下问题:命令成功完成，没有错误，但是我的行没有被删除(修改后的行返回0E0，即零但为真"，即，已删除0行").当我尝试通过PHPMyAdmin时，没有问题地删除了行.经过一些调试并在屏幕上出现空白空白后，我意识到添加时我在DELETE语句中使用的列之一已被截断，这解释了为什么脚本无法找到该行，但是PHPMyAdmin没有问题它.但是，这并不能解释为什么我没有收到任何错误.

I am trying to DELETE a few rows, and ran into the issue that the command completes successfully with no errors, but my rows are not being deleted (modified rows returns 0E0, i.e., "zero but true", i.e., "0 rows deleted"). When I tried via PHPMyAdmin, the rows deleted with no issue. After some debugging and long blank stares at my screen, I realized that one of the columns I was using in my DELETE statement had been truncated when added, which explained why the row could not be found by my script, but PHPMyAdmin had no problems with it. However, this did not explain why I was not getting any errors.

所以，我想这是设计使然，但是我想知道当尝试删除不存在的内容时是否有办法让mysql引发错误.我知道我可以检查受影响的行数是否与提供的元组数相同，但这不能告诉我成功删除了哪些行，以及哪些元组引用了不存在的行(因为我使用的是execute_array).有什么想法吗?

So, I guess this is by design, but am wondering if there is a way to get mysql to raise an error when you try to delete something that is not there. I know I can check to see if the number of affected rows is the same as the number of tuples supplied, but this does not tell me which rows were successfully removed, and which tuples referred to non-existent rows (since I am using execute_array for this very purpose). Any ideas?

推荐答案

我找到了针对我的特定用例的解决方法，并发布了答案，以防其他人使用.

I found a workaround for my specific use-case, and am posting as an answer in case it is useful for someone else out there.

返回execute_array

$sth->execute_array( { ArrayTupleStatus => \@return_vector } )

它实际上每行返回一个修改过的行(成功时；失败时，结果是一个数组引用)，因此尽管这并不是真正的错误，但我只能检查一下每个给定的元组执行都修改了非零行(但是您需要使用==而不是eq，因为返回值是'0E0').

It actually returns a "rows modified" value per row (when successful; when it fails the result is an array ref), so though it is not really an error, I can just check whether each given tuple execution modified non-zero rows (you need to use == not eq though, since the return is '0E0').

if (ref $return_vector[$i])
    { print "DELETE failed, reason: $return_vector[$i][1]"; }
elsif ($return_vector[$i] == 0)
    { print "DELETE failed, no rows matched tuples"; }
else
    { print "DELETE successful"; }

这篇关于如果未删除任何行，则强制mysql抛出错误(尝试删除不存在的行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！