最近我處理了一個(gè)項(xiàng)目的數(shù)據(jù)庫遷移，從oracle 11g遷移至mysql 5.7癞尚，其中的一項(xiàng)主要工作就是對(duì)代碼中的sql進(jìn)行改寫禾乘。這里針對(duì)兩個(gè)庫的不同點(diǎn)做一下總結(jié)，以備后查措近。
oracle與mysql之常用函數(shù)的區(qū)別：
nvl(xx, 0) ==> coalesce(xx, 0)
說明：返回第一個(gè)非空值溶弟。
to_char(xx) ==> cast(xx as char)
說明：轉(zhuǎn)換為char類型
to_char(xx,'yyyymmdd') ==> date_format(xx, '%Y%m%d')
說明：日期格式化，date_format具體參數(shù)查詢文檔
to_char(xx,'yyyyq') ==> concat(date_format(xx, '%Y'), quarter(xx))
說明：mysql date_format無法格式化季度瞭郑，需要借助quarter函數(shù)
to_number(xx) ==> cast(xx as unsigned integer)
說明：轉(zhuǎn)換為數(shù)字類類型辜御，unsigned integer為無符號(hào)整數(shù)
sysdate ==> now()
說明：獲取當(dāng)前時(shí)間
decode(cond, val1, res1, default) ==> case when cond = val1 then res1 else default end
說明：根據(jù)cond的值返回不同結(jié)果
trunc(xx, 2) ==> convert(xx, decimal(6,2))
說明：保留2位小數(shù)
wm_concat(xx) ==> group_concat(xx)
說明：列轉(zhuǎn)行
over() ==> 無
說明：別想了，mysql沒有開窗函數(shù)屈张，代碼實(shí)現(xiàn)吧
oracle與mysql之語法的區(qū)別：
connect by…start with ==> 無
說明：別想了擒权，mysql沒有遞歸查詢袱巨，代碼實(shí)現(xiàn)吧
rownum ==> limit
說明：分頁
'a'||'b' ==> concat('a', 'b')
說明：字符串拼接
select xx from (select xx from a) ==> select xx from (select xx from a) t1
說明：from后的子查詢必須有別名
nulls last ==> 無
說明：mysql排序時(shí)，認(rèn)為null是最小值碳抄，升序時(shí)排在最前面愉老，降序時(shí)排在末尾
group by (a, b) ==> group by a, b
說明：mysql group by 字段不能加括號(hào)
begin end; ==> begin; commit;
說明：mysql事務(wù)控制begin后需要加分號(hào)執(zhí)行，提交使用commit剖效。P.S.禁止在sql中進(jìn)行事務(wù)控制
select 1, 1 from dual
union
select 1, 1 from dual
==>
select 1 as a, 1 as b from dual
union
select 1 as a, 1 as b from dual
說明：mysql的union查詢的每個(gè)字段名必須不同
mapper.xml:
<selectKey resultType="java.lang.Integer" keyProperty="id" order="BEFORE" >
select seq_VEM_ORG.nextval from dual
</selectKey>
insert into VEM_ORG (ID, NAME)
values (#{id,jdbcType=DECIMAL}, #{name,jdbcType=VARCHAR})
==>
<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER" >
SELECT LAST_INSERT_ID()
</selectKey>
insert into vem_org (NAME)
values (#{name,jdbcType=VARCHAR})
說明：oracle使用sequence嫉入，在插入新值前查詢下一個(gè)id。mysql使用自增主鍵璧尸，插入新值時(shí)無須顯示為id賦值咒林。在插入新值后通過SELECT LAST_INSERT_ID()可獲取最新插入的值的id
mysql 5.7新特性帶來的好處：
mysql 5.7新增了計(jì)算字段generated column，可以根據(jù)一般字段動(dòng)態(tài)地生成計(jì)算字段中的值逗宁。彌補(bǔ)了mysql沒有函數(shù)索引的缺憾映九。在我們這次遷移過程中，我們把order表加上了create_day(20171207), pay_day(20171207), pay_month(201712), pay_year(2017), pay_quarter(2017Q4)五個(gè)計(jì)算字段瞎颗，并且加上了索引件甥，如果有要根據(jù)這些時(shí)間粒度進(jìn)行匯總的sql可以直接使用這些字段進(jìn)行g(shù)roup by，通過走索引增加效率哼拔。這幾個(gè)字段的值并不需要人為插入引有，在插入create_datetime和pay_datetime的時(shí)候會(huì)自動(dòng)生成對(duì)應(yīng)的值，所以對(duì)現(xiàn)有代碼是透明的倦逐。
摘錄涉及到的建表語句如下：
CREATE TABLE ORDER
……
CREATE_DAY char(8) GENERATED ALWAYS AS (date_format(CREATE_DATETIME,'%Y%m%d')) STORED COMMENT '創(chuàng)建日譬正，格式：20171207',
PAY_DAY char(8) GENERATED ALWAYS AS (date_format(PAY_DATETIME,'%Y%m%d')) STORED COMMENT '支付日，格式：20171207',
PAY_MONTH char(6) GENERATED ALWAYS AS (date_format(PAY_DATETIME,'%Y%m')) STORED COMMENT '支付月檬姥，格式：201712',
PAY_QUARTER char(6) GENERATED ALWAYS AS (concat(year(PAY_DATETIME),'Q',quarter(CREATE_DATETIME))) STORED COMMENT '支付季度曾我，格式：2017Q4',
PAY_YEAR char(4) GENERATED ALWAYS AS (year(PAY_DATETIME)) STORED COMMENT '支付年，格式：2017'
……
KEY IDX_CREATE_DAY (CREATE_DAY),
KEY IDX_PAY_DAY (PAY_DAY),
KEY IDX_PAY_MONTH (PAY_MONTH),
KEY IDX_PAY_QUARTER (PAY_QUARTER),
KEY IDX_PAY_YEAR (PAY_YEAR)
……
