从Mysql迁移到openGauss需要注意哪些方面
本文背景：
面对严峻的国际形势以及国家大力推动信创国产化，以金融、医疗和交通等为代表的产业以推动软件系统国产化，在数据库方面以华为为代表的国产数据库,在很多国企的系统中已经开始迁移。
项目迁移数据库类型可是一个比较大的工程，在迁移之初一定要做好充分的调研。建议在确定模式之前，进行性能和兼容性测试，以确保能够选择能满足业务和技术需求。
1.JDBC连接方式不同
JDBC（Java Database Connectivity，Java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问接口，应用程序可基于它操作数据。openGauss库提供了对JDBC4.0特性的支持，需要使用JDK1.8版本编译程序代码，不支持JDBC桥接ODBC方式。
openGauss提供两种JDBCjar包：postgresql.jar和openGauss-jdbc-x.x.x.jar，两种jar包功能一致，仅仅是为了解决和PostgreSQL之间的JDBC驱动包名冲突。
在Linux服务器端源代码目录下执行build.sh，获得驱动jar包postgresql.jar和opengauss-jdbc-x.x.x.jar，包位置在源代码内output目录下。也可以从官网发布包中获取，包名为openGauss-x.x.x-JDBC.tar.gz。
驱动包与PostgreSQL保持兼容，其中类名、类结构与PostgreSQL驱动完全一致，曾经运行于PostgreSQL的应用程序可以直接移植到当前系统使用。
| MySQL | openGauss | |
|---|---|---|
| 驱动名称 | com.mysql.cj.jdbc.Driver | org.postgresql.Driver |
| org.opengauss.Driver | ||
| JDBC地址 | jdbc:mysql://ip:port/pds_ficp | jdbc:postgresql://ip:port/postgres |
| jdbc:opengauss://ip:port/postgres |
说明:
- openGauss在JDBC的使用上与PG的使用方法保持兼容。在同一个JVM虚拟机里加载PostgreSQL和openGauss的JDBC同名驱动时，可能存在org.postgresql.Driver驱动类冲突问题。
- 相比于PG驱动，openGauss JDBC驱动主要做了以下特性的增强：
- 支持SHA256加密方式登录。
- 支持对接实现sf4j接口的第三方日志框架。
- 支持容灾切换。
2.MySQL和OpenGauss不兼容的函数及处理建议
2.1数学函数
| MySQL | openGauss | 备注 |
|---|---|---|
| RAND() | RANDOM() | 返回0到1内的随机值。 |
| TRUNCATE(x,y) | TRUNC() | 返回数字x截短为y位小数的结果 |
2.2字符串函数
| MySQL | openGauss | 备注 |
|---|---|---|
| INSERT(str,x,y,instr) | overlay() | 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果 |
| FIELD() | 返回值在值列表中的索引位置 | |
| FIND_IN_SET(str,list) | 分析逗号分隔的list列表，如果发现str，返回str在list中的位置 | |
| QUOTE(str) | quote_literal() | 用反斜杠转义str中的单引号 |
| SUBSTRING_INDEX() | split_part() | 在出现指定数量的分隔符之前返回字符串的子字符串 |
| STRCMP(s1,s2) | = | 比较字符串s1和s2 |
| UCASE(str) | UPPER(str) | 返回将字符串str中所有字符转变为大写后的结果 |
| LCASE(str) | LOWER(str) | 返回将字符串str中所有字符改变为小写后的结果 |
2.3日期和时间函数
| MySQL | openGauss | 备注 |
|---|---|---|
| SYSDATE() | SYSDATE | 返回当前日期和时间 |
| CURRENT_DATE() | CURRENT_DATE | 返回当前的日期 |
| CURDATE() | CURRENT_DATE | 返回当前的日期 |
| CURRENT_TIME() | CURRENT_TIME | 返回当前的时间 |
| CURTIME() | CURRENT_TIME | 返回当前的时间 |
| CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP | 返回当前日期和时间 |
| DATE_ADD(date,INTERVAL int keyword) | date + INTERVAL‘1 day’ | 返回日期date加上间隔时间int的结果 |
| DATE_FORMAT(date,fmt) | TO_CHAR() | 依照指定的fmt格式格式化日期date值 |
| DATE_SUB(date,INTERVAL int keyword) | date–INTERVAL‘1 day’ | 返回日期date减去间隔时间int的结果 |
| DAYOFWEEK(date) | EXTRACT() | 返回date所代表的一星期中的第几天(1~7) |
| DAYOFMONTH(date) | EXTRACT() | 返回date是一个月的第几天(1~31) |
| DAYOFYEAR(date) | EXTRACT() | 返回date是一年的第几天(1~366) |
| QUARTER(date) | EXTRACT() | 返回date在一年中的季度(1~4) |
| FROM_UNIXTIME(ts,fmt) | 根据指定的fmt格式，格式化UNIX时间戳ts | |
| YEAR(date) | EXTRACT(YEAR FROM DATE date)或者DATE_PART(‘year’,date) | 返回日期date的年份(1000~9999) |
| MONTH(date) | EXTRACT()或者DATE_PART(‘month’, date) | 返回date的月份值(1~12) |
| WEEK(date) | EXTRACT()或者DATE_PART(‘week’, date) | 返回日期date为一年中第几周(0~53) |
| HOUR(time) | EXTRACT()或者DATE_PART(‘hour’, time) | 返回time的小时值(0~23) |
| MINUTE(time) | EXTRACT()或者DATE_PART(‘minute’, time) | 返回time的分钟值(0~59) |
| SECOND(time) | EXTRACT()或者DATE_PART(‘second’, time) | |
| MONTHNAME(date) | TO_CHAR() | 返回date的月份名 |
| DAYNAME(date) | TO_CHAR() | 返回date的星期名， |
| TIMESTAMPDIFF() | EXTRACT() | TIMESTAMPDIFF函数用于返回计算两个日期指定单位的时间差 |
| DATEDIFF(startdate,enddate) | EXTRACT() | 返回两个日期之间的时间差 |
2.4条件表达式
| MySQL | openGauss | 备注 |
|---|---|---|
| IF(test,t,f) | CASEWHEN test THEN t ELSEf END | 如果test是真，返回t；否则返回f |
| IFNULL(arg1,arg2) | coalesce()NVL() | 如果arg1不是空，返回arg1，否则返回arg2 |
2.5格式化函数
| MySQL | openGauss | 备注 |
|---|---|---|
| DATE_FORMAT(date,fmt) | TO_CHAR() | 依照字符串fmt格式化日期date值 |
| STR_TO_DATE() | TO_DATE() | 函数根据字符串和格式返回日期。 |
| FORMAT(x,y) | TO_CHAR() | 把x格式化为以逗号隔开的数字序列，y是结果的小数位数 |
| INET_ATON(ip) | 返回IP地址的数字表示 | |
| INET_NTOA(num) | 返回数字所代表的IP地址 | |
| TIME_FORMAT(time,fmt) | TO_CHAR() | 依照字符串fmt格式化时间time值 |
2.6系统信息函数
| MySQL | openGauss | 备注 |
|---|---|---|
| DATABASE() | current_database() | 返回当前数据库名 |
| BENCHMARK(count,expr) | 将表达式expr重复运行count次 | |
| SYSTEM_USER() | USER()或者current_user | 返回当前登陆用户名 |
3.数据类型差异
| MySQL | openGauss | 备注 |
|---|---|---|
| ENUM 类型 | 无对应，可用 CHECK 约束代替 | 类型不一致 |
| SET 类型 | 无对应，可用 ARRAY 类型代替 | 类型不一致 |
| YEAR 类型 | 无对应，可用 INTERVAL YEAR 或DATE 类型代替 | 类型不一致 |
| UNSIGNED 类型 | 无对应，需注意数值范围 | 类型不一致 |
| ZEROFILL 类型 | 无对应，需在应用层处理 | 类型不一致 |
| VARCHAR(n)n表示字符数 | VARCHAR(n)n表示字节数 | 存储汉字个数不一致 |
| CHAR(n) n表示字符数 | CHAR(n) n表示字节数 | 存储汉字个数不一致 |
4. 语法差异
4.1 DDL语句
| MySQL | openGauss | 备注 |
|---|---|---|
| AUTO_INCREMENT | SERIAL | 自增序列 |
| COMMENT‘’ | COMMENTON COLUMN X IS’ ’ | 字段注释 |
UNIQUEKEY unique_name(c1,c2) | CONSTRAINTunique_name UNIQUE (c1,c2) | 多列联合唯一约束 |
| RENAME COLUMN | ALTER COLUMN RENAME | 重命名列 |
| RENAME INDEX | ALTER INDEX RENAME | 重命名索引 |
| RENAME DATABASE | 无对应 | 重命名数据库 |
| ALTER DATABASE 不支持修改所有者 | ALTER DATABASE支持修改所有者 | 修改数据库所有者 |
| ALTER DATABASE 支持修改字符集和排序规则 | ALTER DATABASE 不支持修改字符集和排序规则 | 修改数据库字符集和排序规则 |
| ALTER TABLE支持一次修改多个列 | ALTER TABLE 一次只能修改一个列 | 修改表列 |
| ALTER TABLE 支持一次添加多个索引 | ALTER TABLE 一次只能添加一个索引 | 添加表索引 |
| ALTER TABLE 支持一次删除多个索引 | ALTER TABLE 一次只能删除一个索引 | 删除表索引 |
| ALTER TABLE 支持修改存储引擎 | ALTER TABLE 不支持修改存储引擎 | 修改表存储引擎 |
| ALTER TABLE 支持修改字符集和排序规则 | ALTER TABLE 不支持修改字符集和排序规则 | 修改表字符集和排序规则 |
| ALTER TABLE 支持修改自增值 | ALTER TABLE 不支持修改自增值 | 修改表自增值 |
| TRUNCATE TABLE 不可在事务中 | TRUNCATE TABLE 可在事务中 | 清空表数据 |
| 存储过程使用 BEGIN 和 END | 存储过程使用 $$ | 存储过程定义 |
4.2 DML语句
| 描述 | MySQL | openGauss |
|---|---|---|
| 插入并在存在时更新 | REPLACE INTO 或INSERT … ON DUPLICATE KEY UPDATE … | INSERT … ONCONFLICT DO UPDATE … |
| 单行插入返回插入的 ID | INSERT INTO …;SELECT 248442; | INSERT INTO …RETURNING id; |
| 单行更新返回更新的行 | UPDATE …;SELECT …; | UPDATE …RETURNING *; |
| 单行删除返回删除的行 | DELETE …;SELECT …; | DELETE …RETURNING *; |
| 创建临时表 | CREATETEMPORARY TABLE table_name | CREATETEMP TABLE table_name |
4.3 DQL语句
| 描述 | MySQL | openGauss |
|---|---|---|
| 随机获取行记录 | SELECT … ORDERBY RAND() LIMIT 1 | SELECT … ORDERBY RANDOM() LIMIT 1 |
| 索引字段查询优化 | SELECT * FROM tableUSE INDEX(index) WHERE column = value | SET enable_seqscan TOOFF; SELECT * FROM table WHERE column = value; SET enable_seqscan TO ON; |
| 使用LIMIT 进行分页查询 | SELECT … LIMIToffset, count | SELECT … LIMITcount OFFSET offset |
| 查询特定范围的行 | SELECT * FROM tableLIMIT x, y | SELECT * FROM tableLIMIT y OFFSET x |
| 对分组的限制 | SELECT … FROM … GROUP BY … WITH ROLLUP | SELECT … FROM … GROUP BY … ROLLUP(…) |
| MySQL使用反引号(``)引用表名和列名 | SELECTcolumn FROM table | SELECT“column” FROM “table” |
| 使用存储过程 | CALL procedure_name() | SELECT * FROM procedure_name() |
4.4 DCL 语句
| MySQL | openGauss | 备注 |
|---|---|---|
| REVOKE ALL PRIVILEGES | REVOKE ALL | 撤销权限 |
| GRANT SELECT ON . | GRANT SELECT ON ALLTABLES IN SCHEMA | 授予权限 |
4.5 其他
| MySQL | openGauss | 备注 |
|---|---|---|
| SHOW TABLES | \dt | 查看所有表 |
| SHOWDATABASES | \l | 查看所有数据库 |
| DESCRIBEtable or DESC table | \d table | 描述表结构 |
| SHOW CREATETABLE | pg_dump -ttable -s | 查看表创建语句 |
| ENGINE =InnoDB | 无对应 | 存储引擎 |
| CHARSET =utf8mb4 | ENCODING =UTF8 | 字符集 |
| COLLATE =utf8mb4_bin | COLLATE"C" | 排序规则 |
| LOCK TABLES | LOCK TABLE | 锁表 |
| UNLOCK TABLES | COMMIT | 解锁 |
| KILL QUERY | CANCEL | 取消查询 |
| SHOW PROCESSLIST | SELECT * FROM pg_stat_activity | 查看进程列表 |
| LOAD DATA INFILE | COPY | 数据导入 |
| SELECT INTO OUTFILE | COPY TO | 数据导出 |
5.解决方案
采用B模式数据库解决不兼容问题
5.1 dolphin插件概述
openGauss提供dolphin Extension。dolphin Extension是openGauss的MySQL兼容性数据库（dbcompatibility='B'）扩展，从关键字、数据类型、常量与宏、函数和操作符、表达式、类型转换、DDL/DML/DCL语法、存储过程/自定义函数、系统视图等方面兼容MySQL数据库。
5.2使用dolphin插件
创建B库并使用初始用户连接B库。
create database db_name dbcompatibility 'B';
openGauss将在第一次通过初始用户或拥有初始用户权限的用户连接B数据库时自动加载dolphin插件。
