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) |
判空 | nvl | IsNull(filed) |
去日期 | sysdate | now() |
字符串拼接 | concat('11','12') | 1. '11' + '12' |
求子串 | substr('1122',2) | substring('1122',2) |
首字母大写 | initcap('abcd') | 无 |
日期计算 | sysdate+8/60/24 | SELECT DATE_ADD(now(), INTERVAL 1 DAY); |
求字符 | chr(97) | char(97) |
decode | decode | 1. case when then |
字符日期转换 | 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') | FORMAT(1210.73, 1) (出来的字符串自动带“,”分隔) |
字符串到数值转换 | to_number('123') | conv('123',10,10) |
常见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/