开发人员Oracle转用MySQL时的一些使用上的区别

MySQL在使用上非常自由，非常不严格，以下主要以MySQL为例子作为演示。

1.数据库的使用上

oracle是一个库多用户的概念。MySQL是多个库多个用户的概念。一般一个库授权一个用户。当然也可以一个用户对应使用多个库，使用时可以用use dbname切换数据库。

2.分页oracle用的rownum，mysql用的limit

mysql> select * from t1 limit 3;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from t1 limit 2,3;
+------+------+
| a    | b    |
+------+------+
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
3 rows in set (0.00 sec)

3.mysql的select后面可以没有from

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.01 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-09-20 09:56:59 |
+---------------------+
1 row in set (0.01 sec)

4.对空值的判断上

4.1 演示错误的选择null列用法

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
| NULL | aaa  |
+------+------+
6 rows in set (0.00 sec)

mysql> select * from t1 where a='';
Empty set (0.00 sec)

mysql> select * from t1 where a="";
Empty set (0.00 sec)

#结果是对的，但属于错误用法！
mysql> select * from t1 where a!="";
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from t1 where a is null;
+------+------+
| a    | b    |
+------+------+
| NULL | aaa  |
+------+------+
1 row in set (0.00 sec)

mysql> select * from t1 where a is not null;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

4.2 MySQL除数为0时，会产生NULL值，然后再做表关联时可能就会有不是你想要的结果。而oracle报错。所以使用MySQL时建议可以计算后用ifnull函数先判断过滤空值再关联查询。

mysql> select 1/0;
+------+
| 1/0  |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

10:39:08 sys@orcl(localhost.localdomain)> select 1/0 from dual;
select 1/0 from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

5.单双引号

MySQL别名可以无引号或单双引号，但O只能无引号或者双引号

mysql> select a,b hao from t1;     
+------+------+
| a    | hao  |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
| NULL | aaa  |
+------+------+
6 rows in set (0.00 sec)

mysql> insert into t1 values (1,'aaa');     
Query OK, 1 row affected (0.01 sec)

mysql> select a,b 'hao' from t1;
+------+------+
| a    | hao  |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
| NULL | aaa  |
|    1 | aaa  |
+------+------+
7 rows in set (0.00 sec)

mysql> select a,b "hao" from t1;  
+------+------+
| a    | hao  |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
| NULL | aaa  |
|    1 | aaa  |
+------+------+
7 rows in set (0.00 sec)

10:20:09 sys@orcl(localhost.localdomain)> select * from t1;

         A B
---------- ----------
         1 a

Elapsed: 00:00:00.00
10:22:14 sys@orcl(localhost.localdomain)> select a,b 'hao' from t1;
select a,b 'hao' from t1
           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected


Elapsed: 00:00:00.01
10:22:48 sys@orcl(localhost.localdomain)> select a,b hao from  t1;                 

         A HAO
---------- ----------
         1 a

Elapsed: 00:00:00.00

通常使用无引号即可。

6.一些语法问题

正确的sql应该用values
insert into t1 values (1,'a');
但MySQL也可以
insert into t1 value (1,'a');

7.自增列问题。

MySQL可以很容易的创建自增id列

CREATE TABLE `jishu8cc` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT ,
`art_type` varchar(200) NULL ,
PRIMARY KEY (`id`)
)'

8.关于隐式转换

Oracle不能字符转数字，而MySQL可以。

mysql> select 'a' + 0 from dual;
+---------+
| 'a' + 0 |
+---------+
|       0 |
+---------+

10:53:52 sys@orcl(localhost.localdomain)> select 'a' + 0 from dual;
select 'a' + 0 from dual
       *
ERROR at line 1:
ORA-01722: invalid number

Oracle不能转换错误的日期格式,而MySQL可以。

mysql> select  case when now() > '2018d-09-20 12:10:24' then 'y' else 'n'end  from dual;
+---------------------------------------------------------------+
| case when now() > '2018d-09-20 12:10:24' then 'y' else 'n'end |
+---------------------------------------------------------------+
| n                                                             |
+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

11:42:14 sys@orcl(localhost.localdomain)> select  case when sysdate > '2018d-09-20 12:10:24' then 'y' else 'n'end  from dual;
select	case when sysdate > '2018d-09-20 12:10:24' then 'y' else 'n'end  from dual
                            *
ERROR at line 1:
ORA-01861: literal does not match format string


Elapsed: 00:00:00.18

9.group by

MySQL在5.6版本时，可以执行
select * from t1 group by a;

实际上select * from t1 group by a;本身就是不建议这样写的，会留坑，因为结果依赖于执行计划，依赖数据集的顺序。同样地也不可以用
select distinct b from t1 group by a;
而Oracle不允许group by列没有出现在select里，所以会报错。

有趣的时，MySQL5.7版本后已经无法执行上述SQL，实际上是因为sql_mode的默认值的修改。MySQL在变得规范！

mysql> select * from t1 group by a;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zabbix.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

10.from “子查询” 后要接别名。

mysql> select * from (select * from t1);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select * from (select * from t1) a;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
| NULL | aaa  |
|    1 | aaa  |
+------+------+
7 rows in set (0.00 sec)

oracle没有这个问题。