​sql_export_columns(sqlText)
sql_export_columns(sqlText, dbType)
sql_export_columns(sqlText, dbType, throwError)​
  • 命令说明:返回sqlText语句中所有出现的列,用逗号分隔,列所属的表会进行关联推导,如果没有找到明确的表,则为UNKNOWN。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_export_columns(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_columns(a.sql_text)                                                                                                                                                                                                                                                                                                                  |
      +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | UNKNOWN.s_acctbal,UNKNOWN.s_name,UNKNOWN.n_name,UNKNOWN.p_partkey,UNKNOWN.p_mfgr,UNKNOWN.s_address,UNKNOWN.s_phone,UNKNOWN.s_comment,UNKNOWN.ps_partkey,UNKNOWN.s_suppkey,UNKNOWN.ps_suppkey,UNKNOWN.p_size,UNKNOWN.p_type,UNKNOWN.s_nationkey,UNKNOWN.n_nationkey,UNKNOWN.n_regionkey,UNKNOWN.r_regionkey,UNKNOWN.r_name,UNKNOWN.ps_supplycost |​

SQL_EXPORT_FUNCTIONS

​sql_export_functions(sqlText)
sql_export_functions(sqlText, dbType)
sql_export_functions(sqlText, dbType, throwError)​
  • 命令说明:返回sqlText中所有出现的函数名,用逗号分隔,列所属的表会进行关联推导,如果没有找到明确的表,则为UNKNOWN。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR。

  • 示例:

    ​  SELECT sql_export_functions(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
    +----------------------------------+
    | sql_export_functions(a.sql_text) |
    +----------------------------------+
    | min                              |​

SQL_EXPORT_PREDICATE_COLUMNS

​sql_export_predicate_columns(sqlText)
sql_export_predicate_columns(sqlText, dbType)
sql_export_predicate_columns(sqlText, dbType, throwError)​
  • 命令说明:返回sqlText中出现在谓词条件表达式中的列名,用逗号分隔,列所属的表会进行关联推导,如果没有找到明确的表,则为UNKNOWN。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_export_predicate_columns(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_predicate_columns(a.sql_text)                                                                                                                                                                                     |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | UNKNOWN.p_partkey,UNKNOWN.ps_partkey,UNKNOWN.s_suppkey,UNKNOWN.ps_suppkey,UNKNOWN.p_size,UNKNOWN.p_type,UNKNOWN.s_nationkey,UNKNOWN.n_nationkey,UNKNOWN.n_regionkey,UNKNOWN.r_regionkey,UNKNOWN.r_name,UNKNOWN.ps_supplycost |​

SQL_EXPORT_PREDICATES

​sql_export_predicates(sqlText)
sql_export_predicates(sqlText, dbType)
sql_export_predicates(sqlText, dbType, compactValues)
sql_export_predicates(sqlText, dbType, compactValues, throwError)​
  • 命令说明:返回sqlText中所有出现的谓词条件表达式元素数组,用逗号分隔,列所属的表会进行关联推导,如果没有找到明确的表,则为UNKNOWN。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • compactValues:BOOLEAN,值为TRUE时,谓词条件中的值以数组形式返回。可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_export_predicates(a.sql_text, 'mysql', true)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_predicates(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                  |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | [["UNKNOWN","p_partkey","=",null],["UNKNOWN","ps_partkey","=",null],["UNKNOWN","s_suppkey","=",null],["UNKNOWN","ps_suppkey","=",null],["UNKNOWN","p_size","=",35],["UNKNOWN","p_type","LIKE","%NICKEL"],["UNKNOWN","s_nationkey","=",null],["UNKNOWN","n_nationkey","=",null],["UNKNOWN","n_regionkey","=",null],["UNKNOWN","r_regionkey","=",null],["UNKNOWN","r_name","=",["MIDDLE EAST","MIDDLE EAST"]],["UNKNOWN","ps_supplycost","IN",null]] |​

SQL_EXPORT_SELECT_LIST_COLUMNS

​sql_export_select_list_columns(sqlText)
sql_export_select_list_columns(sqlText, dbType)
sql_export_select_list_columns(sqlText, dbType, throwError)​
  • 命令说明:返回sqlText中SELECT子句返回列中出现的列名列表(包括子查询),用逗号分隔,列所属的表会进行关联推导,如果没有找到明确的表,则为UNKNOWN。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_export_select_list_columns(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_export_select_list_columns(a.sql_text)                                                                                                                 |
      +------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | UNKNOWN.s_acctbal,UNKNOWN.s_name,UNKNOWN.n_name,UNKNOWN.p_partkey,UNKNOWN.p_mfgr,UNKNOWN.s_address,UNKNOWN.s_phone,UNKNOWN.s_comment,UNKNOWN.ps_supplycost |​

SQL_EXPORT_TABLES

​sql_export_tables(sqlText)
sql_export_tables(sqlText, dbType)
sql_export_tables(sqlText, dbType, throwError)​
  • 命令说明:返回sqlText中所有出现的表名,用逗号分隔。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_export_tables(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      | sql_export_tables(a.sql_text)        |
      +--------------------------------------+
      | part,supplier,partsupp,nation,region |​

SQL_FORMAT

​sql_format(sqlText)
sql_format(sqlText, dbType)
sql_format(sqlText, dbType, throwError)​
  • 命令说明:格式化sqlTextSQL语句。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_format(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_format(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
          , s_address, s_phone, s_comment
      FROM part, supplier, partsupp, nation, region
      WHERE p_partkey = ps_partkey
          AND s_suppkey = ps_suppkey
          AND p_size = 35
          AND p_type LIKE '%NICKEL'
          AND s_nationkey = n_nationkey
          AND n_regionkey = r_regionkey
          AND r_name = 'MIDDLE EAST'
          AND ps_supplycost IN (
              SELECT min(ps_supplycost)
              FROM partsupp, supplier, nation, region
              WHERE s_suppkey = ps_suppkey
                  AND s_nationkey = n_nationkey
                  AND n_regionkey = r_regionkey
                  AND r_name = 'MIDDLE EAST'
          )
      ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
      LIMIT 100; |​

SQL_PARAMS

​sql_params(sqlText)
sql_params(sqlText, dbType)
sql_params(sqlText, dbType, throwError)​
  • 命令说明:提取sqlText的SQL语句中的literal值。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR

  • 示例:

    ​  SELECT sql_params(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------------------------+
      | sql_params(a.sql_text)                         |
      +------------------------------------------------+
      | [35,"%NICKEL","MIDDLE EAST","MIDDLE EAST",100] |​

SQL_PATTERN

​sql_pattern(sqlText)
sql_pattern(sqlText, dbType)
sql_pattern(sqlText, dbType, throwError)​
  • 命令说明:提取SQL语句参数化后的SQL Pattern,literal用?代替。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR。

  • 示例:

    ​  SELECT sql_pattern(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | sql_pattern(a.sql_text)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
          , s_address, s_phone, s_comment
      FROM part, supplier, partsupp, nation, region
      WHERE p_partkey = ps_partkey
          AND s_suppkey = ps_suppkey
          AND p_size = ?
          AND p_type LIKE ?
          AND s_nationkey = n_nationkey
          AND n_regionkey = r_regionkey
          AND r_name = ?
          AND ps_supplycost IN (
              SELECT min(ps_supplycost)
              FROM partsupp, supplier, nation, region
              WHERE s_suppkey = ps_suppkey
                  AND s_nationkey = n_nationkey
                  AND n_regionkey = r_regionkey
                  AND r_name = ?
          )
      ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
      LIMIT ?; |​

SQL_PATTERN_HASH

​sql_pattern_hash(sqlText)
sql_pattern_hash(sqlText, dbType)
sql_pattern_hash(sqlText, dbType, throwError)​
  • 命令说明:提取SQL语句参数化后的SQL Pattern,literal用?代替。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:VARCHAR。

  • 示例:

    ​  SELECT sql_pattern_hash(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------+
      | sql_pattern_hash(a.sql_text) |
      +------------------------------+
      |           925870115679910184 |​

SQL_SYNTAX_CHECK

​sql_syntax_check(sqlText)
sql_syntax_check(sqlText, dbType)
sql_syntax_check(sqlText, dbType, throwError)​
  • 命令说明:对sqlText进行语法检查,返回值1表示正确,0表示错误。

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

    • dbType:VARCHAR,SQL方言,默认为MySQL,可选参数。

    • throwError:BOOLEAN,值为TRUE时,遇到非法SQL将抛出异常;值为FALSE时,遇到非法SQL不会抛出异常,返回NULL。可选参数。

  • 返回值类型:BOOLEAN。

  • 示例:

    ​  SELECT sql_syntax_check(a.sql_text)
      FROM (
      SELECT 'SELECT   s_acctbal, 
               s_name, 
               n_name, 
               p_partkey, 
               p_mfgr, 
               s_address, 
               s_phone, 
               s_comment 
      FROM     part, 
               supplier, 
               partsupp, 
               nation, 
               region 
      WHERE    p_partkey = ps_partkey 
      AND      s_suppkey = ps_suppkey 
      AND      p_size = 35 
      AND      p_type LIKE ''%NICKEL'' 
      AND      s_nationkey = n_nationkey 
      AND      n_regionkey = r_regionkey 
      AND      r_name = ''MIDDLE EAST'' 
      AND      ps_supplycost IN 
               ( 
                      SELECT min(ps_supplycost) 
                      FROM   partsupp, 
                             supplier, 
                             nation, 
                             region 
                      WHERE  s_suppkey = ps_suppkey 
                      AND    s_nationkey = n_nationkey 
                      AND    n_regionkey = r_regionkey 
                      AND    r_name = ''MIDDLE EAST'' ) 
      ORDER BY s_acctbal DESC, 
               n_name, 
               s_name, 
               p_partkey 
      LIMIT    100;' AS sql_text
      ) a;
      +------------------------------+
      | sql_syntax_check(a.sql_text) |
      +------------------------------+
      |                            1 |​