可以实现 SQL 移植的技术

不同类型数据库的函数语法或多或少有些差异，要想让功能相同的SQL查询语句在不同类型的数据库中都能被顺利执行，就要把各数据库都有的那些差异化函数语法进行翻译，即SQL移植。本文将探讨几种SQL移植的技术并加以对比。

ORM技术

使用ORM技术能够将程序员书写的查询转换成不同数据库的SQL，相当于有一定的移植能力。但 ORM 技术只适合应对 OLTP 场景下的简单 SQL，难以实现 OLAP 场景下较复杂 SQL 的移植。

Hibernate和Mybatis是两种较常见的ORM技术。Mybatis被称为半自动ORM，很主要的一个原因是需要程序员自己编写原生态SQL，也就几乎没有可移植性，即使借助Mybatis-plus、Mybatis-ext这样的第三方增强扩展包来改善输入方式，其可移植性也远比不上Hibernate；Hibernate则可以使用HQL根据配置文件中的数据库方言自动生成对应的SQL，对于一些简单的情况（Hibernate中直接支持），具有一定的可移植性，例如：

HQL：

SELECT
            client,
            YEAR(orderDate),
            sum(amount),
            count(orderId)
FROM
            OrdersEntity
GROUP BY
            client,
            YEAR(orderDate)
HAVING
            sum(amount)>2000

MySQL：

SELECT
            ordersenti0_.Client AS col_0_0_,
            YEAR(ordersenti0_.OrderDate) AS col_1_0_,
            sum(ordersenti0_.Amount) AS col_2_0_,
            count(ordersenti0_.OrderID) AS col_3_0_
FROM
            orders ordersenti0_
GROUP BY
            ordersenti0_.Client,
            YEAR(ordersenti0_.OrderDate)
HAVING
            sum(ordersenti0_.Amount)>2000

Oracle：

SELECT
            ordersenti0_.Client AS col_0_0_,
            EXTRACT(YEAR FROM ordersenti0_.OrderDate) AS col_1_0_,
            sum(ordersenti0_.Amount) AS col_2_0_,
            count(ordersenti0_.OrderID) AS col_3_0_
FROM
            system.orders ordersenti0_
GROUP BY
            ordersenti0_.Client,
            EXTRACT(YEAR FROM ordersenti0_.OrderDate)
HAVING
            sum(ordersenti0_.Amount)>2000

HQL生成的SQL中，对应MySQL时用了YEAR(d)函数，对应Oracle时用了EXTRACT(YEAR FROM d)函数。可以实现SQL的移植效果。

但是更复杂一些的情况，Hibernate也会失去可移植性。

例如使用HQL本身不直接支持的函数时，需要先根据当前使用的数据库注册自定义函数后再用HQL生成SQL，以MySQL为例：

注册自定义函数关键代码：

registerFunction("udf_dateadd", new SQLFunctionTemplate(DateType.INSTANCE,"date_add(?1,INTERVAL ?2 DAY)"));

HQL：

SELECT
            udf_dateadd (orderDate,3)
FROM
            OrdersEntity

MySQL：

SELECT
            date_add(ordersenti0_.OrderDate,INTERVAL 3 DAY) AS col_0_0_
FROM
            orders ordersenti0_

但Oracle没有date_add函数，如果数据库变为Oracle，又需要再根据Oracle的函数语法，另外再注册新的自定义函数来使用，这就无法自动移植了。

还有些HQL无法描述的运算，例如不支持FROM中的子查询：

SELECT
            orderId,
            m
FROM
            (
            SELECT
                        orderId,
                        MONTH(orderDate) m
            FROM
                        OrdersEntity) t1

这种情况通常会使用SQLQuery接口来解决，但这时执行的是原生SQL了，也失去移植性。

SQL转换工具

借助某些工具移植SQL，这种办法的优点是，可以直接将现有的源SQL（如Oracle SQL）翻译成目标SQL（如MySQL SQL），即使复杂情况下，如嵌套子查询，也可以完成转换。

例如在https://www.sqlines.com/online中：

源SQL的数据库类型选择MySQL，需要被翻译的SQL为：

SELECT
            O_YEAR ,
            SUM(CASE WHEN NATION = 'CHINA' THEN VOLUME ELSE 0 END) / SUM(VOLUME) ASMKT_SHARE
FROM
            (
            SELECT
                        YEAR (O_ORDERDATE) AS O_YEAR,
                        L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
                        N2.N_NAME AS NATION
            FROM
                        PART,
                        SUPPLIER,
                        …

目标SQL的数据库类型选择Oracle，点击“转换”按钮，即可将源SQL转为目标数据库可以执行的SQL：

SELECT
            O_YEAR ,
            SUM(CASE WHEN NATION = 'CHINA' THEN VOLUME ELSE 0 END) / SUM(VOLUME) ASMKT_SHARE
FROM
            (
            SELECT
                        EXTRACT(YEAR FROM O_ORDERDATE) AS O_YEAR,
                        L_EXTENDEDPRICE * (1 - L_DISCOUNT) AS VOLUME,
                        N2.N_NAME AS NATION
            FROM
                        PART,
                        SUPPLIER,
                        …

这种办法的缺点是，这类工具多为独立的小程序，仅支持在命令行或结果文件中输出目标SQL，缺少程序接口以便被各类开发工具集成。

集算器 SPL

SPL设计了一套标准的SQL查询语法，该语法内置大量函数（还在持续追加中），可描述更多常用运算。SPL中有一个sqltranslate函数，可以把这种标准SQL翻译成不同数据库的SQL，实现数据库的迁移。

比如这样的标准SQL：

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)
            , COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, YEAR(ORDERDATE)
HAVING SUM(AMOUNT) > 2000

用.sqltranlate("MYSQL")翻译后就得到：

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)
            , COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, YEAR(ORDERDATE)
HAVING SUM(AMOUNT) > 2000

而如果使用.sqltranslate("ORACLE")将返回：

SELECT CLIENT, EXTRACT(YEAR FROM ORDERDATE), SUM(AMOUNT)
            , COUNT(ORDERID)
FROM ORDERS
GROUP BY CLIENT, EXTRACT(YEAR FROM ORDERDATE)
HAVING SUM(AMOUNT) > 2000

可以看到，标准函数能够正确地根据数据库选择相应的函数。

此外，对于上文中提及的HQL本身不支持的：INTERVAL n DAY，SPL也可以处理：

标准SQL：

SELECT
            ADDDAYS(ORDERDATE,3)
FROM
            ORDERS

翻译成MySQL SQL：

SELECT
            ORDERDATE + INTERVAL 3 DAY
FROM
            ORDERS

翻译成Oracle SQL：

SELECT
            ORDERDATE + NUMTODSINTERVAL(3,'DAY')
FROM
            ORDERS

SPL实现SQL移植，采取的策略是只对标准SQL中的函数进行翻译，不翻译（原样照抄）语句，从而使标准SQL可描述更多的运算。比如，对于下面的子查询无论翻译成哪种数据库SQL都不会变，也都可以正常执行。

SELECT
            ORDERID,
            M
FROM
            (
            SELECT
                        ORDERID,
                        MONTH(ORDERDATE) M
            FROM
                        ORDERS) T1

SPL很容易被JAVA 集成而使这个移植功能在应用程序中使用，进一步信息可参考：《Java 如何调用 SPL 脚本》。