如何在不执行MySQL的情况下检查MySQL查询是否有效? [英] How to check if MySQL query is valid without executing it?

问题描述

我正在制作一个简单的工具，它将获取一串MySQL命令并运行它(依次在多个DB服务器上).我相信用户会明智，但是会发生错误，我正在寻找一种防止基本错字的方法:

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?

我不是在寻找语义正确性，例如表名或联接适用性；就像对SQL查询进行拼写检查一样.

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

换句话说

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;

对于SELECT，我可以根据需要调整EXPLAIN-运行EXPLAIN SELECT (...)并观察错误，但是还有没有办法检查其他命令?

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?

推荐答案

并非不了解架构(例如，"x"是表吗?)并编写SQL解析器.您的MySQL查询工具应该能够进行这种验证(如果您愿意，可以进行智能验证)，但是据我的第一手经验，大多数(免费)MySQL工具都很糟糕.

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.

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

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

这篇关于如何在不执行MySQL的情况下检查MySQL查询是否有效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！