MySQL 如何在运行之前测试 SQL Update 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11011384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to test an SQL Update statement before running it?
提问by static_rtti
In some cases, running an UPDATE statement in production can save the day. However a borked update can be worse than the initial problem.
在某些情况下，在生产中运行 UPDATE 语句可以节省时间。然而，一个无聊的更新可能比最初的问题更糟糕。
Short of using a test database, what are options to tell what an update statement will do before running it?
除了使用测试数据库之外，在运行更新语句之前，有哪些选项可以告诉它会做什么？
采纳答案by a_horse_with_no_name
In addition to using a transaction as Imad has said (which should be mandatory anyway) you can also do a sanity check which rows are affected by running a select using the same WHERE clause as the UPDATE.
除了使用 Imad 所说的事务（无论如何这应该是强制性的）之外，您还可以通过使用与 UPDATE 相同的 WHERE 子句运行选择来进行完整性检查。
So if you UPDATE is
所以如果你 UPDATE 是
UPDATE foo
SET bar = 42
WHERE col1 = 1
AND col2 = 'foobar';
The following will show you which rows will be updated:
以下将显示哪些行将被更新：
SELECT *
FROM foo
WHERE col1 = 1
AND col2 = 'foobar';
回答by Marcel Lange
What about Transactions? They have the ROLLBACK-Feature.
交易呢？它们具有回滚功能。
@see https://dev.mysql.com/doc/refman/5.0/en/commit.html
@see https://dev.mysql.com/doc/refman/5.0/en/commit.html
For example:
例如：
START TRANSACTION;
SELECT * FROM nicetable WHERE somthing=1;
UPDATE nicetable SET nicefield='VALUE' WHERE somthing=1;
SELECT * FROM nicetable WHERE somthing=1; #check
COMMIT;
# or if you want to reset changes
ROLLBACK;
SELECT * FROM nicetable WHERE somthing=1; #should be the old value
Answer on question from @rickozoe below:
回答下面来自@rickozoe 的问题：
In general these lines will not be executed as once. In PHP f.e. you would write something like that (perhaps a little bit cleaner, but wanted to answer quick ;-) ):
通常，这些行不会一次性执行。在 PHP fe 中，你会写出类似的东西（也许更简洁一些，但想快速回答 ;-) ）：
$MysqlConnection->query('START TRANSACTION;');
$erg = $MysqlConnection->query('UPDATE MyGuests SET lastname='Doe' WHERE id=2;');
if($erg)
$MysqlConnection->query('COMMIT;');
else
$MysqlConnection->query('ROLLBACK;');
Another way would be to use MySQL Variables (see https://dev.mysql.com/doc/refman/5.7/en/user-variables.html and https://stackoverflow.com/a/18499823/1416909):
另一种方法是使用 MySQL 变量（参见https://dev.mysql.com/doc/refman/5.7/en/user-variables.html和 https://stackoverflow.com/a/18499823/1416909）：
# do some stuff that should be conditionally rollbacked later on
SET @v1 := UPDATE MyGuests SET lastname='Doe' WHERE id=2;
IF(v1 < 1) THEN
ROLLBACK;
ELSE
COMMIT;
END IF;
But I would suggest to use the language wrappers available in your favorite programming language.
但我建议使用您最喜欢的编程语言中可用的语言包装器。
回答by Imad Moqaddem
Autocommit OFF ...
自动提交关闭...
MySQL
MySQL
set autocommit=0;
It sets the autommit off for the current session.
它为当前会话设置自动提交。
You execute your statement, see what it has changed, and then rollback if it's wrong or commit if it's what you expected !
你执行你的语句，看看它发生了什么变化，然后如果它错了就回滚或者如果它是你所期望的就提交！
EDIT: The benefit of using transactions instead of running select query is that you can check the resulting set easierly.
编辑：使用事务而不是运行选择查询的好处是您可以更轻松地检查结果集。
回答by Johan
I know this is a repeat of other answers, but it has some emotional support to take the extra step for testing update :D
我知道这是其他答案的重复，但是采取额外步骤来测试更新有一些情感上的支持：D
For testing update, hash # is your friend.
对于测试更新，hash # 是你的朋友。
If you have an update statement like:
如果您有如下更新语句：
UPDATE
wp_history
SET history_by="admin"
WHERE
history_ip LIKE '123%'
You hash UPDATE and SET out for testing, then hash them back in:
您对 UPDATE 和 SET 进行散列以进行测试，然后将它们散列回：
SELECT * FROM
#UPDATE
wp_history
#SET history_by="admin"
WHERE
history_ip LIKE '123%'
It works for simple statements.
它适用于简单的语句。
An additional practically mandatory solution is, to get a copy (backup duplicate), whenever using update on a production table. Phpmyadmin > operations > copy: table_yearmonthday. It just takes a few seconds for tables <=100M.
一个额外的实际强制解决方案是，在生产表上使用更新时获取副本（备份副本）。phpmyadmin > 操作 > 复制：table_yearmonthday。表 <=100M 只需要几秒钟。
回答by David M
Not a direct answer, but I've seen many borked prod data situations that could have been avoided by typing the WHEREclause first! Sometimes a WHERE 1 = 0can help with putting a working statement together safely too. And looking at an estimated execution plan, which will estimate rows affected, can be useful. Beyond that, in a transaction that you roll back as others have said.
不是直接的答案，但我已经看到了许多可以通过首先输入WHERE子句来避免的无聊的产品数据情况！有时WHERE 1 = 0也可以帮助将工作声明安全地放在一起。查看估计的执行计划会很有用，该计划将估计受影响的行。除此之外，在您像其他人所说的那样回滚的事务中。
回答by Mohammad Naji
In these cases that you want to test, it's a good idea to focus on only currentcolumn values and soon-to-be-updatedcolumn values.
在您想要测试的这些情况下，最好只关注当前列值和即将更新的列值。
Please take a look at the following code that I've written to update WHMCS prices:
请查看我为更新 WHMCS 价格而编写的以下代码：
# UPDATE tblinvoiceitems AS ii
SELECT ### JUST
ii.amount AS old_value, ### FOR
h.amount AS new_value ### TESTING
FROM tblinvoiceitems AS ii ### PURPOSES.
JOIN tblhosting AS h ON ii.relid = h.id
JOIN tblinvoices AS i ON ii.invoiceid = i.id
WHERE ii.amount <> h.amount ### Show only updatable rows
# SET ii.amount = h.amount
This way we clearly compare already existing values versus new values.
通过这种方式，我们可以清楚地比较现有值与新值。
回答by manurajhada
Run select query on same table with all whereconditions you are applying in update query.
使用where您在更新查询中应用的所有条件在同一个表上运行选择查询。
回答by EaterOfCode
make a SELECTof it,
做一个SELECT，
like if you got
就像如果你有
UPDATE users SET id=0 WHERE name='jan'
UPDATE users SET id=0 WHERE name='jan'
convert it to
将其转换为
SELECT * FROM users WHERE name='jan'
SELECT * FROM users WHERE name='jan'
