博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
DLA SQL分析函数：SQL语句审计与分析的利器
阅读量：7008 次
发布时间：2019-06-28

本文共 28881 字，大约阅读时间需要 96 分钟。

1. 简介

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

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

2. SQL分析函数详解

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

SQL方言
mysql
postgresql
oracle
db2
sqlserver
hive
odps

支持的函数列表：

Name Description
sql_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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型 说明
VARCHAR sqlText的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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型 说明
VARCHAR sqlText的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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型 说明
VARCHAR sqlText的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)

参数说明：

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

返回值:

类型 说明
VARCHAR sqlText的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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型 说明
VARCHAR sqlText的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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。true时，遇到非法SQL抛出异常；false时，遇到非法SQL不抛出异常，返回null

返回值:

类型 说明
VARCHAR sqlText的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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。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_commentFROM part, supplier, partsupp, nation, regionWHERE 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_partkeyLIMIT 100; |+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2.8 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

返回值:

类型 说明
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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。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_commentFROM part, supplier, partsupp, nation, regionWHERE 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_partkeyLIMIT ?; |+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

2.11 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

返回值:

类型 说明
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)

参数说明：

参数名 类型 说明
sqlText VARCHAR 必选
dbType VARCHAR 可选。SQL方言，默认为mysql
throwError BOOLEAN 可选。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 |+------------------------------+

转载地址：http://uintl.baihongyu.com/

你可能感兴趣的文章
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-02-05 22:58:56   当前IP: 18.117.251.240   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我