本文档主要介绍了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 | 否 | 取值：
|
命令说明：返回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 | 否 | 取值：
|
命令说明：返回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 | 否 | 取值：
|
命令说明：返回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 | 否 | 取值：
|
| throwError | BOOLEAN | 否 | 取值：
|
命令说明：返回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 | 否 | 取值：
|
返回值类型：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 | 否 | 取值：
|
命令说明：返回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 | 否 | 取值：
|
命令说明：格式化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 | 否 | 取值：
|
命令说明：提取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 | 否 | 取值：
|
命令说明：提取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 | 否 | 取值：
|
命令说明：提取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 | 否 | 取值：
|
命令说明：对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 |