区分Dynamic SQL和 Unified SQL

数据服务内的 脚本API 和 原生API 里的 SQL 语法都是 Unified SQL, Unified SQL 是一个 SQL 语法，用于转化成不同的引擎SQL执行。Dynamic SQL 是一个 SQL 模板语言，用于提供参数化的方式生成特定的SQL, 这里的 特定的SQL 就是指 Unified SQL。
一句话说就是: 我们在Dynamic SQL 中指定一些参数, Dynamic SQL 根据参数的不同生成不同的 Unified SQL 用于执行。

概述

如果你有在程序中直接拼接 SQL 调用数据库的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号, 需要注意去掉第一个where条件前的AND和OR。利用 动态 SQL 这一特性可以彻底摆脱这种痛苦。
动态 SQL 借鉴了MyBatis-3的元素设计，并在Golang上进行实现，使其运行的很快，且在Mybatis-3的方案上添加了Function元素，用于支持更强大的功能，比如递归解析一些嵌套结构。
Dynamic Sql 支持以下类型的元素

• if
• choose (when, otherwise)
• trim (where)
• foreach
• bind
• function

动态SQL用于提供强大的 SQL 生成功能。根据SQL模板和参数，生成最终的SQL语句。
SQL模板提供常见的参数替换功能，同时还提供强大的逻辑控制能力。
动态SQL的格式是XML,类似于:

Perl
<select>
    select a from b where c = #{c_value}....

如果你的动态SQL比较简单，XML内部只有一个元素，可以使用简写方式，去掉XML元素, 如下:

select a from b where c = #{c_value}....

注意: 在XML格式的dynamic sql中如果包含 > 符号或 < 符号, 在有的情况下会导致XML解析失败，此时需要对xml格式的 dynamic sql 中使用的 > 符号和 < 符号进行转义:(xml解析不一定会失败，取决于XML中的> 和< 符号是否会引起解析XML歧义，保险起见就进行转义)

Shell
<  => &lt;  如 a <= 10 改成 a &lt;= 10
>  => &gt;

常见例子

假设 dynamic sql 的参数(env)环境为下面的 JSON 数据

{
    "table_name": "table_test",
    "int_value": 10,
    "string_value": "10",
    "array_value": [10, 5],
    "dims": ["field_a", "field_b"],
    "other": {
        "array_value": ["a", "b"],
        "string_value":null
    },
    "keyword": "新中国",
    "prefix": "pp",
    "suffix": "ss"，
    "pageSize": 100,
    "pageCount": 3

实例一 基础用法: 使用整数，字符串和数组

select * from ${table_name} where a = #{int_value} and b = #{string_value} and c in #{array_value} and d in #{other.array_value}

这个实例会生成下列的 SQL:

select * from table_test where a = 10 and b = '10' and c in (10,5) and d in ('a','b')

#{} 运算符会根据数据类型自动生成 SQL 片段，而 ${} 运算符 直接将对应的数据插入到 SQL 中。
注意: 应当尽量使用#{}符号，这可以有效避免 SQL 注入问题
#{} 会根据提供的参数类型来决定如何生成最终的SQL语句。例如 date=#{date}表达式，如果Env中date参数为数值类型，比如20190714，则最终生成的SQL语句为date=20190714。如果date为字符串类型，则最终生成的SQL语句为date='20190714'。如果dynamic-sql语句为 date in #{date}, 且date为数组，?比如["20190714"," 20190715"],则最终生成的SQL语句为 date in ('20190714', '20190715')
实例二 基础用法: 使用表达式计算，例如利用计算表达式实现分页功能。

select a, b from ${table_name} limit #{pageSize*(pageCount-1)}, #{pageSize} 

这个实例实现了一个分页功能，pageSize表示页大小，pageCount表示第几页。
这会生成下列的SQL:

select a, b from table_test limit 200,100

实例三 进阶: 使用内置函数

select ${Join(dims, ', ')} from table_test where a like #{Format('%v', keyword)} and d = #{Format('%v_%v', prefix, suffix)} group by ${Join(dims, ', ')}

这个实例会生成下列的 SQL:

select field_a, field_b from table_test where a like '新中国' and d = 'pp_ss' group by field_a, field_b

实例中使用到了 Join(array, delimiter) 函数和 Format(format, args) 函数。
Join 函数将数组使用 delimiter 拼接起来。
Format 函数将参数进行格式化。 注意，Format函数的format参数使用的是GOLANG的格式化方式，如果字符串中需要用到符号 % ,需要使用 %% 进行转义， 如

select * from table_test where keyword like #{Format('%%%v%%', keyword)} 
==> 生成的 SQL 是 select * from table_test where keyword like '%新中国%'

实例四 进阶: 使用XML格式的dynamic sql

Lua
<select>
     select field_a, field_b from table_test 
        <where> 
            <if test = 'other.string_value != nil'>
                and a like '%${other.string_value}%'
            </if>
            <if test = 'int_value != nil and string_value != nil'>
                and b = #{int_value} and c = #{string_value}
            </if>
        </where>
    group by field_a, field_b   

上面的实例会生成下列的SQL

select field_a, field_b from table_test  WHERE b = 10 and c = '10' group by field_a, field_b

if 标签会判断 test 里的语句是否成立，如果成立会将对应的 SQL 拼接到最终的 SQL 中。
where 标签会在其子标签返回的SQL子句前添加 WHERE 子句，并会去掉 多余的 and/or 连接词。 并且如果 where 标签的所有子标签都没有返回 SQL 子句，那 where 标签 也不会拼接 WHERE SQL子句。
可以使用支持 XML 高亮插件的编辑器编辑 XML，或者使用在线的XML fomatter检查语法