文章目录
(一)继续动数据库(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 CASCADE
MySQL:DROP TABLE TName CASCADE
(2.4)序列
不建议通过其它方式让MySQL实现类似序列功能,因为目标数据库可能存在权限问题。
由于序列是先取出值再使用,而自增字段是Insert后再取得,所以涉及到部分代码逻辑不同。
(2.4.1)创建
Oracle: 使用序列对象CREATE SEQUENCE SEQXXX START WITH 1000 MAXVALUE 99999999 MINVALUE 1000
PostgreSQL: 使用序列对象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 dual
PostgreSQL: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 dual
PostgreSQL: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 dual
PostgreSQL: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 T2
PostgreSQL:select * from T1 EXCEPT select * from T2
MySQL: 没有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.a
PostgreSQL:select X from T1 left join T2 on T1.a=T2.a
MySQL: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$version
PostgreSQL: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_encoding
MySQL:show variables like '%character_set%'
😴 ……暂时的文章末尾……✌️
如果觉得《业务系统兼容数据库Oracle/PostgreSQL(openGauss)/MySQL的琐事》对你有帮助,请点赞、收藏,并留下你的观点哦!