本文档主要介绍了SQL分析函数的语法结构、参数说明和使用示例。

​SQL_EXPORT_COLUMNS

语法结构:
​sql_export_columns(sqlText)
sql_export_columns(sqlText, dbType)
sql_export_columns(sqlText, dbType, throwError)​
参数说明:
参数名称 参数类型 是否必选 描述
sqlText VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

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

返回值类型: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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

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

返回值类型: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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

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

返回值类型: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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
compactValues BOOLEAN 取值:
  • TRUE:谓词条件中的值以数组形式返回。
  • FALSE
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

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

返回值类型: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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

返回值类型:VARCHAR。

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

示例:
​  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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

命令说明:返回sqlText中所有出现的表名,用逗号分隔。

返回值类型: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)​
参数说明:
参数名称 参数类型 是否必选 描述
sqlText VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

命令说明:格式化sqlTextSQL语句。

返回值类型: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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

命令说明:提取sqlText的SQL语句中的literal值。

返回值类型: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)​
参数说明:
参数名称 参数类型 是否必选 描述
sqlText VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

命令说明:提取SQL语句参数化后的SQL Pattern,literal用?代替。

返回值类型: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)​
参数说明:
参数名称 参数类型 是否必选 描述
sqlText VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

命令说明:提取SQL语句参数化后的SQL Pattern,literal用?代替。

返回值类型: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 VARCHAR
dbType VARCHAR SQL方言,默认为MySQL。
throwError BOOLEAN 取值:
  • TRUE:遇到非法SQL将抛出异常。
  • FALSE:遇到非法SQL不会抛出异常,返回NULL。

命令说明:对sqlText进行语法检查,返回值1表示正确,0表示错误。

返回值类型: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 |​