一、说明
平常我用到最多的数据库是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篇文章，正好一个月一篇。即使工作再忙，也希望自己能不断的写下去。期待大家给我投票，最后提前祝大家元旦快乐。
