mysql 转pg 两者不同的地方

左手的ㄟ右手 2024-04-20 22:16 27阅读 0赞

因项目数据库（原来是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：

![6f3cb46094f84ce6893322284acede41.png][]

改为：

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：执行会报错

![db1e055738394bf2b931da7ca453becb.png][]

修改为

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：

执行报错：

![9c52f2a8f6994394a3ee4b5eae1fff1f.png][]

修改为：

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

#### 3，返回值大小写 ####

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

pg： 返回小写

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

### 2，数据类型的区别： ###

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> mysql数据类型 </td> 
 <td colspan="1"> pg数据类型 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> datetime </td> 
 <td colspan="1"> timestamp </td> 
 </tr> 
 <tr> 
 <td colspan="1"> bigint </td> 
 <td colspan="1"> int8 </td> 
 </tr> 
 </tbody> 
</table>

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

##### 日期格式化的区别： #####

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> 数据类型 </td> 
 <td colspan="1"> mysql格式 </td> 
 <td colspan="1"> pg格式 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 日期时间 </td> 
 <td colspan="1"> %Y-%m-%d %H:%i:%s </td> 
 <td colspan="1"> YYYY-MM-DD HH24:MI:SS </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 日期 </td> 
 <td colspan="1"> %Y-%m-%d </td> 
 <td colspan="1"> YYYY-MM-DD </td> 
 </tr> 
 <tr> 
 <td colspan="1"> 时间 </td> 
 <td colspan="1"> %H:%i:%s </td> 
 <td colspan="1"> HH24:MI:SS </td> 
 </tr> 
 </tbody> 
</table>

### 3，函数上的区别： ###

#### 1，判断函数： ####

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> mysql函数 </td> 
 <td colspan="1"> pg函数 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> ifnull() </td> 
 <td colspan="1"> COALESCE() </td> 
 </tr> 
 <tr> 
 <td colspan="1"> </td> 
 <td colspan="1"> </td> 
 </tr> 
 </tbody> 
</table>

COALESCE() 是两边都可以用

#### 2，日期函数： ####

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> mysql函数 </td> 
 <td colspan="1"> pg函数 </td> 
 <td colspan="1"> 作用 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> date_format() </td> 
 <td colspan="1"> to_char() </td> 
 <td colspan="1"> 把日期转为日期字符串 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> STR_TO_DATE() </td> 
 <td colspan="1"> to_date() </td> 
 <td colspan="1"> 把日期字符串转为日期 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> STR_TO_DATE() </td> 
 <td colspan="1"> to_timestamp() </td> 
 <td colspan="1"> 把日期时间字符串转为日期时间 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> CURRENT_TIMESTAMP()、LOCALTIME()、 NOW()、 SYSDATE() </td> 
 <td colspan="1"> NOW() </td> 
 <td colspan="1"> 获取当前日期时间 </td> 
 </tr> 
 </tbody> 
</table>

##### 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，字符串函数： ####

<table> 
 <tbody> 
 <tr> 
 <td colspan="1"> mysql函数 </td> 
 <td colspan="1"> pg函数 </td> 
 <td colspan="1"> 作用 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> find_in_set() </td> 
 <td colspan="1"> ANY(string_to_array('', ',')) </td> 
 <td colspan="1"> 查找字符串 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> GROUP_CONCAT() </td> 
 <td colspan="1"> array_to_string(array_agg(cname), ',') 、 string_agg(cname, ',') </td> 
 <td colspan="1"> 字符串聚合 </td> 
 </tr> 
 <tr> 
 <td colspan="1"> INSTR() </td> 
 <td colspan="1"> strpos() </td> 
 <td colspan="1"> 查找字符串位置 </td> 
 </tr> 
 </tbody> 
</table>

##### 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，总结： ###

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

[6f3cb46094f84ce6893322284acede41.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/5247d5d575eb4b428bff3cff678dcb73.png
[db1e055738394bf2b931da7ca453becb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/7d7ce4b20c7644faafeec83777188ab7.png
[9c52f2a8f6994394a3ee4b5eae1fff1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/20/d8c1a058ef684b4e8b04ff2a76173e79.png