若依mysql切换成pgsql

一、数据库迁移

1.使用Navicat工具，把mysql里面表结构和数据同步到pgsql里面

2.选中mysql中要同步到pgsql的数据库，点击工具选择数据传输

3.数据传输

4.选择目标连接换成pgsql连接，选择数据库（先在pgsql中建好数据库），选择public，点击下一步

5.选择要导入的表，点击下一步

6.可以勾选是否删除原来存在的表，最后点开始

7.执行完出现successfully代表导入成功

8.表和数据成功导入到pgsql中

二、添加pgsql自增

1.mysql的表结构和数据已经导入到pgsql中了，因为pgsql没有自增，所以还要把mysql的自增换成pgsql的自增方式

2.pgsql可以通过序列的方式实现自增

-- 添加自增序列
CREATE SEQUENCE test_user_id_seq
 
START WITH 1
 
INCREMENT BY 1
 
NO MINVALUE
 
NO MAXVALUE
 
CACHE 1;

-- 设置表某个字段自增
alter table test_user alter column id set default nextval('test_user_id_seq');

-- 从当前最大id依次递增
select setval('test_user_id_seq',(select max(id) from test_user));

3.mysql中有自增id的都有改成这样。

三、修改后端代码

1.后端代码切换数据源把mysql换成pgsql

2.引用pgsql驱动包

        
            org.postgresql
            postgresql
            42.2.18
        

3.修改配置源

# 数据源配置 
spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: org.postgresql.Driver
        druid:
            # 主库数据源
            master:
                url: jdbc:postgresql://127.0.0.1:5432/ruoyi?currentSchema=public
                username: postgres
                password: 123456
            # 从库数据源
            slave:
                # 从数据源开关/默认关闭
                enabled: false
                url: 
                username: 
                password: 
            # 配置检测连接是否有效
            validationQuery: select version()

4.把mysql的 validationQuery: SELECT 1 FROM DUAL 换成 validationQuery: select version()

5.PageHelper分页插件换成pgsql的

# PageHelper分页插件
pagehelper:
  helperDialect: postgresql
  supportMethodsArguments: true
  params: count=countSql

6.若依后端mapping.xml代码中有sysdate()的函数，全部换成now（），ifnull（字段，‘’） 函数换成 COALESCE（字段，‘’）函数

7.字段类型必须对应上例如SysMenuMapper.xml中status字段是char类型，查询时必须带上单引号 status = 0 换成 status = ‘0’

可以使用CAST函数将字符串转换为整数。以下是一个例代码，演示如何使用函数将字符串转换为整数：

SELECT CAST('123' AS INTEGER);

这种的也要把mysql关键字转义符去掉 改成 query

最后大功告成启动项目

四、遇到的坑

1.mysql的默认值切换成pgsql没有了，需要一个一个添加

2.字段类型必须对应上，数字类型不能有引号，字符类型必须有引号

3.mysql关键字转义符去掉

4.pgsql不支持sysdate()函数，要换成now（），pgsql不支持ifnull（字段，‘’） 函数，要换成 COALESCE（字段，‘’）函数

5.pgsql不支持mysql自增，需要换成pgsql自增，可以通过序列方式实现，参考目录二

6.上面数据库迁移只能迁移表结构和数据，函数，视图需要自己添加

-- 创建函数
CREATE OR REPLACE FUNCTION "public"."find_in_set"(int8, varchar)
  RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE
  STR ALIAS FOR $1;
  STRS ALIAS FOR $2;
  POS INTEGER;
  STATUS BOOLEAN;
BEGIN
	SELECT POSITION( ','||STR||',' IN ','||STRS||',') INTO POS;
	IF POS > 0 THEN
	  STATUS = TRUE;
	ELSE
	  STATUS = FALSE;
	END IF;
	RETURN STATUS;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

7.通过navicat中的数据传输将表从mysql转到PostgreSQL时，mysql中的tinyint类型会转为int2类型，即smallint类型，而mysql中可以使用tinyint表示boolean类型，但PostgreSQL不可以，只可以把类型改成bool

– 修改int2为bool

ALTER TABLE sys_role ALTER COLUMN menu_check_strictly TYPE VARCHAR USING menu_check_strictly ::VARCHAR;
ALTER TABLE sys_role ALTER COLUMN menu_check_strictly TYPE bool USING menu_check_strictly ::bool;

8、函数 date_format(character varying, unknown) 不存在，日期转换语法跟oracle类似，使用to_char,to_date,to_timestamp

9、若依生成代码的坑，创建视图，修改GenTableMapper.xml和GenTableColumnMapper.xml文件

-- 创建视图
---用户处理自动生成时的差异

CREATE OR REPLACE view view_self_table_columns
as select
                     table_catalog ,
                     table_schema ,
                     table_name ,
                     ordinal_position as sort,
                     column_name ,
                     data_type as TypeName,
                     (case
                          when (is_nullable = 'no' and contype !='p' ) then '1'
                          else null
                         end) as is_required,
                     (case
                          when contype = 'p' then '1'
                          else '0'
                         end) as is_pk,
                     coalesce(character_maximum_length, numeric_precision,-1) as Length,
                     numeric_scale as scale,
                     case
                         is_nullable when 'NO' then 0
                                     else 1
                         end as canNull,
                     column_default as defaultval,
                     case
                         when position('nextval' in column_default)>0 then 1
                         else 0
                         end as IsIdentity,
                     (case
                          when position('nextval' in column_default)>0 then 1
                          else 0
                         end) as is_increment,
                     c.DeText as column_comment,
                     c.typname as column_type,
                     c.contype,
                     ordinal_position
   from
                     information_schema.columns
                         left join (select
                                        datname,pg_get_userbyid(relowner) AS tableowner,nspname,relname,attname, description as DeText,typname,pg_cons.contype
                                    from
                                        pg_class
                                            left join pg_attribute pg_attr on
                                                pg_attr.attrelid = pg_class.oid
                                            left join pg_description pg_desc on
                                                    pg_desc.objoid = pg_attr.attrelid
                                                and pg_desc.objsubid = pg_attr.attnum
                                            left join pg_namespace pg_ns on
                                                pg_ns."oid" = pg_class.relnamespace
                                            left join pg_database on relowner = datdba
                                            left join pg_type on pg_attr.atttypid = pg_type."oid"
                                            left join (select pg_con.*,unnest(conkey) conkey_new from pg_constraint pg_con)  pg_cons on
                                                    pg_attr.attrelid = pg_class.oid
                                                and pg_attr.attnum = pg_cons.conkey_new and pg_cons.conrelid = pg_class.oid
                                    where
                                            pg_attr.attnum>0
                                      and pg_attr.attrelid = pg_class.oid
                     ) c
                                   on table_catalog = datname  and table_schema = nspname and table_name = relname and column_name = attname;
--where
--	table_schema = 'public'
--	and table_name = 'sys_user_role'
--order by ordinal_position asc



CREATE OR REPLACE view view_self_table
as select
              datname as table_catalog,
              pg_get_userbyid(relowner) AS tableowner,
              nspname as table_schema,
              relname as table_name,
              cast(obj_description(relfilenode,'pg_class') as varchar) as table_comment ,
              now() create_time,
              now() update_time
   from pg_class c
            left join pg_namespace pg_ns on
           pg_ns."oid" = c.relnamespace
            left join pg_database on relowner = datdba
   where relname in (select tablename from pg_tables);

修改GenTableMapper.xml

	
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
	

	
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
		
	

	
		select table_id, table_name, table_comment, sub_table_name, sub_table_fk_name, class_name, tpl_category, package_name, module_name, business_name, function_name, function_author, gen_type, gen_path, options, create_by, create_time, update_by, update_time, remark from gen_table
	

	
		
		
			
				AND lower(table_name) like lower(concat('%', #{tableName}, '%'))
			
			
				AND lower(table_comment) like lower(concat('%', #{tableComment}, '%'))
			
		
	

	
		select table_name, table_comment, create_time, update_time from view_self_table
		where table_schema = (select current_schema()) and table_catalog  = (select current_catalog)
		AND table_name NOT LIKE 'qrtz_%' AND table_name NOT LIKE 'gen_%'
		AND table_name NOT IN (select table_name from gen_table)
		
			AND lower(table_name) like lower(concat('%', #{tableName}, '%'))
		
		
			AND lower(table_comment) like lower(concat('%', #{tableComment}, '%'))
		
		order by create_time desc
	

	
		select table_name, table_comment, create_time, update_time from view_self_table
		where table_name NOT LIKE 'qrtz_%' and table_name NOT LIKE 'gen_%' and table_schema = (select current_schema()) and table_catalog  = (select current_catalog)
		and table_name in
		
			#{name}
		
	

	
		select table_name, table_comment, create_time, update_time from view_self_table
		where table_comment <![CDATA[  ]]> '' and table_schema = ( select current_schema()) and table_catalog  = (select current_catalog)
		  and table_name = #{tableName}
	

	
		SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,
			   c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort
		FROM gen_table t
				 LEFT JOIN gen_table_column c ON t.table_id = cast(c.table_id as integer)
		where t.table_id = #{tableId,jdbcType=BIGINT} order by c.sort
	

	
		SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,
			   c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort
		FROM gen_table t
				 LEFT JOIN gen_table_column c ON t.table_id = cast(c.table_id as integer)
		where t.table_name = #{tableName} order by c.sort
	

	
		SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,
			   c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort
		FROM gen_table t
				 LEFT JOIN gen_table_column c ON t.table_id = cast(c.table_id as integer)
		order by c.sort
	

	
		insert into gen_table (
		table_name,
		table_comment,
		class_name,
		tpl_category,
		package_name,
		module_name,
		business_name,
		function_name,
		function_author,
		gen_type,
		gen_path,
		remark,
		create_by,
		create_time
		)values(
		#{tableName},
		#{tableComment},
		#{className},
		#{tplCategory},
		#{packageName},
		#{moduleName},
		#{businessName},
		#{functionName},
		#{functionAuthor},
		#{genType},
		#{genPath},
		#{remark},
		#{createBy},
		now()
		)
	

	
		${sql}
	

	
		update gen_table
		
			table_name = #{tableName},
			table_comment = #{tableComment},
			sub_table_name = #{subTableName},
			sub_table_fk_name = #{subTableFkName},
			class_name = #{className},
			function_author = #{functionAuthor},
			gen_type = #{genType},
			gen_path = #{genPath},
			tpl_category = #{tplCategory},
			package_name = #{packageName},
			module_name = #{moduleName},
			business_name = #{businessName},
			function_name = #{functionName},
			options = #{options},
			update_by = #{updateBy},
			remark = #{remark},
			update_time = now()
		
		where table_id = #{tableId}
	

	
		delete from gen_table where table_id::bigint in
		
			#{tableId}
		
	

修改GenTableColumnMapper.xml

    
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
        
    

    
        select column_id, table_id, column_name, column_comment, column_type, java_type, java_field, is_pk, is_increment, is_required, is_insert, is_edit, is_list, is_query, query_type, html_type, dict_type, sort, create_by, create_time, update_by, update_time from gen_table_column
    

    
        
        where cast(table_id as integer) = #{tableId}
        order by sort
    

    
        select
            column_name,
            is_required,
            is_pk,
            sort,
            column_comment,
            is_increment,
            column_type
        from
            view_self_table_columns
        where
            table_catalog  = (select current_catalog)
          and table_schema = (select	current_schema())
          and table_name = (#{tableName})
    

    
        insert into gen_table_column (
        table_id,
        column_name,
        column_comment,
        column_type,
        java_type,
        java_field,
        is_pk,
        is_increment,
        is_required,
        is_insert,
        is_edit,
        is_list,
        is_query,
        query_type,
        html_type,
        dict_type,
        sort,
        create_by,
        create_time
        )values(
        #{tableId},
        #{columnName},
        #{columnComment},
        #{columnType},
        #{javaType},
        #{javaField},
        #{isPk},
        #{isIncrement},
        #{isRequired},
        #{isInsert},
        #{isEdit},
        #{isList},
        #{isQuery},
        #{queryType},
        #{htmlType},
        #{dictType},
        #{sort},
        #{createBy},
        now()
        )
    

    
        update gen_table_column
        
            column_comment = #{columnComment},
            java_type = #{javaType},
            java_field = #{javaField},
            is_insert = #{isInsert},
            is_edit = #{isEdit},
            is_list = #{isList},
            is_query = #{isQuery},
            is_required = #{isRequired},
            query_type = #{queryType},
            html_type = #{htmlType},
            dict_type = #{dictType},
            sort = #{sort},
            update_by = #{updateBy},
            update_time = now()
        
        where column_id = #{columnId}
    

    
        delete from gen_table_column where table_id::bigint in
        
            #{tableId}
        
    

    
        delete from gen_table_column where column_id in
        
            #{item.columnId}