MySQL Oracle的SQL大对比

前言

  MySQL和Oracle的SQL语句有时候写起来不一样，比如什么地方用单引号 '，什么地方用双引号 "，甚至还有反引号 ` 等等令人挺纠结的问题，所以我来总结一下。

字段类型对比

  MySQL的字段类型，和Oracle的字段类型 “貌似” 不一样 (∩_∩)，这就来对比一下：

字段分类	数据库类型	字段类型	字段含义
数值类型	MySQL	TINYINT	占用空间： 1个字节(Byte) 数值范围： 0~255 Java Type： Integer
		SMALLINT	占用空间： 2个字节(Byte) 数值范围： -32768~32767 Java Type： Integer
		INT [常用]	占用空间： 4个字节(Byte) 数值范围： -231~231 - 1 Java Type： Integer
		BIGINT [常用]	占用空间： 8个字节(Byte) 数值范围： -263~263 - 1 Java Type： Long
		FLOAT	单精度浮点数 占用空间： 4个字节(Byte) Java Type： Float
		DOUBLE(m,d)	双精度浮点数，m(有效位数)和d(小数位数)，m d可以不写 占用空间： 8个字节(Byte) Java Type： Double
		DECIMAL(m,d) [常用]	占用空间： 依赖于m(有效位数)和d(小数位数)，m可以取0 ~ 65，n可以取0 ~ 30 Java Type： BigDecimal 需要注意： BigDecimal比较时，有scale的概念，所以即便是1.00与1.0也是不相同的。另外，在MySQL中设置DECIMAL(5,5)，然后Java查询出来的就是1.00000 (scale = 5) ，也就是说精度是完整的，不像Oracle会缩小精度
数值类型	Oracle	NUMBER(p,s) [常用]	p(有效位数)s(小数位数)，它是十进制精度 占用空间： 可以高达38位，其中p的默认值是38，s的默认值是0。如果把scale设成负数，Oracle将把该数字取舍到小数点左边的指定位数 Java Type： BigDecimal 需要注意： BigDecimal比较时，有scale的概念，所以即便是1.00与1.0也是不相同的。另外，你在Oracle设置NUMBER(5,5)，然后Java查询出来的是1 (也就是scale = 0) ，而不是1.00000，所以比较的时候，要注意设置scale。MySQL不会有这个问题
		DECIMAL(p,s)	它是NUMBER(p,s)的子类型 Java Type： BigDecimal 需要注意： BigDecimal比较时，有scale的概念，所以即便是1.00与1.0也是不相同的。另外，你在Oracle设置DECIMAL(5,5)，然后Java查询出来的是1 (也就是scale = 0) ，而不是1.00000 (scale = 5)，所以比较的时候，要注意设置scale。MySQL不会有这个问题
		FLOAT(n)	它是NUMBER(p,s)的子类型，n可以取1~126的值 Java Type： 未知
		BINARY_FLOAT	采用二进制精度，单精度浮点数字数据类型 占用空间： 32位，5字节存储空间，其中1字节用于存储数据值的长度 Java Type： 未知
		BINARY_DOUBLE	采用二进制精度，双精度浮点数字数据类型 占用空间： 64位，9字节存储空间，其中1字节用于存储数据值的长度 Java Type： 未知
字符类型	MySQL	CHAR(n) [常用]	固定长度字符串，n可取1~255的值，n表示字符个数，而不是字节个数 Java Type： String
		VARCHAR(n) [常用]	可变长度字符串，在MySQL4.x及以前，n是字节数，可以取1 ~ 65535的值。但是在MySQL5.x及以后，n表示字符数，又由于maximun row size是65535，一个utf8字符占3个字节，所以n可以取1 ~ 21844的值 DO类型： String
		TINYBLOB(n)	n可取1~255的值，不超过 255 个字符的二进制字符串 Java Type： String
		TINYTEXT(n)	n可取1~255的值，短文本字符串 Java Type： String
		BLOB(n)	n可取1~65535的值，二进制形式的长文本数据 Java Type： String
		TEXT(n)	n可取1~65535的值，长文本数据 Java Type： String
		MEDIUMBLOB(n)	n可取1~16 777 215的值，二进制形式的中等长度文本数据 Java Type： String
		MEDIUMTEXT(n)	n可取1~16 777 215的值，中等长度文本数据 Java Type： String
		LONGBLOB(n)	n可取1~4 294 967 295的值，二进制形式的极大文本数据 Java Type： String
		LONGTEXT(n)	n可取1~4 294 967 295的值，极大文本数据 Java Type： String
字符类型	Oracle	CHAR(n) [常用]	固定长度字符串，n可取1~2000的值，n表示字符个数，而不是字节个数 Java Type： String
		VARCHAR(n)	【废弃】可变长度字符串，n表示字符数，设置前需要考虑是单字节，还是多字节 占用空间： 1~4000字节 特点： ①VARCHAR只对汉字和全角等字符占两字节，数字，英文字符等都是一个字节； ②VARCHAR把空字符串，就当做空字符串来处理。 Java Type： String
		VARCHAR2(n) [常用]	【推荐】可变长度字符串，n表示字符数，可取1~4000，设置前需要考虑是单字节，还是多字节。   全是单字节的话，n取4000表示可以存4000个单字节；全是汉字，如果每个汉字占2个字节的话，n取4000表示可以存2000个汉字。 占用空间： 1~4000字节 特点： ①一般情况下，VARCHAR2把所有字符都占两字节处理； ②VARCHAR2把空串等同于null处理。 Java Type： String
		NCHAR(n)	Unicode数据类型，根据字符集而定的固定长度字符串，最大长度2000bytes
		NVARCHAR2(n)	Unicode数据类型，根据字符集而定的可变长度字符串，最大长度4000bytes
		LONG(n)	【废弃】可变长字符列，最大长度限制为2GB，用于不需要作字符串搜索的长串数据
		CLOB	最大4G，存储单字节字符型数据。适用于存储超长文本
		NCLOB	最大4G，存储多字节国家字符型数据。适用于存储超长文本
		BLOB	最大4G，存储二进制数据。适用于存储图像、视频、音频等
		BFILE	最大长度是4GB，在数据库外部保存的大型二进制对象文件
日期时间类型	MySQL	DATE [常用]	格式： yyyy-MM-dd Java Type： Date
		TIME	格式： hh:mm:ss Java Type： java.sql.Time
		TIMESTAMP [常用]	时间戳 格式： yyyy-MM-dd hh:mm:ss Java Type： java.sql.Timestamp
		DATETIME [常用]	格式： yyyy-MM-dd hh:mm:ss Java Type： Date
日期时间类型	Oracle	DATE [常用]	格式： yyyy-MM-dd Java Type： Date
		TIMSTAMP [常用]	时间戳 格式： yyyy-MM-dd hh:mm:ss Java Type： java.sql.Timestamp
		TIMESTAMP WITH TIME ZONE	时间戳的扩展，加上时区
枚举类型	MySQL	ENUM [常用]	CREATE TABLE student (gender ENUM(‘Male’,‘Femal’,‘Unknown’));
	Oracle	没有	/

引号使用对比

  一行行文字，不如用表格对比来得痛快！

引号	数据库类型	用法
单引号 ’	MySQL	将字符串常量括起来，需要注意的是： ① '\''使用转义，可以在两个单引号中包含一个单引号； ② "'"也可以用两个双引号包围一个单引号
	Oracle	① 单引号里的内容，不区分大小写，系统自动转为大写； ② 表示字符串常量； ③ 被单引号包围的双引号，不需要成对出现； ④ 单引号有转义的作用，比如''，最终得到的是'，相当于\'
双引号 "	MySQL	将字符串常量括起来，'"' 两个单引号包围一个双引号，也可以使用"\""转义
	Oracle	① 双引号括起来的内容，更确切一点叫引证标识符； ② 双引号内部的字符串严格区分大小写 ； ③ 用于避免和关键字冲突； ④ 在给列起别名时，只能加双引号或者不加引号，但不能使用单引号； ⑤ 在to_char(value, pattern)的pattern里时，双引号有特殊的作用，就是将非法的字符包起来。例如 select to_char(sysdate, 'hh24'"小时"mm"分"ss"秒"') 结果就是 06时39分51秒
反引号 `	MySQL	用于区分MySQL的关键字与普通字符，比如想要用 desc 作列名，就可以加反引号

SELECT后面一定有FROM吗？

数据库类型	SELECT后面一定有FROM吗？
Oracle	SELECT后面 一定要有 FROM，否则会报错ORA-00923：未找到要求的 FROM 关键字。 如果没有实际的查询表，可以把dual表放到FROM后面。 dual是Oracle系统内部提供的一个用于实现临时数据计算的特殊表，它只有一行一列。可以select * from dual;试一下。
MySQL	SELECT后面 可以没有 FROM

INSERT语句的不同

数据库类型	INSERT语句的不同
MySQL	支持INSERT INTO 表名 (列名) VALUE (列的值) 和 INSERT INTO 表名 (列名) VALUES (列的值), (列的值), ...
Oracle	只支持 INSERT INTO 表名 (列名) VALUES (列的值) ，不能在VALUES后面加2个及以上的(), ()，不支持VALUE

   Oracle 想要批量插入的话, 可以参考下面的 SQL, 注意 jdbcType 需要写明.

<insert id="insertAll">
    INSERT INTO TABLE_STUDENT ( STU_ID, STU_NAME, STU_GENDER )
    SELECT A.*
    FROM (
    <foreach collection="list" item="item" separator="union all">
    SELECT
    #{item.stuId} STU_ID,
    #{item.stuName} STU_NAME,
    #{item.stuGender} STU_GENDER
    FROM DUAL
    </foreach>
    ) A
</insert>

  另一种 Oracle 批量插入的方式:

<insert id="insertCuxProfessorInfoList">
    INSERT ALL
    <foreach collection="list" item="item">
        INTO TABLE_STUDENT (STU_ID, STU_NAME, STU_GENDER,
        STU_ID_NUMBER, STU_TELEPHONE, STU_EMAIL)
        VALUES
        (#{item.stuId}, #{item.stuName}, #{item.stuGender},
        #{item.stuIdNumber}, #{item.stuTelephone}, #{item.stuEmail})
    </foreach>
    SELECT 1 FROM DUAL
</insert>