肿瘤康复网 > 业务系统兼容数据库Oracle/PostgreSQL(openGauss)/MySQL的琐事

业务系统兼容数据库Oracle/PostgreSQL(openGauss)/MySQL的琐事

时间：2019-07-18 11:47:26

相关推荐

文章目录

（一）继续动数据库（1.1）关于GoldenDB（1.2）关于openGauss兼容性（二）数据库的差异（2.1）Oracle/PostgreSQL/MySQL基本差异（2.2）建表语句等（DDL）（2.2.1）常用数据类型（2.2.2）表的注释（2.2.3）字段的注释（2.3）删表语句（2.4）序列（2.4.1）创建（2.4.2）使用（2.5）检查用户对象（2.5.1）表（2.5.2）字段（2.6）表数据导入（2.7）常用函数（2.7.1）日期转字符串（2.7.2）日期计算（2.7.3）字符串转日期（2.7.4）字符串拼接（2.7.5）子字符串函数（2.7.6）返回非空值函数（2.7.7）长度（Blob类型）（2.8）数据集操作（DML）（2.9）表的连接语法（左连接）（2.10）更新语句（特殊的）（2.11）取数据库信息（2.11.1）基本信息（版本）（2.11.1）数据库字符集（编码）

之前记了《业务系统从Oracle迁移到openGauss数据库的简单记录》：）

但事儿还没完……

（一）继续动数据库

没完没了啊……

PostgreSQL -> 华为openGauss

MySQL -> 中兴GoldenDB

……

（1.1）关于GoldenDB

👉 官网

💬成熟稳定，商用领先的金融级分布式数据库。

💬产品坚如磐石，市场商用领先，引领行业标准制定，共建产业新生态

从中兴给的资料看出，开发上是完全兼容MySQL的，连JDBC包和驱动都是MySQL。

而且貌似不开源，也不是免费软件，所以只能当它是MySQL得了。

（1.2）关于openGauss兼容性

凡是PostgreSQL的东西openGauss都兼容。

而openGauss扩展了部分语法类似Oracle的，反过来PostgreSQL无法用。

所以为了统一和简化，都使用PostgreSQL的标准。

（二）数据库的差异

（2.1）Oracle/PostgreSQL/MySQL基本差异

为了代码统一谁严谨就照着谁的做。

差异挺多的，也不知道怎么列出来更合适。

默认对象名Oracle大写而PostgreSQL/MySQL小写。均可通过加引号（连引号都不一样）强制小/大写。正常情况下别加引号。强制改变大小写的字段Oracle在SQL语句中必须也加引号指明，MySQL似乎可以无视大小写。对于字段类型不同的Oracle可以直接比较CharField1 = NumField2（似乎openGauss也是），而PostgreSQL/MySQL需数据类型一致。程序中绑定值也和SQL中一样，Oracle可以混着String和Int用，而PostgreSQL/MySQL需数据类型一致。很多语句都稍有不同，就不逐一列出了，比如Oracle的CONSTRAINT这个词，哪儿都有其它数据库不认……

（2.2）建表语句等（DDL）

主要差异是数据类型，比如varchar有没有2，number，numberic，decimal……

这部分比较简单，修改一下语句就可以了，或者用Navicat直接Data Transfer。

PS：发现Navicat跨数据库同步表结构和数据很方便啊（但不想改原来文章），可以先工具互导表结构，然后再从对应的库中导出DDL语句，避免了自己一个个改，yeah！

（2.2.1）常用数据类型

Oracle:VARCHAR2，NUMBER，DATE，……PostgreSQL:varchar，numeric，timestamp，……MySQL:varchar，numeric，datetime，……

（2.2.2）表的注释

写法不同，但差异很小：

Oracle: 先Create Table TName (Field1,Field2,...)，再COMMENT ON Table TName IS '注释'PostgreSQL: 先Create Table TName (Field1,Field2,...)，再COMMENT ON Table TName IS '注释'，同上MySQL: 先Create Table TName (Field1,Field2,...)，再ALTER Table TName COMMENT = '注释'

（2.2.3）字段的注释

字段注释就不一样了，涉及到部分代码逻辑：

Oracle: 先Create Table TName (Field1,Field2,...)

再循环注释每个字段：COMMENT ON COLUMN TName.Field1 IS '注释'PostgreSQL: 先Create Table TName (Field1,Field2,...)

再循环注释每个字段：COMMENT ON COLUMN TName.Field1 IS '注释'，同上MySQL: 直接Create Table TName (Field1 COMMENT '注释1‘,Field2 COMMENT '注释2‘,...)

其实也可以先建表再注释字段，但是用的是Alter Table语句，而且稍微复杂（啥都要写全），所以代码里面一次注释完较为简单。

（2.3）删表语句

正常情况下，需要CASCADE删除表关联的其它对象（索引，约束）。

对于Oracle来说我们的数据表太大了且无需闪回，所以加PURGE。

而MySQL的drop … if exists虽然很有意思，但尽量统一还是不用了。

Oracle:DROP TABLE TName CASCADE CONSTRAINTS PURGE'PostgreSQL:DROP TABLE TName CASCADEMySQL:DROP TABLE TName CASCADE

（2.4）序列

不建议通过其它方式让MySQL实现类似序列功能，因为目标数据库可能存在权限问题。

由于序列是先取出值再使用，而自增字段是Insert后再取得，所以涉及到部分代码逻辑不同。

（2.4.1）创建

Oracle: 使用序列对象CREATE SEQUENCE SEQXXX START WITH 1000 MAXVALUE 99999999 MINVALUE 1000PostgreSQL: 使用序列对象CREATE SEQUENCE SEQXXX START WITH 1000 MAXVALUE 99999999 MINVALUE 1000，基本同上MySQL: 设置表字段自增Create Table TName (Field1 int NOT NULL AUTO_INCREMENT ,Field2,...)，

（2.4.2）使用

Oracle:Select SEQXXX.nextval from dualPostgreSQL:select nextval('SEQXXX')MySQL: 先Insert into TName记录，自增字段值赋0或Null，再select last_insert_id()选出自增后的值。

需要注意：插入语句和选出自增值语句需要在同一个连接中进行。

即便是同一个连接中，由于开发语言/驱动不同，有时候select last_insert_id()也会选出0值来（嗯遇到了😄）。

（2.5）检查用户对象

（2.5.1）表

由于数据库差异，这里的表述不太准确，按实际情况改吧。

到底是选(User/Owner)用户的表，还是(Schema)方案的表？

Oracle:select TABLE_NAME from user_tables where TABLE_NAME like ?

——用户就是方案PostgreSQL:select tablename from pg_tables where schemaname = ? and upper(tablename) like ?

——用户拥有方案，也可以和MySQL一样，如下：MySQL:select TABLE_NAME from information_schema.tables where TABLE_SCHEMA=? and upper(TABLE_NAME) like ?

—— 用户和库（方案）

（2.5.2）字段

Oracle:select * from USER_TAB_COLUMNS where TABLE_NAME = ? and COLUMN_NAME = ?PostgreSQL:select * from information_schema.columns where TABLE_SCHEMA= ? and upper(TABLE_NAME) = ? and upper(COLUMN_NAME) = ?，呃没有pg_columns。MySQL:select * from information_schema.columns where TABLE_SCHEMA= ? and upper(TABLE_NAME) = ? and upper(COLUMN_NAME) = ?，同上。

（2.6）表数据导入

除了insert以外，大量数据导入数据库都有自己的方法。

我这里这个地方openGauss表现得很奇怪，也太慢了吧。

Oracle: 使用SQL Loader，也就是sqlldr...命令，需要客户端下有这个命令文件和相关的信息文件等，这里略过。PostgreSQL: Java使用org.postgresql.copy.CopyManager，COPY ... FROM STDIN...，其它语言没来得及研究。MySQL: 执行SQL语句：LOAD DATA LOCAL INFILE ... INTO TABLE ...，需要客户端和服务端都允许这种Load文件的方式（具体安全风险自行了解）。

否则报错：Loading local data is disabled; this must be enabled on both the client and server sides

需要服务端设置（Windows下是ini）：my.ini文件[mysqld]下加入local-infile=1

//客户端设置：Property.setProperty("allowLoadLocalInfile","true");Connection conn = DriverManager.getConnection(jdbcUrl, Property);......

对比了一下：

（2.7）常用函数

（2.7.1）日期转字符串

Oracle:To_Char(DATE,'YYYY-MM-DD')PostgreSQL:To_Char(DATE,'YYYY-MM-DD')，同上MySQL:date_format(DATE,'%Y-%m-%d')

不建议通过其它方式让MySQL实现类似To_Char功能，因为目标数据库可能存在权限问题。

附：不建议的MySQL自定义函数：

CREATE FUNCTION to_char(d datetime, format varchar(40)) RETURNS varchar(40) DETERMINISTICbegindeclare str varchar(40) DEFAULT '';set str = replace(format, 'YYYY', '%Y');set str = replace(str, 'yyyy', '%Y');set str = replace(str, 'MM', '%m');set str = replace(str, 'mm', '%m');set str = replace(str, 'DD', '%d');set str = replace(str, 'dd', '%d');set str = replace(str, 'HH24', '%H');set str = replace(str, 'hh24', '%H');set str = replace(str, 'HH', '%h');set str = replace(str, 'hh', '%h');set str = replace(str, 'MI', '%i');set str = replace(str, 'mi', '%i');set str = replace(str, 'SS', '%s');set str = replace(str, 'ss', '%s');return date_format(d, str);end

（2.7.2）日期计算

加减月份为例：

Oracle:select add_months(sysdate,xxx) from dualPostgreSQL:select now()::timestamp + 'yyy month'MySQL:select date_add(now(),interval zzz month)

（2.7.3）字符串转日期

注意date和timestamp在Oracle中的区别。

Oracle:select to_date('-06-01 23:45:59','yyyy-mm-dd hh24:mi:ss') from dualPostgreSQL:select to_timestamp('-06-01 23:45:59','yyyy-mm-dd hh24:mi:ss')MySQL:select str_to_date('-06-01 23:45:59','%Y-%m-%d %T')

（2.7.4）字符串拼接

Oracle:Field1 || '_' || Field2 || '_' ||...，也可以用CONCAT()PostgreSQL:Field1 || '_' || Field2 || '_' ||...，也可以用CONCAT()，同上MySQL:CONCAT(Field1,'_',Field2,'_',...)

（2.7.5）子字符串函数

❗️❗️❗️ ⚠️大坑❗️❗️❗️

大家都支持substr，问题是substr是从1开始算Index，但Oracle从0从1都一样。

所以如果原本写了类似substr(field1,0,x)这种语句……

那么Oracle下没事，换其它数据库则取不到对应的结果（错得还各不相同）。

Oracle:substr(name,0,2)=substr(name,1,2)：取得2个字符。PostgreSQL:substr(name,0,2)=substr(name,1,1)：取得1个字符。MySQL:substr(name,0,2)：取得0个字符（取不到）。

（2.7.6）返回非空值函数

同样不建议自定义函数实现nvl名称（宁愿在程序的SQL中加变量）。

Oracle:nvl(field,-1)PostgreSQL:coalesce(field,-1)MySQL:ifnull(field,-1)

（2.7.7）长度（Blob类型）

Oracle:Length(Blobfield)PostgreSQL:pg_column_size(Blobfield)MySQL:Length(Blobfield)

（2.8）数据集操作（DML）

好在【增删改】是一样的，毕竟都是SQL，集合操作嘛：

首先union（过滤重复）, union all（不过滤重复）在不同数据库中也是一样的意思，但是……

Oracle:select * from T1 MINUS select * from T2PostgreSQL:select * from T1 EXCEPT select * from T2MySQL: 没有MINUS或EXCEPT，用其它SQL代替（什么是其它？呃：join, not in）。

如果用not in 则一定要小心数据量引起的效率问题（可能本来1秒钟的操作变成几十分钟）。

（2.9）表的连接语法（左连接）

用了Oracle的(+)方式的都需要改成left join，哎……

如果一开始就不要用(+)的Oracle语法就省事儿了。

至于inner join和where的区别，自行了解吧。结果是一样的，中间临时集大小不同而已。

Oracle:select x from T1,T2 where T1.a=T2.a(+)等价select X from T1 left join T2 on T1.a=T2.aPostgreSQL:select X from T1 left join T2 on T1.a=T2.aMySQL:select X from T1 left join T2 on T1.a=T2.a

（2.10）更新语句（特殊的）

类似下面这样的SQL（简化过的，所以看起来完全没意义）：

update tablenameset name = 'XXXXXX'where id=0and id IN(select id from tablename);

Oracle: 执行成功✔️PostgreSQL: 执行成功✔️MySQL: 执行失败✖️You can't specify target table 'XXXXX' for update in FROM clause

需要再嵌套一层，就能执行：

update tablenameset name = 'XXXXXX'where id=0and id IN(select id from(select id from tablename) tn);

（2.11）取数据库信息

（2.11.1）基本信息（版本）

Oracle:select * from v$versionPostgreSQL:select version()MySQL:select version()+show variables like '%storage_engine%'

（2.11.1）数据库字符集（编码）

Oracle:select value from nls_database_parameters where Parameter='NLS_CHARACTERSET'PostgreSQL:show server_encodingMySQL:show variables like '%character_set%'

😴 ……暂时的文章末尾……✌️

如果觉得《业务系统兼容数据库Oracle/PostgreSQL(openGauss)/MySQL的琐事》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。