【我和openGauss的故事】浅尝 openGauss v5.0.0 的 MySQL 语法兼容性

老老老JR老北
发布于 2023-8-24 16:39
浏览
0收藏

在 openGauss 中,以下简称 og5 ,提供了一个名为 Dolphin 的插件,并以此来提供 MySQL 的兼容性。

本文将以 openGauss v5.0.0 版本为基础,对照 PostgreSQL v15.2 和 MariaDB 10.7.3 来演示 Dolphin 插件的基础功能。

openGauss提供dolphin Extension(版本为dolphin-1.0.0)。dolphin Extension是openGauss的MySQL兼容性数据库(dbcompatibility=‘B’)扩展,从关键字、数据类型、常量与宏、函数和操作符、表达式、类型转换、DDL/DML/DCL语法、存储过程/自定义函数、系统视图等方面兼容MySQL数据库。

环境准备

为验证本文中所使用的 SQL 语句,特意准备了一套实验环境,并为了做对照实验,还准备了 PostgreSQL 和 MariaDB 。
分别连接到 DB 后,屏幕信息展示如下。

【我和openGauss的故事】浅尝 openGauss v5.0.0 的 MySQL 语法兼容性-鸿蒙开发者社区

启用 Dolphin 插件

在 og5 中,新创建一个数据库,安装 Dophin 插件,并将参数开关打开:

-- 新建数据库 dophindb
CREATE DATBASE dolphindb DBCOMPATIBILITY 'B';

-- 安装 Dophin 插件
CREATE EXTENSION dolphin;

-- 打开 dolphin.b_compatibility_mode 开关
SET dolphin.b_compatibility_mode = on;

校验以下,可以看到插件已经安装,兼容模式已经开启:

(shawnyan@192) [dolphindb] 14:24:43> \dx
                                List of installed extensions
+-----------------+---------+------------+--------------------------------------------------+
|      Name       | Version |   Schema   |                   Description                    |
+-----------------+---------+------------+--------------------------------------------------+
...
| dolphin         | 1.0     | public     | sql engine                                       |
...
+-----------------+---------+------------+--------------------------------------------------+
(8 rows)

(shawnyan@192) [dolphindb] 14:30:23> show dolphin.b_compatibility_mode;
+------------------------------+
| dolphin.b_compatibility_mode |
+------------------------------+
| on                           |
+------------------------------+
(1 row)

这里也出现了第一个兼容说明,可以像在mysql中查看插件一样,在og中使用 show plugins 语法

(shawnyan@192) [dolphindb] 15:24:07> show plugins;
+-----------------+----------+------+---------+---------+--------------------------------------------------------------+
|      Name       |  Status  | Type | Library | License |                           Comment                            |
+-----------------+----------+------+---------+---------+--------------------------------------------------------------+
| dblink          | DISABLED |      | NULL    |         | connect to other PostgreSQL databases from within a database |
| dist_fdw        | ACTIVE   |      | NULL    |         | foreign-data wrapper for distfs access                       |
| dolphin         | ACTIVE   |      | NULL    |         | sql engine                                                   |
| file_fdw        | ACTIVE   |      | NULL    |         | foreign-data wrapper for flat file access                    |
| hstore          | ACTIVE   |      | NULL    |         | data type for storing sets of (key, value) pairs             |
| log_fdw         | ACTIVE   |      | NULL    |         | Foreign Data Wrapper for accessing logging data              |
| mot_fdw         | ACTIVE   |      | NULL    |         | foreign-data wrapper for MOT access                          |
| plpgsql         | ACTIVE   |      | NULL    |         | PL/pgSQL procedural language                                 |
| postgres_fdw    | DISABLED |      | NULL    |         | foreign-data wrapper for remote PostgreSQL servers           |
| security_plugin | ACTIVE   |      | NULL    |         | provides security functionality                              |
+-----------------+----------+------+---------+---------+--------------------------------------------------------------+
(10 rows)

兼容性测试

启用兼容模式后,意味着兼容 MySQL 语法,但并不代表完全兼容,可能是完全兼容、部分兼容,或者不兼容。
下面具体举例来看。

DDL

在 mysql 中,创建 schema 之后,可以使用 use 切换,而在 pg 中需要 set search_path.
在 og 中,同样可以使用 use,并且功能同 search_path 切换。

  • mariadb

MariaDB [(none)]> use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [sbtest]> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| t1               |
+------------------+
1 row in set (0.001 sec)
  • pg

dophindb=# create table sbtest.t1(id int);
CREATE TABLE
dophindb=# set search_path=sbtest;
SET
dophindb=# \d
        List of relations
Schema | Name | Type  |  Owner
--------+------+-------+----------
sbtest | t1   | table | postgres
(1 row)
  • og

(shawnyan@192) [dolphindb] 14:51:57> use sbtest
dolphindb-> ;
SET
Time: 0.655 ms
(shawnyan@192) [dolphindb] 14:52:00> \d
                           List of relations
+--------+------+-------+----------+----------------------------------+
| Schema | Name | Type  |  Owner   |             Storage              |
+--------+------+-------+----------+----------------------------------+
| sbtest | t1   | table | shawnyan | {orientation=row,compression=no} |
+--------+------+-------+----------+----------------------------------+
(1 row)

(shawnyan@192) [dolphindb] 14:52:02> show search_path;
+-------------+
| search_path |
+-------------+
| sbtest      |
+-------------+
(1 row)

(shawnyan@192) [dolphindb] 15:18:34> show current_schema;
+----------------+
| current_schema |
+----------------+
| sbtest         |
+----------------+
(1 row)

再看下创建表:

og/pg 不支持 create or replcace,

MariaDB [sbtest]> create or replace table t1 (id int, c1 bit, c2 boolean);
Query OK, 0 rows affected (0.005 sec)

(shawnyan@192) [dolphindb] 15:18:54> create or replace table t1 (id int, c1 bit, c2 boolean);
ERROR:  syntax error at or near "table"
LINE 1: create or replace table t1 (id int, c1 bit, c2 boolean);
                          ^

但是都支持 create table if not exists,

MariaDB [sbtest]> create table if not exists t2 (id int, c1 bit, c2 boolean);
Query OK, 0 rows affected (0.008 sec)

dophindb=# create table if not exists t2 (id int, c1 bit, c2 boolean);
CREATE TABLE

(shawnyan@192) [dolphindb] 15:18:55> create table if not exists t2 (id int, c1 bit, c2 boolean);
CREATE TABLE

在 pg 中查询建表语句不是很方便,只能自己写方法或者借助第三方工具,在 MariaDB 中只需 show create table 即可,
来看下 og 的表现:

(shawnyan@192) [dolphindb] 15:26:04> show create table t2;
+-------+-----------------------------------------+
| Table |              Create Table               |
+-------+-----------------------------------------+
| t2    | SET search_path = sbtest;              +|
|       | CREATE TABLE t2 (                      +|
|       |     id integer,                        +|
|       |     c1 bit(1),                         +|
|       |     c2 boolean                         +|
|       | )                                      +|
|       | WITH (orientation=row, compression=no); |
+-------+-----------------------------------------+
(1 row)

更多 DDL 语法可参考文档:DDL语法一览表

进程信息

在 pg 中,可以通过系统表 pg_stat_activity 查看活跃的进程信息,效果如下:

dophindb=# select * from pg_stat_activity;
datid | datname  | pid | leader_pid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |
    backend_start         |          xact_start           |          query_start          |         state_change          | wait_e
vent_type |     wait_event      | state  | backend_xid | backend_xmin | query_id |              query              |         backe
nd_type
-------+----------+-----+------------+----------+----------+------------------+-------------+-----------------+-------------+-----
--------------------------+-------------------------------+-------------------------------+-------------------------------+-------
----------+---------------------+--------+-------------+--------------+----------+---------------------------------+--------------
----------------
       |          |  11 |            |          |          |                  |             |                 |             | 2023
-07-07 04:50:01.059294+00 |                               |                               |                               | Activi
ty        | AutoVacuumMain      |        |             |              |          |                                 | autovacuum la
uncher
       |          |  12 |            |       10 | postgres |                  |             |                 |             | 2023
-07-07 04:50:01.059472+00 |                               |                               |                               | Activi
ty        | LogicalLauncherMain |        |             |              |          |                                 | logical repli
cation launcher
16390 | dophindb | 159 |            |       10 | postgres | psql             |             |                 |          -1 | 2023
-07-07 05:32:07.47404+00  | 2023-07-07 07:22:57.126396+00 | 2023-07-07 07:22:57.126396+00 | 2023-07-07 07:22:57.126399+00 |
          |                     | active |             |          741 |          | select * from pg_stat_activity; | client backen
d
       |          |   8 |            |          |          |                  |             |                 |             | 2023
-07-07 04:50:01.034653+00 |                               |                               |                               | Activi
ty        | BgWriterHibernate   |        |             |              |          |                                 | background wr
iter
       |          |   7 |            |          |          |                  |             |                 |             | 2023
-07-07 04:50:01.033115+00 |                               |                               |                               | Activi
dophindb=#

而在 mysql 中可以使用 show processlist, 如:

MariaDB [sbtest]> show processlist;
+----+------+-----------+--------+---------+------+----------+------------------+----------+
| Id | User | Host      | db     | Command | Time | State    | Info             | Progress |
+----+------+-----------+--------+---------+------+----------+------------------+----------+
|  4 | root | localhost | sbtest | Query   |    0 | starting | show processlist |    0.000 |
+----+------+-----------+--------+---------+------+----------+------------------+----------+
1 row in set (0.001 sec)

那么在 og 中,也兼容了该语法,只是会将内部线程和外部会话都显示出来:

(shawnyan@192) [dolphindb] 16:14:25> show processlist
dolphindb-> ;
+-----------------+-----------------+-----------------+-------------+----------+-----------------------+-----------+--------------
----------+-------------------------------+-------------------------------+-------+--------+--------------------------------------
--+
|       Id        |       Pid       |     QueryId     | UniqueSqlId |   User   |         Host          |    db     |        Comman
d         |         BackendStart          |           XactStart           | Time  | State  |                  Info
  |
+-----------------+-----------------+-----------------+-------------+----------+-----------------------+-----------+--------------
----------+-------------------------------+-------------------------------+-------+--------+--------------------------------------
--+
| 139817127892736 | 139817127892736 |               0 |           0 | omm      |                       | postgres  | TxnSnapCaptur
er        | 2023-07-07 02:01:47.418851+00 | [null]                        | 19113 | idle   |
  |
| 139817161455360 | 139817161455360 |               0 |           0 | omm      |                       | postgres  | ApplyLauncher
          | 2023-07-07 02:01:47.42906+00  | [null]                        | 19113 | idle   |
  |
| 139817111111424 | 139817111111424 |               0 |           0 | omm      |                       | postgres  | CfsShrinker
          | 2023-07-07 02:01:47.429884+00 | [null]                        | 19113 | idle   |
  |
| 139817094330112 | 139817094330112 |               0 |           0 | omm      |                       | postgres  | PercentileJob
          | 2023-07-07 02:01:47.438804+00 | [null]                        |     7 | active |
  |
| 139817077548800 | 139817077548800 |               0 |           0 | omm      |                       | postgres  | Asp
          | 2023-07-07 02:01:47.440282+00 | [null]                        |     1 | active |
  |
| 139817060767488 | 139817060767488 |               0 |           0 | omm      |                       | postgres  | statement flu
sh thread | 2023-07-07 02:01:47.501574+00 | [null]                        | 19113 | idle   |
  |
| 139817211799296 | 139817211799296 |               0 |           0 | omm      |                       | postgres  | JobScheduler
          | 2023-07-07 02:01:47.508451+00 | [null]                        |     0 | active |
  |
| 139816661403392 | 139816661403392 |               0 |           0 | omm      |                       | postgres  | WorkloadMonit
or        | 2023-07-07 02:01:47.527399+00 | [null]                        | 19113 | idle   |
  |
| 139816678184704 | 139816678184704 |               0 |           0 | omm      |                       | postgres  | workload
          | 2023-07-07 02:01:47.530114+00 | 2023-07-07 02:01:47.536091+00 | 19113 | active | WLM fetch collect info from data node
s |
| 139816644622080 | 139816644622080 |               0 |           0 | omm      |                       | postgres  | WLMArbiter
          | 2023-07-07 02:01:47.540853+00 | [null]                        | 19113 | idle   |
  |
| 139816484992768 | 139816484992768 |               0 |           0 | shawnyan | 192.168.195.1:56481   | postgres  | HeidiSQL
          | 2023-07-07 02:14:30.859179+00 | [null]                        |     5 | idle   |
  |
| 139816407267072 | 139816407267072 | 562949953425604 |   927755207 | shawnyan | 192.168.195.128:14942 | dolphindb | gsql
          | 2023-07-07 07:14:15.693419+00 | 2023-07-07 07:20:20.074231+00 |     0 | active | show processlist
+|
|                 |                 |                 |             |          |                       |           |
          |                               |                               |       |        | ;
  |
+-----------------+-----------------+-----------------+-------------+----------+-----------------------+-----------+--------------
----------+-------------------------------+-------------------------------+-------+--------+--------------------------------------
--+
(12 rows)

这当前这个演示示例中,我们看到了一个命令为 HeidiSQL 的进程,此时我们想杀掉这个会话,
那么在 pg 中需要使用 
select pg_terminate_backend(pid);

而在兼容 mysql 的 og 里可以使用 kill <id> 命令来处理:

(shawnyan@192) [dolphindb] 16:31:27> kill 139816484992768;
+--------+
| result |
+--------+
| t      |
+--------+
(1 row)

再次查询进程信息,发现对应 id 的进程已经消失。

字符处理

dophin 对个别数据类型,及四则运算都有一定程度的改动,由于篇幅有限,详细内容请参考文档。
这里演示一下 format 函数的使用, pg 中不支持,

MariaDB [sbtest]> select format(1234.567,2);
+--------------------+
| format(1234.567,2) |
+--------------------+
| 1,234.57           |
+--------------------+
1 row in set (0.004 sec)

postgres=# select format(1234.567,2);
ERROR:  function format(numeric, integer) does not exist
LINE 1: select format(1234.567,2);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

在 og 中的效果如下,与mairadb中的表现一致:

(shawnyan@192) [dolphindb] 16:53:19> select format(1234.567,2);
+----------+
|  format  |
+----------+
| 1,234.57 |
+----------+
(1 row)

但需要说明的是,og 中默认使用 en_US 格式,还可以指定其他格式,如:

(shawnyan@192) [dolphindb] 16:54:38> select format(1234.567,2,'de_DE');
+----------+
|  format  |
+----------+
| 1.234,57 |
+----------+
(1 row)

德语的数字标点和英文中的有所区别。

总结

关于 Dophin 插件还有很多特性有待探索,比如最基础的数据类型比对,后期有时间可以专门出个长篇与大家分享。

openGauss 作为国产数据库的标杆产品,有着庞大的资源投入与关注度,希望 openGauss 可以持续发力,为国产数据库事业添砖加瓦。



文章转载自公众号:openGauss

分类
标签
已于2023-8-24 16:39:26修改
收藏
回复
举报
回复
    相关推荐