一、导读
一次需求将oracle数据库迁移到mysql数据库,期间有些注意点这里记录学习一下,不定期更新~~
二、不同点
- 查询数据库中的所有表
- oracle:
SELECT * FROM USER_TAB_COMMENTS A WHERE A.TABLE_NAME like ‘YW_%’ ORDER BY A.TABLE_NAME
- mysql:
SELECT TABLE_NAME, TABLE_COMMENT FROM Information_schema.TABLES A WHERE table_schema=‘fxbdb’ AND A.TABLE_NAME like ‘credit_%’ ORDER BY A.TABLE_NAME
- 查询表中的字段
- oracle:
SELECT * FROM USER_COL_COMMENTS A WHERE A.TABLE_NAME like ‘yv_%’ ORDER BY COLUMN_NAME ASC
- mysql:
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT FROM Information_schema.COLUMNS WHERE TABLE_NAME like ‘yv_%’ ORDER BY COLUMN_NAME ASC
- 日期和字符相互转换方法
date_format(date,’%Y-%m-%d’) -------------->oracle中的to_char();
str_to_date(date,’%Y-%m-%d’) -------------->oracle中的to_date();
- oracle 的 sysdate 对应mysql 的 current_date
- oracle row_number() 用法 mysql如何替换
oracle:
SELECT
*
FROM
(
SELECT
*
FROM
(
SELECT
t.,
row_number () over ( partition BY t.subject_id ORDER BY t.create_time DESC ) rn
FROM
credit_score t
WHERE
t.score_model_id = ‘402894e56816d7ca0168177d7ab301aa’
)
WHERE
1 = 1
AND rn = 1
)
ORDER BY
score DESC
mysql写法:
SELECT
*
FROM
(
SELECT
base_tmp.,
@rownum := @rownum + 1,
IF
( @puid = base_tmp.subject_id, @rank := @rank + 1, @rank := 1 ) AS NO,
@puid := base_tmp.subject_id
FROM
( SELECT * FROM credit_score WHERE score_model_id = ‘402894e56816d7ca0168177d7ab301aa’ ORDER BY subject_id ASC, create_time DESC ) base_tmp,
( SELECT @rownum := 0, @puid := NULL, @rank := 0 ) a
) result
WHERE
NO = 1
ORDER BY
score DESC