logo标签列表

使用EXPLAIN命令验证CREATE TABLE DDL语法

sql-serveroracledb2teradatanetezza
5

我有一些创建表格的脚本，需要在我的应用程序中进行预验证。我可以想到两种方式：

  1. 使用该脚本创建表格，然后立即删除表格。
  2. 使用EXPLAIN命令查找语法错误，而不创建表格。

我发现第二种方法更有效。因此，我使用Explain命令验证了CREATE TABLE DDLs。

工作原理：

Teradata

Explain <CREATE TABLE DDL>

Oracle

EXPLAIN PLAN FOR <CREATE TABLE DDL>

无法工作：

SQL SERVER

找不到存储过程'explain'。 SQLState: S00062 ErrorCode: 2812

Netezza

^ 在字符18处发现"CREATE"，期望 DELETE' 或 INSERT' 或 SELECT' 或UPDATE' 或 `WITH'

DB2

错误：DB2 SQL 错误：SQLCODE=-104，SQLSTATE=42601，SQLERRMC=TABLE;EXPLAIN CREATE;JOIN，DRIVER=4.14.111 SQLState: 42601 ErrorCode: -104

  • 是否有更好的方法来验证创建表DDL？

  • 是否有一种通用方法可以跨流行的RDBMS处理这个问题？

  • 如果explain是唯一可用的解决方案，如何为SQL Server、Netezza和DB2执行explain操作？

编辑：

这里的验证意味着检查语法（存储大小、精度、范围违规、保留关键字作为表或列名等。）

例如，像这样的查询-

create table abc (c1 decimal(555,44))

我希望能提前检测到精度溢出错误。

“验证”这个词你具体是指什么？ - Nick Krasnov
@NicholasKrasnov 验证意味着查找精度、规模溢出等。例如 create table abc (c1 decimal(555,44))。 - Dev
6
有没有通用的方式可以在流行的关系型数据库管理系统中处理这个问题？绝对没有。 - user330315
@a_horse_with_no_name 好的，谢谢...！ - Dev
如果你正在寻找一种与DBMS无关的方式来管理你的DDL，我建议你看看Liquibase。 - user330315
显示剩余5条评论
4个回答
3

没有一种通用/标准的方法适用于所有DBMS。

我希望所有流行的DBMS都有类似EXPLAIN命令的东西。可以返回执行计划而不是运行查询本身。每个服务器都有自己的做法。

http://use-the-index-luke.com/sql/explain-plan展示了如何在几个DBMS中执行此操作。搜索<your DBMS name> explain plan command通常会得到很好的结果。

  • DB2 - EXPLAIN PLAN FOR（对于DB2，使用“EXPLAIN PLAN FOR”命令）
  • SQLBase - SET PLANONLY ON（对于SQLBase，使用“SET PLANONLY ON”命令）
  • MySQL - EXPLAIN（对于MySQL，使用“EXPLAIN”命令）
  • Oracle - EXPLAIN PLAN FOR（对于Oracle，使用“EXPLAIN PLAN FOR”命令）
  • PostgreSQL - EXPLAIN（对于PostgreSQL，使用“EXPLAIN”命令）
  • SQL Server - SET SHOWPLAN_ALL ON（对于SQL Server，使用“SET SHOWPLAN_ALL ON”命令）
  • Teradata - EXPLAIN（对于Teradata，使用“EXPLAIN”命令）
  • Netezza - EXPLAIN VERBOSE（对于Netezza，使用“EXPLAIN VERBOSE”命令）
另一种方法是启动一个事务，运行您的语句并回滚事务。当然，您需要适当的错误处理，这在不同的服务器之间也有所不同。在SQL Server中，有TRY ... CATCH。
还值得检查所选择的DBMS是否支持事务中的DDL语句。例如，在MySQL中，“某些语句无法回滚。一般来说，这包括数据定义语言(DDL)语句，例如创建或删除数据库的语句，创建、删除或修改表或存储例程的语句。”
1

对于您至少前两个问题的建议：

  • 有没有其他更好的方法来验证Create Table DDLs？
  • 有没有通用的方法来处理流行的RDBMS？

建议使用Perl的解析和数据库接口功能（即Perl DBI模块），编写一个脚本，通过预处理语句调用针对您选择的数据库的SQL进行验证。

高级代码流程如下：

  1. 连接到您选择的数据库
  2. 通过Perl的prepare()调用运行您的SQL（例如$dbh->prepare('CREATE TABLE emp (emp_name VARCHAR2(30)')）
  3. 检查prepare()调用的输出状态

参考A Short Guide to DBI

准备调用准备要由数据库执行的查询。参数可以是任何SQL。在高端数据库上，prepare将发送SQL到数据库服务器，该服务器将编译它。如果prepare成功，则返回表示语句的语句句柄对象；否则返回未定义值，我们中止程序。$dbh->errstr将返回失败的原因，可能是“SQL语法错误”。如果可能，它从实际数据库获取此原因。
Perl还有一些其他模块值得一看，可能会有用，即：
SQL::Translator SQL::Statement
1
您应该能够使用仅格式选项 SET FMTONLY ON 在 SQL Server 中评估查询的正确性。设置此选项后，SQL Server 实际上不会尝试创建表格。使用您的示例，T-SQL 将如下所示：
SET FMTONLY ON
create table abc (c1 decimal(555,44))
SET FMTONLY OFF

执行上述T-SQL将返回错误消息“列或参数＃1：指定的列精度555大于最大精度38。”
您还可以创建一个存储过程，使用在您正在使用的数据库平台上效果最佳的方法来评估查询。我不熟悉Netezza、Teradata和DB2，但我假设它们可以执行动态SQL。使用此方法，您只需将要评估的查询作为参数从应用程序层传递给存储过程即可。
以下代码片段显示了如何在SQL Server中完成此操作：
CREATE PROCEDURE ValidateQuerySyntax
(
    @query NVARCHAR(MAX)
)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @validationQuery NVARCHAR(MAX) = 'SET FMTONLY ON; ' + CHAR(13) + @query + ';' +  CHAR(13) + 'SET FMTONLY OFF;';

    BEGIN TRY
        EXEC (@validationQuery);

        -- Return error code 0 if query validation was successful.
        SELECT  
             0 AS ErrorNumber  
            ,0 AS ErrorSeverity  
            ,0 AS ErrorState  
            ,0 AS ErrorLine  
            ,'Query evaluated successfully' AS ErrorMessage; 

    END TRY  
    BEGIN CATCH  

        -- Return error information if query validation failed.
        SELECT  
             ERROR_NUMBER() AS ErrorNumber  
            ,ERROR_SEVERITY() AS ErrorSeverity  
            ,ERROR_STATE() AS ErrorState  
            ,ERROR_LINE() AS ErrorLine  
            ,ERROR_MESSAGE() AS ErrorMessage; 
    END CATCH; 

END

查询可以按以下方式进行评估：

DECLARE @query_1 NVARCHAR(MAX) = 
'CREATE TABLE A 
(   
     c1 INT
)';

DECLARE @query_2 NVARCHAR(MAX) = 
'CREATE TABLE B 
(   
     c1 INT
     c2 INT
)';

DECLARE @query_3 NVARCHAR(MAX) = 
'CREATE TABLE B 
(   
     c1 INT
     ,c2 DECIMAL(555,44)
)';

EXEC dbo.ValidateQuerySyntax @query = @query_1;

EXEC dbo.ValidateQuerySyntax @query = @query_2;

EXEC dbo.ValidateQuerySyntax @query = @query_3;

以上验证调用的输出如下：
-------------------------------------------------------------------------------------------------------------------------------------------------------------
ErrorNumber | ErrorSeverity | ErrorState    | ErrorLine | ErrorMessage
-------------------------------------------------------------------------------------------------------------------------------------------------------------
0           | 0             | 0             | 0         | Query evaluated successfully
-------------------------------------------------------------------------------------------------------------------------------------------------------------
102         | 15            | 1             | 4         | Incorrect syntax near 'c2'.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
2750        | 16            | 1             | 1         | Column or parameter #2: Specified column precision 555 is greater than the maximum precision of 38.
-------------------------------------------------------------------------------------------------------------------------------------------------------------

当然，这意味着首先需要创建评估查询的存储过程，但它应该简化在不同数据库平台上验证查询的过程。
0

或许可以使用针对DDL语句的字符串发出SQL PREPARE语句，以便在DDL CREATE TABLE语句无效时显示SQLCODE和SQLSTATE。类似以下REXX式的伪代码：

sCrtTable="create table abc (c1 decimal(555,44))" ;
prepare DDL_stmt from :sCrtTable ;
say sqlCode ":" sqlState ; /* e.g.: "-604 : 42611"  per invalid length attribute */
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接