DLA SQL分析函数：SQL语句审计与分析的利器

于 2019-04-23 07:43:23 发布
阅读量181 收藏
点赞数
CC 4.0 BY-SA版权
文章标签： 数据库
原文链接：https://juejin.im/post/5cbec1c96fb9a03224159a26
介绍DataLakeAnalytics最新发布的SQL内置函数，用于SQL语句的深入分析，包括提取列、函数、谓词、表等信息，支持多种SQL方言，适用于安全审计、数据库日志分析等场景。

1. 简介

Data Lake Analytics最新release一组SQL内置函数，用来进行SQL语句的分析、信息提取，方便用户对SQL语句进行语法层面的审计、分析，可以应用于很多安全、数据库日志分析等场景。参考DLA（Data Lake Analytics）之前的文档，只要您的SQL语句文本保存在DLA支持的数据源系统中，就能使用DLA的SQL分析函数，方便、快捷的进行SQL语句的审计和分析，或者基于此构建相关应用程序和系统。

前提条件，您的SQL语句文本已经作为一个字符串、文本字段存储在DLA支持的数据源中，或者在统一格式的日志文件中，日志文件放到OSS中（应用自己上传、开源日志工具上传、日志服务投递，然后采用DLA进行SQL分析。

2. SQL分析函数详解

所有提供的SQL分析函数都是scalar标量函数。SQL分析函数支持多种SQL方言，默认SQL方言是mysql。

SQL方言mysqlpostgresqloracledb2sqlserverhiveodps

支持的函数列表：

NameDescriptionsql_export_columns提取SQL语句中所有出现的列sql_export_functions提取SQL语句中所有出现的函数sql_export_predicate_columns提取SQL语句中所有出现的谓词条件表达式涉及的列sql_export_predicates提取SQL语句中所有出现的谓词条件表达式sql_export_select_list_columns提取SQL语句中SELECT子句表达式中出现的列（包括子查询）sql_export_tables提取SQL语句中所有出现的表sql_format对SQL语句进行格式化sql_params提取SQL语句中所有的literal值sql_pattern提取SQL语句参数化后的SQL pattern，literal用?代替sql_pattern_hash提取sqlText的SQL语句参数化后的SQL pattern，并生成hash值sql_syntax_check对sqlText进行语法检查


2.1 sql_export_columns

函数调用形式：

  • sql_export_columns(sqlText)
  • sql_export_columns(sqlText, dbType)
  • sql_export_columns(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHARsqlText的SQL语句中所有出现的列，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN

示例：

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 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+复制代码



2.2 sql_export_functions

函数调用形式：

  • sql_export_functions(sqlText)
  • sql_export_functions(sqlText, dbType)
  • sql_export_functions(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHARsqlText的SQL语句中所有出现的函数名，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN

示例：

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                              |
+----------------------------------+复制代码


2.3 sql_export_predicate_columns

函数调用形式：

  • sql_export_predicate_columns(sqlText)
  • sql_export_predicate_columns(sqlText, dbType)
  • sql_export_predicate_columns(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHARsqlText的SQL语句中出现在谓词条件表达式中的列名，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN

示例：

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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+复制代码


2.4 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)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlcompactValuesBOOLEAN可选。true时，谓词条件中值以数组的形式出现在返回值中throwErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHARsqlText的SQL语句中所有出现的谓词条件表达式元素数组，用逗号分隔，列所属的表会进行关联推导，如果没有找到明确的表，则为UNKNOWN

示例：

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]] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+复制代码


2.5 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)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHARsqlText的SQL语句中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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------+复制代码


2.6 sql_export_tables

函数调用形式：

  • sql_export_tables(sqlText)
  • sql_export_tables(sqlText, dbType)
  • sql_export_tables(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHARsqlText的SQL语句中所有出现的表名，用逗号分隔

示例：

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 |
+--------------------------------------+复制代码


2.7 sql_format

函数调用形式：

  • sql_format(sqlText)
  • sql_format(sqlText, dbType)
  • sql_format(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHAR格式化sqlText的SQL语句

示例：

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; |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+复制代码


2.8 sql_params

函数调用形式：

  • sql_params(sqlText)
  • sql_params(sqlText, dbType)
  • sql_params(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHAR提取sqlText的SQL语句中的literal值

示例：

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] |
+------------------------------------------------+复制代码


2.10 sql_pattern

函数调用形式：

  • sql_pattern(sqlText)
  • sql_pattern(sqlText, dbType)
  • sql_pattern(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHAR提取sqlText的SQL语句参数化后的SQL pattern, literal值换成?

示例：

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 ?; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+复制代码


2.11 sql_pattern_hash

函数调用形式：

  • sql_pattern_hash(sqlText)
  • sql_pattern_hash(sqlText, dbType)
  • sql_pattern_hash(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明VARCHAR提取sqlText的SQL语句参数化后的SQL pattern，并生成hash值

示例：

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 |
+------------------------------+复制代码


2.12 sql_syntax_check

函数调用形式：

  • sql_syntax_check(sqlText)
  • sql_syntax_check(sqlText, dbType)
  • sql_syntax_check(sqlText, dbType, throwError)

参数说明：

参数名类型说明sqlTextVARCHAR必选dbTypeVARCHAR可选。SQL方言，默认为mysqlthrowErrorBOOLEAN可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型说明BOOLEAN对sqlText进行语法检查，1表示正确，0表示错误

示例：

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 |
+------------------------------+复制代码


原文链接

本文为云栖社区原创内容，未经允许不得转载。


转载于:https://juejin.im/post/5cbec1c96fb9a03224159a26

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值