怎样写出可在各种数据库间移植的 SQL 语句

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

爱意满满的作品展示区。

这是一个创建于 379 天前的主题，其中的信息可能已经有所发展或是发生改变。

任意的 SQL 语句都可移植是做不到的。因为各种数据库的功能并不一样，某些数据库的语法在另一种数据库中根本就没有对应的实现机制，这时当然就不可能移植了。

如果我们不用数据库的特殊功能，只在国际标准的范围内，也就是只使用所有数据库的功能交集，其实也相当丰富了，这总该能移植了吧。

基本的情况确实是可以，比如

select * from employee where age>50
select area,sum(amount) from orders group by area

在所有数据库中都能正常执行。

但是，还有很多 SQL 函数在各种数据库中的写法不一样，特别是字符串和日期相关的，比如

MySQL: year( x )
Oracle: extract( year from x )

国际标准没有对这些函数做规定，这种 SQL 语句就会和数据库相关而无法移植了，而这些函数在应用开发中还特别常见。

Hibernate 能将包括这些函数的标准 HQL 语句转换成不同数据库的 SQL ，但 HQL 支持的函数太少，碰到不认识的函数还是要分别注册，这就丧失了可移植性；而且 HQL 能生成的 SQL 本身也比较简单，覆盖面太窄了。

转换 SQL 语句中的不同的函数，esProc SPL 是个更好的解决方案。

esProc SPL 是个 Java 写的开源软件，在这里 https://github.com/SPLWare/esProc 。

SPL 中有个 sqltranslate 函数，能够使用了某些“标准”的函数语法书写的 SQL 翻译成各种数据库的函数。比如：

sql = "select * from Orders where year(OrderDate)=2000"
sql.sqltranslate("MySQL") -> "select * from Orders where year(OrderDate)=2000"
sql.sqltranslate("Oracle") -> "select * from Orders where extract( year from OrderDate )=2000"

sqltranslate 中已经预定义了很多数据库中很多函数的写法，比如：

sql = "select * from Orders where ADDDAYS(OrderDate,3)>ShipDate"
sql.sqltranslate("MySQL") -> "select * from Orders where (OrderDate + INTERVAL 3 DAY)>ShipDate"
sql.sqltranslate("Oracle") -> "select * from Orders where (OrderDate + NUMTODSINTERVAL(3, "DAY")>ShipDate"
sql.sqltranslate("DB2") -> "select * from Orders where (OrderDate + 3 DAYS )>ShipDate"

如果碰到了新的函数或新的数据库，esProc 还允许程序员自己在配置表中增加。

esProc 提供了 JDBC 接口，很容易被集成进 Java 应用以实现 SQL 移植。这里有完整的使用方法：SPL：跨数据库移植 SQL。

esProc 当然并不是仅仅为了移植 SQL 而设计的，SPL 是功能强大的结构数据处理语言，转换 SQL 只是 SPL 顺便实现的一点点小功能而已，SPL 更多的作用在于替代 SQL 实现复杂的查询运算。

当计算需求复杂时我们会用到某些数据库特有的语法或函数。比如 Oracle 有个 KEEP 函数可以方便地计算每组的第一条/最后一条记录，其它很多数据库都没有。如果 SQL 语句中用到了这个函数，就不能再用上面的简单办法移植到其它数据库上了。这种情况下，我们可以只用基本的 SQL 读取数据，而把更复杂的运算交给 SPL 来做，SPL 是完全不依赖于数据库的，这样写出来的代码就可以继续拥有可移植性了。

select Area, max(Amount) KEEP( dens_rank first order by extract( month from OrderDate) )
from Orders where extract( year from OrderDate)=2000 group by Area

这条 Oracle 的 SQL 语句可以用 SPL 改成这样：

	A	B
1	'select OrderDate, Area, Amount from Orders where year(OrderDate)=2000	/读数 SQL
2	=A1.sqltranslate("Oracle")	/转成目标数据库的 SQL
3	=db.query@x(A2)	/读出数据
4	=A3.group(Area;~.minp@a(month(OrderDate)).max(Amount))	/用 SPL 计算目标结果

（ SPL 代码写在格子里，这和普通程序语言很不像，参考这里：写在格子里的程序语言）

SPL 拥有所有 SQL 的运算能力（过滤、分组、连接等），并且都是自行实现的（不是翻译成 SQL ），不依赖于任何数据库，保证这个代码可以在任何数据库上正确执行，就可以移植了。

事实上，SPL 提供的运算能力远比 SQL 更强大丰富（所以也不可能翻译成 SQL 执行）。有些即使可以用 SQL 写出来的复杂运算，用 SPL 也会更简单。比如我们经常举例的任务：计算一支股票最长连续上涨的天数，SQL 要写成多层嵌套，冗长且难懂：

    select max(ContinuousDays) from (
        select count(*) ContinuousDays from (
            select sum(UpDownTag) over (order by TradeDate) NoRisingDays from (
                select TradeDate,case when Price>lag(price) over ( order by TradeDate) then 0 else 1 end UpDownTag from Stock ))
        group by NoRisingDays )

SPL 提供了更多的基础函数，同样的计算逻辑，写起来要简单得多：

    Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())

这样的代码，是不是既开发简单又容易移植了？

SPL 资料

1 条回复 • 2023-12-14 10:00:01 +08:00

beetlerx

2023-12-14 10:00:01 +08:00

orm 不就是用来干这事的