Oracle到mysql转换的问题总结要点.docx
《Oracle到mysql转换的问题总结要点.docx》由会员分享，可在线阅读，更多相关《Oracle到mysql转换的问题总结要点.docx（52页珍藏版）》请在冰点文库上搜索。
Oracle到mysql转换的问题总结要点
Oracle到mysql转换的问题总结
常用字段类型区别
oracle
mysql
number（10,0）
int
number（10,2）
decimal（10,2）
varchar2
varchar
date
datetime
Colb
text
个别语句写法区别
1.oracle里只可以用单引号包起字符串，mysql里可以用双引号和单引号。
2.mysql在select*from（）....，from后面是一个结果集时，括号后面必须加上别名。
3.mysql在delete数据时不能给表加别名，如：
deletefromtable1Twhere....，会报错，但是可以这样写：
deleteTfromtable1Twhere....。
4.Mysql不支持在同一个表中先查这个表在更新这个表，举个例子说明一下，
insertintotable1values（字段1，（select字段2fromtable1where...）），
但是可以在后面那个table1加上别名就没有问题了。
insertintotable1values（字段1，（selectT.字段2fromtable1Twhere...））
5.MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。
ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。
也可以自定义函数实现oracle的nextval。
6.翻页的sql语句处理，MYSQL处理翻页的SQL语句比较简单，用LIMIT开始位置,记录个数，例如：
select*fromtablelimitm,n，意思是从m+1开始取n条。
常见的函数替换
1.日期转换方面的函数
oracle
mysql
说明
to_char（date,’yyyy-MM-ddhh24:
mi:
ss’）
date_format（date,'%Y-%m-%d%H:
%i:
%s'）
注意时间格式的对应
to_date（str,’yyyy-MM-ddhh24:
mi:
ss’）
str_to_date（str,'%Y-%m-%d%H:
%i:
%s'）
注意时间格式
to_date（str,’yyyy-MM-ddhh24:
mi:
ss’）
str_to_date（str,'%Y-%m-%d%T'）
注意时间格式
to_char（）、to_number（）
convert（字段名,类型）
类型转换
date+n
date_add（date,intervalnday）
日期增加n天
selectdate_add（sysdate（）,INTERVAL2DAY）;
add_months（date,n）
date_add（date,intervalnmonth）
日期增加n个月
selectdate_add（sysdate（）,INTERVAL2MONTH）;
date1-date2
datediff（date1,date2）
日期相减获取天数
2.oracle中decode（）函数，可以用casewhen进行替换
例子：
Oracle：
selectdecode（a,b,c,d）ascol1fromtable1;
Mysql：
select
case
whena=bthenc
whena!
=bthend
endascol1
fromtable1
3.oracle的函数ROW_NUMBER（）OVER（PARTITIONBYcol1ORDERBYcol2），根据col1分组，在分组内根据col2排序，改函数计算的值就表示每组内部排序后的顺序编号。
Mysql没有这个函数，可以用mysql的用户变量来实现
例子：
Oracle：
selectrow_number（）over（partitionbycol1orderbycol2）asnumfromtable1
Mysql：
selectnum1asnumfrom（selectif（@pdept=col1,@rank:
=@rank+1,@rank:
=1）asnum1,@pdept:
=col1fromtable1orderbycol2）H
这里用到mysql的用户变量。
4.oracle的行号ROWNUM，mysql没有这个行号，也需要用用户变量来实现。
例子：
Oracle：
select*fromtable1whererownum-col1=0
Mysql:
select*fromtable1,（SELECT（@rowNum:
=0））HHwhere（@rowNum:
=@rowNum+1）-col1=0
5.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字段加上x：
updatetable1setname=concat（x，name）。
1.1 移植过程中重点问题
1.1.1 数据类型差异
ORACLE数据库和MYSQL数据库在数据类型方面差异比较大，而且数据类型也是一个数据库存储数据的基础，所以找到数据类型之间的对应是整个系统进行移植的基础。
以下给出了ORACLE à MYSQL数据类型的对应关系。
数值类型：
NUMBER à DECIMAL，精度刻度都不变
注：
如果是序列用BIGINT
字符串类型：
VARCHAR2 à VARCHAR长度不变。
LONG à LONGTEXT
这里有可能遇到的问题是超过主键key长度的问题，根据实际情况适当修改，如果是TEXT类型也需要指名长度，否则建立key会报错
日期类型：
DATE à DATETIME
TIMESTAMP（N） à TIMESTAMP
1.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_INCREM
ENT）;
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 AS
SELECT 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_TYPE
FROM （SELECT PERSON.USER_ID , PERSON.USER_NAME, NVL（POSITION.ORG_ID, -1） AS ORG_ID, NVL（POSITION.ORG_PERSON_NO, 0） AS ORG_PERSON_NO, NVL（POSITION.PERSON_LEVEL, '64'） AS PERSON_LEVEL FROM MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON PERSON WHERE PERSON.USER_ID = POSITION.USER_ID （+） AND PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='A'） PERSON_POSITION, MOCHA_IM_ORG_VIEW ORG
WHERE PERSON_POSITION.ORG_ID = ORG.ORG_ID （+）
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.20000000.00.64000000000' AS CODE, 1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='I'
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.30000000.00.64000000000' AS CODE
1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = '1' AND STATUS='A'
UNION ALL
SELECT LPAD（ORG_ID,20,' '） AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, 'ORG' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW
UNION ALL
SELECT '-1' AS ID, '未分派人员' AS NAME, '00000001.10000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-2' AS ID, '待删除人员' AS NAME, '00000001.20000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-3' AS ID, '系统管理员' AS NAME, '00000001.30000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE From DUAL
/
转为mysql：
CREATE OR REPLACE VIEW PERSON_POSITION AS
SELECT PERSON.USER_ID , PERSON.USER_NAME, IFNULL（POSITION.ORG_ID, -1） AS ORG_ID, IFNULL（POSITION.ORG_PERSON_NO, 0） AS ORG_PERSON_NO, IFNULL（POSITION.PERSON_LEVEL, '64'） AS PERSON_LEVEL
FROM MOCHA_IM_PERSON_POSITION POSITION LEFT JOIN MOCHA_IM_PERSON PERSON ON PERSON.USER_ID = POSITION.USER_ID
WHERE PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='A';
/
CREATE OR REPLACE VIEW MOCHA_IM_ALL_ACCOUNT_VIEW AS
SELECT USER_ID AS ID, USER_NAME AS NAME,
CONCAT（CONCAT（CONCAT（IFNULL（ORG.ORG_CODE,'00000001.10000000'）, '.00.'）,
PERSON_POSITION.PERSON_LEVEL）, LPAD（PERSON_POSITION.ORG_PERSON_NO,9,'0'）） AS CODE,
IFNULL（ORG.ORG_LEVEL, 1） AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_ORG_VIEW ORG LEFT JOIN PERSON_POSITION ON PERSON_POSITION.ORG_ID = ORG.ORG_ID
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.20000000.00.64000000000' AS CODE, 1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON PERSON WHERE PERSON.ADMIN_FLAG = '0' And PERSON.STATUS='I'
UNION ALL
SELECT USER_ID AS ID, USER_NAME AS NAME, '00000001.30000000.00.64000000000' AS CODE
1 AS REC_LEVEL, 'PERSON' AS REC_TYPE
FROM MOCHA_IM_PERSON WHERE ADMIN_FLAG = '1' AND STATUS='A'
UNION ALL
SELECT LPAD（ORG_ID,20,' '） AS ID, ORG_NAME AS NAME, ORG_CODE AS CODE,ORG_LEVEL AS REC_LEVEL, 'ORG' AS REC_TYPE FROM MOCHA_IM_ORG_VIEW
UNION ALL
SELECT '-1' AS ID, '未分派人员' AS NAME, '00000001.10000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-2' AS ID, '待删除人员' AS NAME, '00000001.20000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE FROM DUAL
UNION ALL
SELECT '-3' AS ID, '系统管理员' AS NAME, '00000001.30000000' AS CODE, 1 AS REC_LEVEL, 'ORG' AS REC_TYPE From DUAL
/
触发器，函数，存储过程
语法的区别，难点在于异常处理模块，其他要关注的比如定义游标的语法，mysql控制流程等等。
对于触发器来讲对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
例如，对于某一表，不能有两个BEFORE UPDATE触发程序。
但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
就是说mysql不支持oracle中create trigger *** before insert or
Update or delete on ***.此时应该把这个触发器拆分为3个触发器。
例子：
Oracle下触发器：
CREATE OR REPLACE TRIGGER MOCHA_IM_DUTY_TRG
BEFORE INSERT OR UPDATE OR DELETE ON MOCHA_IM_DUTY
FOR EACH ROW
DECLARE
befImg VARCHAR2（2000）;
afterImg VARCHAR2（2000）;
action VARCHAR2（10）;
action_module VARCHAR2（10）;
info_category VARCHAR2（20）;
BEGIN
action_module :
= 'IM';
info_category :
= 'duty';
befImg :
= '[DUTY_ID]' || :
old.duty_id || '[DUTY_NAME]' || :
old.duty_name;
afterImg :
= '[DUTY_ID]' || :
new.duty_id || '[DUTY_NAME]' || :
new.duty_name;
IF （:
old.duty_id is null） THEN
action :
= 'Insert';
befImg :
= '';
INSERT INTO MOCHA_IM_AUDIT_LOG （ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE） VALUES （SYSDATE, action, action_module, info_category, befImg, afterImg）;
ELSIF （:
new.duty_id is null） THEN
action :
= 'Delete';
afterImg :
= '';
INSERT INTO MOCHA_IM_AUDIT_LOG （ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE） VALUES （SYSDATE, action, action_module, info_category, befImg, afterImg）;
ELSE
action :
= 'Update';
IF（befImg !
= afterImg） THEN
INSERT INTO MOCHA_IM_AUDIT_LOG （ACTION_TIMESTAMP, ACTION, ACTION_MODULE, INFO_CATEGORY, BEFORE_IMAGE, AFTER_IMAGE） VALUES （SYSDATE, action, action_module, info_category, befImg, afterImg）;
END IF;
END IF;
EXCEPTION
WHEN OTHERS then
befImg :
= '';
end;
/
mysql下：
CREATE TRIGGER MOCHA_IM_DUTY_TRG_INSERT
BEFORE INSERT ON MOCHA_IM_DUTY
FOR EACH ROW
BEGIN
DECLARE befImg VARCHAR（2000）;
DECLARE afterImg VARCHAR
升级会员 