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
SELECTT1.ID,T1.MANAGER_NICK,T1.GMT_CREATE,T1.NICK
FROM(SELECTID
FROMALI_MALL_USER
WHEREMANAGER_NICK='我的小二'
ANDIS_OPEN=2
ORDERBYIDLIMIT1000,10)T2,
ALI_MALL_USER T1 FORCEINDEX(PRIMARY)
WHERET1.ID=T2.ID;
2. Mysql中子查询不可用(非常低效)
比如下面的sql千万不能用
SELECTT.*
FROMTEST_USER T
WHEREUSER_IDIN
(SELECTA.USER_ID
FROMALL_USERS A
WHEREA.USER_ID=T.USER_ID USER_NAME='abc');
要改写成
SELECTT.*
FROMTEST_USER T,ALL_USERS A
WHEREA.USER_ID=T.USER_ID
ANDUSER_NAME='abc'
Update语句同样
这个写法是不允许的
UPDATETEST_USER T
SETCC='**'
WHEREUSER_IDIN
(SELECTA.USER_ID
FROMALL_USERS A
WHEREA.USER_ID=T.USER_ID USER_NAME='abc');
需要改成:
UPDATETEST_USER T,ALL_USERS A
SETT.CC='**'
WHEREA.USER_ID=T.USER_ID
ANDUSER_NAME='abc'
--推荐先select出id,在根据ID进行更新,可以有效的提高效率、降低锁的概率
3. 外连接写法,(尽量少用外连接,效率低)
Oracle写法
SELECTE.NAME,D.NAME
FROMEMPLOYEES E,DEPARTMENTS D
WHEREE.DEPT_ID=D.ID(+);
Mysql写法
SELECTE.NAME,D.NAME
FROMEMPLOYEES E
LEFTOUTERJOINDEPARTMENTS D
ONE.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/