MySQL 转 PostgreSQL 笔记

发表 分类

由于前一段时间公司项目需求，参与了把 MySQL 数据库迁移到 PostgreSQL 数据库的工作，本文主要记录常用语法的转换规则，待补充完善。

数据库版本：

  • MySQL 8.0.x
  • PostgreSQL 15.0.x

基本规则

引号、反引号

在 PostgreSQL 中，单引号 AS 'col1' 和反引号 `` 都不支持，要么去掉，要么用双引号代替。

在 Java 的 MyBitis xml 文件中，如果返回结果是 Map 结构，并且返回字段用 AS 转为驼峰，则必须用双引号包起来，否则返回后会转换为小写，如

1
2
3
4
5
<select id="getWaitAuditPurchaseReturn" resultType="java.util.Map">
  select
    coalesce(count(1),0) as "waitAuditNum",
    min(prepared_bill_time) as "preparedBillMintime"
    ...

WHERE 中字段值的大小写与实际数据不一致

1
2
3
4
5
6
-- 这样在 MySQL 可以正常查询出来结果，但是在 PostgreSQL 中查询不到
-- 表中存的实践数据是大写的 MATERIAL_USE_TYPE
select id from system_user_privilege where tableAlias.privilege_type = 'material_use_type'

-- 可以用 UPPER 或 LOWER 函数处理一下
select id from system_user_privilege where tableAlias.privilege_type = UPPER('material_use_type')

WHERE 后面用 ||

如果有，替换成 OR ，否则对应的过滤条件不生效。

因为在 PG 中 || 通常只用于字符串连接。同时它也可以连接数组、行、列等，其行为将取决于这些数据类型的定义和 PostgreSQL 中的类型转换规则。

类型错误

PostgreSQL 是强类型的，不同类型的字段进行比较时需要显示的转换成同一类型，否则会报错；而 MySQL 中会自动进行转换。

转换时候可以使用 cast() 函数或 :: 符号。

1
2
3
4
5
6
7
-- MySQL
AND (DATE(NOW()) - DATE(wb.start_time)) > ss.value

-- 上面的写法，pg 报错：nested exception is org.postgresql.sdsdf.il.PSQLException: ERROR: operator does not exist: integer > text

-- PostgreSQL
AND (DATE(NOW()) - DATE(wb.start_time)) > ss.value::integer

NULL 值的默认排序规则

在 PostgreSQL 中，NULL 值的排序规则与 MySQL 不一致，需要手动指定，才能与原 MySQL 排序规则一致

1
2
3
4
5
-- 降序 NULL 值放最后显示
ORDER BY material_code DESC NULLS LAST

-- 升序 NULL 值放最前面显示
ORDER BY material_code ASC NULLS FIRST

默认排序行为差异

下面这段 SQL

1
SELECT * FROM sys_dict_data ORDER BY dict_sort ASC

查询到的结果中 dict_sort 都是 0，这种情况的查询结果 MySQL 和 PostgreSQL 的默认处理方式会存在差异：

  • MySQL会再按照表中的主键 id 进行排序；
  • PostgreSQL 则会根据记录在表中的物理存储顺序排序；

这种差异会导致两边排序不一致，在 PostgreSQL 中需要明确指定再按 id 排序，以保证与 MySQL 的默认行为一致。

FOR UPDATE 不能跟着 GROUP BY 一起使用

1
2
3
4
5
-- 下面这样写会报错：Cause: org.postgresql.util.PSQLException: ERROR: FOR UPDATE is not allowed with GROUP BY clause
WHERE
  tableAlias.id IN ( '' )
GROUP BY tableAlias.id
FOR UPDATE

需要改成

1
2
3
4
5
6
7
8
-- 再包一层，外层加 FOR UPDATE
SELECT t.* FROM (
  -- .....
  GROUP BY tableAlias.id
) t
WHERE
  t.id IN ( '' )
FOR UPDATE

聚合函数不能嵌套

1
2
3
4
5
6
-- wa.warehouse_area_type 不在后面的 GROUP BY 里，所以需要加聚合函数
-- 下面这样写，会报错：### Cause: org.postgresql.util.PSQLException: ERROR: aggregate function calls cannot be nested
SELECT
  -- .....
  SUM(CASE WHEN MIN(wa.warehouse_area_type) = 'STORE_AREA' THEN COALESCE(wsb.frozen_stock_num, 0) ELSE 0 END) AS occupyNum
  -- .....

需要改成

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 使用子查询，外层 sum ，内层使用 case...when 计算明细
SELECT
  -- .....
  SUM(occupyNum) AS occupyNum,
  -- .....
FROM (
  SELECT
    -- .....
    (CASE WHEN wa.warehouse_area_type = 'STORE_AREA' THEN COALESCE(wsb.frozen_stock_num,0) ELSE 0 END) AS occupyNum
    -- .....
  FROM warehouse_stock_batch wsb
) t
GROUP BY
  -- .....

聚合函数构造的字段，不能直接跟在 HAVING 后面

1
2
3
4
5
6
7
8
9
10
-- 下面这样写会报错：column "custommd5" does not exist LINE 56:  having customMd5 in ('')
SELECT
  -- .....
  -- customMd5 是由聚合函数 CONCAT_WS() 构造出来的
  MD5(CONCAT_WS('_', xx, xx, xx,xx)) AS customMd5
FROM warehouse_stock_batch wsb
GROUP BY
  -- .....
HAVING
  customMd5 IN ('')

需要像下面这样再包一层，或者把聚合的字段在 having 中再写一遍，这种适合聚合的内容不多的场景。

1
2
3
4
5
6
7
8
9
10
11
SELECT t.* FROM (
  SELECT
    -- .....
    md5(concat_ws('_', xx, xx, xx,xx)) as customMd5
    -- .....
  FROM warehouse_stock_batch wsb
  GROUP BY
    -- .....
) t
WHERE
  t.customMd5 IN ('')

GROUP BY 区分大小写

MySQL 中 GROUP BY 的字段默认不区分大小写，PG 中严格区分，会造成数据行数不一致。

常用函数替换

like –> ilike

PostgreSQL 中 like 默认区分大小，直接改成 ilike 以与 MySQL 保持一致。经测试，二者性能差异不大。

group_concat –> string_agg

1
2
3
4
5
-- MySQL
GROUP_CONCAT(DISTINCT c.hospital_area_id SEPARATOR ',') AS hospital_area_id

-- PostgreSQL
STRING_AGG(DISTINCT c.hospital_area_id, ',') AS hospital_area_id

ifnull –> coalesce

1
2
3
4
5
-- MySQL
IFNULL(sn.price, 0)

-- PostgreSQL
COALESCE(sn.price, 0)

date_format –> to_char

1
2
3
4
5
-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')

-- PostgreSQL
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')

date_add

1
2
3
4
5
-- MySQL 返回 2023-03-03
SELECT DATE_ADD(CURDATE(), INTERVAL 1 DAY)

-- PostgreSQL
SELECT TO_CHAR(CURRENT_DATE::TIMESTAMP + '1 day', 'YYYY-MM-DD')

last_day –> date_trunc

1
2
3
4
5
-- MySQL 中获取某个时间所在月的最后一天，返回 2023年03月31日
select DATE_FORMAT(LAST_DAY('2023-03-02'), '%Y年%m月%d日')

-- PostgreSQL
SELECT TO_CHAR(DATE_TRUNC('month', '2023-03-02'::TIMESTAMP) + interval '1 month - 1 day', 'YYYY年MM月DD日')

date_iff

1
2
3
4
5
-- MySQL 返回相差的天数
SELECT DATEDIFF('2023-03-31 23:59:59', NOW())

-- PostgreSQL
SELECT DATE('2023-03-31 23:59:59') - DATE(NOW())

if –> case when

1
2
3
4
5
-- MySQL
SUM( IF(in_out_flag = 'OUT', material_amount, 0) ) AS out_house_amount

-- PostgreSQL
SUM( CASE WHEN in_out_flag = 'OUT' THEN material_amount ELSE 0 END ) AS out_house_amount

json_extract –> json_extract_path_text

1
2
3
4
5
-- MySQL
SELECT JSON_EXTRACT(`json_col1`, '$[*].financeKind') AS v

-- PostgreSQL
SELECT json_extract_path_text(json_array_elements(json_col1::JSON), 'financeKind') AS v

find_in_set –> any

1
2
3
4
5
6
7
-- MySQL
SELECT 1 FROM material_kind mk
WHERE find_in_set(mk.id, 'id1,id2,id3,id4,id5')

-- PostgreSQL
SELECT 1 FROM material_kind mk
WHERE mk.id = any(string_to_array('id1,id2,id3,id4,id5'))

其它常用代码片段

REPLACE INTO 转成 INSERT INTO … ON CONFLICT … DO UPDATE

1
2
3
4
5
6
7
8
9
-- MySQL
REPLACE INTO `version_info` (`id`, `version_no`, `platform`, `update_by`, `update_date`, `update_desc`)
VALUES (2, 'v1.17.4.2', 'SPD', '16094bcb5000004', now(), '生产');

-- PostgreSQL
INSERT INTO version_info (id, version_no, platform, update_by, update_date, update_desc)
VALUES (2, 'v1.17.4.2', 'SPD', '16094bcb5000004', NOW(), '生产')
ON CONFLICT (id)
DO UPDATE SET version_no = 'v1.17.4.2', platform = 'SPD', update_by = '16094bcb5000004', update_date = NOW(), update_desc = '生产';

INSERT IGNORE INTO 转成 ON CONFLICT DO NOTHING

1
2
3
4
5
-- MySQL
INSERT IGNORE INTO `cloud_sys_role_menu` (`role_id`, `menu_id`) VALUES ('100', '2xt807qn3g1lymlrx');

-- PostgreSQL
INSERT INTO cloud_sys_role_menu (role_id, menu_id) VALUES ('100', '2xt807qn3g1lymlrx') ON CONFLICT DO NOTHING;

给表添加新字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- MySQL
ALTER TABLE `department_limit_audit_his`
ADD COLUMN `department_practical_amount` decimal(19, 6) NULL DEFAULT NULL COMMENT '科室实际收货量' AFTER `limit_value`;

-- PostgreSQL
-- 1. 添加字段，多个用逗号间隔，如：ADD COLUMN col1, ADD COLUMN col2, ...;
ALTER TABLE department_limit_audit_his
ADD COLUMN department_practical_amount NUMERIC(19, 6) DEFAULT NULL;
-- AFTER limit_value;

-- 2. 给字段添加注释
COMMENT ON COLUMN material_charge.medical_advice_stage IS '科室实际收货量';

-- 3. 调整位置
-- 暂时没发现好的实现方式