不同类型数据库的函数语法或多或少有些差异，要想让功能相同的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 脚本》。