Oracle迁移到MySQL 一些注意

• 数据类型

oracle

mysql

 

 

Number(2)

TINYINT

-128到127(SIGNED)，0到255(UNSIGNED)需要1个字节存储

 

 

SMALLINT

-32768到32767(SIGNED)，0到65535(UNSIGNED)，需要2个字节存储 

 

NUMBER(5)

INT

-2147493648到2147493647(SIGNED)，0到4294967295(UNSIGNED)，需要4个字节存储 

 

NUMBER

BIGINT

一般大表主键都用bigint-9223372036854775808到9223372036854775807(SIGNED)，0到18446744073709551615(UNSIGNED)，需要8个字节存储 

 

 

数字类型能用小类型的尽量用小的，可以提高db的存储量和sql执行效率

 

 

VARCHAR2(120)

VARCHAR(120)

可变长的字符，最多可以支持到varchar(32766)迁移过程符合原来长度即可，如果原库是ascii类型，需要将长度翻倍

 

Lob

TEXT/MEDIUMTEXT

支持16777215个字符。需要长度+3字节的存储。(16M) 

 

DATE

DATETIME

 

 

SEQUENCE

MALANDA获取sequence；

 

 

 

 

• 常用函数（Mysql函数官方参考）

名称

oracle

mysql

取整

trunc(1.601)

floor(1.601) 
TRUNCATE(1.601,0)

判空

nvl

IsNull(filed)

去日期

sysdate

now() 
sysdate()

字符串拼接

concat('11','12')

1. '11' + '12' 
2. CONCAT('11','12')

求子串

substr('1122',2)

substring('1122',2)

首字母大写

initcap('abcd')

无

日期计算

sysdate+8/60/24

SELECT DATE_ADD(now(), INTERVAL 1 DAY); 
SELECT DATE_ADD(now(), INTERVAL 1 HOUR); 
SELECT DATE_ADD(now(), INTERVAL -1 DAY);

求字符

chr(97)

char(97)

decode

decode

1. case when then 
2. if

字符日期转换

to_date('2005-02-01 13:14:20', 'yyyy-MM-dd HH24:mi:ss')

STR_TO_DATE('2005-02-01 13:14:20', '%Y-%d-%m %H:%i:%s')

to_char

to_char(1210.73, '9999.9')
to_char(sysdate, 'yyyy/mm/dd')

FORMAT(1210.73, 1) (出来的字符串自动带“,”分隔）
DATE_FORMAT(now(), '%Y/%m/%d')

字符串到数值转换

to_number('123')

conv('123',10,10)
cast('123' as signed integer);
cast('-123' as signed integer);

 

 

常见sql写法：

1.      高效的分页写法  翻页 Efficient Pagination Using MySQL

 

SELECT T1.ID, T1.MANAGER_NICK, T1.GMT_CREATE, T1.NICK

  FROM(SELECTID

          FROM ALI_MALL_USER

         WHERE MANAGER_NICK='我的小二'

           AND IS_OPEN=2

         ORDERBYIDLIMIT1000,10) T2,

       ALI_MALL_USER T1 FORCEINDEX(PRIMARY)

 WHERE T1.ID= T2.ID;

 

 

2.      Mysql中子查询不可用（非常低效）

比如下面的sql千万不能用

SELECT T.*

  FROM TEST_USER T

 WHERE USER_IDIN

       (SELECT A.USER_ID

          FROM ALL_USERS A

         WHERE A.USER_ID= T.USER_ID USER_NAME='abc');

要改写成

SELECT T.*

  FROM TEST_USER T, ALL_USERS A

 WHERE A.USER_ID= T.USER_ID

   AND USER_NAME='abc'

 

Update语句同样

这个写法是不允许的

UPDATE TEST_USER T

   SET CC='**'

 WHERE USER_IDIN

       (SELECT A.USER_ID

          FROM ALL_USERS A

         WHERE A.USER_ID= T.USER_ID USER_NAME='abc');

 

需要改成：

UPDATE TEST_USER T, ALL_USERS A

   SET T.CC='**'

 WHERE A.USER_ID= T.USER_ID

   AND USER_NAME='abc'

 

--推荐 先select出id，在根据ID进行更新，可以有效的提高效率、降低锁的概率

 

3.      外连接写法,（尽量少用外连接，效率低）

Oracle写法

SELECT E.NAME, D.NAME

  FROM EMPLOYEES E, DEPARTMENTS D

 WHERE E.DEPT_ID= D.ID(+);

 Mysql写法

SELECT E.NAME, D.NAME

  FROM EMPLOYEES E

  LEFTOUTERJOIN DEPARTMENTS D

    ON E.DEPT_ID= D.ID;

 

 

4.      使用主键进行更新

一般更新数据建议根据主键ID进行，可以有效降低死锁的发生。

比如update xxx set status=1 where user_name=’aaa’ and ..

改下成 select id from xxx where user_name=’aaa’ and ..

        Update xxx set status = 1 where id in(…)

 

5.      每个表都需要增长类型的主键ID，主键必须使用数字类型，字段名建议是 “id”

 

6.      关键字 一般建表工具做了控制，另外可以参考 http://blog.163.com/tianhui_dai/blog/static/1739111432010115115643331/