写在前文：
近期由于公司业务产品发展需要，要求项目逐渐国产化：（1）项目国产操作系统部署；（2）数据库国产化；国产数据库最终选型为highgo（瀚高），该数据库基于pg开发，所以要求先将mysql适配到postgresql数据库；
一、初识postgresql
1.1 docker安装postgresql
1.1.1 镜像拉取
dockerhub官网选取自己想安装的版本（https://hub.docker.com/_/postgres/tags），我这里选取的是13.9版本；
拉取镜像到本地
docker pull postgres:13.9
1.1.2 执行镜像安装postgresql
docker run --name 1.postgres \
--restart=always \
-e POSTGRES_PASSWORD='jY%kng8cc&' \
-p 5432:5432 \
-v /data/postgresql:/var/lib/postgresql/data \
-d postgres:13.9
1.1.3 创建数据库,设置默认查询模式
-- 建库
CREATE DATABASE "test"
WITH
OWNER = "testuser" -- 数据库用户
;
-- 创建模式
CREATE SCHEMA "test" AUTHORIZATION "test";
-- 设置默认查询模式 pg连接时默认使用public这个schmel，想让项目连接时使用自己创建的模式需要修改一下默认查询模式
ALTER ROLE testuser SET search_path="test";
1.2 postgresql学习
这里放上一个学习地址，大家可以参照性学习，边使用边学习
https://www.sjkjc.com/postgresql/psql-commands/
1.3 项目中引入postgresql数据库
1.3.1 版本问题
postgresql-42.2.10(支持PostgreSQL 42)
JDK 8 - JDBC 4.2 Support for JDBC4 methods is not complete, but the majority of methods are implemented.
pg驱动版本：springboot2.5.14中默认集成的是42.2.25
1.3.2 添加maven依赖
1.3.3 配置文件修改
# 1.postgres为容器名称，也可以直接指定ip
pring.datasource.url=jdbc:postgresql://1.postgres:5432/test?autoReconnect=true&autoReconnectForPools=true&useUnicode=true&characterEncoding=utf8&createDatabaseIfNotExist=true&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull
spring.datasource.username=testuser
spring.datasource.password=test
spring.datasource.driver-class-name=org.postgresql.Driver
二、 MYSQL和PG基础语法差异汇总整理
2.1 数据结构对比
mysqlpostgresqlTINYINTSMALLINTSMALLINTSMALLINTMEDIUMINTINTEGERBIGINTBIGINTFLOATREALDOUBLEDOUBLE PRECISIONBOOLEANBOOLEANTINYTEXTTEXTTEXTTEXTMEDIUMTEXTTEXTLONGTEXTTEXTBINARY(n)BYTEAVARBINARY(n)BYTEATINYBLOBBYTEABLOBBYTEAMEDIUMBLOBBYTEALONGBLOBBYTEADATEDATETIMETIME [WITHOUT TIME ZONE]DATETIMETIMESTAMP [WITHOUT TIME ZONE]TIMESTAMPTIMESTAMP [WITHOUT TIME ZONE]AUTO_INCREMENTSERIAL , BIGSERIALcolumn 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 ... )
2.2 基础语法差异对比
语法差异mysqlpostgresql是否相同分页select * from t1 limit 2,2;select * from tbl limit 2 offset 2;否插入数据时：如果不存在则insert，存在则updatereplace实现upsert否大小写兼容通过配置可兼容表字段或表名为大写时，字段或表名必须添加双引号否if(), case whenif(), 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.nameINSERT INTO distributors ( did , dname ) VALUES ( 9 , ' Antwerp Design' ) ON CONFLICT （did）DO UPDATE SET name = EXCLUDED.name否insert-不存在插入，存在更新replace实现upsert语句否SELECT相同相同是DELETEDELETE FROM tableDELTE FROM table（不可全表删除）是DELETEDELETE FROM table WHEREDELETE FROM table WHERE是DELETE-删除并返回不支持DELETE FROM table WHERE RETURNING * ;INDEX-add支持alter，create创建支持createINDEX-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。
三、MYSQL数据结构转换PG数据结构
3.1 mysql数据结构转换PG数据结构
网上有很多转换工具，有些需要收费，这里借助一个最简单最常用的工具-navicat premium（我这里使用的是16版本）转换mysql数据结构到PG的数据结构
点击database选择你想转换的数据库，这里选择postgreSQL，下面选择版本
至此，一份pg的数据结构就保存完整了（注意：此时转换出来的数据结构会存在一些错误，还需要额外手动处理一些问题）
3.2 MYSQL转换PG数据结构存在的问题及解决方案
3.2.1 默认值丢失问题default
（1）时间字段的CURRENT_TIMESTAMP默认值丢失
解决方案：
-- 从mysql默认表information_schema中获取默认为CURRENT_TIMESTAMP列的信息
SELECT TABLE_NAME,column_name,column_default,extra FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default = 'CURRENT_TIMESTAMP';
-- 拼接所有时间字段默认为CURRENT_TIMESTAMP的alter 语句，提取到脚本中执行
SELECT
CONCAT("ALTER TABLE \"bigdata-web\".\"",TABLE_NAME, "\" ALTER COLUMN ","\"",column_name,"\"", " SET DEFAULT ", column_default, ";")
FROM information_schema.columns
WHERE
table_schema = 'bigdata-web' and column_default is not null AND column_default = 'CURRENT_TIMESTAMP';
（2）时间字段类型的on update CURRENT_TIMESTAMP，PG中无该使用方式
解决方案：
利用触发器和pg扩展函数实现更新数据时更新时间字段值
-- 安装pg扩展函数moddatetime（使用pg的useradmin用户）
create extension moddatetime;
ALTER FUNCTION "moddatetime"() OWNER TO "test用户";
-- 触发器语句：create trigger gmt_modified_timestamp_trigger before update on test_ly for each row execute procedure moddatetime(gmt_modified);
-- 查询所有设置了on update CURRENT_TIMESTAMP的列
SELECT TABLE_NAME,COLUMN_NAME,EXTRA,DATA_TYPE FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != '' AND column_default = 'CURRENT_TIMESTAMP' AND EXTRA = 'on update CURRENT_TIMESTAMP';
-- 拼接处理默认值为on update CURRENT_TIMESTAMP字段类型的默认值，添加触发器的语句
SELECT CONCAT("create trigger ", COLUMN_NAME, "_trigger ","before update on \"", TABLE_NAME, "\" for each row execute procedure moddatetime(\"",COLUMN_NAME,"\");") FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != '' AND column_default = 'CURRENT_TIMESTAMP' AND EXTRA = 'on update CURRENT_TIMESTAMP';
（3）其他默认值可以参考上面拼接处理，如字符串，数字
解决方案
-- 拼接所有默认值为字符串的alter语句
SELECT TABLE_NAME,column_name,column_default,DATA_TYPE,extra FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != 'CURRENT_TIMESTAMP' AND DATA_TYPE = 'varchar' AND column_default != '';
SELECT
CONCAT("ALTER TABLE \"bigdata-web\".\"",TABLE_NAME, "\" ALTER COLUMN ","\"",column_name,"\"", " SET DEFAULT '", column_default, "';")
FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != 'CURRENT_TIMESTAMP' AND DATA_TYPE = 'varchar' AND column_default != '';
-- 拼接默认值字段为enum的alter语句
SELECT * FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != '' AND DATA_TYPE = 'enum';
SELECT
CONCAT("ALTER TABLE \"bigdata-web\".\"",TABLE_NAME, "\" ALTER COLUMN ","\"",column_name,"\"", " SET DEFAULT '", column_default, "';")
FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != '' AND DATA_TYPE = 'enum';
-- 查询默认值为数字的列，拼接alter语句 除了tinyint(1) TABLE_NAME,column_name,column_default,DATA_TYPE,extra
SELECT * FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != 'CURRENT_TIMESTAMP' AND DATA_TYPE IN('tinyint', 'int', 'bigint') AND column_default != ''
AND COLUMN_TYPE != 'tinyint(1)' and
table_name = 't_user'
SELECT
CONCAT("ALTER TABLE \"bigdata-web\".\"",TABLE_NAME, "\" ALTER COLUMN ","\"",column_name,"\"", " SET DEFAULT ", column_default, ";")
FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND column_default != 'CURRENT_TIMESTAMP' AND DATA_TYPE IN('tinyit', 'int', 'bigint') AND column_default != ''
AND COLUMN_TYPE != 'tinyint(1)';
-- double
SELECT * FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND DATA_TYPE IN('double') AND column_default != '';
SELECT
CONCAT("ALTER TABLE \"bigdata-web\".\"",TABLE_NAME, "\" ALTER COLUMN ","\"",column_name,"\"", " SET DEFAULT ", column_default, ";")
FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND DATA_TYPE IN('double') AND column_default != '';
-- decimal
SELECT * FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND DATA_TYPE IN('decimal') AND column_default != '';
SELECT
CONCAT("ALTER TABLE \"bigdata-web\".\"",TABLE_NAME, "\" ALTER COLUMN ","\"",column_name,"\"", " SET DEFAULT ", column_default, ";")
FROM information_schema.columns
WHERE table_schema = 'bigdata-web' and column_default is not null AND DATA_TYPE IN('decimal') AND column_default != '';
（4）MYSQL的tinyint(1)（业务代码中boolean值）转换为了int2
navicat工具转换映射时将mysql的bit(1)转换为了int2，需要处理该部分字段
-- tinyint
SELECT * FROM information_schema.columns
WHERE table_schema = 'test-database' AND COLUMN_TYPE = 'tinyint(1)'
ORDER BY TABLE_NAME
-- 批量转换语句拼接
3.2.2 自增id设置丢失
navicat工具转换时将自增id设置丢失了
解决方案：
（1）修改建表语句，使用SERIAL关键字
（2）修改已经创建的表的某个字段为自增
--1、在PostgreSQL当中，我们实现ID自增首先创建一个关联序列，以下sql语句是创建一个从1开始的序列：
CREATE SEQUENCE menu_id_seq START 1;
--2、设置该字段默认值nextval('menu_id_seq'::regclass)
ALTER TABLE menu ALTER COLUMN id SET DEFAULT nextval('menu_id_seq'::regclass);
四、业务代码中的语法差异转换
4.1 常见修改场景汇总
序号场景示例1mapper接口方法上使用注解编写sql语句@SELECT("SELECT * FROM TEST")2mapper的xml文件中的语句3mybatis-plus使用的实体类相关特殊列名修改
4mybatis-plus使用QueryWrapper条件构造相关列修改5代码中使用字符串拼接的sql语句
4.2 业务代码语法修改问题汇总
序号差异关键字差异描述mysql示例pg示例1limitlimit 0,1 改成 limit 1 offset 0select * from t_user limit 0,1select * from t_user limit 1 offset 02字段大小写查询字段为驼峰命名，加上双引号，select \"startTime\" form ...--3ifnull没有ifnull函数，改用COALESCE()函数，select ifnull(avatar, 'aa'),"name" from t_user;select COALESCE(avatar, 'aa'),"name" from t_user;4DATE_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*';7binarymysql的where判断加上binary来区分大小写，where binary id = ‘abc’，在pg中是直接区分大小写的，将binary去掉就行select * from t_user where binary name = 'ADMIN';select * from t_user where name = 'ADMIN';8group_concat_max_lenset session group_concat_max_len=...在pg中没有，注释--9GROUP_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;10ISNULLISNULL没有使用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 = '日志管理员';12date_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;13if()没有if()函数，改用 case when 条件 then 值 else 值 endselect if(name = 'admin', true, false) as isAdmin from t_userselect case when name = 'admin' then true else false end as isAdmin from t_user14FIND_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.idselect * from t_model_layout_task_record as r left join t_model_layout_form as f on r.formId = cast(f.id as VARCHAR)16SYSDATE()没有 SYSDATE() 函数，换成 NOW()select SYSDATE();select now();17from_unixtime()没有from_unixtime()函数，换成to_timestamp()select from_unixtime(1673833489);select to_timestamp(1673833489);18auto_incrementmysql查询information_schema.tables的auto_increment字段获取主键自增的值，而pg的information_schema.tables中不存在auto_increment。pg通过该函数 pg_get_serial_sequence(‘库名.表名’, '自增字段名') 获取表的自增值--19unix_timestamp()没有unix_timestamp()函数，换成date_part('epoch', now())::integer，例如，select date_part('epoch', start_time)::integer from t_model_layout_task_recordselect unix_timestamp(createtime) from t_user;select date_part('epoch', createtime)::integer from t_user;20ON DUPLICATE KEY UPDATEmysql的存在则更新写法，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;21instr()没有instr()函数，改成like。例：SELECT * FROM user WHERE INSTR(username,'2')>0SELECT * FROM user WHERE username like '%2%'select * from t_user where INSTR(name,'a')>0select * 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 ...--25GROUP BYGROUP BY 分组查询不能查不在分组内的数据（也不能对非GROUP BY的字段进行ORDER BY排序），需要变成连表查询，链表查需要注意是否有重复数据。例如：SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname ORDER BY wmname;（查询失败）如果没有重复数据换成：SELECT m.cname, m.wmname, t.mxFROM ( 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 mxFROM makerar如果需要对不是去重的字段进行排序则在最外层加上排序：SELECT * FROM ( SELECT cname, DISTINCT ON(wmname)wmname wmname, MAX(avg) OVER (PARTITION BY wmname) AS mx FROM makerar) bbORDER BY m.wmnameselect 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;26uuid()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%'28replace 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, ...;需要注意的是：唯一或排除约束字段名必须是一个唯一索引或唯一联合索引，如果填写多个唯一索引则不生效，如果要生效的话，必须把他们建立成一个唯一联合索引。--29like如果对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\"")--34curdate()没有curdate()函数，换成，current_date。注意没有括号例如：select current_date;select curdate();select current_date;35inet_aton()、inet6_aton()没有inet_aton()和inet6_aton()函数，使用inet()函数进行判断select inet_aton('1.1.1.1')select inet('1.1.1.1')36locate()没有locate()函数，换成position()select locate('b','ayyvkhlbm')select position('b' in 'ayyvkhlbm')37ORDER BY按照指定字段值排序ORDER BY FIELD (`field`, value1, value2)使用CASE WHEN END替代（eg：ORDER BY CASE field WHEN value1 THEN WHEN value2 THEN 2 END）38convert()没有convert()函数，用convert_from()替换convert(vendor using gbk)convert_from(vendor::bytea, 'gbk')39bool字段查询和插入，不可以使用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;41substring_index()PG中无该函数，创建自定义函数，实现该功能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;
好文阅读
您阅读本篇文章共花了：
发表评论