oracle转mysql问题总结(oracle和mysql函数语法区别)_mysql不能使用的oracle函数-CSDN博客

文章目录

1. 函数问题

这里列出所有遇到需要修改的函数

函数	oracle	mysql
字符串转日期	to_date(date,‘yyyy-mm-dd’)	str_to_date(date,’%Y-%m-%d’)
日期转字符串	to_char(date,‘yyyy-mm-dd’)	date_format(date,’%Y-%m-%d’)
字符串拼接	name`\|\|`age	concat(name,age)或CONCAT_WS(’’,name,age)
ask码	char(10),char(13)	无
字符串长度	LENGTHB	length
获取当前日期	sysdate	sysdate()
正则表达式	REGEXP_LIKE	REGEXP
分页	ROWNUM	LiMIT
字符串转数字	to_number()	cast( '123 ’ as SIGNED INTEGER) 或者cast( ‘12.32’ as decimal(10,2))
字符串截取	substrb	substring

ps:字符串拼接建议使用 CONCAT_WS,原因请看这里

2. 视图问题

mysql视图不支持子查询：

View’s SELECT contains a subquery in the FROM clause

例子：
oracle

CREATE OR REPLACE  VIEW view_test AS
select * from (select * from t2a_cust_c t);

mysql

DROP view IF EXISTS view_test_mid;
create or REPLACE VIEW view_test_mid as
select * from t2a_cust_c;

DROP view IF EXISTS view_test;
CREATE OR REPLACE  VIEW view_test AS
select * from view_test_mid t;

这里做个中间视图处理就好

分区表问题

mysql简历分区的列必须为主键
oracle建立分区的列不需要为主键

3. 查询问题

Every derived table must have its own alias
每个派生表都必须有自己的别名
例子:

-- oracle 是合适的
select * from (select * from t2a_cust_c t)
-- mysql 这里必须给子查询的结果取个别名
select * from (select * from t2a_cust_c t) t

full join
mysql 没有full join ，看其他的人建议是用left join 和right join 加 union代替,个人感觉不是完全能替换
row_number() over(partition by 列名1 order by 列名2 desc)
oracle 有 row_number() 函数，mysql 具体转换可以参考一下公式:

-- oracle
SELECT
   COUNT(*) OVER (PARTITION BY T.CASE_ID) AS CNT,
   T.CASE_ID,
   T.CUST_ID
  FROM
   T3A_CASE_TRANS_MID T
-- mysql
SELECT
	T.CASE_ID,T.CUST_ID,
	( SELECT cnt FROM
			( SELECT T1.CASE_ID, count(*) AS CNT
				FROM
					T3A_CASE_TRANS_MID t1
				GROUP BY
					T1.CASE_ID
			) x
		WHERE
			x.CASE_ID = t.CASE_ID
	) AS CNT
FROM
	T3A_CASE_TRANS_MID t
ORDER BY
	CASE_ID

1、通用公式

--分组排序加序号
--ORACLE:
SELECT 
tion.分组字段名称,
ROW_NUMBER() OVER(PARTITION BY tion.分组字段名称 ORDER BY 排序字段名称) AS 
FROM 目标表名称 tion ;
-- MYSQL:
SELECT
  tion.分组字段名称,
  @last := IF(@first = tion.分组字段名称, @last + 1, 1),
  @first := tion.分组字段名称
FROM 目标表名称 tion,
     (SELECT
         @last := 0,
         @first := NULL) c
ORDER BY 分组字段名称, 排序字段名称

2、ROW_NUMBER() OVER(order by x desc)

-- 根据X排序，不分组，取序号
--ORACLE:
select day_dt,row_number() over(order by day_dt desc) as row_number from t1a_workday;  
--MYSQL:
SELECT day_dt,(@row_number:=@row_number+1) AS row_number FROM t1a_workday a,(SELECT (@row_number:=0))b ORDER BY day_dt desc;

4. 条件控制函数

oracle decode 和case when都支持
但是mysql没有 decode，需要转换为decode
例子：

-- oracle 
select T.ACCT_ID, T.CUST_ID,
DECODE(T.DEBIT_CREDIT, 'D', T_CNT, '0') D_CNT,
DECODE(T.DEBIT_CREDIT, 'D', T_SUM, '0') D_SUM
from student
-- mysql
SELECT T.ACCT_ID, T.CUST_ID,
case T.DEBIT_CREDIT when 'D' then T_CNT else '0' end D_CNT,
case T.DEBIT_CREDIT when 'D' then T_SUM else '0' end D_SUM from student

5. delete 取别名问题

-- oracle delete取别名
delete from T3H_ALERT_TRANS t;
-- mysql 取别名
delete t from T3H_ALERT_TRANS t;

6. 一个表数据去更新另一个表数据

单个字段插入

-- oracle
UPDATE T3B_MULTI_CUST_TEMP T
SET (CUST_NAME, CUST_TYPE) = (
	SELECT
		CUST_NAME,
		CUST_TYPE
	FROM
		V2A_CUST T2
	WHERE
		T.CUST_ID = T2.CUST_ID
)
-- mysql
UPDATE T3B_MULTI_CUST_TEMP T
SET CUST_NAME = (
	SELECT
		CUST_NAME
	FROM
		V2A_CUST T2
	WHERE
		T.CUST_ID = T2.CUST_ID
),
 CUST_TYPE = (
	SELECT
		CUST_TYPE
	FROM
		V2A_CUST T2
	WHERE
		T.CUST_ID = T2.CUST_ID)

多个字段插入:

-- oracle
UPDATE TMP_SEIF_N T1
SET (
	T1.SEVC, 
	T1.SRNM,
	T1.SRID,
	T1.SCNM,
	T1.SCID
) = (
	SELECT
		B.PBC_INDUS,
		B.LEGAL_NAME,
        B.LEGAL_CERT_NO,
        B.HOLD_NAME,
        B.HOLD_CERT_NO
FROM
	T2A_CASE_CUST_CUR B
WHERE
	T1.CUST_ID = B.CUST_ID
AND B.CUST_TYPE = 'C'
)
WHERE
	T1.CUST_TYPE = 'C'

-- mysql
UPDATE TMP_SEIF_N T1
INNER JOIN (
	SELECT
		B.CUST_ID,
		B.CUST_TYPE,
		B.PBC_INDUS AS SEVC,
		B.LEGAL_NAME AS SRNM,
 		B.LEGAL_CERT_NO AS SRID,
 		B.HOLD_NAME AS SCNM,
 		B.HOLD_CERT_NO AS SCID
FROM
	T2A_CASE_CUST_CUR B
) B ON T1.CUST_ID = B.CUST_ID
AND B.CUST_TYPE = 'C'
SET T1.SEVC = B.SEVC,
 T1.SRNM = B.SRNM,
 T1.SRID = B.SRID,
 T1.SCNM = B.SCNM,
 T1.SCID = B.SCID
WHERE
	T1.CUST_TYPE = 'C'

三表关联：

UPDATE T2A_DPST_ACCT_I T
       SET (T.ACCT_TYPE,
            T.OPEN_DT,
            T.CLOSE_DT,
            T.OPEN_TM,
            T.CLOSE_TM,
            T.CARD_NO,
            T.CARD_STYLE,
            T.OTH_CARD_STYLE) =
           (SELECT T1.ACCT_TYPE,
                   T1.OPEN_DT,
                   T1.CLOSE_DT,
                   T1.OPEN_TM,
                   T1.CLOSE_TM,
                   T1.CARD_NO,
                   T1.CARD_STYLE,
                   T1.OTH_CARD_STYLE
              FROM T2A_CASE_ACCT_CUR T1, T2A_ACCT_MID T2
             WHERE T1.ACCT_ID = T2.ACCT_ID
               AND T2.ACCT_ID = T.ACCT_ID)
     WHERE EXISTS
     (SELECT 'X' FROM T2A_ACCT_MID TT WHERE T.ACCT_ID = TT.ACCT_ID)

 UPDATE T2A_DPST_ACCT_I T, T2A_CASE_ACCT_CUR T1, T2A_ACCT_MID T2
             SET T.ACCT_TYPE      = T1.ACCT_TYPE,
                 T.OPEN_DT        = T1.OPEN_DT,
                 T.CLOSE_DT       = T1.CLOSE_DT,
                 T.OPEN_TM        = T1.OPEN_TM,
                 T.CLOSE_TM       = T1.CLOSE_TM,
                 T.CARD_NO        = T1.CARD_NO,
                 T.CARD_STYLE     = T1.CARD_STYLE,
                 T.OTH_CARD_STYLE = T1.OTH_CARD_STYLE
           WHERE T.ACCT_ID = T1.ACCT_ID
             AND T1.ACCT_ID = T2.ACCT_ID

7. 不要使用子查询，能用连接查询的就用连接查询(具体性能优化问题请用explain分析)

子查询会创建一张临时表，查询完又要删除这个临时表，所以效率非常低下，不推荐使用

例子

DELETE T FROM T2A_CASE_CUST_CUR T
        WHERE EXISTS
        (SELECT 'X' FROM T2A_CUST_MID TT WHERE T.CUST_ID = TT.CUST_ID)

修改为:

 DELETE T FROM T2A_CASE_CUST_CUR T, T2A_CUST_MID TT
         WHERE T.CUST_ID = TT.CUST_ID

8. 字段长度问题

字段长度问题可以看这个例子

9.null 和`''`

oracle 中null 和’'是一样的
mysql中 null是null，''是''不是null

varhar(10) 和 varchar2(10) 的区别

oracle中10表示10个字节，字母和数字占一个字节，中文占3个字节（utf-8）
mysql 10就便是能存储10个汉字或10个数字或字母

10. DATE时间类型问题

oracle date 类型可以包含时分秒，mysql date没有时分秒有时分秒需要使用datetime