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

简介: 国际标准没有对这些函数做规定,这种 SQL 语句就会和数据库相关而无法移植了,而这些函数在应用开发中还特别常见。Hibernate 能将包括这些函数的标准 HQL 语句转换成不同数据库的 SQL,但 HQL 支持的函数太少,碰到不认识的函数还是要分别注册,这就丧失了可移植性;而且 HQL 能生成的 SQL 本身也比较简单,覆盖面太窄了。

任意的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())

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

相关文章
|
6天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
42 10
|
23天前
|
SQL 存储 BI
【软件设计师备考 专题 】数据库语言(SQL)
【软件设计师备考 专题 】数据库语言(SQL)
89 0
|
1月前
|
SQL 安全 数据库
SQL-Server 数据库部署
SQL-Server 数据库部署
56 0
|
27天前
|
SQL 数据库
sql server中创建数据库和表的语法
sql server中创建数据库和表的语法
17 1
|
6天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
60 6
|
3天前
|
SQL 数据库
数据库SQL语言实战(二)
数据库SQL语言实战(二)
|
6天前
|
SQL 监控 数据库
数据库管理与电脑监控软件:SQL代码优化与实践
本文探讨了如何优化数据库管理和使用电脑监控软件以提升效率。通过SQL代码优化,如使用索引和调整查询语句,能有效提高数据库性能。同时,合理设计数据库结构,如数据表划分和规范化,也能增强管理效率。此外,利用Python脚本自动化收集系统性能数据,并实时提交至网站,可实现对电脑监控的实时性和有效性。这些方法能提升信息系统稳定性和可靠性,满足用户需求。
25 0
|
6天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
14天前
|
数据库 SQL 索引
什么是数据库 SQL Execution Plan
什么是数据库 SQL Execution Plan
10 0
|
27天前
|
SQL 数据可视化 Apache
阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移
阿里云数据库 SelectDB 内核 Doris 的 SQL 方言转换工具, Doris SQL Convertor 致力于提供高效、稳定的 SQL 迁移解决方案,满足用户多样化的业务需求。兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移。
阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言,助力业务平滑迁移