oracle与mysql差异对比v10

1、Oracle与mysql差异对比希望每个人都把移植经验记录下来，补充到该文档，分享给大家。1. 函数oraclemysql用法round支持ROUND(字段,精度)，如SELECT ROUND(666.66,1);->666.67Decode不支持用case when 代替，如CASE WHEN A.EL_POWER > B.EL_POWER THEN 0 WHEN A.EL_POWER < B.EL_POWER THEN 1 ELSE 2 END as 别名replace支持将f1字段的abc替换为def， 第二个和第三个任一参数为NULL, MySQL返回NULL;REP

2、LACE(f1, 'abc', 'def');Nvl不支持用IFNULL代替，如果字段f1值为空，则替换为xIFNULL(f1，x)Nvl2不支持用IF代替，如：IF(字段 is null, 0, 1)substr支持一种用法：SUBSTR(str,pos,len)Trim支持普通用法没区别，eg:mysql> SELECT TRIM(' bar '); /默认删除前后空格 -> 'bar'instr支持Mysql中只支持2参数，用法INSTR('bbbbbaa','a')->6t

3、o_number不支持Mysql可用cast,如：/*整型*/select cast(-1111 as SIGNED int) ;->-1111/*浮点型*/select cast(-11.125 as decimal(10,2)->-11.13BITAND不支持位运算符，在mysql中使用&代替Bitand;|代替bitor；代替bitxor add_months、不支持可用date_add代替，如，当前月份增加2个月date_add(NOW(), interval 2 month)next_day、不支持如果用的，考虑写个函数代替months_between、不支持My

4、sql替代办法，结果是整数MONTH(now()-MONTH('1998-02-03');7天前日期：SUBDATE（NOW（），INTERVAL 7 DAY）-> 2013-04-14 14:08:08或sysdate() + interval 7 daysys_guid()不支持Mysql中使用UUID()代替，但UUID()生成的串要多4个-字符及全部是小写。如Oracle:62DE8AE55FB84FE28E6908ADB936F791Mysql；79e8a397-a959-11e2-83d9-d4ae528cf127sysdate不支持用SYSDATE(),no

5、w()代替NOW()函数以'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。CURDATE()以YYYY-MM-DD的格式返回今天的日期，可以直接存到DATE字段中。CURTIME()以HH:MM:SS的格式返回当前的时间，可以直接存到TIME字段中。trunc不支持Oracle中的trunc功能强大，可对日期及数字进行截取Mysql中对日期截取可以参考DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');To_dateOracle 常见用法：to_date(to_char(sysdate

6、,'yyyy/mm/dd'),'yyyy-mm-dd')mysql中：SELECT date_format(NOW(),'%Y-%m-%d');-> 2013-04-19SELECT str_to_date(NOW(),'%Y-%m-%d')-> 2013-04-19CONVERT(xxx,类型)，类型必须用下列的类型：可用的类型 二进制,同带binary前缀的效果 : BINARY 字符型,可带参数 : CHAR() 日期 : DATE 时间: TIME 日期时间型 : DATETIME 浮点数 : DECIMAL

7、整数 : SIGNED 无符号整数 : UNSIGNEDTo_charnlssort不支持Mysql中的替代办法，如：Oracle:nlssort(字段, 'NLS_SORT=SCHINESE_PINYIN_M')mysql:CONVERT(字段 USING gbk)或convert(vender_abbrev USING gbk) COLLATE gbk_chinese_ciChr()不支持Mysql中使用char，如Select Char(97)->aascii支持标准函数，mysql还可以用ORD函数代替，如Select ord(a);->97Lead(),o

8、ver()不支持暂没找到替换办法sign支持Last_day支持但返回结果不太一样Oracle：select last_day(sysdate) from DUAL ;-> 2013-4-30 AM 09:40:39Mysql:SELECT last_day(NOW()-> 2013-04-30length支持用法相同Lpad,rpad支持Floor支持2. 数据类型在mysql中没有像oracle中的varchar2、number，mysql有与之对应的varchar、numeric，当然在oracle中没有mysql的time类型等等。详细如下：MysqlOracleJavaB

9、IGINTNUMBER(19,0)java.lang.LongBITRAWbyteBLOBBLOB RAWbyteCHARCHARjava.lang.StringDATEDATEjava.sql.DateDATETIMEDATEjava.sql.TimestampDECIMALFLOAT (24)java.math.BigDecimalDOUBLEFLOAT (24)java.lang.DoubleDOUBLE PRECISIONFLOAT (24)java.lang.DoubleENUMVARCHAR2java.lang.StringFLOATFLOATjava.lang.FloatINTN

10、UMBER(10,0)java.lang.IntegerINTEGERNUMBER(10,0)java.lang.IntegerLONGBLOBBLOB RAWbyteLONGTEXTCLOB RAWjava.lang.StringMEDIUMBLOBBLOB RAWbyteMEDIUMINTNUMBER(7,0)java.lang.IntegerMEDIUMTEXTCLOB RAWjava.lang.StringNUMERICNUMBERREALFLOAT (24)SETVARCHAR2java.lang.StringSMALLINTNUMBER(5,0)java.lang.IntegerT

11、EXTVARCHAR2 CLOBjava.lang.StringTIMEDATEjava.sql.TimeTIMESTAMPTIMESTAMPjava.sql.TimestampTINYBLOBRAWbyteTINYINTNUMBER(3,0)java.lang.BooleanTINYTEXTVARCHAR2java.lang.StringVARCHARVARCHAR2 CLOBjava.lang.StringYEARNUMBERjava.sql.Date3. SQL语法oraclemysql说明|不是字符串连接符用concat代替Concat(hello ,zhangsan)->Hel

12、lo zhangsanCONCAT('type','.','en','=','zh')字段/表别名省略as支持Mysql中字段别名 可以省略as，如：SELECT now() nowdate from dual子查询省略别名不支持mysql子查必须有别名转义符Oracle:select * from P_MPED pWHERE p.MP_NAME LIKE 'a_b' escape ''Mysql:select * from P_MPED pWHERE p.MP_NAME LIKE

13、'a_b' escape ''日期运算Oracle中：SELECT COUNT(DISTINCT T.MCSET) FROM GROUNDHEAT_GATHER t WHERE T.DATA_DATE > SYSDATE - 0.5Mysql：SELECT DATE_ADD(NOW(),INTERVAL -12 HOUR)可指定计算单：位Second,minute ,hour,day,year,日期比较支持都可以用比较操作符：>=<For update nowait不支持只支持for update,不支持nowaitrowid不支持暂没有替代办

14、法ROWNUM不支持Oracle:select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;mysql替代办法:SELECT rownum:=rownum+1 AS rownum, o_org.* FROM (SELECT rownum:=0) r, o_org;如果用在mysql视图中可能存在问题注释Mysql语句支持/*/或“#”注释，不支持- - 注释IS NULL支持是否为NULL判断Distinct()支持Exists/not exists支持select * f

15、rom psmd.es_dept dWHERE EXISTS(SELECT C.CONS_ID FROM SGPM.C_CONS C )In/not in支持翻页Limit的3中用法：SELECT * FROM table LIMIT 5,10;检索记录行 6-15SELECT * FROM table LIMIT 95,-1;检索记录行 96-lastSELECT * FROM table LIMIT 5; 检索前 5 个记录行。LIMIT n 等价于 MySQL LIMIT 0,n。Oralce：select * from(select my_table.*, rownum as my_r

16、ownum from(业务sql语句) my_tablewhere rownum<=20) where my_rownum>=11)Mysql:select * from mytableorder by afieldimit 10, 10即意为从第11条记录开始的10条记录。递归Oracle：SELECT * FROM SGPM.C_CONS C START WITH C.CONS_ID = '1355195859774'CONNECT BY PRIOR C.CONS_ID = C.CONS_PIDMysql通过存储过程+临时表或中间表实现：待补充表连接语法mysq

17、l内连接可以不用关键词，外连接必须用关键词left join、right join，不支持oracle的(+)oracle select a.*,b.*,c.*,d.* from a,b,c,d where a.id=b.id and is not null and a.id=c.id(+) and a.id=d.id(+) mysql 方法一 select a.*,c.*,d.* from a left join (c,d) on (a.id=c.id and a.id=d.id),b where a.id=b.id and is not null 方法二 sel

18、ect a.*,c.*,d.* from a left join c on a.id=c.id left join d on a.id=d.id,b where a.id=b.id and is not null子查询支持一般子查询都支持，但mysql的子查询必须有别名。例如：SELECT NAME, VALUE, (SELECT COUNT(1) FROM P_QUERY_DEF WHERE USAGE_SORT = P.VALUE) SUB_COUNT FROM P_CODE P WHERE CODE_TYPE = 'fieldTypeCode' ORDER

19、BY VALUE;Select field1 from (select NOW() as field1 from dual) aaselect * from psmd.es_dept dWHERE EXISTS(SELECT C.CONS_ID FROM SGPM.C_CONS C )SELECT o.ORG_NAME from o_org o where o.ORG_NAME=(SELECT _name from o_org o1 WHERE o1.ORG_NAME='www');SELECT o.ORG_NAME from o_org o where o.ORG

20、_NAME in (SELECT _name from o_org o1 )SELECT o.ORG_NAME from o_org o where (o.ORG_NAME,o.ORG_ID)=(SELECT _name ,o1.ORG_ID from o_org o1 WHERE o1.ORG_NAME='www');不支持的子句：UPDATE o_org o SET o.ORG_NAME=( SELECT _name from o_org o1 WHERE o1.ORG_NAME='www');匿名块不支持使用存储过程解决

21、保留字Mysql中：把sql语句中的保留字用符号引起来，这个符号位于键盘的tab键上面;如果是字段名还有另外一种方法tablename.字段名。像这样：insert into tablename (id, interval) value(. 或insert into tablename (id, eval) value(.有些保留字不能直接用在SQL语句里,要加表名或别名限制如select RIGHT FROM XX要改成select a.RIGHT FROM XX a语句执行顺序语句执行顺序：开始->FROM子句->WHERE子句->GROUP B

22、Y子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果 每个子句执行后都会产生一个中间结果 ，供接下来的子句使用，如果不存在某个子句，就跳过。MINUS不支持4. 表Oracle的系统表，mysql不支持临时表5. 视图Mysql视图限制:1) SELECT语句不能包含FROM子句中的子查询。2) SELECT语句不能引用系统或用户变量。3) SELECT语句不能引用预处理语句参数。4) 在存储子程序内，定义不能引用子程序参数或局部变量。5) 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视

23、图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。6) 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。7) 在视图定义中命名的表必须已存在。8) 不能将触发程序与视图关联在一起。子查询的解决方案是重新写查询语句，尽量避免子查询，避免不了，就将子查询中的内容，单独create成一个新的视图，然后再建立所需要的视图。6. 序列MYSQL没有ORACLE中的SEQUENCE对象，我们在迁移的时候需要特别注意，一般SEQUENCE有两种用途：1、 作为表中自增字段的序列号。2、 程序中获得自动编号。MYSQL数据类型中存在 AUTO_INCREMENT为自

24、增数据类型。可以利用该数据类型变通一下来满足我们现有系统中的SEQUENCE功能。1、 对于ORACLE中SEQUENCE作为表的自增列一般是通过与触发器绑定实现的，在MYSQL中我们可以直接利用MYSQL的AUTO_INCREMENT类型来实现。2、 ORACLE开发的应用程序中直接SELECT SEQUENCT来获得自动编号，对于这个功能我们也可以利用MYSQL的AUTO_INCREMENT类型来实现。首先介绍一个函数，我们可以利用如下函数查询最后一个序列号的值：mysql> SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 3

25、|+-+1 row in set (0.06 sec)可以创建一个含有自增列的表，对该表进行INSERT操作后，再利用LAST_INSERT_ID()函数来获得刚刚INSERT的值，也就是相当于ORACLE中的SEQUENCE.NETVAL。也就是INSERT操作+SELECT操作获得一个自动编号。mysql> CREATE TABLE MOCHA_BE_SEQUENCE(ID BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT);Query OK, 0 rows affected (0.63 sec)mysql> INSERT INTO MOCH

26、A_BE_SEQUENCE VALUES(NULL);Query OK, 1 row affected (0.09 sec)mysql> SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-+| 1 |+-+1 row in set (0.05 sec)mysql> INSERT INTO MOCHA_BE_SEQUENCE VALUES(NULL);Query OK, 1 row affected (0.06 sec)mysql> SELECT LAST_INSERT_ID();+-+| LAST_INSERT_ID() |+-

27、+| 2 |+-+1 row in set (0.00 sec)7. 索引普通索引，唯一索引，全文索引都支持。8. 同义词Mysql中不支持同义词，如果是表的同义词可以通过创建视图方式解决，如果是存储过程的同义词，需要创建同名存储过程，并将同义词引用的存储过程进行封装。9. 定时任务10. 触发器对于触发器来讲对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触

28、发程序。就是说mysql不支持oracle中create trigger * before insert or Update or delete on *.此时应该把这个触发器拆分为3个触发器。11. 存储过程和函数程序包是Oracle用来组织逻辑功能的一个Object，MySQL不支持，因此需要将包里的存储过程函数等全部放到该数据库公有过程和函数里面。oraclemysql说明创建格式不同格式不同，例如：Oracle为：CREATE OR REPLACE procedure procedure1(paramName in varchar2) asMySQL应该为：CREATE procedu

29、re procedure1( in paramName varchar(200)变量名Mysql中变量名不能与列名相同赋值语句不同Oracle赋值语句为：strSQL := update table set field1=1;MySQL应该为：Set StrSQL = update table set field1=1;(用:=也行)CREATE PROCEDURE sp_add(a int, b int,out c int)begin set c=a+ b;end;调用过程：call sp_add (1,2,a);select a;声明变量方式oracle为 li_qx int; mysql

30、为declare li_qx int;变量赋值方式Oracle 为 result := v_int1|v_int2; Mysql 为 set result = v_int1 + v_int2;声明变量的位置Oracle声明变量在beginend体之前； Mysql声明变量在begin.end体内，begin之后其他任何内容之前while语句区别Oracle：while 条件 loop 执行体 end loop Mysql：while 条件do 执行体 End while申明游标Oracle： declare cursor cursor_name is select col from tab;

31、Mysql： declare stopFlag int; declare cursor_name cursor for select col from tab; DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;打开游标Oracle：open cursor_name; loop fetch cursor_name into var_1,var_2,var_n; exit when cursor_name %notfound; delete tab where col1=var_1 and coln=var_n; end loop;clo

32、se cursor_name; Mysql：open cursor_name; REPEAT fetch cursor_name into var_1,var_2,var_n; begin delete from tab where col1=var_1 and coln=var_n; end; UNTIL stopFlag = 1 END REPEAT; close cursor_name;exit Oracle： while 条件 loop exit; end loop; Mysql：label_name:while 条件 do leave label_name; end while la

33、bel_name;no_data_found不支持 Mysql：NOT FOUND，待补充12. 用户Oracle的用户管理和MySQL下有较大区别，需分别建立用户，并赋予合适的权限。13. 表空间Mysql也有表空间概念，待完善。14. 分区15. 其它查询时，有些结果不正确，可能是由is null引起的，在oracle中，字段中插入''值，默认为Null,而在mysql中，则认为是空字符串，所以oracle查询中is null判断，有时候在mysql中需要改成=''原来oracle字段中默认值，转成mysql时丢到了，如sysdate，to_char(sysdate,'yyyymm')等等，现已发现日志表有该问题，导致部分数据查不出来，估计其他表也有类似问题在我本地建了msyql数据库，启动核心类库应用后，访问页面报了“Access denied for user 'root''Victor' (using password: YES)”，咱们遇到过吗？Reply:是对root用户没有授权，执行GRANT ALL PRIVILE