MySQL迁移到PostgreSQL
本文用于指引将数据库从MySQL迁移到PostgreSQL。
1. 数据库迁移
1.1 database与schema的选择
MySQL和PG的概念区别：
- 在MySQL中，database与schema是等同的概念，database与database之间可以关联访问
- 在PostgreSQL中，一个database可以包含多个schema,database与database之间不能关联访问，schema与schema之间可以关联访问
建议从MySQL迁移到PostgreSQL时:
- 每个MySQL Database对应PG新建一个Database
- PG侧根据应用名建立专用schema和专用用户，将专用用户的默认schema设置为应用专用schema
-- 查询默认schemaSHOW search_path;-- 创建schema并设置用户默认schemaCREATE SCHEMA my_schema;GRANT ALL ON SCHEMA my_schema TO my_user;-- 连接级别设置默认schemaSET search_path TO my_schema;-- 数据库级别设置默认schemaALTER database "my_database" SET search_path TO my_schema;- 在不明确自己是否需要的情况下，不使用多schema特性，保持迁移前后逻辑统一
若代码中使用了select * from
database.table这种语法，需将database前缀去掉，因为在PG中，这种语法会去访问其他schema，导致找不到表
1.2 数据割接
根据PostgreSQL和MySQL的语法异同，重新组织DDL，在目标PostgresSQL建库建表
主要语法区别包括字段自增、字段注释、建索引、字段类型转换等
使用
数据同步与迁移工具提供的MySQL->PostgreSQL数据迁移工具，将数据从MySQL迁移到PostgreSQL
2. 研发与使用差异
2.1 命名规则差异
【强制】库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。
【强制】对象名（表名、列名、函数名、视图名、序列名等对象名称）规范，对象名务必只使用小写字母，下划线，数字。不要以pg开头，不要以数字开头，不要使用保留字。
保留字参考
https://www.postgresql.org/docs/9.5/static/sql-keywords-appendix.html
【强制】query中的别名不要使用 “小写字母，下划线，数字” 以外的字符，例如中文。
参考：
http://120.76.251.97:8181/docs/pgbook/pgbook-1c1ajj5u77j6t
2.2 常用DML语法差异
| mysql | postgresql | 说明 |
|---|---|---|
| # | – | mysql的单行注释使用#，pg使用– |
| ‘ “ vs. ` | ‘vs. “ | mysql使用单引号或双引号代表字符串，pg只接受单引号作为字符串；mysql使用`标识表名、列名，pg使用双引号 |
| … WHERE lastname=”smith” | … WHERE lower(lastname)=’smith’ | mysql字符串比较是大小写无关；pg的则是大小写敏感的，pg实现大小写无关的字符串比较，可以使用lower函数，或者ILIKE等 |
LastName = lastname | “LastName” <> “lastname” | mysql字段、表名等都可以大小写无关;pg 名字规范是只能用小写 ，不能用大写，如果一定要用大写字母，那么要加双引号。 |
| ‘foo’ || ‘bar’ | ‘foo’ or ‘bar’ | mysql支持||,&& 作为逻辑运算符；pg只支持and ,or这种标准SQL语法 |
| SELECT … LIMIT offset, limit SELECT … LIMIT limit OFFSET offset | SELECT … LIMIT limit OFFSET offset | 翻页语法 |
| LAST_INSERT_ID() | CURRVAL(‘serial_variable’) | mysql使用LAST_INSERT_ID()返回AUTO_INCREMENT生成的最后一个id 值; pg有更具可读性的方法，返回插入的数据列：INSERT INTO mytable VALUES (…) RETURNING my_serial_column_name; |
2.3 常用DDL语法差异
用例子来说明：
mysql 的建表语句:
DROP TABLE IF EXISTS `telepg_node`;CREATE TABLE `telepg_node` ( `pg_node_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'PGSQL服务标识', `pg_inst_id` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'PGSQL实例标识', `pg_host_id` bigint(20) NULL DEFAULT NULL COMMENT 'PGSQL服务器标识', `install_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '安装路径', `data_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '数据路径', `script_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '脚本路径', `backup_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '备份路径', `service_port` int(11) NOT NULL COMMENT '服务端口', `user_id` bigint(20) NULL DEFAULT NULL COMMENT '用户标识', `tenant_id` bigint(20) NULL DEFAULT NULL COMMENT '租户标识', `zk_path` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT 'pg节点的zookeeper路径', `create_timestamp` datetime NULL DEFAULT NULL COMMENT '创建时间', `update_timestamp` datetime NULL DEFAULT NULL COMMENT '更新时间', `remark` varchar(1024) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注', PRIMARY KEY (`pg_node_id`) USING BTREE, INDEX `idx_tenant_inst`(`tenant_id`,`pg_inst_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;转成postgresql：
drop table if EXISTS telepg_node;CREATE TABLE "telepg_node" ( "pg_node_id" bigserial , "pg_inst_id" varchar(64) NOT NULL , "pg_host_id" bigint DEFAULT NULL , "install_path" varchar(256) NOT NULL , "data_path" varchar(256) NOT NULL , "script_path" varchar(256) NOT NULL , "backup_path" varchar(256) NOT NULL , "service_port" int NOT NULL , "user_id" bigint DEFAULT NULL , "tenant_id" bigint DEFAULT NULL , "zk_path" varchar(256) DEFAULT NULL , "create_timestamp" timestamp NULL DEFAULT NULL , "update_timestamp" timestamp NULL DEFAULT NULL , "remark" varchar(1024) DEFAULT NULL , primary key ("pg_node_id")) ;CREATE INDEX public_telepg_node_tenant_id0_idx ON public.telepg_node USING btree (tenant_id, pg_inst_id);COMMENT ON COLUMN "telepg_node". "pg_node_id" IS 'PGSQL服务标识';COMMENT ON COLUMN "telepg_node". "pg_inst_id" IS 'PGSQL实例标识';COMMENT ON COLUMN "telepg_node". "pg_host_id" IS 'PGSQL服务器标识';COMMENT ON COLUMN "telepg_node". "install_path" IS '安装路径';COMMENT ON COLUMN "telepg_node". "data_path" IS '数据路径';COMMENT ON COLUMN "telepg_node". "script_path" IS '脚本路径';COMMENT ON COLUMN "telepg_node". "backup_path" IS '备份路径';COMMENT ON COLUMN "telepg_node". "service_port" IS '服务端口';COMMENT ON COLUMN "telepg_node". "user_id" IS '用户标识';COMMENT ON COLUMN "telepg_node". "tenant_id" IS '租户标识';COMMENT ON COLUMN "telepg_node". "zk_path" IS 'pg节点的zookeeper路径';COMMENT ON COLUMN "telepg_node". "create_timestamp" IS '创建时间';COMMENT ON COLUMN "telepg_node". "update_timestamp" IS '更新时间';COMMENT ON COLUMN "telepg_node". "remark" IS '备注';简单来说，postgresql的ddl语句，不能直接带注释,也不需要指定字符编码。字符编码在create database时指定的。
索引需要单独语句建.索引的名字要求唯一性，最好跟表名相关。
2.4 常用元数据查询差异
| mysql | postgresql | psql客户端快捷语法 |
|---|---|---|
| DESCRIBE table | select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = ‘t1’; | \d table |
| SHOW DATABASES | SELECT datname AS Database FROM pg_database WHERE datistemplate = ‘f’; | \l |
| SHOW TABLES | SELECT c.relname AS Tables_in FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relkind = ‘r’ AND relname NOT LIKE ‘pg_%’; | \dt |
- psql 是自带的命令行工具
2.5 数据类型差异
| mysql | postgresql |
|---|---|
| TINYINT | SMALLINT |
| SMALLINT | SMALLINT |
| MEDIUMINT | INTEGER |
| BIGINT | BIGINT |
| FLOAT | REAL |
| DOUBLE | DOUBLE PRECISION |
| BOOLEAN | BOOLEAN |
| TINYTEXT | TEXT |
| TEXT | TEXT |
| MEDIUMTEXT | TEXT |
| LONGTEXT | TEXT |
| BINARY(n) | BYTEA |
| VARBINARY(n) | BYTEA |
| TINYBLOB | BYTEA |
| BLOB | BYTEA |
| MEDIUMBLOB | BYTEA |
| LONGBLOB | BYTEA |
| DATE | DATE |
| TIME | TIME [WITHOUT TIME ZONE] |
| DATETIME | TIMESTAMP [WITHOUT TIME ZONE] |
| TIMESTAMP | TIMESTAMP [WITHOUT TIME ZONE] |
| AUTO_INCREMENT | SERIAL , BIGSERIAL |
| column ENUM (value1, value2, […] | column VARCHAR(255) NOT NULL, CHECK (column IN (value1, value2, […])) pg可以自定义数据类型实现类似效果： CREATE TYPE mood AS ENUM ('sad','ok','happy'); CREATE TABLE person ( current_mood mood ... ) |
2.4 常用元数据查询差异
| mysql | postgresql | psql客户端快捷语法 |
|---|---|---|
| DESCRIBE table | select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = ‘t1’; | \d table |
| SHOW DATABASES | SELECT datname AS Database FROM pg_database WHERE datistemplate = ‘f’; | \l |
| SHOW TABLES | SELECT c.relname AS Tables_in FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE pg_catalog.pg_table_is_visible(c.oid) AND c.relkind = ‘r’ AND relname NOT LIKE ‘pg_%’; | \dt |
- psql 是自带的命令行工具
2.5 语法差异&改造建议
(1) LIMIT & OFFSET
| mysql | postgresql | 备注 |
|---|---|---|
| LIMIT n | LIMIT n | 偏移量0，取前n行数据。 |
| LIMIT m,n | LIMIT n OFFSET m | 偏移量m，取n行数据。 |
| LIMIT n OFFSET m | LIMIT n OFFSET m | 偏移量m，取n行数据。两者语法一致，兼容性好 |
（2）字符串常量
字符串常量MySQL可以用单引号或双引号包含, 但PostgreSQL只能用单引号，如：
select * from t1 where t1.name = ‘a’; // MySQL和PostgreSQL都可以
select * from t1 where t1.name = “a”; // PostgreSQL不可以
（3）插入数据时自增主键
对于自增主键的value为null的插入语句，如insert into t1(id, name) values(null, ‘zhangshan’);
MySQL可正确插入，主键成功自增，PostgreSQL则报错。
所以PostgreSQL的插入语句，有2种写法：
1）删除自增主键id字段：insert into t1(name) values(‘zhangshan’);
2）显示提供自增主键id值：insert into t1(id, name) values(nextval(‘t1_id_seq’::regclass), ‘zhangshan’);
推荐使用第1种写法，此写法兼容MySQL。
请注意，如果客户端使用Mybatis ORM框架，当不指定自增主键值插入数据时，使用Dao层的insertSelective方法，该方法最终生成写法1的SQL语句。
（4）数据记录顺序
记录被update后，在select不加order by的情况下：
MySQL会按照id顺序排序记录。
PostgreSQL不保证记录顺序，所以如果需要保证记录的顺序，需加order by。
(5) bool字段
一般mysql中用bit(1)表示布尔值bool，查询支持的参数为1、true或0、false。
PostgreSQL用bool表示bool值。查询支持的参数如下：
真
- TRUE
- true
- ‘t’
- ‘true’
- ‘y’
- ‘yes’
- ‘1’
假
- FALSE
- false
- ‘f’
- ‘false’
- ‘n’
- ‘no’
- ‘0’
Mybatis框架生成的对应POJO为Boolean值
兼容性实践，建议：查询参数传true或false
PG也支持int自动转bool , 需要用超级用户执行SQL ：
postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype; UPDATE 1 这个修改是database级别的。
参考文章： https://github.com/digoal/blog/blob/master/201801/20180131_01.md
参考
A.参考阅读
https://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL
https://www.postgresql.org/docs/12/sql-createtable.html
C. 一些MySQL 转PostgreSQL工具
SQL 转化工具
https://github.com/ahammond/mysql2pgsql.git (有部分语句无法正常转)
https://www.convert-in.com/mysql-to-postgres.htm (收费软件)
数据迁移工具：
https://github.com/AnatolyUss/nmig
