是否有命令可以在不执行SQL查询的情况下测试SQL查询?(MySQL或ANSI SQL)

Is there anything like this:
TEST DELETE FROM user WHERE somekey = 45;

是否存在这样的情况:测试从某某键= 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.

它可以返回任何错误，例如某个键不存在，或某个约束违反或其他，并报告将影响多少行，但不执行查询?我知道您可以很容易地在一个select查询中转换任何在任何行中没有写或删除效果的查询，但是这可能会导致错误，如果您想测试和调试许多查询，那么这不是很实用。

7 个解决方案

#1

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:

如果目的是找到查询处理时间没有返回任何行(我经常需要这个,我想知道我是多么长的一段代码使用将不返回一个几百万行我看到不感兴趣),那么黑洞引擎可以非常有用:

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:

例如，我有两个表，t1和t2，有数百万行，我把它们连接在一起。我想检查在GUI (SQLYog或mysql workbench或其他库)中，这可能需要花费多长时间，而不返回数百万行，这些行会占用内存，可能需要花时间让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).

注意，因为我只是在寻找执行时间，所以我不需要返回所有的列(即“*”)，而只返回一个占位符(在本例中为“1”)。

#2

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.

确保执行整个块，而不仅仅是删除语句。此外，不要在任何生产环境或任何无法承受数据丢失的系统上运行此命令。

#3

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

在这里引用http://dev.mysql.com/doc/refman/5.0/en/commit.html

#4

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。

#5

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

据我所知，这种东西不存在。此外，如果不存在任何值为45的键，则不会出现错误。它不会删除任何东西。

#6

You can use F11 with Teradata SQL Assistant to do this

您可以使用F11和Teradata SQL助手来实现这一点

#7

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

种。假设您有一个要更新的表:“Entry”。你可以

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是条目表的列。如果你写

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.

而不是初始选择，您将拥有更新。它并不适用于所有的场景，但是，如果是一个简单的更新，您可以通过这种方式获得快速预览。

#1