mysql 转pg 两者不同的地方

        因项目数据库（原来是MySQL）要改成PostgreSQL。 项目里面的sql要做一些调整。

1，写法上的区别：

1，数据准备：

新建表格：

CREATE TABLE property_config ( 
   CODE       VARCHAR(50)         NULL,
   VALUE      VARCHAR(1000)         NULL,
   remark     VARCHAR(250)         NULL 
);

插入数据：

INSERT INTO property_config 
VALUES (1, 'test', 'test'),(2, 'test2', 'test2');

2，select 写法

1，查询条件

1，类型

pg的会更严格，字符串的，要加引号

msyql：

SELECT CODE, VALUE, remark
FROM property_config WHERE CODE = 1;

        这样写，虽然mysql支持，但是要进行类型转换。

pg：

改为：

SELECT CODE, VALUE, remark
FROM property_config WHERE CODE = '1';

这样就正常。

2，别名 as

1，特殊处理：

别名中带 type的需要带as

mysql：

select code, value, remark,  'property' type  from property_config;
select code, value, remark,  'property' types  from property_config;
select code, value, remark,  'property' typess  from property_config;

这个执行正常。

pg：执行会报错

修改为

select code, value, remark,  'property' as type  from property_config;
select code, value, remark,  'property' as types  from property_config;
select code, value, remark,  'property' typess  from property_config;

2，as 不能加 ''

mysql:

SELECT CODE, VALUE, remark,  'property' AS 'key_1'  
FROM property_config;

这个执行正常。

pg：

执行报错：

修改为：

SELECT CODE, VALUE, remark,  'property' AS key_1  
FROM property_config;

3，返回值大小写

        mysql: 区分大小写，根据写的字段大小写返回

        pg： 返回小写

        因此，写sql的时候，mysql的平时都用小写。如果写java代码，使用对象。

2，数据类型的区别：

mysql数据类型	pg数据类型
datetime	timestamp
bigint	int8

        mysql也有timestamp，但是跟datetime的精度不同。

日期格式化的区别：

数据类型	mysql格式	pg格式
日期时间	%Y-%m-%d %H:%i:%s	YYYY-MM-DD HH24:MI:SS
日期	%Y-%m-%d	YYYY-MM-DD
时间	%H:%i:%s	HH24:MI:SS

3，函数上的区别：

1，判断函数：

mysql函数	pg函数
ifnull()	COALESCE()

COALESCE() 是两边都可以用

2，日期函数：

mysql函数	pg函数	作用
date_format()	to_char()	把日期转为日期字符串
STR_TO_DATE()	to_date()	把日期字符串转为日期
STR_TO_DATE()	to_timestamp()	把日期时间字符串转为日期时间
CURRENT_TIMESTAMP()、LOCALTIME()、 NOW()、 SYSDATE()	NOW()	获取当前日期时间

1，把日期转为日期字符串

mysql:

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s') AS format_datetime;
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d') AS format_date;
SELECT DATE_FORMAT(NOW(),'%H:%i:%s') AS format_time;

pg:

SELECT to_char(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS format_datetime;
SELECT to_char(NOW(), 'YYYY-MM-DD') AS format_date;
SELECT to_char(NOW(), 'HH24:MI:SS') AS format_time; 

2，把日期字符串转为日期

mysql:

SELECT STR_TO_DATE('2024-02-28', '%Y-%m-%d') AS format_date;

pg:

SELECT TO_DATE('2024-02-28', 'YYYY-MM-DD') AS format_date;

3，把日期时间字符串转为日期时间

mysql:

SELECT STR_TO_DATE('2024-02-28 12:15:16', '%Y-%m-%d %H:%i:%s');

pg:

select to_timestamp('2024-02-28 12:15:16','yyyy-MM-dd hh24:mi:ss');

3，字符串函数：

mysql函数	pg函数	作用
find_in_set()	ANY(string_to_array('', ','))	查找字符串
GROUP_CONCAT()	array_to_string(array_agg(cname), ',') 、 string_agg(cname, ',')	字符串聚合
INSTR()	strpos()	查找字符串位置

1，查找字符串

mysql:

SELECT * FROM property_config  WHERE FIND_IN_SET('test', VALUE);

ps:

SELECT * FROM property_config t WHERE 'test' = ANY(string_to_array(value, ','));

2，字符串聚合

mysql:

SELECT GROUP_CONCAT(CODE) AS codes FROM property_config;

pg：

SELECT array_to_string(array_agg(code),',') as codes 
from property_config;
SELECT string_agg(code, ',') as codes from property_config;

3，查找字符串位置

mysql:

SELECT LOCATE('ba1l', 'footba1l');
SELECT POSITION('ba1l' IN 'footba1l');
SELECT INSTR('footba1l', 'ba1l');

pg:

 SELECT strpos('football', 'ball');  
 SELECT POSITION('ball' in 'football');

这边查找位置的POSITION()函数是一样的。

4，分页写法

mysql:

        分页limit x,y，x代表跳过的条数，y代表查询的数量，

pg:

        采用 limit y offset x，写法等价于mysql的limit x,y

mysql 也支持 offset 后续用，都用offset

通用写法：  LIMIT size offset size * page

java代码的话，用分页插件

5，id插入

pg没有对应于值就不能写

mysql:

insert into form_cfg(id, form_id, attr_name, attr_value, attr_type)
values(#{id}, #{formId}, #{attrName}, #{attrValue}, #{attrType})

pg:

insert into form_cfg(form_id, attr_name, attr_value, attr_type)
values(#{formId}, #{attrName}, #{attrValue}, #{attrType})

2，总结：

        在平时写代码的时候，如果有不同数据库的，要考虑通用性。像判断是否为空这种，由代码去判断，没办法兼容的，再考虑用不同的方法实现。