超全mysql转换postgresql数据库方案

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 ... )

语法差异

mysql

postgresql

是否相同

分页

select * from t1 limit 2,2;

select * from tbl limit 2 offset 2;

否

插入数据时：如果不存在则insert，存在则update

replace实现

upsert

否

大小写兼容

通过配置可兼容

表字段或表名为大写时，字段或表名必须添加双引号

否

if(), case when

if(), case when 条件1 then 符合值 else 不符合值 end；

case when 条件1 then 符合值 else 不符合值 end；

否

round(字段，小数位数)

round(字段，小数位数)

round(case(‘字段’ as numeric),小数位数)

否

null值判断

支持 ifnull()，NVL(),COALESCE()

支持COALESCE()

否

Update-单表更新

相同

相同（不可全表更新）

是

update-更新单表多个字段

相同

相同

是

update-更新并返回

select tem1,tem2 from update test set tem1 = '',tem2 = ''

UPDATE test SET tem1 = '',tem2 = '' RETURNING tem2,tem2;

否

Update表关联更新

相同

相同

是

Insert-单行插入

相同

相同

是

Insert-插入指定字段

相同

相同

是

insert-插入多行

相同

相同

是

insert-插入并返回

不支持

INSERT INTO() RETURNING did

否

Insert-插入，存在则更新

INSERT INTO () VALUE() ON DUPLICATE KEY UPDATE name = EXCLUDED.name

INSERT INTO distributors ( did , dname ) VALUES ( 9 , ' Antwerp Design' ) ON CONFLICT （did）DO UPDATE SET name = EXCLUDED.name

否

insert-不存在插入，存在更新

replace实现

upsert语句

否

SELECT

相同

相同

是

DELETE

DELETE FROM table

DELTE FROM table（不可全表删除）

是

DELETE

DELETE FROM table WHERE

DELETE FROM table WHERE

是

DELETE-删除并返回

不支持

DELETE FROM table WHERE RETURNING * ;

INDEX-add

支持alter，create创建

支持create

INDEX-delete

支持alter，drop

支持drop

字符串常量

支持单双引号

支持双引号

否

插入数据时自增主键

写法一：insert into t1(name) values(‘zhangshan’); 写法二：insert into t1(id, name) values(null, ‘zhangshan’);

insert into t1(name) values(‘zhangshan’);

否

库名长度

无强制限制

库名、表名限制命名长度，建议表名及字段名字符总长度小于等于63。

序号

场景

示例

1

mapper接口方法上使用注解编写sql语句

@SELECT("SELECT * FROM TEST")

2

mapper的xml文件中的语句

3

mybatis-plus使用的实体类相关特殊列名修改

4

mybatis-plus使用QueryWrapper条件构造相关列修改

5

代码中使用字符串拼接的sql语句

序号

差异关键字

差异描述

mysql示例

pg示例

1

limit

limit 0,1 改成 limit 1 offset 0

select * from t_user limit 0,1

select * from t_user limit 1 offset 0

2

字段大小写

查询字段为驼峰命名，加上双引号，select \"startTime\" form ...

-

-

3

ifnull

没有ifnull函数，改用COALESCE()函数，

select ifnull(avatar, 'aa'),"name" from t_user;

select COALESCE(avatar, 'aa'),"name" from t_user;

4

DATE_SUB()

没有DATE_SUB()时间计算函数，改用select now() + '1 seconds' 或 select now() + '-1 seconds'

select DATE_SUB(logintime,INTERVAL 1 DAY) from t_user;

select logintime + '1 days' from t_user;

5

别名大小写

查询字段的别名也需要用双引号包起来，select start_time as \"startTime\" from ...

-

-

6

正则表达式、REGEXP

正则表达式匹配，where taget_name ~ '^123$|asd'...，其中~为匹配正则表达式区分大小写，~*为不区分大小写，前面加叹号则为不匹配正则表达式如：!~

select * from t_user where name REGEXP '^adm*';

select * from t_user where name ~ '^adm*';

7

binary

mysql的where判断加上binary来区分大小写，where binary id = ‘abc’，在pg中是直接区分大小写的，将binary去掉就行

select * from t_user where binary name = 'ADMIN';

select * from t_user where name = 'ADMIN';

8

group_concat_max_len

set session group_concat_max_len=...在pg中没有，注释

-

-

9

GROUP_CONCAT()

GROUP_CONCAT()函数没有，使用array_to_string(array_agg(target_name), ',') from ... 代替

select GROUP_CONCAT(industry) from t_company group by province;

select array_to_string(array_agg(industry), ',') from t_company group by province;

10

ISNULL

ISNULL没有使用is null来进行判断

select * from t_user where ISNULL(avatar);

select * from t_user where avatar is null;

11

&&，||

&&和||没有这个符号，用and和or替换

select * from t_user where ISNULL(avatar) && realname = '日志管理员';

select * from t_user where avatar is null and realname = '日志管理员';

12

date_format()

date_format()函数用不了，换成 to_char，select to_char(create_time, 'yyyy-MM-dd hh24:mi:ss') from ...

select date_format(logintime, '%Y-%m-%d') from t_user;

select to_char(logintime, 'yyyy-MM-dd') from t_user;

13

if()

没有if()函数，改用 case when 条件 then 值 else 值 end

select if(name = 'admin', true, false) as isAdmin from t_user

select case when name = 'admin' then true else false end as isAdmin from t_user

14

FIND_IN_SET()

where FIND_IN_SET('123', user_ids) ... 使用不了，换成 where '123' = ANY(string_to_array(user_id, ',')) ...

select * from t_company where find_in_set('浙江省', address);

select * from t_company where '浙江省' = ANY(string_to_array(address, ','));

15

数字字符串比较、连表

mysql中能直接对数字和字符串进行=相等判断，pg不行，换成，'123' = cast(123 as VARCHAR) 或者 123 = cast('123' as INTEGER)；或者123 = '123'::INTEGER

例如 select * from t_model_layout_task_record as r left join t_model_layout_form as f on r.formId = f.id，其中r.formId是vachar，f.id是int，这样连表是报错的，

改成：select * from t_model_layout_task_record as r left join t_model_layout_form as f on r.formId = cast(f.id as VARCHAR)

select * from t_model_layout_task_record as r left join t_model_layout_form as f on r.formId = f.id

select * from t_model_layout_task_record as r left join t_model_layout_form as f on r.formId = cast(f.id as VARCHAR)

16

SYSDATE()

没有 SYSDATE() 函数，换成 NOW()

select SYSDATE();

select now();

17

from_unixtime()

没有from_unixtime()函数，换成to_timestamp()

select from_unixtime(1673833489);

select to_timestamp(1673833489);

18

auto_increment

mysql查询information_schema.tables的auto_increment字段获取主键自增的值，而pg的information_schema.tables中不存在auto_increment。

pg通过该函数 pg_get_serial_sequence(‘库名.表名’, '自增字段名') 获取表的自增值

-

-

19

unix_timestamp()

没有unix_timestamp()函数，换成date_part('epoch', now())::integer，例如，select date_part('epoch', start_time)::integer from t_model_layout_task_record

select unix_timestamp(createtime) from t_user;

select date_part('epoch', createtime)::integer from t_user;

20

ON DUPLICATE KEY UPDATE

mysql的存在则更新写法，pg换成，

改成：

INSERT INTO 表名(列1,列2...) VALUES ('值１', '值２', ...）ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列１='值', 列２='值', ...;

如果是批量插入的话改成：

INSERT INTO 表名(列1,列2...) VALUES ('值１', '值２', ...）ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列１=excluded.列1, 列２=excluded.列2, ...;

需要注意的是：

唯一或排除约束字段名必须是一个唯一索引或唯一联合索引，如果填写多个唯一索引则不生效，如果要生效的话，必须把他们建立成一个唯一联合索引。

insert into t_user(id,name,logintime,PASSWORD,createuser,updateuser) values(1,'test',now(),'test',1,1) on duplicate key update logintime = values(logintime);

insert into t_user(id,name,logintime,"PASSWORD",createuser,updateuser) values(1,'test',now(),'test',1,1) ON CONFLICT(id) DO UPDATE SET logintime=excluded.logintime;

21

instr()

没有instr()函数，改成like。例：

SELECT * FROM user WHERE INSTR(username,'2')>0

SELECT * FROM user WHERE username like '%2%'

select * from t_user where INSTR(name,'a')>0

select * from t_user where name like '%a%'

22

` `

查询的字段 ` 号换成双引号，select \"name\" from ...

select `name` from t_user;

select "name" from t_user;

23

表名大小写

表名称中含有大写字母需要用双引号将表格名称包含起来

select PASSWORD from t_user;

select "PASSWORD" from t_user;

24

表名别名大小写

查询结果别名中有大写需要用双引号包含起来如果不包含起来查询结果会自动转为小写，select area_id as "areaId" from ...

-

-

25

GROUP BY

GROUP BY 分组查询不能查不在分组内的数据（也不能对非GROUP BY的字段进行ORDER BY排序），需要变成连表查询，链表查需要注意是否有重复数据。

例如：

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname ORDER BY wmname;（查询失败）

如果没有重复数据换成：

SELECT m.cname, m.wmname, t.mx

FROM (

SELECT cname, MAX(avg) AS mx

FROM makerar

GROUP BY cname

) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg

ORDER BY m.wmname

;

如果有重复数据需要进行去重则换成：（利用窗口函数）

SELECT

cname,

DISTINCT ON(wmname)wmname wmname,

MAX(avg) OVER (PARTITION BY wmname) AS mx

FROM makerar

如果需要对不是去重的字段进行排序则在最外层加上排序：

SELECT * FROM (

SELECT

cname,

DISTINCT ON(wmname)wmname wmname,

MAX(avg) OVER (PARTITION BY wmname) AS mx

FROM makerar

) bb

ORDER BY m.wmname

select name,count(type) from t_company group by type order by type desc;

select aa.name, aa.count from (select distinct on(type)type "type",name,count(type) OVER (PARTITION BY type) from t_company) aa order by aa.type desc;

26

uuid()

uuid()函数不存在，安装扩展函数

create extension "uuid-ossp";

select uuid();

select uuid_generate_v4();

27

时间模糊查询

在进行时间模糊查询的时候需要转换一下时间，例如：

SELECT * FROM xxx WHERE to_char(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE CONCAT(#{mouth},'%')

select realname,logintime from t_user where logintime like '%2023%'

select realname,logintime from t_user where to_char(logintime, 'yyyy-MM-dd hh24:mi:ss') like '%2023%'

28

replace into

在业务先删除在插入。

如果可以的话可以用下面的写法，看业务需要

pg 没有这个语法，

如果根据以为能改成存在更新不存在就插入的话可以用以下写法：

INSERT INTO 表名(列1,列2...) VALUES ('值１', '值２', ...）ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列１='值', 列２='值', ...;

如果是批量插入的话改成：

INSERT INTO 表名(列1,列2...) VALUES ('值１', '值２', ...）ON CONFLICT(唯一或排除约束字段名) DO UPDATE SET 列１=excluded.列1, 列２=excluded.列2, ...;

需要注意的是：

唯一或排除约束字段名必须是一个唯一索引或唯一联合索引，如果填写多个唯一索引则不生效，如果要生效的话，必须把他们建立成一个唯一联合索引。

-

-

29

like

如果对int字段进行like判断需要将字段转为VARCHAR类型，例如：

where state::VARCHAR like concat('%', #{state},'%') ...

select id from t_user where id like '%2023%'

select id from t_user where id::varchar like '%2023%'

30

时间/between and

如果判断between and是时间，

情况1、数据库是timestamp，and两边是字符串格式为'yyyy-MM-dd HH:mm:ss'则

where to_char(create_time, 'yyyy-MM-dd hh24:mi:ss') between #{startTime} and #{endTime}

select * from t_user where logintime between '2023-01-01 00:00:00' and '2023-01-16 23:59:59'

select * from t_user where to_char(logintime, 'yyyy-MM-dd hh24:mi:ss') between '2023-01-01 00:00:00' and '2023-01-16 23:59:59'

31

关键字

关键字：

name、value、label、moudle

需要双引号包含起来

-

-

32

运算符号 +

mysql 返回结果可直接通过+拼接字符串， pg不支持，改成concat方法

-

-

33

实体类

实体类里面@TableField里写的字段有大写或者 ` 号的要改成双引号，例如 @TableField("\"order\"")、@TableField("\"createTime\"")

-

-

34

curdate()

没有curdate()函数，换成，current_date。注意没有括号例如：select current_date;

select curdate();

select current_date;

35

inet_aton()、inet6_aton()

没有inet_aton()和inet6_aton()函数，使用inet()函数进行判断

select inet_aton('1.1.1.1')

select inet('1.1.1.1')

36

locate()

没有locate()函数，换成position()

select locate('b','ayyvkhlbm')

select position('b' in 'ayyvkhlbm')

37

ORDER BY

按照指定字段值排序

ORDER BY FIELD (`field`, value1, value2)

使用CASE WHEN END替代（eg：ORDER BY CASE field WHEN value1 THEN WHEN value2 THEN 2 END）

38

convert()

没有convert()函数，用convert_from()替换

convert(vendor using gbk)

convert_from(vendor::bytea, 'gbk')

39

bool字段查询和插入，不可以使用0，1；修改PG数据库内置转换规则SQL语句：

update pg_cast set castcontext='i' where castsource ='integer'::regtype and casttarget='boolean'::regtype;

update pg_cast set castcontext='i' where castsource ='integer'::regtype and casttarget='boolean'::regtype;

-- eg: type为bool类型

insert into test_ly(id, type) VALUES(2,0);

select * from test_ly where type = 1;

-- eg: type为bool类型

insert into test_ly(id, type) VALUES(2,0);

select * from test_ly where type = 1;

40

关联字段，条件查询字段类型不一致时，产生报错问题；强制转换，如转换为字符串 ::varchar,bigint ::BIGINT

::BIGINT

-

select u.id from t_user as u left join t_cascade_work_order w on u.id = w.author_id::BIGINT;

41

substring_index()

PG中无该函数，创建自定义函数，实现该功能文章来源地址https://www.toymoban.com/news/detail-464479.html

CREATE OR REPLACE FUNCTION substring_index(varchar, varchar, integer)
RETURNS varchar AS $$
DECLARE
tokens varchar[];
length integer ;
indexnum integer;
BEGIN
tokens := pg_catalog.string_to_array($1, $2);
length := pg_catalog.array_upper(tokens, 1);
indexnum := length - ($3 * -1) + 1;
IF $3 >= 0 THEN
RETURN pg_catalog.array_to_string(tokens[1:$3], $2);
ELSE
RETURN pg_catalog.array_to_string(tokens[indexnum:length], $2);
END IF;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;