是否有一个命令可以在不执行 SQL 查询的情况下测试它？( MySQL 或 ANSI SQL )
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2433633/
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
Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )
提问by Petruza
Is there anything like this:TEST DELETE FROM user WHERE somekey = 45;
有没有这样的：TEST DELETE FROM user WHERE somekey = 45;
That can return any errors, for example that somekey doesn't exist, or some constraint violation or anything, and reporting how many rows would be affected, but not executing the query?
I know you can easily turn any query in a select query that has no write or delete effect in any row, but that can lead to errors and it's not very practical if you want to test and debug many queries.
这可以返回任何错误，例如 somekey 不存在，或某些约束违规或任何内容，并报告有多少行会受到影响，但不执行查询？
我知道您可以轻松地在任何行中没有写入或删除效果的选择查询中转换任何查询，但这可能会导致错误，如果您想测试和调试许多查询，这不是很实用。
采纳答案by Giles
I realise this is a bit of an old question but for completeness...
我意识到这是一个老问题，但为了完整性......
If the intention is to find the query processing time without returning any rows (I need this quite often, I want to know how long a piece of code I am using will take without having it return a couple of million rows I am not interested in seeing) then the BLACKHOLE engine can be very useful:
如果目的是在不返回任何行的情况下找到查询处理时间（我经常需要这个，我想知道我使用的一段代码需要多长时间而不返回几百万行我不感兴趣看到）那么 BLACKHOLE 引擎可能非常有用：
https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html
https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html
For instance say I have 2 tables, t1 & t2, with millions of rows, that I am joining together. I want to check how long this is likely to take, in a GUI (SQLYog or mysql workbench or somesuch) without returning millions of rows that will eat up memory and presumably take time for the GUI to process and display. I use the blackhole engine to 'dump' the rows to nowhere. EG:
例如，假设我有 2 个表，t1 和 t2，有数百万行，我将它们连接在一起。我想在 GUI（SQLYog 或 mysql 工作台等）中检查这可能需要多长时间，而不会返回数百万行，这些行会占用内存，并且可能需要时间让 GUI 处理和显示。我使用黑洞引擎将行“转储”到任何地方。例如：
CREATE TABLE tBH (a TINYINT) ENGINE = BLACKHOLE;
SELECT NOW(); -- Show start time
INSERT tBH
SELECT 1 FROM t1
LEFT JOIN t2 ON t1.key1 = t2.key1;
SELECT NOW(); -- Show end time
Note that as I am just looking for execution time I do not bother returning all the columns (IE with "*") but just a placeholder ("1" in this case).
请注意，因为我只是在寻找执行时间，所以我不会费心返回所有列（IE 带有“*”），而只是一个占位符（在这种情况下为“1”）。
回答by DCNYAM
The only thing I know of is to wrap it in a transaction that is always rolled back:
我唯一知道的是将它包装在一个总是回滚的事务中：
BEGIN TRANSACTION
DELETE FROM user WHERE somekey = 45;
ROLLBACK TRANSACTION
Make sure you execute the entire block and not just the delete statement. Also, DO NOT run this on any production environment or any system where you cannot afford to lose the data.
确保您执行整个块，而不仅仅是删除语句。此外，请勿在任何生产环境或任何您无法承受丢失数据的系统上运行此程序。
回答by u1336811
In MySQL use this
在 MySQL 中使用这个
START TRANSACTION;
QUERY;
It is important to use ";" because if you don't, it won't work. For example
使用“;”很重要 因为如果你不这样做，它就行不通。例如
START TRANSACTION;
UPDATE tableX SET colX = valueA, colY = valueB WHERE id=1
Reference here http://dev.mysql.com/doc/refman/5.0/en/commit.html
回答by DOOManiac
As of MySQL 5.6, the EXPLAINkeyword works with SELECT, DELETE, INSERT, REPLACE, and UPDATEstatements.
从 MySQL 5.6 开始，该EXPLAIN关键字适用于SELECT, DELETE, INSERT, REPLACE, 和UPDATE语句。
If your query has a syntax error, then it will still fail, however if successful you will only see the results of an EXPLAIN and the query will not make any changes.
如果您的查询有语法错误，那么它仍然会失败，但是如果成功，您只会看到 EXPLAIN 的结果，并且查询不会进行任何更改。
This is much simpler than doing schema changes, using temp tables, or aborting transactions as all you need to do is insert "EXPLAIN " in front of your existing query.
这比更改架构、使用临时表或中止事务要简单得多，因为您只需在现有查询前插入“EXPLAIN”即可。
More information: https://dev.mysql.com/doc/refman/5.6/en/explain.html
回答by Larry Lustig
ANSI SQL: No.
ANSI SQL：否。
MySQL: Maybe. The EXPLAIN keyword originally worked only with SELECT, but it might have been extended to UPDATE and DELETE by now.
MySQL：也许吧。EXPLAIN 关键字最初仅适用于 SELECT，但现在它可能已扩展到 UPDATE 和 DELETE。
回答by Klaus Byskov Pedersen
To my knowledge no such thing exists. Furthermore it would not be an error if no somekeywith value 45 did not exist. It would just not delete anything.
据我所知，不存在这样的事情。此外，如果somekey不存在值为 45 的no也不会出错。它不会删除任何内容。
回答by Engle
This is an old question but after looking for the same thing today I kinda solved it with
这是一个老问题，但在今天寻找同样的事情后，我有点解决了
$prepResult = $connection->prepare($query);
$prepResult = $connection->prepare($query);
Seems to work for most cases - meaning it reliably checks syntax but doesn'ttest against the database. So for example with INSERT INTO it doesn't fail for inserting a string into an INT column, DROPing an unknown TABLE as well, same for TRUNCATEing an unknown table...
似乎适用于大多数情况 - 这意味着它可靠地检查语法但不针对数据库进行测试。因此，例如使用 INSERT INTO 将字符串插入到 INT 列中不会失败，也不会删除未知表，对于 TRUNCATEing 未知表也是如此......
So, this will take you there - half way ;)
所以，这会带你去那里 - 中途;)
回答by Brandon See
You can use F11with Teradata SQL Assistant to do this
您可以使用F11Teradata SQL Assistant 来执行此操作
回答by Jon S
Kind of. Say you have a table that you want to update: "Entry". You can
的种类。假设您有一个要更新的表：“条目”。你可以
SELECT Col1 = OTHER.Col4,
Col2 = EXTRA.Col2,
FROM dbo.Entry E
INNER JOIN MYOTHERTABLE OTHER ON OTHER.Id = E.Id
INNER JOIN MYEXTRATABLE EXTRA ON EXTRA.Id = OTHER.Id
In this instance, Col1 and Col2 are columns of the Entry table. If you wrote
在本例中，Col1 和 Col2 是 Entry 表的列。如果你写
UPDATE E
SET
instead of the initial SELECT, you'd have your update. It doesn't work for all scenarios but, if it's a simple update, you can get a quick preview this way.
而不是最初的 SELECT，你会有你的更新。它并不适用于所有场景，但是，如果它是一个简单的更新，您可以通过这种方式快速预览。
