Oracle和Mysql在sql上的一些差异

原创

wx57edf9829d3c7 2022-12-31 21:42:48 ©著作权

文章标签 mysql oracle sql SQL bc 文章分类 虚拟化云计算

©著作权归作者所有：来自51CTO博客作者wx57edf9829d3c7的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、说明

平常我用到最多的数据库是Oracle和Mysql，本文将介绍这两个数据库在sql上的一些差异。
测试环境数据库的版本为Oracle 11G，Mysql 8.0。

二、实验

1. 字符类型大小写

Oracle和Mysql最典型的区别在于是否区分字符大小写。Oracle默认区分大小写，Mysql默认不区分大小写。因此这会影响字符串的选择和排序。从下面的例子可以看出‘B’和‘b’在Oracle中不同，在Mysql中相同。
Oracle:

create table stt1(id int, name varchar2(10));
insert into stt1 values (1, 'a');
insert into stt1 values (2, 'b');
insert into stt1 values (3, 'B');
insert into stt1 values (4, 'c');

SQL> select * from stt1 order by name;

        ID NAME
---------- ----------
         3 B
         1 a
         2 b
         4 c
         
SQL> select * from stt1 where name = 'b';

        ID NAME
---------- ----------
         2 b

Mysql:

create table stt1(id int, name varchar(10));
insert into stt1 values (1, 'a'),(2, 'b'),(3, 'B'),(4, 'c');

(root@localhost)[hello]> select * from stt1 order by name;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | B    |
|    4 | c    |
+------+------+

(root@localhost)[hello]> select * from stt1 where name = 'b';
+------+------+
| id   | name |
+------+------+
|    2 | b    |
|    3 | B    |
+------+------+

Mysql也可以区分字符大小写，使用的排序字符集是utf8mb4_bin，这样排序和过滤就和Oracle一样了

create table stt2(id int, name varchar(10)) collate utf8mb4_bin;
insert into stt2 values (1, 'a'),(2, 'b'),(3, 'B'),(4, 'c');
            
(root@localhost)[hello]> select * from stt2 order by name;
+------+------+
| id   | name |
+------+------+
|    3 | B    |
|    1 | a    |
|    2 | b    |
|    4 | c    |
+------+------+

(root@localhost)[hello]> select * from stt2 where name = 'b';
+------+------+
| id   | name |
+------+------+
|    2 | b    |
+------+------+

那么问题就来了，Mysql中字符串大小写敏感和不敏感的表关联会出现什么现象呢？

(root@localhost)[hello]> select a.*, b.* from stt1 a, stt2 b where a.name = b.name;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    2 | b    |    2 | b    |
|    3 | B    |    3 | B    |
|    4 | c    |    4 | c    |
+------+------+------+------+

可以看到两表关联，它们的name完全一样，区分了大小写，即如果表与表之间关联，有一张表的字符区分大小写，那么整体的关联条件都是区分大小写的。接下来复制一张stt1表。看看两张表都不区分大小写关联是个什么情况。

create table stt3 as select * from stt1;
      
(root@localhost)[hello]> select a.*, b.* from stt1 a, stt3 b where a.name = b.name;
+------+------+------+------+
| id   | name | id   | name |
+------+------+------+------+
|    1 | a    |    1 | a    |
|    3 | B    |    2 | b    |
|    2 | b    |    2 | b    |
|    3 | B    |    3 | B    |
|    2 | b    |    3 | B    |
|    4 | c    |    4 | c    |
+------+------+------+------+

2. char与varchar关联

在这两个数据库中都提供了定长char和不定长varchar（在Oracle中是varchar2，为了简便，统一称为varchar）这两个字符类型，那么char跟varchar之间关联会是个什么情况呢？
Oracle:

create table ctt10(id int, name char(10));
create table ctt12(id int, name char(12));
create table vtt10(id int, name varchar2(10));
create table vtt12(id int, name varchar2(12));
insert into ctt10 values(1, '20221230');
insert into ctt12 values(2, '20221230');
insert into vtt10 values(3, '20221230');
insert into vtt12 values(4, '20221230');

Oracle中char(10)与char(12)，varchar(10)与varchar(12)关联，即同类型不同字段长度，都能取到结果

SQL> select a.*, b.* from ctt10 a, ctt12 b where a.name = b.name;

        ID NAME               ID NAME
---------- ---------- ---------- ------------
         1 20221230            2 20221230

SQL> select a.*, b.* from vtt10 a, vtt12 b where a.name = b.name;

        ID NAME               ID NAME
---------- ---------- ---------- ------------
         3 20221230            4 20221230

Oracle中char(10)与varchar(10)，char(10)与varchar(12)关联，即不同类型，不管字段长度是否一样，都不能取到结果

SQL> select a.*, b.* from ctt10 a, vtt10 b where a.name = b.name;

no rows selected

SQL> select a.*, b.* from ctt10 a, vtt12 b where a.name = b.name;

no rows selected

Mysql:

create table ctt10(id int, name char(10));
create table ctt12(id int, name char(12));
create table vtt10(id int, name varchar(10));
create table vtt12(id int, name varchar(12));
insert into ctt10 values(1, '20221230');
insert into ctt12 values(2, '20221230');
insert into vtt10 values(3, '20221230');
insert into vtt12 values(4, '20221230');

(root@localhost)[hello]> select a.*, b.* from ctt10 a, ctt12 b where a.name = b.name;
(root@localhost)[hello]> select a.*, b.* from vtt10 a, vtt12 b where a.name = b.name;
(root@localhost)[hello]> select a.*, b.* from ctt10 a, vtt10 b where a.name = b.name;
(root@localhost)[hello]> select a.*, b.* from ctt10 a, vtt12 b where a.name = b.name;

在Mysql中，以上4条sql语句都能取到结果，为了节约篇幅，这里将结果省略。可以看出在Mysql中其实不用在意varchar和char之间的关联情况，但在Oracle中需要注意varchar和char之间关联的空格问题。可以采用以下两种写法。

select a.*, b.* from ctt10 a, vtt10 b where trim(a.name) = b.name; -- 去掉char的空格
select a.*, b.* from ctt10 a, vtt10 b where a.name = rpad(b.name, 10, ' '); -- varchar后面填充空格

在Oracle中还有另外一种情况就是在varchar中插入空格会是什么情况？

insert into vtt12 values(5, '20221230 ');

SQL> select a.*, b.* from vtt12 a, vtt12 b where a.name = b.name;     

        ID NAME                 ID NAME
---------- ------------ ---------- ------------
         4 20221230              4 20221230
         5 20221230              5 20221230

可以看到‘20221230 ’和‘20221230’能关联上。但使用以下sql又能明显看出这两个值的不同。

SQL>  select id, name || 'xx' from vtt12;

        ID NAME||'XX'
---------- --------------
         4 20221230xx
         5 20221230 xx

3. 隐式转换

在sql中，隐式转换总是从字符串转数字。
Oracle:

create table tra1(id int, name varchar2(10));
insert into tra1 values(1, '5abc');
insert into tra1 values(2, 'def5');
insert into tra1 values(3, '5');

SQL> select * from tra1 where id = '3';

        ID NAME
---------- ----------
         3 5

SQL> select * from tra1 where id = '3abc';
select * from tra1 where id = '3abc'
                              *
ERROR at line 1:
ORA-01722: invalid number

SQL> select * from tra1 where name = 5;
select * from tra1 where name = 5
                         *
ERROR at line 1:
ORA-01722: invalid number

SQL> select * from tra1 where name = '5';

        ID NAME
---------- ----------
         3 5

Mysql:

create table tra1(id int, name varchar(10));
insert into tra1 values(1, '5abc');
insert into tra1 values(2, 'def5');
insert into tra1 values(3, '5');

(root@localhost)[hello]> select * from tra1 where id = '3';
+------+------+
| id   | name |
+------+------+
|    3 | 5    |
+------+------+

(root@localhost)[hello]> select * from tra1 where id = '3abc';
+------+------+
| id   | name |
+------+------+
|    3 | 5    |
+------+------+

(root@localhost)[hello]> select * from tra1 where name = 5;
+------+------+
| id   | name |
+------+------+
|    1 | 5abc |
|    3 | 5    |
+------+------+

(root@localhost)[hello]> select * from tra1 where name = '5';
+------+------+
| id   | name |
+------+------+
|    3 | 5    |
+------+------+

可以对比看出在Oracle中字符串转数字，碰到不能转的，例如‘5abc’就直接报错。但是在Mysql中，‘5abc’在隐式转换成数字的时候，会转成5，即字符串开头的数字部分。

4. update语句

在Mysql中执行update语句

(root@localhost)[hello]> select * from stt1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | B    |
|    4 | c    |
+------+------+

我想将id为1的这条数据的id改成5，并且name改成‘A’，sql语句如下

(root@localhost)[hello]> update stt1 set id=5 and name='A' where id =1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

(root@localhost)[hello]> select * from stt1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | B    |
|    4 | c    |
+------+------+

可以看到匹配上了，但是数据没改。细心的小伙伴发现是什么问题了吗？之前我碰到一个业务老师拿这个问题问我，一下子把我问住了，大脑卡了很久都没想明白是什么一回事。
其实正常的update语句应该是这样的，update stt1 set id=5, name=‘A’ where id =1; 更改两个值应该是用逗号隔开而不是用and。用and最后呈现出来的sql语句其实等价于update stt1 set id=(5 and name=‘A’) where id =1; (5 and name=‘A’)刚好就是1，所以id的值就没有变。
这个语句在Oracle中执行会直接语法报错。

5. 生成自增的行

自增的行在数据库中作用挺大，很多地方都用得到。在Oracle中生成自增行可以采用connect by，例如

SQL> select rownum rn from dual connect by rownum < 10;

        RN
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

在Mysql中可以用递归函数

(root@localhost)[hello]> with recursive cte(n) as (
    ->     select 1
    ->     union all
    ->     select n + 1 from cte where n < 9)
    -> select n rn from cte;
+------+
| rn   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
+------+

三、总结

工作中经常会交叉使用这两种数据库，这两个数据库在一些sql细节上面有着不少的差异，需要我们不断测试和积累。
今天是2022年最后的一个工作日，这一年有着太多不可思议，好的坏的也都过去了。感恩还有一个健康的身体，还有一个稳定的工作。有幸能参与墨天轮举办的2022年度优秀原创作者评选，加上本文，这一年一共写了12篇文章，正好一个月一篇。即使工作再忙，也希望自己能不断的写下去。期待大家给我投票，最后提前祝大家元旦快乐。