blocks|key|1089824|text|我所知道的唯一一件事就是将它包装在一个总是回滚的事务中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1089825|BEGIN+TRANSACTION

DELETE+FROM+user+WHERE+somekey+=+45;

ROLLBACK+TRANSACTION|code-block|syntax|javascript|1089826|确保执行整个代码块，而不仅仅是delete语句。此外，不要在任何生产环境或任何您不能丢失数据的系统上运行此命令。|1089827|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The only thing I know of is to wrap it in a transaction that is always rolled back:

<pre><code>BEGIN TRANSACTION

DELETE FROM user WHERE somekey = 45;

ROLLBACK TRANSACTION
</code></pre>

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.

blocks|key|1305094|text|据我所知，这种东西是不存在的。此外，如果没有值为45的somekey不存在，也不会出现错误。它不会删除任何东西。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1305095|entityMap^0|R|7|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

To my knowledge no such thing exists. Furthermore it would not be an error if no <code>somekey</code> with value 45 did not exist. It would just not delete anything.

blocks|key|1089946|text|ANSI+SQL:否。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1089947|MySQL:也许吧。EXPLAIN关键字最初只适用于SELECT，但现在它可能已经扩展到UPDATE和DELETE。|1089948|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

ANSI SQL: No.

MySQL: Maybe. The EXPLAIN keyword originally worked only with SELECT, but it might have been extended to UPDATE and DELETE by now.

blocks|key|1305238|text|在MySQL中使用以下代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1305239|START+TRANSACTION;
QUERY;|code-block|syntax|javascript|1305240|使用";“很重要，因为如果你不这样做，它就不会起作用。例如|1305241|START+TRANSACTION;
UPDATE+tableX+SET+colX+=+valueA,+colY+=+valueB+WHERE+id=1|1305242|在此引用http://dev.mysql.com/doc/refman/5.0/en/commit.html|offset|length|1305243|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/commit.html^0|0|0|0|0|4|1E|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

In MySQL use this

<pre><code>START TRANSACTION;
QUERY;
</code></pre>

It is important to use ";" because if you don't, it won't work. For example

<pre><code>START TRANSACTION;
UPDATE tableX SET colX = valueA, colY = valueB WHERE id=1
</code></pre>

Reference here <a href="http://dev.mysql.com/doc/refman/5.0/en/commit.html">http://dev.mysql.com/doc/refman/5.0/en/commit.html</a>

blocks|key|1305329|text|有点。假设您有一个想要更新的表："Entry“。你可以的|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1305330|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|code-block|syntax|javascript|1305331|在本例中，Col1和Col2是条目表的列。如果你写了|1305332|UPDATE+E
SET|1305333|您将获得更新，而不是初始的SELECT。它并不适用于所有的场景，但是，如果它是一个简单的更新，您可以通过这种方式获得快速预览。|1305334|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Kind of. Say you have a table that you want to update: "Entry". You can 

<pre><code>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
</code></pre>

In this instance, Col1 and Col2 are columns of the Entry table. If you wrote

<pre><code>UPDATE E
SET
</code></pre>

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.

blocks|key|1090185|text|我意识到这是一个有点老生常谈的问题，但为了完整性...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1090186|如果其目的是在不返回任何行的情况下找到查询处理时间(我经常需要这样做，我想知道我正在使用的一段代码将花费多长时间，而不返回我不感兴趣看到的几百万行)，那么黑洞引擎可能非常有用：|1090187|https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html|offset|length|1090188|例如，假设我有两个表，t1+&+t2，有数百万行，我将它们连接在一起。我想在图形用户界面(SQLYog或mysql工作台等)中检查这可能需要多长时间，而不返回数以百万计的行，这将消耗内存，并可能需要图形用户界面处理和显示的时间。我使用黑洞引擎将行“转储”到任何地方。例如：|1090189|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|code-block|syntax|javascript|1090190|请注意，由于我只是在寻找执行时间，所以我不会费心返回所有列(IE带有"*")，而只返回一个占位符(在本例中为“1”)。|1090191|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|1X|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@]|9|@$F|10|G|11|1|12]]|A|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|14|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|15|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|E]]]]

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:

<a href="https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html</a>

For instance say I have 2 tables, t1 &amp; 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:

<pre><code>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
</code></pre>

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).

blocks|key|1305478|text|从MySQL+5.6开始，EXPLAIN关键字可用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1305479|如果您的查询有语法错误，那么它仍然会失败，但是如果成功，您将只看到EXPLAIN的结果，并且查询不会进行任何更改。|1305480|这比进行模式更改、使用临时表或中止事务要简单得多，因为您只需在现有查询前面插入"EXPLAIN“。|1305481|更多信息：https://dev.mysql.com/doc/refman/5.6/en/explain.html|1305482|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.6/en/explain.html^0|D|7|Q|6|X|6|14|6|1B|7|1J|6|0|0|0|5|1G|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|16|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|17|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|18|8|@]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|L|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]]]

As of MySQL 5.6, the <code>EXPLAIN</code> keyword works with <code>SELECT</code>, <code>DELETE</code>, <code>INSERT</code>, <code>REPLACE</code>, and <code>UPDATE</code> statements.

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.

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.

More information:
<a href="https://dev.mysql.com/doc/refman/5.6/en/explain.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.6/en/explain.html</a>

Is there anything like this: 
<code>TEST DELETE FROM user WHERE somekey = 45;</code> 

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.

Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )

MySQL

有没有这样的东西：TEST DELETE FROM user WHERE somekey = 45;这可能会返回任何错误，例如某个键不存在，或某些约束违反或任何东西，并报告有多少行将受到影响，但不执行查询？我知道您可以很容易地将select查询中的任何查询转换为在任何行中没有写入或删除效果的查询，但这可能会导致错误，如...

问是否有一个命令可以在不执行SQL查询的情况下测试它？( MySQL或ANSI SQL )
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有一个命令可以在不执行SQL查询的情况下测试它？( MySQL或ANSI SQL )EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有一个命令可以在不执行SQL查询的情况下测试它？( MySQL或ANSI SQL )
EN