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

已于 2022-12-24 16:55:26 修改
阅读量2.8k 收藏 2
点赞数
分类专栏： openGauss 数据库 文章标签： mysql 数据库 java
于 2022-12-23 22:56:39 首次发布
CC BY-NC-SA 3.0
本文链接：https://blog.csdn.net/wwwwwwgame/article/details/128425192
版权

前言

在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
DarkAthena
关注
专栏目录
openGauss学习笔记-17 openGauss 简单数据管理-表达式
超哥的博客
07-22 273
表达式类似一个公式，我们可以将其应用在查询语句中，用来查找数据库中指定条件的结果集。
高斯数据库（openGauss-3.0.0-JDBC）JDBC驱动
07-27
openGauss-3.0.0-JDBC，适用于通过JDBC连接高斯数据库的驱动包，包括opengauss-jdbc-3.0.0.jar
【我和openGauss的故事】浅尝 openGauss v5.0.0 的 MySQL 语法兼容性
gallopingdb的博客
07-20 1183
关于 Dophin 插件还有很多特性有待探索，比如最基础的数据类型比对，后期有时间可以专门出个长篇与大家分享。(1 row)(1 row)复制德语的数字标点和英文中的有所区别。## 总结关于 Dophin 插件还有很多特性有待探索，比如最基础的数据类型比对，后期有时间可以专门出个长篇与大家分享。openGauss 作为国产数据库的标杆产品，有着庞大的资源投入与关注度，希望 openGauss 可以持续发力，为国产数据库事业添砖加瓦。
[openGauss]MySQL兼容性增强
最新发布
jerrywangcsdn的博客
08-21 356
本特性主要从以下几方面增强openGauss与MySQL的兼容性（只列举部分典型语法，详情请参见《数据迁移指南》中“MySQL兼容性说明”章节）：。支持用户锁，允许用户通过sql加自定义的锁，可以让多个程序之间完成加锁相关的交互过程，使得客户端从任何位置访问都可以得到一致性的锁视图。支持建表插入数据时默认记录插入当前时间；更新数据时，如果未指定更新时间，默认显示数据变更的当前时间。支持设置会话级SQL模式，允许运行时变更、全局变更以及会话内变更。支持隐藏索引，隐藏索引不会被优化器使用。
openGauss插件使用指南:表达式—条件表达式
weixin_53596073的博客
07-05 506
仅支持IF(expr1,expr2,expr3)，等价于CASE WHEN expr1 THEN expr2 ELSE expr3 END。CASE子句可以用于合法的表达式中。如果value1为NULL则返回value2，如果value1非NULL，则返回value1。等价于NVL，NVL的语法图请参见。CASE表达式的语法图请参考。
Mysql迁移OpenGauss
weixin_44739106的博客
03-10 5233
公司要做信创的迁移，本文简单描述了一下Mysql迁移到OpenGauss的大概流程步骤
SQL IFNULL()函数详细解析
热门推荐
陈老老老板的博客
08-23 3万+
解析：IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。 IFNULL() 函数语法格式为： IFNULL(expression, alt_value) 如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。 参数说明: 参数 解析 expression 必须，要测试的值 alt_value 必须，expression 表达式为 NULL 时返回的值 参数 描
mysql++ V3.1.0中文用户手册
11-29
mysql++ V3.1.0中文用户手册CHM,适用于初使用mysql++的同学
mysql++ v3.1.0 user manual pdf
12-13
MySQL++ 是一个C++库，它为MySQL数据库管理系统提供了接口，使得C++开发者可以方便地在他们的应用程序中集成MySQL的功能。v3.1.0是该库的一个版本，其用户手册PDF提供了一份详尽的指南，帮助开发者理解和利用MySQL++...
智睿中小学生学籍信息管理系统 v3.1.0
09-30
最后，对于《智睿中小学生学籍信息管理系统 v3.1.0》的升级，可能意味着增加了新功能或者优化了原有功能，比如提升了用户体验、增强了系统的兼容性，或是加入了对学生综合素质评价的支持。这些改进都是为了更好地...
GT3.1.0 app性能测试工具
10-31
尽管GT3.1.0不再更新，但它在性能测试方面的全面性仍然使其在Android app性能优化中占有重要地位。对于那些预算有限或者对perfdog收费模式有所顾忌的开发者来说，GT无疑是一个理想的选择。不过，使用旧版本也需要...
openGauss 3.1.0 特性介绍
cindy__007的博客
10-28 531
openGauss 3.1.0版本，在3.0.0版本功能的基础上，新增的特性
openGauss 数据迁移指南：MySQL迁移—MySQL语法兼容性评估工具
weixin_53596073的博客
06-29 2075
openGauss兼容性评估工具，支持从多个场景下获取sql语句进行兼容性评估，并输出评估报告。支持从mysql端采集sql语句，支持从mysql端采集以下几种sql类型：对象定义语句（DDL）。慢sql语句。此类sql通过查询mysql.slow_log表获得，需要用户开启慢sql记录至该表中。全量sql语句。此类sql通过查询mysql.general_log表获得，同样需要用户开启general log记录至该表中。支持从文件中获取sql语句，支持以下几种文件类型：sql文件。
openGauss MySQL兼容性增强
cindy__007的博客
11-22 587
可获得性本特性自openGauss 3.0.0版本开始引入。特性简介本特性主要从以下三方面增强openGauss与MySQL的兼容性。同时，支持兼容Insert语法，Insert into可以简写为insert。支持用户锁，允许用户通过sql加自定义的锁，可以让多个程序之间完成加锁相关的交互过程，使得客户端从任何位置访问都可以得到一致性的锁视图。支持建表插入数据时默认记录插入当前时间；更新数据时，如果未指定更新时间，默认显示数据变更的当前时间。支持设置会话级SQL模式，允许运行时变更、全局变更以及会话内变更
GBase 8c V3.0.0数据类型——条件表达式函数
futianxia061112的博客
06-27 658
GBase 8c V3.0.0数据类型——条件表达式函数
openGauss的MySQL兼容性
07-21 933
DDLCREATE DATABASE支持IF NOT EXISTS选项。
高斯数据库openGauss适配
u012785397的博客
07-02 1万+
高斯数据库openGauss适配，mysql转高斯数据库。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

DarkAthena

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值