[mysql使用(2)] mysql的一些语法与Oracle的差别

一、表空间

mysql的表空间有共享表空间和独占表空间，独占表空间，其实就是一张表一个表空间，其实也就是一张表一个数据文件，共享表空间似乎有点类似oracle的表空间，不同的表可以保存在同一个数据文件里，一般mysql没有单独创建表空间的步骤而oracle要先创建好表空间，然后指定某张表放到某个表空间。

二、数据类型

1、oracle表结构移植到mysql中需要的类型转换

MYSQL                  ORACLE
BLOB（220）             RAW(220)
BLOB （20）             RAW(20)
BLOG(1024)             RAW(1024)
VARCHAR(n)             VARCHAR2(n)
CHAR                   CHAR
FLOAT(22,6)            NUMBER(22,6)
DOUBLE(44,12)          NUMBER(44,12)
TINYINT (3)            NUMBER(3)
SMALLINT(5)            NUMBER(5)
MEDIUMINT(8)           NUMBER(8)
INT(10)                NUMBER(10)
BIGINT(20)             NUMBER(20)
DATATIME               DATA

　

2、mysql和oracle数据类型对应关系参照表

MySQL Data Type	Oracle Data Type
BIGINT	NUMBER(19, 0)
BIT	RAW
BLOB	BLOB, RAW
CHAR	CHAR
DATE	DATE
DATETIME	DATE
DECIMAL	FLOAT (24)
DOUBLE	FLOAT (24)
DOUBLE PRECISION	FLOAT (24)
ENUM	VARCHAR2
FLOAT	FLOAT
INT	NUMBER(10, 0)
INTEGER	NUMBER(10, 0)
LONGBLOB	BLOB, RAW
LONGTEXT	CLOB, RAW
MEDIUMBLOB	BLOB, RAW
MEDIUMINT	NUMBER(7, 0)
MEDIUMTEXT	CLOB, RAW
NUMERIC	NUMBER
REAL	FLOAT (24)
SET	VARCHAR2
SMALLINT	NUMBER(5, 0)
TEXT	VARCHAR2, CLOB
TIME	DATE
TIMESTAMP	DATE
TINYBLOB	RAW
TINYINT	NUMBER(3, 0)
TINYTEXT	VARCHAR2
VARCHAR	VARCHAR2, CLOB
YEAR	NUMBER

　

三、常用函数比较

1、返回当前时间的时间戳。

Oracle ：( (in_DATETIME -TO_DATETIME('19700101','yyyymmdd'))*86400 - TO_INT(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)
mysql  ：( timestampdiff(second,'19700101',in_DATETIME) - timestampdiff(second,utc_time(),now()))　

2、日期前一天

Oracle ： sysdate - 1;
mysql  :  date_sub(curdate(),interval 1 day);

3、trim函数

trim('  xxxx  ')、ltrim(' xxxx ')、rtrim('  xxxx  ')            #Oracle与mysql用法一致都是去除两边、左边、右边的空格。 

Oracle
trim('1112222667671122','12') 、ltrim('1112222667671122','12')、rtrim('1112222667671122','12')    #oracle用法是去除两边开始、左边开始、右边开始起的所有包含第二个字符串中的字符，所以结果为66767、667671122、111222266767。 

mysql要想实现这个方法也可以，但是mysql只提供了trim一个方法，至于其他的就要自己写函数了。
TRIM('12' FROM '11122342341122')     #2234234

　　

　

其他函数一览表：

编号	类别	ORACLE	MYSQL	注释
1	数字函数	round(1.23456,4)	round(1.23456,4)	一样： ORACLE：select round(1.23456,4) value from dual MYSQL：select round(1.23456,4) value
2		abs(-1)	abs(-1)	功能: 将当前数据取绝对值 用法: oracle和mysql用法一样 mysql: select abs(-1) value oracle: select abs(-1) value from dual
3		ceil(-1.001))	ceiling(-1.001)	功能: 返回不小于 X 的最小整数 用法: mysqls: select ceiling(-1.001) value oracle: select ceil(-1.001) value from dual
4		floor(-1.001)	floor(-1.001)	功能: 返回不大于 X 的最大整数值 用法: mysql: select floor(-1.001) value oracle: select floor(-1.001) value from dual
5		Max(expr)/Min(expr)	Max(expr)/Min(expr)	功能:返回 expr 的最小或最大值。MIN() 和 MAX() 可以接受一个字符串参数；在这 种情况下，它们将返回最小或最大的字符串传下。 用法:  ROACLE: select max(user_int_key) from sd_usr; MYSQL: select max(user_int_key) from sd_usr;
6	字符串函数	ascii(str)	ascii(str)	功能:返回字符串 str 最左边的那个字符的 ASCII 码值。如果 str 是一个空字符串， 那么返回值为 0。如果 str 是一个 NULL，返回值也是 NULL. 用法:  mysql:select ascii('a') value oracle:select ascii('a') value from dual
7		CHAR(N,...)	CHAR(N,...)	功能:CHAR() 以整数类型解释参数，返回这个整数所代表的 ASCII 码值给出的字符 组成的字符串。NULL 值将被忽略. 用法:  mysql:select char(97) value oracle:select chr(97) value from dual
8		REPLACE(str,from_str,to_str)	REPLACE(str,from_str,to_str)	功能: 在字符串 str 中所有出现的字符串 from_str 均被 to_str 替换，然后返回这个字符串. 用法:  mysql: SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value  oracle: SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual
9		INSTR('sdsq','s',2)	INSTR('sdsq','s')	参数个数不同 ORACLE: select INSTR('sdsq','s',2) value from dual（要求从位置2开始） MYSQL: select INSTR('sdsq','s') value（从默认的位置1开始）
10		SUBSTR('abcd',2,2)	substring('abcd',2,2)	函数名称不同： ORACLE: select substr('abcd',2,2) value from dual MYSQL: select substring('abcd',2,2) value
11		instr(‘abcdefg’,’ab’)	locate(‘ab’,’abcdefg’)	函数名称不同： instr -> locate（注意：locate的子串和总串的位置要互换） ORACLE: SELECT instr('abcdefg', 'ab') VALUE FROM DUAL MYSQL: SELECT locate('ab', 'abcdefg') VALUE
12		length（str）	char_length()	函数名称不同： ORACEL: SELECT length('AAAASDF') VALUE FROM DUAL MYSQL: SELECT char_length('AAAASDF') VALUE
13		REPLACE('abcdef', 'bcd', 'ijklmn')	REPLACE('abcdef', 'bcd', 'ijklmn')	一样： ORACLE: SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value from dual MYSQL: SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value
14		LPAD('abcd',14, '0')	LPAD('abcd',14, '0')	一样： ORACLE: select LPAD('abcd',14, '0') value from dual MYSQL: select LPAD('abcd',14, '0') value from dual
15		UPPER(iv_user_id)	UPPER(iv_user_id)	一样： ORACLE: select UPPER(user_id) from sd_usr; MYSQL: select UPPER(user_id) from sd_usr;
16		LOWER(iv_user_id)	LOWER(iv_user_id)	一样： ORACLE: select LOWER(user_id) from sd_usr; MYSQL: select LOWER(user_id) from sd_usr;
17	控制流函数	nvl(u.email_address, 10)	IFNULL(u.email_address, 10) 或 ISNULL(u.email_address)	函数名称不同（根据不同的作用进行选择）： ORACLE: select u.email_address, nvl(u.email_address, 10) value from sd_usr u (如果u.email_address=NULl,就在DB中用10替换其值) MYSQL: select u.email_address, IFNULL(u.email_address, 10) value from sd_usr u(如果u.email_address=NULl,显示结果中是10，而不是在DB中用10替换其值) select u.email_address, ISNULL(u.email_address) value from sd_usr u（如果u.email_address是NULL, 就显示1<true>,否则就显示0<false>）
18		DECODE(iv_sr_status,g_sr_status_com, ld_sys_date, NULL)	无，请用IF或CASE语句代替. IF语句格式:(expr1,expr2,expr3)	说明:  1. decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 该函数的含义如下： IF 条件=值1 THEN 　　　　RETURN(翻译值1) ELSIF 条件=值2 THEN 　　　　RETURN(翻译值2) 　　　　...... ELSIF 条件=值n THEN 　　　　RETURN(翻译值n) ELSE 　　　　RETURN(缺省值) END IF 2. mysql If语法说明 功能: 如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在 语境而定。 用法:  mysql: SELECT IF(1>2,2,3);
19	类型转换函数	TO_CHAR(SQLCODE)	date_format/ time_format	函数名称不同 SQL> select to_char(sysdate,'yyyy-mm-dd') from dual; SQL> select to_char(sysdate,'hh24-mi-ss') from dual; mysql> select date_format(now(),'%Y-%m-%d'); mysql> select time_format(now(),'%H-%i-%S');
20		to_date(str,format)	STR_TO_DATE(str,format)	函数名称不同： ORACLE:SELECT to_date('2009-3-6','yyyy-mm-dd') VAULE FROM DUAL MYSQL: SELECT STR_TO_DATE('2004-03-01', '%Y-%m-%d') VAULE
21		trunc(-1.002)	cast(-1.002 as SIGNED)	函数名称不同： TRUNC函数为指定元素而截去的日期值。 ORACLE： select trunc(-1.002) value from dual MYSQL：select cast(-1.002 as SIGNED) value MYSQL： 字符集转换 :   CONVERT(xxx  USING   gb2312) 类型转换和SQL Server一样,就是类型参数有点点不同  : CAST(xxx  AS   类型)  ,   CONVERT(xxx,类型)，类型必须用下列的类型：     可用的类型　     二进制,同带binary前缀的效果 : BINARY      字符型,可带参数 : CHAR()       日期 : DATE       时间: TIME       日期时间型 : DATETIME       浮点数 : DECIMAL        整数 : SIGNED       无符号整数 : UNSIGNED
22		TO_NUMBER(str)	CAST("123" AS SIGNED INTEGER)	函数名称不同 ORACLE:SELECT TO_NUMBER('123') AS VALUE FROM DUAL; MYSQL: SELECT CAST("123" AS SIGNED INTEGER) as value; SIGNED INTEGER:带符号的整形
23	日期函数	SYSDATE	now() / SYSDATE()	写法不同： ORACLE:select SYSDATE value from dual MYSQL:select now() value select sysdate() value
24		Next_day(sysdate,7)	自定义一个函数:F_COMMON_NEXT_DAY(date,int)	函数名称不同： ORACLE: SELECT Next_day(sysdate,7) value FROM DUAL MYSQL: SELECT F_COMMON_NEXT_DAY(SYSDATE(), 3) value from DUAL; (3:指星期的索引值)返回的指定的紧接着下一个星期的日期
25		ADD_MONTHS(sysdate, 2)	DATE_ADD(sysdate(), interval 2 month)	函数名称不同: ORACLE: SELECT ADD_MONTHS(sysdate, 2) as value from DUAL; MYSQL: SELECT DATE_ADD(sysdate(), interval 2 month) as value from DUAL;
26		2个日期相减(D1-D2)	DATEDIFF(date1,date2)	功能: 返回两个日期之间的天数。 用法: mysql: SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate oracle: 直接用两个日期相减（比如d1-d2=12.3）
27	SQL函数	SQLCODE	MYSQL中没有对应的函数，但JAVA中SQLException。getErrorCode()函数可以获取错误号	Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。 MYSQL: 可以从JAVA中得到错误代码，错误状态和错误消息
28		SQLERRM	MYSQL中没有对应的函数，但JAVA中SQLException。getMessage()函数可以获取错误消息	Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。 MYSQL: 可以从JAVA中得到错误代码，错误状态和错误消息
29		SEQ_BK_DTL_OPT_INT_KEY.NEXTVAL	自动增长列	在MYSQL中是自动增长列. 如下方法获取最新ID:   START TRANSACTION;         INSERT INTO user(username,password)       VALUES (username,MD5(password));     SELECT LAST_INSERT_ID() INTO id;   COMMIT;
30		SUM(enable_flag)	SUM(enable_flag)	一样： ORCALE： SELECT SUM(enable_flag) FROM SD_USR; MYSQL： SELECT SUM(enable_flag) FROM SD_USR;
31		DBMS_OUTPUT.PUT_LINE(SQLCODE)	在MYSQL中无相应的方法，其作用是在控制台中打印，用于测试，对迁移无影响。	db

四、其他差别

1、在mysql5.6版本中，删除数据的时候不能加入别名，在Oracle中这样是可以的。

delete from Wooyun_Threat_Info w where w.wooyunId ='efg';
delete from Wooyun_Threat_Info as w where w.wooyunId ='efg';      #这两种都是错误的，

若想加入别名则可以用下面的方式

delete from w using Wooyun_Threat_Info w where w.wooyunId ='10'
delete w from Wooyun_Threat_Info w where w.wooyunId ='11';

2、对于Oracle与mysql主键增长的问题

Oracle ： select seq_vul_risk_task_info.nextval as taskid from dual
mysql  :  select max(auto_increment) from information_schema.TABLES where table_name='vul_risk_task_info'      #由于有时候在mysql里面auto_increment的值可能不止一个，所以就要取最大值。

但是对于Oracle来说每调用一次.nextval后，序列值就会加1，而mysql却没有若也想达到此目的，需要以下设置自增值。

alter table xx表名 AUTO_INCREMENT = yyy       #yyy一定得是正整数才可以，这个是设置xx表的自增值。

　　

1，oracle没有offet,limit，在mysql中我们用它们来控制显示的行数，最多的是分页了。oracle要分页的话，要换成rownum。

2，oracle建表时，没有auto_increment，所有要想让表的一个字段自增，要自己添加序列，插入时，把序列的值，插入进去。

3，oracle有一个dual表，当select后没有表时，加上的。不加会报错的。select 1 这个在mysql不会报错的，oracle下会。select 1 from dual这样的话，oracle就不会报错了。

4，对空值的判断，name != ""这样在mysql下不会报错的，但是oracle下会报错。在oracle下的要换成name is not null

5，oracle下对单引号，双引号要求的很死，一般不准用双引号，用了会报

ERROR at line 1:
ORA-00904: "t": invalid identifier

而mysql要求就没有那么严格了，单引号，双引号都可以。

6，oracle有to_number,to_date这样的转换函数，oracle表字段是number型的，如果你$_POST得到的参数是123456，入库的时候，你还要to_number来强制转换一下，不然后会被当成字符串来处理。而mysql却不会。

7，group_concat这个函数，oracle是没有的，如果要想用自已写方法。

8，mysql的用户权限管理，是放到mysql自动带的一个数据库mysql里面的，而oracle是用户权限是根着表空间走的。

9，group by,在下oracle下用group by的话，group by后面的字段必须在select后面出现，不然会报错的，而mysql却不会。

10，mysql存储引擎有好多，常用的mysiam,innodb等，而创建oracle表的时候，不要这样的，好像只有一个存储引擎。

11，oracle字段无法选择位置，alter table add column before|after，这样会报错的，即使你用sql*plus这样的工具，也没法改字段的位置。

12，oracle的表字段类型也没有mysql多，并且有很多不同，例如：mysql的int,float合成了oracle的number型等。

13，oracle查询时from 表名后面 不能加上as 不然会报错的，select t.username from test as t而在mysql下是可以的。

14，oracle中是没有substring这个函数的，mysql有的。