c从oracle到mysql移植_oracle移植至mysql相关sql语句

http://ykdn2010.iteye.com/blog/1511349

oracle函数的转换

一．项目已用到

1．Oracle中的TO_DATE()

示例：select * from admadjustmoney t where t.sendtime> to_date(?,'yyyy-mm-dd      hh24:mi:ss') and t.sendtime

转换后：

SELECT *

FROM `ADMADJUSTMONEY` t

WHERE t.SENDTIME > STR_TO_DATE(sysdate(), '%Y-%m-%d %h:%i:%s') AND

t.SENDTIME

2．Oracle中的nvl()

示例:select distinct a.id,a.parentId,a.description,a.name,a.status,NVL(ur.roleid,'')AS roleid from admrole a left join admuserrole ur on (a.id = ur.roleid and ur.userid = ? and ur.status = 1) where a.status = ?

转换后：

select distinct a.id,a.parentId,a.description,a.name,a.status,IFNULL(ur.roleid,'')AS roleid from admrole a left join admuserrole ur on (a.id = ur.roleid and ur.userid = ? and ur.status = 1) where a.status = ?

3．Oracle中的decode()

示例：

SELECT DECODE(MAX(PIECECODE), NULL, 0, MAX(PIECECODE)) AS PIECECODE FROM PUBPAGEPIECE WHERE 1=1

转换后：

SELECT if(MAX(PIECECODE) IS NULL, 0, MAX(PIECECODE))AS PIECECODE FROM PUBPAGEPIECE WHERE 1=1

或者用 case when的标准写法

SELECT

CASE

WHEN MAX(PIECECODE) IS NULL THEN 0

WHEN MAX(PIECECODE) IS NOT NULL THEN MAX(PIECECODE)

ENDAS PIECECODE

FROM PUBPAGEPIECE WHERE 1=1

4．Oracle中的substr ()

示例：WHERE  t.aid = v.aid and t.province = v.province and t.city = v.city and  t.wid = w.id AND w.startdate>=to_date( substr(?,1,10),'yyyy-MM-dd') and w.stopdate<=to_date( substr(?,1,10),'yyyy-MM-dd')

转换后：

WHERE  t.aid = v.aid and t.province = v.province and t.city = v.city and  t.wid = w.id AND w.startdate>= STR_TO_DATE( substring (?,1,10), '%Y-%m-%d') and w.stopdate<=STR_TO_DATE( substring (?,1,10), '%Y-%m-%d')

5．Oracle中的trunc ()

示例：

from PUBCOINOPDETAIL where opervirtualCoin > 0 and status = 1 and operTime >= ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)  and operTime

转换后：

from PUBCOINOPDETAIL where opervirtualCoin > 0 and status = 1 and operTime >=DATE_SUB(date_sub(CURDATE(),INTERVAL EXTRACT(  day from CURDATE())-1 day),INTERVAL 1 MONTH)  [上个月第一天]and operTime

再次提醒mysql大小写的问题sql统一大写hql统一小写避免不必要的BUG出现

二．网络资料附表：oracle 常用函数 与mysql 的对照表 s:mysql o ：oracle

1.绝对值S:select abs(-1) valueO:select abs(-1) value from dual

2.取整(大)S:select ceiling(-1.001) valueO:select ceil(-1.001) value from dual

3.取整(小)S:select floor(-1.001) valueO:select floor(-1.001) value from dual

4.取整(截取)S:select cast(-1.002 as int) valueO:select trunc(-1.002) value from dual

5.四舍五入S:select round(1.23456,4) value 1.23460O:select round(1.23456,4) value from dual 1.2346

6.e为底的幂S:select Exp(1) value 2.7182818284590451O:select Exp(1) value from dual 2.71828182

7.取e为底的对数S:select log(2.7182818284590451) value 1O:select ln(2.7182818284590451) value from dual; 1

8.取10为底对数S:select log10(10) value 1O:select log(10,10) value from dual; 1

9.取平方S:select SQUARE(4) value 16O:select power(4,2) value from dual 16

10.取平方根S:select SQRT(4) value 2O:select SQRT(4) value from dual 2

11.求任意数为底的幂S:select power(3,4) value 81O:select power(3,4) value from dual 81

12.取随机数S:select rand() valueO:select sys.dbms_random.value(0,1) value from dual;

13.取符号S:select sign(-8) value -1O:select sign(-8) value from dual -1

14.圆周率S:SELECT PI() value 3.1415926535897931O:不知道

15.sin,cos,tan参数都以弧度为单位例如：select sin(PI()/2) value得到1(SQLServer)

16.Asin,Acos,Atan,Atan2返回弧度

17.弧度角度互换(SQLServer，Oracle不知道)DEGREES：弧度-〉角度RADIANS：角度-〉弧度

数值间比较

18.求集合最大值S:select max(value) value from(select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 value)a

O:select greatest(1,-2,4,3) value from dual

19.求集合最小值S:select min(value) value from(select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 value)a

O:select least(1,-2,4,3) value from dual

20.如何处理null值(F2中的null以10代替)S:select F1,IFNull(F2,10) value from TblO:select F1,nvl(F2,10) value from Tbl

21.求字符序号S:select ascii('a') valueO:select ascii('a') value from dual

22.从序号求字符S:select char(97) valueO:select chr(97) value from dual

23.连接S:select '11'+'22'+'33' valueO:select CONCAT('11','22')33 value from dual

23.子串位置--返回3S:select CHARINDEX('s','sdsq',2) valueO:select INSTR('sdsq','s',2) value from dual

23.模糊子串的位置--返回2,参数去掉中间%则返回7S:select patindex('%d%q%','sdsfasdqe') valueO:oracle没发现，但是instr可以通过第四个参数控制出现次数select INSTR('sdsfasdqe','sd',1,2) value from dual返回6

24.求子串S:select substring('abcd',2,2) valueO:select substr('abcd',2,2) value from dual

25.子串代替返回aijklmnefS:SELECT STUFF('abcdef', 2, 3, 'ijklmn') valueO:SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual

26.子串全部替换S:没发现O:select Translate('fasdbfasegas','fa','我' ) value from dual

27.长度S:len,datalengthO:length28.大小写转换lower,upper

29.单词首字母大写S:没发现O:select INITCAP('abcd dsaf df') value from dual

30.左补空格(LPAD的第一个参数为空格则同space函数)S:select space(10)+'abcd' valueO:select LPAD('abcd',14) value from dual

31.右补空格(RPAD的第一个参数为空格则同space函数)S:select 'abcd'+space(10) valueO:select RPAD('abcd',14) value from dual

32.删除空格S:ltrim,rtrimO:ltrim,rtrim,trim

33.重复字符串S:select REPLICATE('abcd',2) valueO:没发现

34.发音相似性比较(这两个单词返回值一样，发音相同)S:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')O:SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dualSQLServer中用SELECT DIFFERENCE('Smithers', 'Smythers')比较soundex的差返回0-4，4为同音，1最高

日期函数35.系统时间S:select getdate() valueO:select sysdate value from dual

36.前后几日直接与整数相加减

37.求日期S:select convert(char(10),getdate(),20) valueO:select trunc(sysdate) value from dualselect to_char(sysdate,'yyyy-mm-dd') value from dual

38.求时间S:select convert(char(8),getdate(),108) valueO:select to_char(sysdate,'hh24:mm:ss') value from dual

39.取日期时间的其他部分S:DATEPART和DATENAME函数(第一个参数决定)O:to_char函数第二个参数决定

参数---------------------------------下表需要补充year yy, yyyyquarter qq, q (季度)month mm, m (m O无效)dayofyear dy, y (O表星期)day dd, d (d O无效)week wk, ww (wk O无效)weekday dw (O不清楚)Hour hh,hh12,hh24 (hh12,hh24 S无效)minute mi, n (n O无效)second ss, s (s O无效)millisecond ms (O无效)----------------------------------------------

40.当月最后一天S:不知道O:select LAST_DAY(sysdate) value from dual

41.本星期的某一天(比如星期日)S:不知道O:SELECT Next_day(sysdate,7) vaule FROM DUAL;

42.字符串转时间S:可以直接转或者select cast('2004-09-08'as datetime) valueO:SELECT To_date('2004-01-05 22:09:38','yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

43.求两日期某一部分的差(比如秒)S:select datediff(ss,getdate(),getdate()+12.3) valueO:直接用两个日期相减(比如d1-d2=12.3)SELECT (d1-d2)*24*60*60 vaule FROM DUAL;

44.根据差值求新的日期(比如分钟)S:select dateadd(mi,8,getdate()) valueO:SELECT sysdate+8/60/24 vaule FROM DUAL;

45.求不同时区时间S:不知道O:SELECT New_time(sysdate,'ydt','gmt' ) vaule FROM DUAL;

-----时区参数,北京在东8区应该是Ydt-------AST ADT大西洋标准时间BST BDT白令海标准时间CST CDT中部标准时间EST EDT东部标准时间GMT格林尼治标准时间HST HDT阿拉斯加?夏威夷标准时间MST MDT山区标准时间NST纽芬兰标准时间PST PDT太平洋标准时间YST YDT YUKON标准时间

移至常见问题

一、数据库环境从oracle 转向mysql 碰到的问题。

因为逻辑不变，所以原则是不改应用程序代码，只改数据库表的创建/ 初始化sql 。下面是我们碰到的问题以及解决办法。

1 、 大小写敏感的区别( 如果服务器OS 是linux) 。

在oracle 中一般情况下不区分大小写。有时候我们在使用oracle 不注意大小写的问题，表名和字段名不加双引号是不区分大小写的，像这样：insert into tableName 和 insert into TABLENAME 效果是一样的，用工具导出创建/ 数据初始化脚本，得到的结果一般表名和字段名转化成了大写。

但在MySQL 中，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。数据库对应数据目录中的目录，数据库中的每个表至少对应数据库目录中的一个文件( 也可能是多个，取决于存储引擎) 。因此，使用数据库或表实际上是操纵这些文件( 夹) ，所以使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。在以linux 为内核的操作系统中是大小写敏感的。

解决的办法是把mysql 的数据库名和oracle 的大小写保持一致，表名与应用程序中sql 字符串中的表名保持一致，如果应用程序中字段名用了双引号，那请把sql 中的字段名大小写与双引号里的字符保持一致。如果你的应用程序所引用的表名、字段没有统一大小写，那麻烦就大了。

2 、保留字的区别。

像sql 语言的函数名( 如：inteval ，show) 等是保留字。Oracle 中保留字是可以作为表名和字段名，并且不影响使用，但mysql 中保留字是不能作为表名和字段名，如果使用会报语法错误。

解决办法，把sql 语句中的保留字用‘`’ 符号引起来，这个符号位于键盘的tab 键上面; 如果是字段名还有另外一种方法tablename. 字段名。像这样：insert into tablename (id, `interval`) value(….. 或insert into tablename (id, tablename.inteval) value(….. 。

3 、数据类型的区别。

在mysql 中没有像oracle 中的varchar2 、number ，mysql 有与之对应的varchar 、numeric ，当然在oracle中没有mysql 的time 类型。

解决办法是替换。

4 、自动增长类型的区别。

Oracle 有sequence ，mysql 中没有，但有auto_increment 属性。

解决办法是把Oracle 中sequence 转换成使用auto_increment 属性，某些情况可能还有一种办法可以解决问题，新建一个独立的表用来专门记录自动增长型的数据。

5 、索引长度限制的区别。

从MySQL 4.1.2 开始，MyISAM 和InnoDB 表索引长度支持1000 字节，也就是说索引字段的长度不能超过1000 字节，如果超过会报这样的错：ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes 。如果是UTF-8 编码，相当于333 个字符的长度( 因为UTF8 一个字符占3 个字节) 。Oracle 的索引长度限制比mysql 要宽松得多。

解决的办法就不必要多说了，要么改索引的定义，要么改字段的定义长度。

二、为了数据库的兼容性我们应该注意些什么。

数据库的兼容性应该是数据库设计应该重视的一个问题，因为有时候客户存在已经在用的数据库，并且不希望同时维护两个数据库，这样的话兼容多种数据库还能成为产品的一个卖点。

作到数据库的兼容性关键是遵守标准用法。

1 、 遵守标准用法，尽量不使用某种数据库特有的用法。

如msyql 的‘`’ 符号的用法，

再比如，很多人有这种用法，在使用oracle 开发的时候创建sequence ，往表中插数据之前先SELECT seq.nextval FROM DUAL; ，然后把查询得到的值作为value 插入表中，这种用法没法适应没有sequence 的数据库，每个数据库都有自动增长型的用法，如果需要使用就应该完全地使用。

再举个例子，不同的数据库对分页查询作了扩展，postgresql 有offset ，limit ，oracle 就没有。

2 、 避免数据库大小写敏感的问题。

选择数据库表名和字段名采用大写还是小写，并且在数据库的设计和编码过程中完全统一。

3 、 保留字。

要求数据库设计者尽量不使用保留字作表名和字段名。也有很多人有这种用法，在表名和字段名前加‘_’ ，像这样：create table _tablename ( _id integer) 。这样永远不会出现保留字引起的问题