Oracle到mysql转换的问题总结要点

Oracle到mysql转换的问题总结常用字段类型区别oraclemysqlnumber（10,0）intnumber（10,2）decimal（10,2）varchar2varchardatedatetimeColbtext个别语句写法区别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.日期转换方面的函数oraclemysql说明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+ndate_add(date,intervalnday)日期增加n天selectdate_add(sysdate(),INTERVAL2DAY);add_months(date,n)date_add(date,intervalnmonth)日期增加n个月selectdate_add(sysdate(),INTERVAL2MONTH);date1-date2datediff(date1,date2)日期相减获取天数2.oracle中decode（）函数，可以用casewhen进行替换例子：Oracle：selectdecode(a,b,c,d)ascol1fromtable1;Mysql：selectcasewhena=bthencwhena!=bthendendascol1fromtable13.oracle的函数ROW_NUMBER()OVER(PARTITIONBYcol1ORDERBYcol2)，根据col1分组，在分组内根据col2排序，改函数计算的值就表示每组内部排序后的顺序编号。Mysql没有这个函数，可以用mysql的用户变量来实现例子：Oracle：selectrow_number()over(partitionbycol1orderbycol2)asnumfromtable1Mysql：selectnum1asnumfrom(selectif(@pdept=col1,@rank:=@rank+1,@rank:=1)asnum1,@pdept:=col1fromtable1orderbycol2)H这里用到mysql的用户变量。4.oracle的行号ROWNUM，mysql没有这个行号，也需要用用户变量来实现。例子：Oracle：select*fromtable1whererownum-col1=0Mysql:select*fromtable1,(SELECT(@rowNum:=0))HHwhere(@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字段加上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àDATETIMETIMESTAMP(N)àTIMESTAMP1.1.2SQL语法差异SEQUENCE：MYSQL没有ORACLE中的SEQUENCE对象，我们在迁移的时候需要特别注意，一般SEQUENCE有两种用途：1、作为表中自增字段的序列号。2、程序中获得自动编号。MYSQL数据类型中存在AUTO_INCREMENT为自增数据类型。我们可以利用该数据类型变通一下来满足我们现有系统中的SEQUENCE功能。1、对于ORACLE中SEQUENCE作为表的自增列一般是通过与触发器绑定实现的，在MYSQL中我们可以直接利用MYSQL的AUTO_INCREMENT类型来实现。2、ORACLE开发的应用程序中直接SELECTSEQUENCT来获得自动编号，对于这个功能我们也可以利用MYSQL的AUTO_INCREMENT类型来实现。首先介绍一个函数，我们可以利用如下函数查询最后一个序列号的值：mysqlSELECTLAST_INSERT_ID();+------------------+|LAST_INSERT_ID()|+------------------+|3|+------------------+1rowinset(0.06sec)我们可以创建一个含有自增列的表，对该表进行INSERT操作后，再利用LAST_INSERT_ID()函数来获得刚刚INSERT的值，也就是相当于ORACLE中的SEQUENCE.NETVAL。也就是INSERT操作+SELECT操作获得一个自动编号。mysqlCREATETABLEMOCHA_BE_SEQUENCE(IDBIGINTNOTNULLPRIMARYKEYAUTO_INCREMENT);QueryOK,0rowsaffected(0.63sec)mysqlINSERTINTOMOCHA_BE_SEQUENCEVALUES(NULL);QueryOK,1rowaffected(0.09sec)mysqlSELECTLAST_INSERT_ID();+------------------+|LAST_INSERT_ID()|+------------------+|1|+------------------+1rowinset(0.05sec)mysqlINSERTINTOMOCHA_BE_SEQUENCEVALUES(NULL);QueryOK,1rowaffected(0.06sec)mysqlSELECTLAST_INSERT_ID();+------------------+|LAST_INSERT_ID()|+------------------+|2|+------------------+1rowinset(0.00sec)BLOG：ORACLE和MYSQL都支持二进制大对象，数据类型的名称都是BLOB，在存储方面都是一样的，BLOB列没有字符集，并且排序和比较基于列值字节位数；在开发应用程序时需要注意对两种数据库BLOG类型的操作的差异。视图Mysql视图限制（1）SELECT语句不能包含FROM子句中的子查询。（2）SELECT语句不能引用系统或用户变量。（3）SELECT语句不能引用预处理语句参数。（4）在存储子程序内，定义不能引用子程序参数或局部变量。（5）在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECKTABLE语句。（6）在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。（7）在视图定义中命名的表必须已存在。（8）不能将触发程序与视图关联在一起。我所要迁移的系统中的视图90%用到了子查询，解决方案是首先重新写查询语句，尽量避免子查询，避免不了，就将子查询中的内容，单独create成一个新的视图，然后再建立所需要的视图。例子：Oracle中带子查询的视图：CREATEORREPLACEVIEWMOCHA_IM_ALL_ACCOUNT_VIEWASSELECTUSER_IDASID,USER_NAMEASNAME,CONCAT(CONCAT(CONCAT(NVL(ORG.ORG_CODE,'00000001.10000000'),'.00.'),PERSON_POSITION.PERSON_LEVEL),LPAD(PERSON_POSITION.ORG_PERSON_NO,9,'0'))ASCODE,NVL(ORG.ORG_LEVEL,1)ASREC_LEVEL,'PERSON'ASREC_TYPEFROM(SELECTPERSON.USER_ID,PERSON.USER_NAME,NVL(POSITION.ORG_ID,-1)ASORG_ID,NVL(POSITION.ORG_PERSON_NO,0)ASORG_PERSON_NO,NVL(POSITION.PERSON_LEVEL,'64')ASPERSON_LEVELFROMMOCHA_IM_PERSON_POSITIONPOSITION,MOCHA_IM_PERSONPERSONWHEREPERSON.USER_ID=POSITION.USER_ID(+)ANDPERSON.ADMIN_FLAG='0'AndPERSON.STATUS='A')PERSON_POSITION,MOCHA_IM_ORG_VIEWORGWHEREPERSON_POSITION.ORG_ID=ORG.ORG_ID(+)UNIONALLSELECTUSER_IDASID,USER_NAMEASNAME,'00000001.20000000.00.64000000000'ASCODE,1ASREC_LEVEL,'PERSON'ASREC_TYPEFROMMOCHA_IM_PERSONPERSONWHEREPERSON.ADMIN_FLAG='0'AndPERSON.STATUS='I'UNIONALLSELECTUSER_IDASID,USER_NAMEASNAME,'00000001.30000000.00.64000000000'ASCODE,1ASREC_LEVE