从 MySQL 迁移到 DM | 达梦技术文档

函数说明：https://eco.dameng.com/document/dm/zh-cn/pm/function.html

一、迁移数据

先跟着官网教程把表和数据移植过去，我按着教程执行到最后一步时，有几个表的数据移植失败了，不支持该类型数据，原因是日期值的问题，'2023-01-12T10:19:59'无法插入

 

无法导入的数据我就复制下insert手动插入了 ，每个表前后都加上set identity_insert 模式.表名 [on|off]，导入sys_job表时，有个\' 转义符的问题报错，把\'换成双引号就好了

-- 要把`这个符号都删了，并且执行插入语句前要加上开启可插入自增列
set identity_insert ry.sys_post on; -- 开启可插入自增列
INSERT INTO ry.sys_post (post_id, post_code, post_name, post_sort, status, create_by, create_time, update_by, update_time, remark) 
VALUES ('1', 'ceo', '董事长', '1', '0', 'admin', '2023-01-12 10:19:54', '', NULL, '');
set identity_insert ry.sys_post off; -- 插入完之后关闭

二、改配置文件

admin/pom.xml

        <!--达梦数据库驱动-->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>Dm8JdbcDriver18</artifactId>
            <version>8.1.1.49</version>
        </dependency>

        <!--达梦数据库方言-->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>DmDialect-for-hibernate5.3</artifactId>
            <version>8.1.1.49</version>
        </dependency>

application-druid.yml

datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: dm.jdbc.driver.DmDriver
        druid:
            master:
                url: jdbc:dm://127.0.0.1
                username: ry
                password: 123456

application.yml

pagehelper: 
  helperDialect: oracle
  supportMethodsArguments: true
  params: count=countSql 

达梦官网文档有提供mysql find_in_set函数的实现，官网里提供的文本格式有点乱一直报错，可以直接复制我这个调整好格式的，管理工具选中文本按F8执行

CREATE OR REPLACE
FUNCTION SYSDBA.FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')
	RETURN NUMBER IS
	l_idx number := 0; -- 用于计算 piv_str2 中分隔符的位置
	str varchar2(500); -- 根据分隔符截取的子字符串
	piv_str varchar2(500) := piv_str2; -- 将 piv_str2 赋值给 piv_str
	res number:=0; -- 返回结果
	BEGIN
	-- 如果 piv_str 中没有分割符，直接判断 piv_str1 和 piv_str 是否相等，相等res=1
	IF instr(piv_str, p_sep, 1) = 0 THEN
		IF piv_str = piv_str1 THEN
		res:= 1;
		END IF;
	ELSE
		-- 循环按分隔符截取 piv_str
		LOOP
			l_idx := instr(piv_str,p_sep);
			-- 当 piv_str 中还有分隔符时
			IF l_idx > 0 THEN
				-- 截取第一个分隔符前的字段 str
				str:= substr(piv_str,1,l_idx- 1);
				-- 判断 str 和 piv_str1 是否相等，相等 res=1 并结束循环判断
				IF str = piv_str1 THEN
					res:= 1;
					EXIT;
				END IF;
				piv_str := substr(piv_str,l_idx+length(p_sep));
			ELSE
				-- 当截取后的 piv_str 中不存在分割符时，判断 piv_str 和 piv_str1 是否相等，相等 res=1 
				IF piv_str = piv_str1 THEN
					res:= 1;
				END IF;
				-- 无论最后是否相等，都跳出循环
				EXIT;
			END IF;
		END LOOP;
		-- 结束循环
	END IF;
	-- 返回 res
	RETURN res;
END FIND_IN_SET;

sysNoticeMapper.xml    cast() 函数改成to_char()

    <sql id="selectNoticeVo">
        select notice_id, notice_title, notice_type, to_char(notice_content) as notice_content, status, create_by, create_time, update_by, update_time, remark
		from sys_notice
    </sql>

GenTableMapper.xml   把selectDbTableList、selectDbTableListByNames、selectTableByName删了，替换成以下内容

    <sql id="selectDamengTableVo">
		select
			a.*,
			b.comment$ as table_comment
		from
			(
				select
					SF_GET_SCHEMA_NAME_BY_ID(SCHID) AS schema_name,
					name as table_name,
					crtdate as create_time
				from
					sys.sysobjects
				where
					type$ = 'SCHOBJ'
					and subType$ = 'UTAB'
					<!-- select SF_GET_SCHEMA_ID_BY_NAME('模式名') 获取模式id -->
					<!-- and schid in (154875840) -->
					and schid = CURRENT_SCHID
			) a
		left join
			sys.systablecomments b on a.schema_name = b.schname and a.table_name = b.tvname and b.table_type = 'TABLE'
	</sql>

	<select id="selectDbTableList" parameterType="GenTable" resultMap="GenTableResult">
		<include refid="selectDamengTableVo"></include>
		where a.table_name NOT LIKE 'QRTZ_%' AND a.table_name NOT LIKE 'GEN_%'
		AND a.table_name NOT IN (select table_name from gen_table)
		<if test="tableSchema != null and tableSchema != ''">
			AND lower(a.table_schema) like lower(concat('%', #{tableSchema}, '%'))
		</if>
		<if test="tableName != null and tableName != ''">
			AND lower(a.table_name) like lower(concat('%', #{tableName}, '%'))
		</if>
		<if test="tableComment != null and tableComment != ''">
			AND lower(b.table_comment) like lower(concat('%', #{tableComment}, '%'))
		</if>
		<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
			AND date_format(a.create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
		</if>
		<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
			AND date_format(a.create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
		</if>
		order by create_time desc
	</select>
	
	<select id="selectDbTableListByNames" resultMap="GenTableResult">
		<include refid="selectDamengTableVo"></include>
		where a.table_name NOT LIKE 'QRTZ_%' and a.table_name NOT LIKE 'GEN_%' and a.schema_name = SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID)
		and a.table_name in
	    <foreach collection="array" item="name" open="(" separator="," close=")">
 			upper(#{name})
        </foreach> 
	</select>
	
	<select id="selectTableByName" parameterType="String" resultMap="GenTableResult">
		<include refid="selectDamengTableVo"></include>
		where b.table_comment <![CDATA[ <> ]]> '' and a.table_schema = SF_GET_SCHEMA_NAME_BY_ID(CURRENT_SCHID)
		and a.table_name = #{tableName}
	</select>

SysMenuMapper.xml 里把`符号全部删掉

GenTableColumnMapper.xml，注意改下模式名

    <select id="selectDbTableColumnsByName" parameterType="String" resultMap="GenTableColumnResult">
    　　select
            sc.NAME column_name,
            (case when sc.INFO2 = 1 then '1' else '0'  end) is_pk,
            sc.COLID sort, scc.COMMENT$ column_comment,
            (case when sc.INFO2 = 1 then '1' else '0' end) as is_increment,
            sc.TYPE$ column_type
    　　from
            sys.syscolumns sc
        left join
            SYS.SYSOBJECTS so on sc.ID = so.ID
        left join SYS.SYSCOLUMNCOMMENTS SCC on sc.NAME = scc.COLNAME and scc.TABLE_TYPE = 'TABLE' and scc.SCHNAME = 换成自己的模式名 and scc.TVNAME= so.NAME
    　　where
            so.TYPE$='SCHOBJ' and so.SUBTYPE$='UTAB' and so.PID = -1
            and SF_GET_SCHEMA_NAME_BY_ID(so.SCHID) = 换成自己的模式名
            and so.NAME = #{tableName}
    </select>

sql.vm

-- 菜单 SQL
insert into ${tableSchema}.sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}', '${parentMenuId}', '1', '${businessName}', '${moduleName}/${businessName}/index', 1, 0, 'C', '0', '0', '${permissionPrefix}:list', '#', 'admin', sysdate(), '', null, '${functionName}菜单');

DECLARE
-- 按钮父菜单ID
parentId BIGINT := @@IDENTITY;

BEGIN
-- 按钮 SQL
insert into ${tableSchema}.sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}查询', parentId, '1',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:query','#', 'admin', sysdate(), '', null, '');

insert into ${tableSchema}.sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}新增', parentId, '2',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:add','#', 'admin', sysdate(), '', null, '');

insert into ${tableSchema}.sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}修改', parentId, '3',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:edit','#', 'admin', sysdate(), '', null, '');

insert into ${tableSchema}.sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}删除', parentId, '4',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:remove','#', 'admin', sysdate(), '', null, '');

insert into ${tableSchema}.sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('${functionName}导出', parentId, '5',  '#', '', 1, 0, 'F', '0', '0', '${permissionPrefix}:export','#', 'admin', sysdate(), '', null, '');
END;