全部产品
云市场

SQL分析函数

更新时间:2020-01-10 10:03:11

SQL_EXPORT_COLUMNS

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_export_columns(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    41. | sql_export_columns(a.sql_text) |
    42. +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    43. | 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

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR。

  • 示例:

    1. SELECT sql_export_functions(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +----------------------------------+
    41. | sql_export_functions(a.sql_text) |
    42. +----------------------------------+
    43. | min |

SQL_EXPORT_PREDICATE_COLUMNS

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_export_predicate_columns(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    41. | sql_export_predicate_columns(a.sql_text) |
    42. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    43. | 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

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_export_predicates(a.sql_text, 'mysql', true)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    41. | sql_export_predicates(a.sql_text) |
    42. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    43. | [["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

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_export_select_list_columns(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +------------------------------------------------------------------------------------------------------------------------------------------------------------+
    41. | sql_export_select_list_columns(a.sql_text) |
    42. +------------------------------------------------------------------------------------------------------------------------------------------------------------+
    43. | 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

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_export_tables(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. | sql_export_tables(a.sql_text) |
    41. +--------------------------------------+
    42. | part,supplier,partsupp,nation,region |

SQL_FORMAT

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_format(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    41. | sql_format(a.sql_text) |
    42. +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    43. | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
    44. , s_address, s_phone, s_comment
    45. FROM part, supplier, partsupp, nation, region
    46. WHERE p_partkey = ps_partkey
    47. AND s_suppkey = ps_suppkey
    48. AND p_size = 35
    49. AND p_type LIKE '%NICKEL'
    50. AND s_nationkey = n_nationkey
    51. AND n_regionkey = r_regionkey
    52. AND r_name = 'MIDDLE EAST'
    53. AND ps_supplycost IN (
    54. SELECT min(ps_supplycost)
    55. FROM partsupp, supplier, nation, region
    56. WHERE s_suppkey = ps_suppkey
    57. AND s_nationkey = n_nationkey
    58. AND n_regionkey = r_regionkey
    59. AND r_name = 'MIDDLE EAST'
    60. )
    61. ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
    62. LIMIT 100; |

SQL_PARAMS

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR

  • 示例:

    1. SELECT sql_params(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +------------------------------------------------+
    41. | sql_params(a.sql_text) |
    42. +------------------------------------------------+
    43. | [35,"%NICKEL","MIDDLE EAST","MIDDLE EAST",100] |

SQL_PATTERN

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR。

  • 示例:

    1. SELECT sql_pattern(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    41. | sql_pattern(a.sql_text) |
    42. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    43. | SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr
    44. , s_address, s_phone, s_comment
    45. FROM part, supplier, partsupp, nation, region
    46. WHERE p_partkey = ps_partkey
    47. AND s_suppkey = ps_suppkey
    48. AND p_size = ?
    49. AND p_type LIKE ?
    50. AND s_nationkey = n_nationkey
    51. AND n_regionkey = r_regionkey
    52. AND r_name = ?
    53. AND ps_supplycost IN (
    54. SELECT min(ps_supplycost)
    55. FROM partsupp, supplier, nation, region
    56. WHERE s_suppkey = ps_suppkey
    57. AND s_nationkey = n_nationkey
    58. AND n_regionkey = r_regionkey
    59. AND r_name = ?
    60. )
    61. ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
    62. LIMIT ?; |

SQL_PATTERN_HASH

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:VARCHAR。

  • 示例:

    1. SELECT sql_pattern_hash(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +------------------------------+
    41. | sql_pattern_hash(a.sql_text) |
    42. +------------------------------+
    43. | 925870115679910184 |

SQL_SYNTAX_CHECK

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

  • 参数说明:

    • sqlText:VARCHAR,必选参数。

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

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

  • 返回值类型:BOOLEAN。

  • 示例:

    1. SELECT sql_syntax_check(a.sql_text)
    2. FROM (
    3. SELECT 'SELECT s_acctbal,
    4. s_name,
    5. n_name,
    6. p_partkey,
    7. p_mfgr,
    8. s_address,
    9. s_phone,
    10. s_comment
    11. FROM part,
    12. supplier,
    13. partsupp,
    14. nation,
    15. region
    16. WHERE p_partkey = ps_partkey
    17. AND s_suppkey = ps_suppkey
    18. AND p_size = 35
    19. AND p_type LIKE ''%NICKEL''
    20. AND s_nationkey = n_nationkey
    21. AND n_regionkey = r_regionkey
    22. AND r_name = ''MIDDLE EAST''
    23. AND ps_supplycost IN
    24. (
    25. SELECT min(ps_supplycost)
    26. FROM partsupp,
    27. supplier,
    28. nation,
    29. region
    30. WHERE s_suppkey = ps_suppkey
    31. AND s_nationkey = n_nationkey
    32. AND n_regionkey = r_regionkey
    33. AND r_name = ''MIDDLE EAST'' )
    34. ORDER BY s_acctbal DESC,
    35. n_name,
    36. s_name,
    37. p_partkey
    38. LIMIT 100;' AS sql_text
    39. ) a;
    40. +------------------------------+
    41. | sql_syntax_check(a.sql_text) |
    42. +------------------------------+
    43. | 1 |