从 MySQL 迁移到 ProtonBase 指南

ProtonBase 提供数据同步服务，可以方便、快捷的将 MySQL 数据一站式迁移至 ProtonBase。

如何同步 MySQL 数据参考文档Teleport 同步 MySQL 数据

字段类型映射


MySQL 数据类型	ProtonBase 数据类型	备注
TINYINT	SMALLINT	范围不同，MySQL: -128 到 127， ProtonBase: -32768 到 32767
BOOL / BOOLEAN	BOOLEAN	ProtonBase 使用 BOOLEAN，值为 TRUE 或 FALSE
SMALLINT	SMALLINT	一致
MEDIUMINT	INTEGER	ProtonBase 无 MEDIUMINT，通常用 INTEGER 替代
INT / INTEGER	INTEGER	一致
BIGINT	BIGINT	一致
FLOAT	REAL	精度略有不同
DOUBLE	DOUBLE PRECISION	一致
DECIMAL(p， s)	NUMERIC(p， s)	一致，精度和范围相同
DATE	DATE	一致
DATETIME	TIMESTAMP [WITHOUT TIME ZONE]	ProtonBase 使用 TIMESTAMP，并支持时区
TIMESTAMP	TIMESTAMP	一致
TIME	TIME	一致
YEAR	SMALLINT / INTEGER	ProtonBase 无 YEAR 类型，需转换为整数表示
CHAR(n)	CHAR(n)	一致
VARCHAR(n)	VARCHAR(n)	一致
TINYTEXT	VARCHAR	ProtonBase 无 TINYTEXT，用 VARCHAR 替代
TEXT	TEXT	一致
MEDIUMTEXT	TEXT	ProtonBase 无 MEDIUMTEXT，用 TEXT 替代
LONGTEXT	TEXT	ProtonBase 无 LONGTEXT，用 TEXT 替代
BINARY(n)	BYTEA	ProtonBase 无 BINARY，用 BYTEA 处理二进制数据
VARBINARY(n)	BYTEA	同上
TINYBLOB	BYTEA	ProtonBase 无 TINYBLOB，用 BYTEA 替代
BLOB	BYTEA	一致
MEDIUMBLOB	BYTEA	ProtonBase 无 MEDIUMBLOB，用 BYTEA 替代
LONGBLOB	BYTEA	ProtonBase 无 LONGBLOB，用 BYTEA 替代
ENUM	VARCHAR / CHECK 约束	ProtonBase 无 ENUM，通常用 VARCHAR 并添加 CHECK 约束实现
SET	无直接对应	可以用 ARRAY 或通过表关联来实现
JSON	JSONB	ProtonBase 推荐使用 JSONB，支持更高效的操作

查询语法

如果你使用的是 ORM(Object-Relational Mapping) 框架，例如 Java 的 Hibernate、Python 的 SQLAlchemy 等，那么迁移过程会相对简单。你只需要修改 ORM 配置中的数据库驱动和连接字符串，切换到 ProtonBase 的驱动程序，大部分查询语句都无需改动，因为 ORM 会根据目标数据库自动生成正确的 SQL 语法。

当然，仍然可能存在一些 ORM 不能很好地抽象和转换的数据库特有语法和功能，这些你可能仍需要手动调整。下面是一些常见的语法差异：

标识符引号

MySQL中使用反引号``来引用数据库、表、列等标识符。ProtonBase 使用双引号"来引用标识符。

字符串引号

MySQL中使用单引号'和双引号"来引用字符串。ProtonBase只使用单引号'来引用字符串。

LIMIT分页语法

MySQL支持LIMIT {偏移量}, {行数}的语法进行分页，如 limit 20, 10。ProtonBase使用OFFSET {偏移量} LIMIT {行数}的语法进行分页，如 offset 20 limit 10。

`NULL` 判断

MySQL 使用= NULL 或 != NULL判断是否等于 NULL。ProtonBase 则是 IS NULL 或 IS NOT NULL。必须使用 IS NULL，不能使用 = 或 != 比较。

字符串拼接

MySQL 使用 CONCAT() 函数。ProtonBase 可以使用 ||，如 'aa' || 'bb'。

Column must appear in the GROUP BY clause or be used in an aggregate

MySQL 里允许查询 ungrouped 字段，ProtonBase 里会报错，如下：

=> create table t(a int, b int, c int);
CREATE TABLE
=> select a, b, sum(c) from t group by a;
ERROR:  column "t.b" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select a, b, sum(c) from t group by a;

select 中的字段必须在 group by 中或者参与聚合操作。

Union/Union all 不同字段类型

MySQL在UNION查询中,不同字段类型会被自动转换为相容类型。ProtonBase在UNION查询中,如果字段类型不完全匹配,需要做类型转换。

=> create table t1(a int);
=> create table t2(b text);
=> select a from t1 union all select b from t2;

在MySQL中,虽然a和b类型不同,会自动进行类型转换。但在ProtonBase中,默认会报错，需要显式进行类型转换:

=> create table t2(b text);
=> select a from t1 union all select b from t2;
ERROR:  UNION types integer and text cannot be matched
 
-- 转换 a 的类型为 text
=> select a::text from t1 union all select b from t2;

常见函数对比


MySQL 函数	ProtonBase 函数	备注
CURDATE()	CURRENT_DATE	返回当前日期，没有时间部分
DATABASE()	CURRENT_DATABASE()	ProtonBase 使用 CURRENT_DATABASE() 返回当前数据库名称
DATE_ADD(date, interval)	date + INTERVAL 'n unit'	ProtonBase 使用 INTERVAL 语法，支持日期加法
DATE_FORMAT(date, format)	TO_CHAR(date, format)	ProtonBase 使用 TO_CHAR 格式化日期
DATE_SUB(date, interval)	date - INTERVAL 'n unit'	ProtonBase 使用 INTERVAL 语法，支持日期减法
DATEDIFF(date1, date2)	date1 - date2	ProtonBase 使用日期减法运算，返回天数差值
FORMAT(number, decimals)	TO_CHAR(number, 'FM999999.00')	ProtonBase 使用 TO_CHAR 格式化数字
FROM_UNIXTIME(unix_timestamp)	TO_TIMESTAMP(unix_timestamp)	TO_TIMESTAMP 将 UNIX 时间戳转换为时间
IF(condition, true_val, false_val)	CASE WHEN condition THEN true_val ELSE false_val END	ProtonBase 使用 CASE 语句代替 IF 函数
IFNULL(expr1, expr2)	COALESCE(expr1, expr2)	COALESCE 返回第一个非 NULL 的值
INSTR(str, substr)	POSITION(substr IN str)	ProtonBase 使用 POSITION 查找子串的位置
JSON_EXTRACT(json, path)	jsonb_extract_path(json, path)	ProtonBase 使用 jsonb_extract_path 提取 JSON 对象的值
JSON_UNQUOTE(json)	jsonb_extract_path_text(json, path)	ProtonBase 使用 jsonb_extract_path_text 提取 JSON 文本值
LAST_INSERT_ID()	RETURNING id 或 CURRVAL('seq_name')	ProtonBase 使用 RETURNING 返回插入后的自动增量主键值
MONTH(date)	EXTRACT(MONTH FROM date)	ProtonBase 使用 EXTRACT 获取日期的月份部分
RAND()	RANDOM()	ProtonBase 使用 RANDOM() 生成随机数
STR_TO_DATE(str, format)	TO_TIMESTAMP(str, format)	ProtonBase 使用 TO_TIMESTAMP 转换字符串为时间类型
UNIX_TIMESTAMP()	EXTRACT(EPOCH FROM NOW())	ProtonBase 使用 EXTRACT(EPOCH FROM timestamp) 获取 UNIX 时间戳
YEAR(date)	EXTRACT(YEAR FROM date)	ProtonBase 使用 EXTRACT 获取日期的年份部分

ProtonBase MySQL 兼容

为了方便用户从 MySQL 兼容的数据库迁移至 ProtonBase，我们提供了一系列兼容功能。这些功能主要涵盖 MySQL 常用函数、类型转换以及一些 MySQL 特性，使得从 MySQL 到 ProtonBase 的迁移过程更加顺利。

快速启用兼容功能：

-- 创建扩展
=> create extension mysql;
CREATE EXTENSION
 
-- 验证扩展是否生效（注：部分功能需要重连才会生效）
=> select ifnull(null, 2);
 ifnull
--------
      2
 
-- 删除扩展
=> drop extension mysql;

MySQL 函数兼容

函数名	示例
`unix_timestamp(date)`	`=> SELECT unix_timestamp('2020-03-29 03:45:00'); unix_timestamp ---------------- 1585424700`
`from_unixtime(unix_timestamp)`	`=> SELECT from_unixtime(1255033470); from_unixtime ---------------- 2009-10-09 04:24:30`
`from_unixtime(unix_timestamp, format)`	`=> SELECT from_unixtime(1255033470, '%Y %D %M %h:%i:%s'); from_unixtime ---------------- 2009 9th October 04:24:30`
`date_add(date, interval expr)`	`=> SELECT date_add('2024-10-01', INTERVAL '1 DAY'); date_add ---------------- 2024-10-02 00:00:00`
`date_sub(timestamp, interval expr)`	`=> SELECT date_sub('2024-10-01', INTERVAL '1 DAY'); date_sub ---------------- 2024-09-30 00:00:00`
`year(date)`	`=> SELECT year('2024-01-01'); year ---- 2024`
`month(date)`	`=> SELECT month('2024-01-01'); month ------ 1`
`datediff(date, date)`	`=> SELECT datediff('2024-10-01 01:01:01', '2024-09-29'); datediff -------- 2`
`curdate()`	`=> SELECT curdate(); curdate -------- 2025-02-21`
`str_to_date(str, format)`	`=> SELECT str_to_date('2023-02-05', '%Y-%m-%d'); str_to_date ---------------- 2023-02-05 00:00:00`
`date_format(date, format)`	`=> SELECT date_format('2024-10-01 16:25:02', '%Y-%m-%d %H:%i:%s'); date_format ---------------- 2024-10-01 16:25:02`
`ifnull(expr1, expr2)`	`=> SELECT ifnull(null, 2); ifnull ------ 2`
`isnull(expr)`	`=> SELECT isnull(1); isnull ------ 0`
`if(expr1, expr2, expr3)`	`=> SELECT if(false, 'More', 'Less'); if --- Less`
`instr(str, substr)`	`=> SELECT instr('xxyyzz', 'yy'); instr ----- 3`
`truncate(number, D)`	`=> SELECT truncate(123.123, 2); truncate -------- 123.12`
`find_in_set(str, strlist)`	`=> SELECT find_in_set('bb', 'aa,bb,cc'); find_in_set ----------- 2`
`substring_index(str, delim, count)`	`=> SELECT substring_index('a.b.c.d.e.f', '.', 2); substring_index --------------- a.b`
`json_extract(json_doc, path)`	`=> SELECT json_extract('{"balance": "7.77"}', '$.balance'); json_extract ------------ "7.77"`
`json_unquote(json_val)`	`=> SELECT json_unquote(json_extract('{"balance": "7.77"}', '$.balance')); json_unquote ------------ 7.77`
`database()`	`=> SELECT database(); database -------- test`
`rand()`	`=> SELECT rand(); rand ---- 0.7860905452848508`

如果所需的函数未在列表中提供，可以通过 CREATE FUNCTION 语句自定义创建所需的函数：

test=> CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
test=> SELECT add(1,1);
 add
-----
   2
(1 row)

类型转换兼容

源类型	目标类型
smallint	boolean
smallint	text
int	text
bigint	text
timestamp	text
numeric	text
text	numeric
int	boolean

如果所需的隐式转换未在列表中提供，可以通过 CREATE CAST 语句自定义创建所需的类型转换：

CREATE CAST (smallint AS boolean) WITH INOUT AS IMPLICIT;

支持 SELECT 非 GROUP BY 字段

行为说明：

MySQL 允许 SELECT 未在 GROUP BY 中声明的字段（隐式选择任意值）。
ProtonBase 默认行为与 PostgreSQL 一致，启用 mysql 扩展后，将兼容此特性。

示例：

-- 启用扩展后可执行
SELECT department, name, AVG(salary)
FROM employees
GROUP BY department;  -- name 未在 GROUP BY 中

除零操作返回 NULL