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可以自定義數(shù)據(jù)類型實現(xiàn)類似效果： 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;

否

插入數(shù)據(jù)時：如果不存在則insert，存在則update

replace實現(xiàn)

upsert

否

大小寫兼容

通過配置可兼容

表字段或表名為大寫時，字段或表名必須添加雙引號

否

if(), case when

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

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

否

round(字段，小數(shù)位數(shù))

round(字段，小數(shù)位數(shù))

round(case(‘字段’ as numeric),小數(shù)位數(shù))

否

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表關(guān)聯(lián)更新

相同

相同

是

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實現(xiàn)

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創(chuàng)建

支持create

INDEX-delete

支持alter，drop

支持drop

字符串常量

支持單雙引號

支持雙引號

否

插入數(shù)據(jù)時自增主鍵

寫法一：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使用的實體類相關(guān)特殊列名修改

4

mybatis-plus使用QueryWrapper條件構(gòu)造相關(guān)列修改

5

代碼中使用字符串拼接的sql語句

序號

差異關(guān)鍵字

差異描述

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函數(shù)，改用COALESCE()函數(shù)，

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

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

4

DATE_SUB()

沒有DATE_SUB()時間計算函數(shù)，改用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

正則表達(dá)式、REGEXP

正則表達(dá)式匹配，where taget_name ~ '^123$|asd'...，其中~為匹配正則表達(dá)式區(qū)分大小寫，~*為不區(qū)分大小寫，前面加嘆號則為不匹配正則表達(dá)式如：!~

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

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

7

binary

mysql的where判斷加上binary來區(qū)分大小寫，where binary id = ‘a(chǎn)bc’，在pg中是直接區(qū)分大小寫的，將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()函數(shù)沒有，使用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來進(jìn)行判斷

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()函數(shù)用不了，換成 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()函數(shù)，改用 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

數(shù)字字符串比較、連表

mysql中能直接對數(shù)字和字符串進(jìn)行=相等判斷，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() 函數(shù)，換成 NOW()

select SYSDATE();

select now();

17

from_unixtime()

沒有from_unixtime()函數(shù)，換成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通過該函數(shù) pg_get_serial_sequence(‘庫名.表名’, '自增字段名') 獲取表的自增值

-

-

19

unix_timestamp()

沒有unix_timestamp()函數(shù)，換成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, ...;

需要注意的是：

唯一或排除約束字段名必須是一個唯一索引或唯一聯(lián)合索引，如果填寫多個唯一索引則不生效，如果要生效的話，必須把他們建立成一個唯一聯(lián)合索引。

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()函數(shù)，改成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

表名別名大小寫

查詢結(jié)果別名中有大寫需要用雙引號包含起來如果不包含起來查詢結(jié)果會自動轉(zhuǎn)為小寫，select area_id as "areaId" from ...

-

-

25

GROUP BY

GROUP BY 分組查詢不能查不在分組內(nèi)的數(shù)據(jù)（也不能對非GROUP BY的字段進(jìn)行ORDER BY排序），需要變成連表查詢，鏈表查需要注意是否有重復(fù)數(shù)據(jù)。

例如：

SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname ORDER BY wmname;（查詢失?。?/span>

如果沒有重復(fù)數(shù)據(jù)換成：

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

;

如果有重復(fù)數(shù)據(jù)需要進(jìn)行去重則換成：（利用窗口函數(shù)）

SELECT

cname,

DISTINCT ON(wmname)wmname wmname,

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

FROM makerar

如果需要對不是去重的字段進(jìn)行排序則在最外層加上排序：

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()函數(shù)不存在，安裝擴展函數(shù)

create extension "uuid-ossp";

select uuid();

select uuid_generate_v4();

27

時間模糊查詢

在進(jìn)行時間模糊查詢的時候需要轉(zhuǎn)換一下時間，例如：

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

在業(yè)務(wù)先刪除在插入。

如果可以的話可以用下面的寫法，看業(yè)務(wù)需要

pg 沒有這個語法，

如果根據(jù)以為能改成存在更新不存在就插入的話可以用以下寫法：

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

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

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

需要注意的是：

唯一或排除約束字段名必須是一個唯一索引或唯一聯(lián)合索引，如果填寫多個唯一索引則不生效，如果要生效的話，必須把他們建立成一個唯一聯(lián)合索引。

-

-

29

like

如果對int字段進(jìn)行l(wèi)ike判斷需要將字段轉(zhuǎn)為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、數(shù)據(jù)庫是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

關(guān)鍵字

關(guān)鍵字：

name、value、label、moudle

需要雙引號包含起來

-

-

32

運算符號 +

mysql 返回結(jié)果可直接通過+拼接字符串， pg不支持，改成concat方法

-

-

33

實體類

實體類里面@TableField里寫的字段有大寫或者 ` 號的要改成雙引號，例如 @TableField("\"order\"")、@TableField("\"createTime\"")

-

-

34

curdate()

沒有curdate()函數(shù)，換成，current_date。注意沒有括號例如：select current_date;

select curdate();

select current_date;

35

inet_aton()、inet6_aton()

沒有inet_aton()和inet6_aton()函數(shù)，使用inet()函數(shù)進(jìn)行判斷

select inet_aton('1.1.1.1')

select inet('1.1.1.1')

36

locate()

沒有l(wèi)ocate()函數(shù)，換成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()函數(shù)，用convert_from()替換

convert(vendor using gbk)

convert_from(vendor::bytea, 'gbk')

39

bool字段查詢和插入，不可以使用0，1；修改PG數(shù)據(jù)庫內(nèi)置轉(zhuǎn)換規(guī)則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

關(guān)聯(lián)字段，條件查詢字段類型不一致時，產(chǎn)生報錯問題；強制轉(zhuǎn)換，如轉(zhuǎn)換為字符串 ::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中無該函數(shù)，創(chuàng)建自定義函數(shù)，實現(xiàn)該功能文章來源地址http://www.zghlxwxcb.cn/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;