Is there a way to get the results of a MySQL 5.0 statement without actually executing it?

Question

I have an INSERT command, but I want MySQL to tell me if the command will be successful without actually executing it. Is there some MySQL syntax that makes this possible? Thanks!

Answer 1

Well, I don't know about MySQL, but in SQL Server you can use a transaction:

BEGIN TRANSACTION

-- Your Statement Here
INSERT INTO MyTable....

ROLLBACK TRANSACTION



If you do a ROLLBACK instead of a COMMIT, it will undo any change made to the database table. And because the INSERT is actually executed, it should show you if the command will actually be successful.

HTH.
- j

Answer 2

You can create a backup of your entire database before you screw it up. You might also be able to do this by disabling auto-commit.

EXPORT to table1.mysql
$ mysqldump -u root table1 > table1.mysql

IMPORT from table1.mysql
$ mysql < table1.mysql

Auto-commit and Transactions
http://rpbouman.blogspot.com/2007/02/mysql-transactions-and-autocommit.html

As a last thought, you might like the --safe-updates option.
For beginners, a useful startup option is --safe-updates (or --i-am-a-dummy, which
has the same effect). It is helpful for cases when you might have issued a DELETE
FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement
deletes all rows from the table. With --safe-updates, you can delete rows only by
specifying the key values that identify them. This helps prevent accidents.

When you use the --safe-updates option, mysql issues the following statement when it
connects to the MySQL server:

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000, SQL_MAX_JOIN_SIZE=1000000;

See Section 5.3, âSET Syntaxâ

The SET statement has the following effects:

You are not allowed to execute an UPDATE or DELETE statement unless you specify a
key constraint in the WHERE clause or provide a LIMIT clause (or both). For
example:

UPDATE tbl_name SET not_key_column=val WHERE key_column=val;
UPDATE tbl_name SET not_key_column=val LIMIT 1;

The server limits all large SELECT results to 1,000 rows unless the statement
includes a LIMIT clause.

The server aborts multiple-table SELECT statements that probably need to examine
more than 1,000,000 row combinations.

To specify limits different from 1,000 and 1,000,000, you can override the defaults
by using the --select_limit and --max_join_size options:

shell> mysql --safe-updates --select_limit=500 --max_join_size=10000