【openGauss】浅试openGauss3.1.0中有关mysql兼容的部分特性

前言

在9月30号，openGauss推出了3.1.0这一预览版（注意，openGauss的“x.y.z”版本号，“y”的位置如果不是0，就不是长期支持版，不建议生产使用）。
这个版本增加了不少新内容，
https://docs.opengauss.org/zh/docs/3.1.0/docs/Releasenotes/特性介绍.html
但让我最感兴趣的是其中关于mysql兼容的部分，其中有很多都是通过dolphin 插件支持的

  • 兼容性：兼容MySQL大部分常用功能和语法

  • 提供dolphin插件，从关键字、数据类型、常量与宏、函数和操作符、表达式、类型转换、DDL/DML/DCL语法、存储过程/自定义函数、系统视图等方面兼容MySQL。

  • 3.1.0版本新增对以下语法点的支持（只列举部分典型语法，详情请参见《开发者指南》中“MySQL兼容性说明”章节）：

  • 数据类型：ENUM、SET、FIXED、LONGBLOB、MEDIUMBLOB、TINYBLOB、MEDIUM INT、DATETIME、YEAR、NVARCHAR以及无符号整数。

  • 操作符：:=、^(异或)、<=>（不等于）、||、&&、regexp、not regexp、rlike、DIV、MOD、XOR、like binary、not like binary。

  • 系统函数：if、ifnull、isnull、strcmp、locate、lcase、ucase、insert、bin、chara、elt、field、find_int_set、hex、space、soundex、length、convert、format、rand、crc32、conv、now、sysdate、current_time、dayofmonth、is_ipv4、inet_aton、inet_ntoa、is_ipv6、inet6_aton、export_set、bit_bool、json_array、json_quote、last_insert_id、group_concat。

  • DDL：

  • 支持创建database时带 if not exists。

  • 支持在字段/table/index/procedure/function里对comment注释的创建和修改。

  • 支持建表、建索引时option里选项无序排列，支持表名区分大小写。

  • 支持建表时指定索引和约束。

  • 支持对添加、删除、交换、重组、TRUNCATE、ANALYZE、REBUILD、REPAIR、REMOVE分区的兼容。

  • 支持建表指定on update current_timestamp。

  • 支持自增列。

  • DML：

  • 支持INSERT、UPDATE、DELETE带IGNORE关键词，对出现冲突时，能忽略掉报错。

  • delete语句支持order by，delete支持指定分区。

  • delete支持多表删除，update支持多表更新。

  • 支持REPLACE INTO语法。

  • PL/SQL：

  • PL/SQL 支持设置DEFINER。

  • 支持在存储过程中命名变量。

  • 使用call关键字调用有参数的存储过程。

  • 支持使用DELIMITER 定义分隔符。

  • DCL：

  • 各类show语法兼容，包括show databases、show processlist、show tables、show create table、show create view、show master status等。

  • 支持USE db_name，DESC table_name。

  • 网络协议兼容：可兼容MySQL客户端基本网络协议。

  • 兼容性：支持兼容性评估工具

  • 基于openGauss内核语法树的兼容性评估工具，支持评估主流数据库兼容性。

以上取自目前官网介绍，但实际上，由于openGauss是开源的，因此3.1.0的源码里，截止到目前，又增加了不少新的功能，如果要体验最新版的，需要下载源码自行编译安装
https://gitee.com/opengauss/openGauss-server
https://gitee.com/opengauss/Plugin/tree/master/contrib/dolphin

内容太多，没法一一测试，就挑几个说说吧。

注意，本文测试环境为

(openGauss 3.1.0 build cd1244c5) compiled at 2022-11-24 14:18:04 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3
.0, 64-bit

且创建了B兼容数据库，安装了dolphin插件

set自定义变量

在mysql中，经常会有如下用法

set @var_name := 123;

在openGauss中，该语法默认没有打开，需要先修改一个环境变量

alter database db_name set enable_set_variable_b_format = on;

然后即可使用

set @var_name := 123;
select @var_name;

image-1671714989283

实际应用：

create function getRowId (RESET BIT) RETURNS INT(11)
 NO SQL
 BEGIN
 	IF RESET THEN 
 	  SET @VAR:=0;
 	 ELSE 
 	 SET @VAR:=IFNULL(@VAR,0)+1;
 	END IF;
 RETURN @VAR;
 END;

上面是mysql中的自定义函数，只要稍作修改（由于我手上版本非最新，不支持delimeter，只能使用pg语法的delimeter了）

create or replace function getRowId (RESET BIT) RETURNS INT(11) as
$$
 BEGIN
 	IF RESET THEN 
 	  SET @VAR:=0;
 	 ELSE 
 	 SET @VAR:=IFNULL(@VAR,0)+1;
 	END IF;
 RETURN @VAR::int;
 END;
$$language plpgsql;

image-1671727402606
可以看到这个函数的执行效果和mysql中完全一致

DEFINER定义属主

目前在openGauss中，支持在创建视图时用和mysql一样的语法定义属主

create definer=username view test_def_v as select proname from pg_proc limit 5;

image-1671715666696

AUTO_INCREMENT自增列

create table test_AUTO_INCREMENT 
(id int AUTO_INCREMENT, 
val varchar(10),
PRIMARY KEY (id)
);
insert into test_AUTO_INCREMENT(val) values ('a');
insert into test_AUTO_INCREMENT(val) values ('b');
select * from test_AUTO_INCREMENT;

image-1671715981330

group_concat函数

这个函数类似于ORACLE中的listagg/wm_concat、PG中的string_agg,
但是，由于这个函数里有语法关键词，所以不能创建一个自定义函数来实现，需要修改sql语法解析, 因此是在内核里实现的

SELECT datcompatibility,
    group_concat(DATNAME ORDER BY DATNAME DESC separator '|')
FROM PG_DATABASE
GROUP BY datcompatibility;

image-1671716811599

aes_decrypt/aes_encrypt 加解密函数

select aes_encrypt('openGauss','123456vfhex4dyu,vdaladhjsadad','1234567890123456');
--由于加密返回的不是可读文本信息，因此这里演示解密是套了一层
select aes_decrypt(aes_encrypt('openGauss','123456vfhex4dyu,vdaladhjsadad','1234567890123456'),'123456vfhex4dyu,vdaladhjsadad','1234567890123456');

image-1671717107294

并且，基于安全角度考虑，在gsql中使用了包含此函数的sql，都不会记录到gsql的历史记录中（上下翻找不到）

sha/sha1/sha2 函数

sha和sha1一样，sha2的可选算法为

0(SHA-256)、224(SHA-224)、256(SHA-256)、384(SHA-384)、512(SHA-512)

select sha('ABC');
select sha2('ABC',256);

image-1671717443983

ENUM 枚举类型

CREATE TABLE shirts (
  name VARCHAR(40),
  size ENUM('small', 'medium', 'large')
);

使用时需要注意，在枚举类型中不能出现"anonymous_enum"的字样，在create type时，type名称的构成里也不能包含"anonymous_enum" （我猜这里用了字符串解析）

like (binary) 操作符

在mysql中，默认的排序方式会让字符串的值不区分大小写，使用 "="或者"like"时，能进行不区分大小写的匹配，需要使用 "binary"关键字才是准确的字符串匹配。注意，要先修改参数 B_COMPATIBILITY_MODE

set B_COMPATIBILITY_MODE= on;
SELECT 'a' like 'A';
SELECT 'a' like binary 'A';

image-1671718396038

week 函数

在mysql中,可以通过配置default_week_format这个参数，或者指定week的第二个参数，来得出某天在不同的算法时，是第几周。
在openGauss中，同样引入了这个变量

show default_week_format;
select week('2000-1-1');
alter system set default_week_format = 2;
select week('2000-1-1');
select week('2000-1-1',0);

image-1671718862569
但是，需要注意的是，同时支持的yearweek函数，目前不受default_week_format这个参数的影响，需要手动指定第二个参数mode（这点很奇怪）
另外，我手上这个版本，如果设置了default_week_format参数，重启数据库，会无法启动，因为这个参数会写入postgresql.conf文件，在启动时会识别是未知参数，所以如果测试的时候遇到了，就先在postgresql.conf里手工去掉default_week_format

json相关函数

在openGauss3.0中，有不少json函数，但没有任何一个是可以修改原json节点值的，因此如果要修改json串，是个极其麻烦的事。而到了3.1版本，通过dolphin又引入了不少json函数，终于能对json进行一些操作了

select json_set('{"student":{"id":1,"gender":"man"}}','$.age',23,'$.student.id',3);
select json_replace('{"a": 1, "b": 2, "c": 3}', '$.b', 9);
select json_insert('{"student":{"id":1,"gender":"man"}}', '$.age', 26);
SELECT JSON_REMOVE('{"x": 1, "y": 2}', '$.x');

image-1671719594636

create table 语法

ENGINE、CHARSET、COLLATE

mysql的create table 语法中有ENGINE、CHARSET、COLLATE，openGauss3.1在语法上支持了，但功能还不支持，不过排序大小写可以通过前面提到的参数B_COMPATIBILITY_MODE进行修改，或者在字段上指定排序方式。而字符集一般在一个数据库中应该保持统一。

CREATE TABLE test_COLLATE2(
  id int(11) NOT NULL,
  name varchar(255) collate 'zh_CN.gbk' DEFAULT null ,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

image-1671720905691

ON UPDATE CURRENT_TIMESTAMP

MySQL的这个特性可以在表记录被更新时，自动更新指定的字段为当前时间，这个功能在旧版本的Oracle中，只能通过触发器实现

CREATE TABLE test_on_update(a int,last_update_time timestamp ON UPDATE CURRENT_TIMESTAMP);
insert into test_on_update(a) values (1);
select * from test_on_update;
update test_on_update set a=2 where 1=1;
select * from test_on_update;
update test_on_update set a=3 where 1=1;
select * from test_on_update;

image-1671770444013

show create table 语法

show create table test_collate2;

image-1671721151989

kill 语法

show processlist;
kill query 140287740606208;

image-1671721502719

USE db_name

在mysql中，可以用use来切换当前数据库，openGauss3.1也实现了类似的操作，不过此语法是切换schema

create table test_use(a int);
create schema test_schema;
use test_schema;
create table test_use(a int);

image-1671721882558

sql_mode

目前openGauss3.1版本中，sql_mode里可以设置下面6个值

    {"sql_mode_defaults", OPT_SQL_MODE_DEFAULT},
    {"sql_mode_strict", OPT_SQL_MODE_STRICT},
    {"sql_mode_full_group", OPT_SQL_MODE_FULL_GROUP},
    {"pipes_as_concat", OPT_SQL_MODE_PIPES_AS_CONCAT},
    {"ansi_quotes", OPT_SQL_MODE_ANSI_QUOTES},
    {"no_zero_date", OPT_SQL_MODE_NO_ZERO_DATE},

其中的sql_mode_full_group是很多mysql初学者会遇到的一个参数，5.7版本能执行的sql，到8.0就报错，很大的概率就是与这个参数有关，这篇就不细讲了。

DELIMITER分隔符

这个特性是在11月28号合入的，我本地版本还不支持，可参考源码中的测试用例

delimiter //
CREATE PROCEDURE p()
  BEGIN
  DECLARE v INT DEFAULT 1;
  CASE v
  WHEN 2 THEN SELECT v;
  WHEN 3 THEN SELECT 0;
  ELSE
  BEGIN
  END;
  END CASE;
END;
//

https://gitee.com/opengauss/Plugin/blob/master/contrib/dolphin/sql/create_function_test/m_type_create_proc.sql

反引号支持

我手上的非最新版，暂时无法测试，请参考源码中的示例

create table `test`("ID" int);
insert into `test` values(10);
\d+ test
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 ID     | integer |           | plain   |              | 
Has OIDs: no
Options: orientation=row, compression=no

select * from `test` where "ID"=10;
 ID 
----
 10
(1 row)

https://gitee.com/opengauss/Plugin/blob/master/contrib/dolphin/expected/case_sensitive_test_backquote/quoted.out

MySQL客户端连接openGauss

https://gitee.com/opengauss/openGauss-server/issues/I5PCM3

简单来说，就是数据库如果有B库，且装了dolphin时，会新起一个监听端口，模拟mysql数据库的通讯协议，从而实现mysql客户端连接openGauss。
相关的guc参数为

  • enable_dolphin_proto
    是否启用mysql协议
  • dolphin_server_port
    MySQL协议端口
  • default_database_name
    mysql客户端连接时指定的数据库名称

另外，还在pg_authid上增加了一个字段，用于存储mysql客户端连接时使用的密码（因为mysql 5.7不支持sha256）

总结

由于这次更新增加的内容实在太多，以至于大部分情况下，完全可以把openGauss当成mysql使用，甚至连客户端驱动都可以沿用mysql的，所以没法通过一篇文章把所有内容说完。官网文档单独为MySQL兼容性这一块做了章节，有独立的语法说明和系统参数说明，有兴趣的可以去openGauss官网了解。

目前源码仓库里还在不断合并新的pr，相当期待明年发布的正式版会是什么样子。

附:dolphin安装会初始化的GUC参数列表

b_compatibility_mode
sql_mode
b_db_timestamp
lower_case_table_names
default_week_format
lc_time_names
version_comment
auto_increment_increment
character_set_client
character_set_connection
character_set_results
character_set_server
collation_server
collation_connection
init_connect
interactive_timeout
license
max_allowed_packet
net_buffer_length
net_write_timeout
query_cache_size
query_cache_type
system_time_zone
time_zone
wait_timeout

更新两张最新编译版本的测试（20221223）

  • mysql客户端连接openGauss
    image-1671813502860
  • delimiter 和 反引号
    image-1671813645327

posted on 2022-12-23 23:01  DarkAthena  阅读(567)  评论(1)  编辑  收藏  举报

导航