CODEWITHSUNDEEP
sqlmysqldebuggingtesting
Petruza
Petruza

Reputation: 12276

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.

Upvotes: 35

Views: 88441

Answers (9)

Engle
Engle

Reputation: 45

This is an old question but after looking for the same thing today I kinda solved it with

$prepResult = $connection->prepare($query);

Seems to work for most cases - meaning it reliably checks syntax but doesn't test 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...

So, this will take you there - half way ;)

Upvotes: 0

DOOManiac
DOOManiac

Reputation: 6284

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

Upvotes: 14

Giles
Giles

Reputation: 1687

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

Upvotes: 2

Jon S
Jon S

Reputation: 168

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.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50998

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.

Upvotes: 6

Brandon See
Brandon See

Reputation: 1

You can use F11 with Teradata SQL Assistant to do this

Upvotes: -1

絢瀬絵里
絢瀬絵里

Reputation: 1023

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

Upvotes: 10

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 121037

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.

Upvotes: 0

DCNYAM
DCNYAM

Reputation: 12126

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.

Upvotes: 55

Related Questions