Oracle与MySQL迁移语法兼容
最近项目上使用Oracle的系统,需要兼容MySQL,原本使用到的SQL语法需要修改以兼容到MySQL。先记录下目前遇到的问题,后续会继续更新。
1,Oracle NVL()
这个函数用的比较多,功能就是从两个表达式返回一个非 null 值,
MySQL:IFNULL(expr1,expr2),也有相同功能
如果第一个参数的表达式 expression 为 NULL,则返回第二个参数的备用值。
2,Oracle 中日期格式化
Oracle中常用to_char(),to_date()这两个函数,MySQL中有对应的函数
date_format(date,'%Y-%m-%d') -------------->oracle中的to_char();
str_to_date(date,'%Y-%m-%d') -------------->oracle中的to_date();
另外日期格式的表达式也不相同
%Y:代表4位的年份
%y:代表2为的年份
%m:代表月, 格式为(01……12)
%c:代表月, 格式为(1……12)
%d:代表月份中的天数,格式为(00……31)
%e:代表月份中的天数, 格式为(0……31)
%H:代表小时,格式为(00……23)
%k:代表小时,格式为(0……23)
%h:代表小时,格式为(01……12)
%I: 代表小时,格式为(01……12)
%l:代表小时,格式为(1……12)
%i: 代表分钟, 格式为(00……59)
%r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T:代表 时间,格式为24 小时(hh:mm:ss)
%s:代表 秒,格式为(00……59)
3,Oracle 日期时间的计算,后一天,上个月之类的
MySQL中计算,正数表示往后,负数往前计算
SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 DAY);
-> '2013-01-30 13:50:27'
SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 HOUR);
-> '2013-01-29 14:50:27'
SELECT DATE_ADD('2013-01-29 13:50:27', INTERVAL 1 MONTH);
-> '2013-02-28 13:50:27'
4,Oracle字符串拼接
Oracle中常用 ‘||’进行字段拼接,而在MySQL中用concat()拼接,返回结果为连接参数产生的字符串。
5,Oracle 的decode
在Oracle中使用decode方法可以轻松实现代码和值之间的转换,MySQL中没有直接的方法可以使用,但是我们可以通过下面两种方法来实现:
1.case when then
如:Select title,case Emergency when 1 then '紧急' else '普通' End as emergency from already_sig
2.if
如:select a.title,if(a.Emergency=1,'紧急','普通')emergency from already_sign a
6,Oracle With as
WITH AS短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个SQL片断,该SQL片断会被整个SQL语句所用到。有的时候,是为了让SQL语句的可读性更高些,也有可能是在UNION ALL的不同部分,作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用WITH AS短语,则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上,则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里,如果只是被调用一次,则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。
MySQL的做法就是把查询结果放到一个临时表
CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name;
临时表中就储存了临时数据。当断开连接时,临时表自动删除。
临时表使用有一些限制条件:
1,临时表在 memory、myisam、merge或者innodb上使用,并且不支持mysql cluster簇);
show tables语句不会列出临时表,在information_schema中也不存在临时表信息;show create table可以查看临时表;
2,不能使用rename来重命名临时表。但是可以alter table rename代替:
mysql>ALTER TABLE orig_name RENAME new_name;
3,可以复制临时表得到一个新的临时表,如:
mysql>create temporary table new_table select * from old_table;
4,同一个query语句中,相同的临时表只能出现一次。但不同的临时表可以出现在同一个query语句中,如临时表temp_tb1, temp_tb2
可以使用:mysql> select * from temp_tb;
但不能使用:mysql> select * from temp_tb, temp_tb as t;
错误信息: ERROR 1137 (HY000): Can’t reopen table: ‘temp_tb’
5,临时表可以手动删除:
DROP TEMPORARY TABLE IF EXISTS temp_tb;
7,Oracle dblink
Oracle 通过dblink实现跨库访问,
MySQL 用federated就是我们说的dblink类似的功能。远程映射。
ENGINE=FEDERATED CONNECTION='mysql://root:mengliang@192.168.2.142:3306/job_admin/ml_1';
原文链接:https://blog.csdn.net/shushugood/article/details/79925150
8,Oracle 字段类型转换,to_char,to_number
Mysql中提供了两个内置函数提供我们使用分别为:CAST和CONVERT,Mysql 的CAST()和CONVERT() 函数可用来转换或者获取一个我们需要的类型。两者具体的语法如下:
CAST(value as type);
CONVERT(value, type);
可以转换的类型是有限制的。这个类型可以是以下值其中的一个:
二进制,同带binary前缀的效果 : BINARY
字符型,可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
9,Oracle 产生随机数和随机字符串,dbms_random.value
Oracle中产生随机数:https://www.cnblogs.com/ulex/p/4415478.html
MySQL中产生随机数的方法:https://www.jb51.net/article/148167.htm,https://blog.csdn.net/bestlove12345/article/details/54583018