常用sql在mysql和oracle中的区别和注意事项

        这篇文章，主要是针对同一个项目，需要运行在不同的数据库环境（mysql和oracle），那么总会有些sql不是两个数据库都支持的，这时候就需要做一些调整，在简化代码的前提下，做到一份sql在两个数据库环境运行。

        这里是站在开发人员的角度上来梳理注意事项，不考虑数据同步，归档，表空间以及存储引擎等其他因素。

        为了使举的栗子更清楚，使用两个表来做参考，sql_test_a和sql_test_b，sql见文章末尾。

1、不推荐使用AS

        在oracle中，查询表之后不支持table+as+别名，但是又支持field + as 别名，在mysql中都支持，为避免使用上的繁琐，建议都不用as，sql都可以正常执行。

        针对表举个栗：

        在oracle中：

            正确：select a.id,a.job from sql_test_b a;

            错误：select a.id,a.job from sql_test_b AS a;

        在mysql中：

            正确：select a.id,a.job from sql_test_b a;

            正确：select a.id,a.job from sql_test_b AS a;

        针对字段举个栗：

        在oracle和mysql中：

        正确：select id as a,job as b from sql_test_b;

        正确：select id a,job b from sql_test_b;

2、不推荐使用+来进行表与表的连接，推荐使用连接语句

    在oracle中，使用+可以用来表示表与表的连接，但是mysql不支持该语法，为了统一sql的规范使用，建议使用left join，right join等连接语句来表示。

    举个栗：

    在oracle中：

        正确：select a.id,b.firstname from sql_test_b a left join sql_test_a b on a.id = b.id;

        正确：select a.id,b.firstname from sql_test_b a, sql_test_a b where a.id=b.id(+);

    在mysql中：

        正确：select a.id,b.firstname from sql_test_b a left join sql_test_a b on a.id = b.id;

        错误：select a.id,b.firstname from sql_test_b a, sql_test_a b where a.id=b.id(+);

3、分页功能中sql的不同

    mysql中分页使用offset、limit，但是oracle不支持该语法，而是使用rownum来处理，所以针对这种不同数据库相同功能的处理，使用拦截器，根据连接数据库的方言(dialect)来判断并对sql重新处理，使用拦截器@Intercepts,对ResultSetHandler和StatementHandler两个mybatis包中的类拦截，重写intercept方法

    举个栗：

    公共查询sql: select * from sql_test_a;

    在oracle中：

        正确：select * from (select tmp_tb.*,ROWNUM row_id from (select * from sql_test_a)  tmp_tb where ROWNUM<=0+5)a where a.row_id>0;

    在mysql中：

        正确：select * from sql_test_a limit 0,5;

4、自增id都要先使用先查询最大id

    因为oracle建表时没有auto_increment，所以如果想要主键实现自增，只能先查询数据库主键最大的值，插入新数据的时候在最大的值上+1

5、对空值的判断不同，推荐建表时字段都尽量非空，如果可以为空的话，建议还是用null

    mysql字段可以存空字符串（“”），但是对于oracle来说，空字符串和null是一个意思，都表示null，所以在mysql中可以用（!=’’）来查询数据，但是在oracle中这就表示不等于null，可能查不到数据，往往有人就把该字段设计成一个空格（“ ”），无形挖坑，最为致命。

6、推荐使用单引号

    oracle对于引号的使用限制比较严格，一般对双引号的sql都是执行报错，mysql都可以，所以推荐统一使用单引号。

    举个栗：

    在oracle中：

        正确：select * from sql_test_a where lastname = 'Keaton';

        错误：select * from sql_test_a where lastname = "Keaton";

    在mysql中：

        正确：select * from sql_test_a where lastname = 'Keaton';

        正确：select * from sql_test_a where lastname = "Keaton";

7、group by的使用不同

    oracle中对于group by的使用，有一个原则，就是select后面的所有列中，没有使用聚合函数的列，必须出现在group by后面

    举个栗：

    在oracle中：

        正确：select job,count(id),count(firstname) from sql_test_a group by job;

        正确：select job,count(id) from sql_test_a group by job;

        错误：select job,id,firstname from sql_test_a group by job;

    在mysql中：

        正确：select job,count(id),count(firstname) from sql_test_a group by job;

        正确：select job,count(id) from sql_test_a group by job;

        正确：select job,id,firstname from sql_test_a group by job;

附：建表语句

oracle：

CREATE TABLE sql_test_a(

    ID         VARCHAR2(4000 BYTE),

    FIRST_NAME VARCHAR2(200 BYTE),

    LAST_NAME  VARCHAR2(200 BYTE)

);

CREATE TABLE sql_test_b(

    ID VARCHAR2(4000 BYTE),

    JOB VARCHAR2(1000 BYTE)

);

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('1', 'John', 'Snow');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('2', 'Mike', 'Tyson');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('3', 'Bill', 'Keaton');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('4', 'Greg', 'Mercury');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('5', 'Steve', 'Jobs');

INSERT INTO sql_test_a (ID, FIRST_NAME, LAST_NAME) VALUES ('6', 'Johhny', 'Depp');

INSERT INTO sql_test_b (ID, JOB) VALUES ('1', 'JAVA');

INSERT INTO sql_test_b (ID, JOB) VALUES ('2', 'PYTHON');

INSERT INTO sql_test_b (ID, JOB) VALUES ('3', 'C++');

INSERT INTO sql_test_b (ID, JOB) VALUES ('4', 'SWIFT');

mysql：

CREATE TABLE sql_test_a (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) NOT NULL

);

CREATE TABLE sql_test_b (

id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,

job varchar(32) NOT NULL

);

INSERT INTO `sql_test_a` (`id`, `firstname`, `lastname`) VALUES ('1', 'John', 'Snow');

INSERT INTO `sql_test_a` (`id`, `firstname`, `lastname`) VALUES ('1', 'Mike', 'Tyson');

INSERT INTO `sql_test_a` (`id`, `firstname`, `lastname`) VALUES ('1', 'Bill', 'Keaton');

INSERT INTO `sql_test_a` (`id`, `firstname`, `lastname`) VALUES ('1', 'Greg', 'Mercury');

INSERT INTO `sql_test_a` (`id`, `firstname`, `lastname`) VALUES ('1', 'Steve', 'Jobs');

INSERT INTO `sql_test_a` (`id`, `firstname`, `lastname`) VALUES ('1', 'Johhny', 'Depp');

INSERT INTO `sql_test_b` (`id`, `job`) VALUES ('1', 'JAVA');

INSERT INTO `sql_test_b` (`id`, `job`) VALUES ('2', 'PYTHON');

INSERT INTO `sql_test_b` (`id`, `job`) VALUES ('3', 'C++');

INSERT INTO `sql_test_b` (`id`, `job`) VALUES ('4', 'SWIFT');

转载于:https://my.oschina.net/varus/blog/3099362