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

Is there anything like this: 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.

Answers


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:

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


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.


In MySQL use this

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


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.


As of MySQL 5.6, the EXPLAIN keyword works with SELECT, DELETE, INSERT, REPLACE, and UPDATE 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: https://dev.mysql.com/doc/refman/5.6/en/explain.html


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.


You can use F11 with Teradata SQL Assistant to do this


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

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.


Need Your Help

What is the relationship between Node.js and V8?

node.js v8

I've been thinking about this question for a while and can't seem to find the answer. What is the relationship between Node.js and V8? and Can Node.js work without V8?

Getting started with Intel x86 SSE SIMD instructions

c gcc x86 sse simd

I want to learn more about using the SSE.