What is the syntax difference between database oracle and mysql

Difference: 1. MySQL does not have a Number type, but Oracle does; 2. The Date type in MySQL only represents date, and the Date type in Oracle represents date and time; 3. To obtain the length of a string, Oracle uses length(), MySQL uses char_length() and so on.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Recently I need to migrate the project and change the database from Oracle to MySQL. Since the syntax of the two is partially different, it is necessary to change the functions/types that can be used in Oracle but not in MySQL to those that can be used in MySQL. The following is a summary of some of the syntax differences:

1. Data type

1. Number type

There is no Number type in MySQL, but there is int/ Decimal type, Number(5,1) in Oracle corresponds to decimal(5,1) in MySQL, and Number(5) corresponds to int(5).

There are many numeric types in MySQL, and they are divided into more detailed categories, including tinyint, smallint, mediumint, bigint and other types

2. Varchar2(n) type

The alternative type corresponding to the Oracle Varchar2(n) type in MySQL is the varchar(n) type.

3. Date type

The date and time types in MySQL include Date, Time, Datetime and other types. The Date type in MySQL only represents the date (year-month-day ), the Time type only represents time (hour:minute:second), while the Datetime type represents date and time (year-month-day hour:minute:second). The Date type in Oracle is consistent with the Datetime type in MySQL.

2. Function

1. length(str) function

length in Oracle (str) is a function to obtain the length of a string. The corresponding function in MySQL is char_length(str).

2. sys_guid() function

Oracle can use the sys_guid() function to generate a random sequence, and MySQL can generate a random sequence through UUID().

3. Time formatting function

Convert time to string time MySQL date_format(NOW(),'%Y-%m-%d') Corresponds to Oracle's to_char(sysdate, 'YYYY-MM-DD');

Convert string time to time type MySQL str_to_date('2019-01-01','%Y-% m-%d') corresponds to to_date('2019-01-01', 'YYYY-MM-DD');

in Oracle including function conversion of hours, minutes and seconds: DATE_FORMAT(NOW(),'% Y-%m-%d %H:%i:%s'), str_to_date('2019-01-01','%Y-%m-%d %H:%i:%s').

4. Conditional functions (nvl(), nvl2(), decode())

nvl(tab.columnName, 0): If the value of tab.columnName is If it is empty, the return value will be 0, otherwise it will be tab.columnName; the corresponding MySQL function is: ifnull(tab.columnName, 0).

nvl2(expr1,expr2,expr3): If expr1 is not null, return expr2, otherwise return expr3; the corresponding MySQL function is: if(expr1,expr2,expr3).

DECODE(value, val1, val2, val3): If value is equal to val1, return val2, otherwise return val3; MySQL can be represented by the IF function: if (value=val1, val2, val3);

DECODE(value, if1, val1, if2,val2,...,ifn, valn, val): If value is equal to if1, return val1, if value is equal to if2, return value2...If value is equal to ifn , then return valn, otherwise return val; MySQL can judge this kind of judgment through case when then else end;l, that is: case when value=if1 then val1 when value=if2 then val2,,,when value=ifn then valn else val end;

5. trunc() function

TRUNC(12.123): Returns an integer (12); MySQL corresponding function: truncate(12.123, 0) ;

TRUNC(12.123, 2): The return value keeps 2 as a decimal (12.12); MySQL corresponding function: truncate(12.123, 2);

TRUNC(SYSDATE): The return value is (2019-07-26 00:00:00); MySQL corresponds to cast(now() as datetime): the return value is (2019-07-26 14:11:38);

MySQL The cast function syntax is: CAST (xxx AS type) (The available types are: binary, with the effect of binary prefix: BINARY; character type, with parameters: CHAR(); date: DATE; time: TIME; date and time type: DATETIME; floating point number: DECIMAL; integer: SIGNED; unsigned integer: UNSIGNED)

6. to_char() to_number()

to_char(123): Convert the number to Convert 123 to string 123; the corresponding function in MySQL is CAST(123 AS CHAR(3));

to_number('123'): Convert the string number 123 to a numeric type; the corresponding function in MySQL is cast('123' as SIGNED);

7. sysdate current time

      sysdate: Returns the current date and time; the corresponding function for MySQL is now();

3. Others

1 . Quotation marks

MySQL can recognize double quotes and single quotes, Oracle can only recognize single quotes.

2. String concatenation ||

Oracle can use '||' to connect strings, but MySQL does not support '||' connection. MySQL can use '||' to connect strings. The concat() function concatenates strings.

Oracle's a.studentname||'['||a.studentno||']' is equivalent to MySQL's concat(a.studentname, '[', a.studentno, ']')

3. ROWNUM

Oracle can get the first n records through rownum, and MySQL can get the first n records through limit, but the writing methods of the two are slightly different. In Oracle Rownum is used as part of the where condition, while limit in MySQL is not part of the where condition.

-- rownum语法如下：
SELECT * FROM XJ_STUDENT WHERE ROWNUM = 1; -- 查询第一条数据
SELECT * FROM XJ_STUDENT WHERE ROWNUM <= 10; -- 获取前10条数据
-- 但rownum不支持查询后几条或第n(n>1)条数据，例如以下sql是不支持的
SELECT * FROM XJ_STUDENT WHERE ROWNUM > 2;
SELECT * FROM XJ_STUDENT WHERE ROWNUM = 3;

-- limit 语法如下：
SELECT * from fw_department limit 3; -- 查询前3条数据
SELECT * from fw_department limit 2, 4; -- 从第2(序号从0开始)条开始，查4条记录

4. Null data sorting (nulls first and nulls last)

-- null值排在最前
SELECT * FROM FW_DEPARTMENT A ORDER BY A.REMARK DESC NULLS FIRST
-- null值排在最后
SELECT * FROM FW_DEPARTMENT A ORDER BY A.REMARK DESC NULLS LAST
 
-- MySQL 可通过IF和ISNULL函数达到相同的效果
-- null值排在最后
select * from FW_DEPARTMENT A order by IF(ISNULL(A.REMARK),1,0),A.REMARK desc
-- null值排在最前
select * from FW_DEPARTMENT A order by IF(ISNULL(A.REMARK),0,1),A.REMARK desc

5. Table (left/right) association ( )

Oracle left join and right join can be implemented using (). MySQL can only use left join, right join and other keywords.

-- Oracle 左关联
select * from taba, tabb where taba.id = tabb.id(+);
-- Oracle 右关联
select * from taba, tabb where taba.id(+) = tabb.id;
-- MySQL 左关联
select * from taba left join tabb on taba.id=tabb.id;
-- MySQL 右关联
select * from taba right join tabb on taba.id=tabb.id;

6. Delete syntax

The delete syntax of MySQL is not as random as Oracle. For example, the following sql can be executed in Oracle, but not in MySQL.

-- Oracle 可执行，但MySQL中不能执行
DELETE FROM FW_DEPARTMENT A WHERE A.DEPID = '111';
DELETE FW_DEPARTMENT WHERE DEPID = '111';
-- MySQL中删除语句格式如下：
DELETE FROM FW_DEPARTMENT WHERE DEPID = '111';

7. Recursive query (start with connect by prior)

MySQL does not support this kind of recursive query (start with connect by prior), but it can be done automatically Define functions to implement.

-- Oracle 递归查询 查询部门ID为‘1111’的所有子部门（包含自身）
SELECT *
FROM FW_DEPARTMENT
START WITH DEPID='1111'
CONNECT BY PRIOR DEPID = PARENTDEPID;
-- Oracle 递归查询 查询部门ID为‘1111’的所有父部门（包含自身）
SELECT *
FROM FW_DEPARTMENT
START WITH DEPID='1111'
CONNECT BY PRIOR PARENTDEPID = DEPID;

-- MySQL 先创建fun_getDepIDList函数，用于查询部门ID字符串
CREATE FUNCTION fun_getDepIDList(rootId VARCHAR(32))
RETURNS VARCHAR(6000)
BEGIN 
	DECLARE pTemp VARCHAR(6000);
	DECLARE cTemp VARCHAR(6000);
	SET pTemp='$';
	SET cTemp=rootId;
	WHILE cTemp is not null DO
		set pTemp=CONCAT(pTemp,',',cTemp);
		SELECT GROUP_CONCAT(depid) INTO cTemp from fw_department
		WHERE FIND_IN_SET(PARENTDEPID,cTemp)>0;
	END WHILE;
	RETURN pTemp;
END;

-- 查询部门ID为‘1111’的所有子部门（包含自己）
select * from fw_department
where FIND_IN_SET(DEPID, fun_getDepIDList('1111'));

-- 查询部门ID为‘1111’的所有父部门(包含自己)
select * from fw_department
where FIND_IN_SET('1111', fun_getDepIDList(DEPID));

8. merge into

MySQL does not support (merge into), but the provided replace into and on duplicate key update can achieve similar functions.

-- Oracle merge into (有则修改，无则新增)
MERGE INTO TMPDEPTAB A
USING (SELECT '1111' DEPID, '哈哈' DEPNAME FROM DUAL) B
ON (A.DEPID = B.DEPID)
WHEN MATCHED THEN 
	UPDATE SET A.DEPNAME = B.DEPNAME
WHEN NOT MATCHED THEN 
	INSERT(DEPID, DEPNAME) VALUES(B.DEPID, B.DEPNAME);

-- MySQL replace into (特点：1、先删后增； 2、插入/更新的表必须有主键或唯一索引；
-- 3、未修改/新增的数据项，如果必填，则必须有默认值)
-- 1、由于是先删后增，所以需要满足以下2个条件之一：
--      1.要么必填项有默认值； 
--      2.要么插入/更新时为没有默认值的必填项赋值， 否则新增时会报错。
-- 2、表中需要有主键或唯一索引，否则下面语句如果执行多次，表中会出现重复数据。
replace into fw_department(DEPID,PARENTDEPID,DEPNO,DEPNAME) 
values('1111111', '1234','123', '哈哈');

-- MySQL on duplicate key update (特点：1、插入/更新的表必须有主键或唯一索引；
-- 2、未修改/新增的数据项，如果必填，则必须有默认值)
insert into fw_department(depid,parentdepid,depno,depname)
select '1111111' depid, '123' parentdepid, 'e12' depno, '哈哈哈哈' depname
from fw_department
on duplicate key 
update parentdepid = values(parentdepid),
	depno=values(depno),
	depname=values(depname);

9. with

Oracle can use with to build a temporary table, but MySQL does not support with. For the corresponding temporary table, MySQL can use parentheses way to handle it, but the temporary table built must be set with a temporary table name.

-- Oracle with使用
WITH TMPTAB AS (
	SELECT A.DEPID FROM FW_DEPARTMENT A
)
SELECT DEPID
FROM TMPTAB

-- MySQL 构建临时表使用(此处必须给括号中的临时表设置表名)
select b.depid
from (
	select depid
	from fw_department
) b

[Related recommendations: mysql video tutorial]

The above is the detailed content of What is the syntax difference between database oracle and mysql. For more information, please follow other related articles on the PHP Chinese website!