如何在不执行的情况下检查 MySQL 查询是否有效？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/377883/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check if MySQL query is valid without executing it?
提问by Piskvor left the building
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 命令并运行它（在几个数据库服务器上依次运行）。我相信用户是明智的，但会发生错误，我正在寻找一种方法来防止基本的拼写错误：
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;
or
或者
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 EXPLAINto 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 (...)并观察错误，但有没有办法检查其他命令？
采纳答案by James Ogden
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 查询工具应该能够进行这种验证（如果您愿意，可以进行智能感知），但我从第一手经验中知道，大多数（免费）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.
“准备”查询会做你想做的事，但它是运行时检查，而不是编译时检查 - 您似乎正在寻找编译时/离线解决方案。
回答by Eli
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 引擎和设置，您可以启动事务、尝试查询，然后进行回滚。假设脏读关闭，那应该可行。
回答by Roman Gelembjuk
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
PS Explain 也适用于插入、更新、删除。不仅选择
回答by soulmerge
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
