How to do a MySQL recursive query?

How to test a query's syntax using MySQL without executing the query

• I want to be able to pass any query to MySQL, have MySQL check whether the query's syntax is valid, including whether non-existing tables or columns have been referenced, and return an error if something is wrong, WITHOUT actually executing the query. There are tricks that accomplish this in some cases, such as using LIMIT 0 on UPDATE or DELETE statements. However, I also need a way to test INSERT statements so this hack is of limited use. I am looking for a general solution.

• Answer:

  Hi, I've been working with MySQL for almost 10 years, and never heard of such a setting. I checked my books to see if perhaps I just never thought to look, and there isn't even a connection switch you could set to put the database in "test mode". The way to do this for your Insert test, is to create a temporary table, perform the insert, check for errors, and then drop the temporary table. mysql> CREATE TEMPORARY TABLE tmp_address SELECT * FROM address While session is connected the temporary table will exist, unless you DROP it. Once the session is closed, then the temporary table will be dropped by MySQL. This isn't a hack, it is the real way to do what you are trying to do. In fact you can set the temporary table name to the same name as the original, and while the session is connected, the temporary table will be used, instead of the original table: mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | employee | | rssnews | +----------------+ 3 rows in set (0.01 sec) mysql> create temporary table employee select * from employee; Query OK, 10 rows affected (0.14 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> select count(*) from employee; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> delete from employee; Query OK, 10 rows affected (0.00 sec) mysql> select count(*) from employee; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> drop table employee; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from employee; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> What this does is allow you to put variable switches in your code to set your program into "test" mode, by using a logic switch to see if the temporary table should be created first before running your queries. This way you don't have to have two sets of queries (one with temp names and one with real names). Of course there are other ways to do this, but this is by far the most effective. You can learn more about temporary tables at these websites: http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html http://dev.mysql.com/doc/refman/5.0/en/create-table.html http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html thanks, webadept-ga