如何检查MySQL查询是否有效而不执行它？

I'm making a simple tool that will get a string of MySQL commands and run it (on several DB servers sequentially). I trust the users to be sensible, but mistakes happen, and I'm looking for a way to prevent basic typos:

我正在制作一个简单的工具，它将获得一串MySQL命令并运行它（在几个DB服务器上顺序运行）。我相信用户是明智的，但错误发生了，我正在寻找一种方法来防止基本的拼写错误：

Is there a way to validate, at runtime, (relatively simple) MySQL queries to see if they're syntactically correct?

有没有办法在运行时验证（相对简单的）MySQL查询，看看它们在语法上是否正确？

I'm not looking for semantic correctness, e.g. table names or join applicability; just something like a spellcheck for SQL queries.

我不是在寻找语义正确性，例如表名或加入适用性;就像SQL查询的拼写检查一样。

In other words,

换一种说法，

SELECT * FROM x;

要么

INSERT INTO x SET id=1,bar="foo";

would be marked valid, whereas any of those would not:

将被标记为有效，而其中任何一个都不会：

SELECT FROM x;
SECLET * RFOM x;
ISNETR INTO x SET id=1;
HJBGYGCRYTCY;

For SELECTs, I could bend EXPLAIN to my needs - run EXPLAIN SELECT (...) and watch for errors, but is there a way to check for other commands as well?

对于SELECTs，我可以根据我的需要弯曲EXPLAIN - 运行EXPLAIN SELECT（...）并查看错误，但有没有办法检查其他命令？

4 个解决方案

#1

Not without knowledge of the schema (for example, is 'x' a table?) and writing a SQL parser. Your MySQL query tool should be able to do that kind of validation (intellisense if you like) but I know from first hand experience, most of the (free) MySQL tools are abysmal.

不是没有模式的知识（例如，'x'是表？）和编写SQL解析器。您的MySQL查询工具应该能够进行这种验证（如果您愿意，可以使用intellisense），但我从第一手经验中了解到，大多数（免费）MySQL工具都很糟糕。

'Preparing' the query would do what you want, but is a runtime check, not a compile time check - you seem to be looking for a compile time/offline solution.

“准备”查询将执行您想要的操作，但是是运行时检查，而不是编译时检查 - 您似乎正在寻找编译时/离线解决方案。

#2

Depending on your MySQL engine and settings, you could start a transaction, try the query, and then do a rollback. Assuming dirty reads are off, that should work.

根据您的MySQL引擎和设置，您可以启动事务，尝试查询，然后执行回滚。假设脏读是关闭的，那应该可行。

#3

You could create a temporary table to circumvent side effects of the query:

您可以创建一个临时表来规避查询的副作用：

CREATE TEMPORARY TABLE users SELECT * FROM users;
INSERT INTO users(name) VALUES('UniqueName');
DROP TABLE users;
SELECT * FROM users WHERE name='UniqueName'; -- Should return 0 results

#4

To verify a query i use the EXPLAIN command. You can take any SQL query and add EXPLAIN before it and execute. If query is wrong, error will be returned.

要验证查询，请使用EXPLAIN命令。您可以执行任何SQL查询并在其之前添加EXPLAIN并执行。如果查询错误，将返回错误。

Examples:

例子：

explain select * from users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

And wrong query

而且查询错误

explain select * from users2;
ERROR 1146 (42S02): Table 'test.users2' doesn't exist

P.S. Explain works for insert, update, delete too. Not only select

附：解释也适用于插入，更新，删除。不仅选择

#1