体验MariaDB 10.3中的Oracle语法支持
2018-09-11 本文已影响149人
hemny
一、背景
MariaDB 10.3发布已经有一段时间了，里面提到了SQL_MODE新增了ORACLE选项，可以支持部分的PL/SQL语法，算是一个比较“新鲜”的更新，所以打算安装体验一下，并测试支持情况。
二、测试环境
硬件：
CPU：i5-8250U （四核八线程）
内存：8GB
磁盘：240GB SSD
系统平台：win10 x64
数据库：MariaDB-10.3.9-winx64
三、准备测试数据
use test;
CREATE TABLE t_A (
id int,
code int,
name VARCHAR(10)
);
CREATE TABLE t_B (
id int,
code int,
name VARCHAR(10)
);
INSERT INTO t_A(id,code,name) VALUES(1,2,'A');
INSERT INTO t_A(id,code,name) VALUES(2,1,'B');
INSERT INTO t_A(id,code,name) VALUES(3,5,'C');
INSERT INTO t_A(id,code,name) VALUES(4,6,'D');
INSERT INTO t_A(id,code,name) VALUES(5,7,'E');
INSERT INTO t_B(id,code,name) VALUES(1,3,'AA');
INSERT INTO t_B(id,code,name) VALUES(1,4,'BB');
INSERT INTO t_B(id,code,name) VALUES(2,1,'CC');
INSERT INTO t_B(id,code,name) VALUES(1,2,'DD');
INSERT INTO t_B(id,code,name) VALUES(7,5,'GG');
CREATE TABLE temp (
id int,
code int,
name VARCHAR(50)
);
四、测试oracle语法支持
1、体验一下SQL_MODE="ORACLE"
Oracle中的示例文档中代码(Sample 1. FOR Loop)：
-- available online in file 'sample1'
DECLARE
x NUMBER := 100;
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;</pre>
我们看看如果是这样一段代码，要在MySQL中运行，应该看起来是怎样的。下面是修改在MySQL5.7中运行的代码：
CREATE PROCEDURE dowhile()
BEGIN
DECLARE x INT DEFAULT 100;
DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
IF MOD(i,2) = 0 THEN -- i is even
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
COMMIT;
SET x=x+100;
SET i=i+1;
END WHILE;
END;
以上两种，主要的不同包括：
MySQL代码必须在一个存储过程中执行，所以这里创建了dowhile
- DECLARE语法不一样，Oracle DECLARE在BEGIN之前，MySQL则在BEGIN里面
- MySQL不支持FOR... IN... LOOP的语法，这里改用WHILE来实现；MySQL也不支持"1..10"这种写法
- 数据类型不同，Oracle中是NUMBER，MySQL是INT
- 变量赋值不同，Oracle使用了“:=”，MySQL是 “SET .. = ...”
我们先看看，前一段Oracle的代码，在MariaDB里面是否能够不做修改的运行：
这个简单的示例可以正常运行：
Enter password: ******
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.3.9-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use test
Database changed
MariaDB [test]> select * from temp;
Empty set (0.003 sec)
MariaDB [test]> show variables like 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)
MariaDB [test]> set session sql_mode="ORACLE";
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> show variables like 'sql_mode';
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)
MariaDB [test]> delimiter /
MariaDB [test]> DECLARE
-> x NUMBER := 100;
-> BEGIN
-> FOR i IN 1..10 LOOP
-> IF MOD(i,2) = 0 THEN -- i is even
-> INSERT INTO temp VALUES (i, x, 'i is even');
-> ELSE
-> INSERT INTO temp VALUES (i, x, 'i is odd');
-> END IF;
-> x := x + 100;
-> END LOOP;
-> COMMIT;
-> END;
-> /
Query OK, 10 rows affected (0.020 sec)
MariaDB [test]> select * from temp/
+------+------+-----------+
| 列 1 | 列 2 | 列 3 |
+------+------+-----------+
| 1 | 100 | i is odd |
| 2 | 200 | i is even |
| 3 | 300 | i is odd |
| 4 | 400 | i is even |
| 5 | 500 | i is odd |
| 6 | 600 | i is even |
| 7 | 700 | i is odd |
| 8 | 800 | i is even |
| 9 | 900 | i is odd |
| 10 | 1000 | i is even |
+------+------+-----------+
10 rows in set (0.000 sec)
2、测试oracle的外关联语法
oracle专用标准的关联查询，以两个关联键值的左关联SQL为例。
select *
from t_a a,t_b b
where a.id=b.id(+) and a.code=b.code(+)
and a.id!=8;
转换为ANSI标准的left /right/full join 的写法如下：
select *
from t_a a
left join t_b b on a.id=b.id and a.code=b.code
where a.id!=8;
测试情况：
MariaDB [test]> select *
-> from t_a a
-> left join t_b b on a.id=b.id and a.code=b.code
-> where a.id!=8;
-> /
+------+------+------+------+------+------+
| id | code | name | id | code | name |
+------+------+------+------+------+------+
| 2 | 1 | B | 2 | 1 | CC |
| 1 | 2 | A | 1 | 2 | DD |
| 3 | 5 | C | NULL | NULL | NULL |
| 4 | 6 | D | NULL | NULL | NULL |
| 5 | 7 | E | NULL | NULL | NULL |
+------+------+------+------+------+------+
5 rows in set (0.004 sec)
MariaDB [test]> select *
-> from t_a a,t_b b
-> where a.id=b.id(+) and a.code=b.code(+)
-> and a.id!=8;
-> /
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') and a.code=b.code(+)
and a.id!=8' at line 3
MariaDB [test]>
不支持oracle的（+）表示外关联的语法
最后
目前还没有完整量化评估MariaDB对PL/SQL的支持情况，对于oracle的外关联语法（+）在oracle中的广泛使用，目前还没有得到支持。也许在后续的版本中，会逐步完善对oracle语法的兼容性。
