侧边栏壁纸
  • 累计撰写 128 篇文章
  • 累计创建 13 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

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

DarkAthena
2022-12-22 / 0 评论 / 1 点赞 / 747 阅读 / 17950 字

前言

在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')
);

image-1671717684798

使用时需要注意,在枚举类型中不能出现"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

1
  1. 支付宝打赏

    qrcode alipay
  2. 微信打赏

    qrcode weixin
博主关闭了所有页面的评论