Oracle到mysql转换的问题总结.doc

Oracle到mysql转换的问题总结常用字段类型区别oraclemysqlnumber〔10,0〕intnumber〔10,2〕decimal〔10,2〕varchar2varchardatedatetimeColbte*t个别语句写法区别1. oracle里只可以用单引号包起字符串，mysql里可以用双引号和单引号2. mysql 在select * from () ....，from后面是一个结果集时，括号后面必须加上别名3. mysql在delete数据时不能给表加别名，如：delete from table1 T where....，会报错，但是可以这样写：delete T from table1 T where....4. Mysql不支持在同一个表中先查这个表在更新这个表，举个例子说明一下， insert into table1 values〔字段1，〔select 字段2 from table1 where...〕〕， 但是可以在后面那个table1加上别名就没有问题了insert into table1 values〔字段1，〔select T.字段2 from table1 T where...〕〕5. MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。

ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段也可以自定义函数实现oracle的ne*tval6.翻页的sql语句处理，MYSQL处理翻页的SQL语句比拟简单，用LIMIT 开场位置, 记录个数，例如：select * from table limit m,n，意思是从m+1开场取n条常见的函数替换1.日期转换方面的函数oraclemysql说明to_char(date,’yyyy-MM-dd hh24:mi:ss’)date_format(date,'%Y-%m-%d %H:%i:%s')注意时间格式的对应to_date(str,’yyyy-MM-dd hh24:mi:ss’)str_to_date(str,'%Y-%m-%d %H:%i:%s')注意时间格式to_date(str,’yyyy-MM-dd hh24:mi:ss’)str_to_date(str,'%Y-%m-%d%T')注意时间格式to_char()、to_number()convert(字段名,类型)类型转换date + ndate_add(date,interval n day)日期增加n天select date_add(sysdate(),INTERVAL 2 DAY);add_months(date,n)date_add(date,interval n month)日期增加n个月select date_add(sysdate(),INTERVAL 2 MONTH);date1 - date2datediff(date1,date2)日期相减获取天数2. oracle中decode〔〕函数，可以用case when进展替换例子：Oracle：select decode(a,b,c,d) as col1 from table1;Mysql：selectcasewhen a=b then cwhen a!=b then dend as col1from table13. oracle的函数 ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col2)，根据col1分组，在分组根据col2排序，改函数计算的值就表示每组部排序后的顺序编号。

Mysql没有这个函数，可以用mysql的用户变量来实现例子：Oracle：select row_number() over(partition by col1 order by col2) as num from table1Mysql：select num1 as num from (select if(pdept=col1,rank:=rank+1,rank:=1) as num1,pdept:=col1 from table1 order by col2 ) H这里用到mysql的用户变量4. oracle的行号ROWNUM，mysql没有这个行号，也需要用用户变量来实现例子：Oracle：select * from table1 where rownum - col1=0Mysql: select * from table1,(SELECT (rowNum := 0)) HH where (rowNum := rowNum + 1) - col1=05. Oracle 中的 substr 〔hello,a,b〕 mysql中是substring〔hello,a,b〕 oracle的a=0和a=1是一样的，都是从第一个开场。

Mysql是从0开场6. Oracle的nvl()对应mysql的ifnull()7. Oracle 中的WMSYS.WM_CONCAT〔〕，列转行函数，以逗号隔开，mysql可以用GROUP_CONCAT()进展替换8. Oracle的||可以用mysql的+替代，但是mysql在往*列加上字符时应该用concat，例如给表中name字段加上*：update table1 set name=concat〔*，name〕1.1 移植过程中重点问题1.1.1 数据类型差异ORACLE数据库和MYSQL数据库在数据类型方面差异比拟大，而且数据类型也是一个数据库存储数据的根底，所以找到数据类型之间的对应是整个系统进展移植的根底以下给出了ORACLE à MYSQL数据类型的对应关系数值类型：   NUMBER à DECIMAL，精度刻度都不变注：如果是序列用BIGINT字符串类型：   VARCHAR2 à VARCHAR长度不变   LONG à LONGTE*T这里有可能遇到的问题是超过主键key长度的问题，根据实际情况适当修改，如果是TE*T类型也需要指名长度，否则建立key会报错日期类型：   DATE à DATETIME   TIMESTAMP(N) à TIMESTAMP1.1.2 SQL语法差异SEQUENCE：MYSQL没有ORACLE中的SEQUENCE对象，我们在迁移的时候需要特别注意，一般SEQUENCE有两种用途：1、 作为表中自增字段的序列号。

2、 程序中获得自动编号MYSQL数据类型中存在 AUTO_INCREMENT为自增数据类型我们可以利用该数据类型变通一下来满足我们现有系统中的SEQUENCE功能1、 对于ORACLE中SEQUENCE作为表的自增列一般是通过与触发器绑定实现的，在MYSQL中我们可以直接利用MYSQL的AUTO_INCREMENT类型来实现2、 ORACLE开发的应用程序中直接SELECT SEQUENCT来获得自动编号，对于这个功能我们也可以利用MYSQL的AUTO_INCREMENT类型来实现首先介绍一个函数，我们可以利用如下函数查询最后一个序列号的值：mysql> SELECT LAST_INSERT_ID();+------------------+| LAST_INSERT_ID() |+------------------+|                3 |+------------------+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 MOCHA_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() |+------------------+|                2 |+------------------+1 row in set (0.00 sec)BLOG：ORACLE和MYSQL都支持二进制大对象，数据类型的名称都是BLOB，在存储方面都是一样的，BLOB列没有字符集，并且排序和比拟基于列值字节位数；在开发应用程序时需要注意对两种数据库BLOG类型的操作的差异。

视图Mysql视图限制〔1〕SELECT语句不能包含FROM子句中的子查询〔2〕SELECT语句不能引用系统或用户变量〔3〕SELECT语句不能引用预处理语句参数〔4〕在存储子程序，定义不能引用子程序参数或局部变量〔5〕在定义中引用的表或视图必须存在但是，创立了视图后，能够舍弃定义引用的表或视图要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句〔6〕在定义中不能引用TEMPORARY表，不能创立TEMPORARY视图〔7〕在视图定义中命名的表必须已存在〔8〕不能将触发程序与视图关联在一起我所要迁移的系统中的视图90%用到了子查询，解决方案是首先重新写查询语句，尽量防止子查询，防止不了，就将子查询中的容，单独create成一个新的视图，然后再建立所需要的视图例子：Oracle中带子查询的视图：CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW ASSELECT USER_ID AS ID, USER_NAME AS NAME, CONCAT(CONCAT(CONCAT(NVL(ORG.ORG_CODE,'00000001.10000000'), '.00.'), PERSON_POSITION.PERSON_LEVEL), LPAD(PERSON_POSITION.ORG_PERSON_NO,9,'0')) AS CODE, NVL(ORG.ORG_LEVEL, 1) AS REC_LEVEL, 'PERSON' AS REC_TYPEFROM (SELECT PERSON.USER_ID , PERSON.USER_NAME, NVL(POSITION.O。