springboot+mybatis+mysql适配达梦数据库

1.项目改造

1) pom.xml中添加达梦驱动依赖

    <!--https://mvnrepository.com/artifact/com.dameng/DmJdbcDriver18 -->
        <dependency>
            <groupId>com.dameng</groupId>
            <artifactId>DmJdbcDriver18</artifactId>
            <version>8.1.2.192</version>
        </dependency>

2) 新建配置类或在启动类中注册DatabaseIdProvider bean对象,用于多类型数据库语法的支持

/**
     * 自动识别使用的数据库类型,注意,此处的key为驱动自带的驱动名,不能更改,value可以更改
     * 在mapper.xml中databaseId的值就是跟这里对应，如果mapper.xml中有相同id的,如果指定 
     * databaseId和当前驱动匹配则使用,如果没有,则读取databaseId为空的节点
     * 如果没有databaseId选择则说明该sql适用所有数据库
     * */
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider(){
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        //key为固有名字，更改后无法匹配，后面的value可以自定义
        properties.setProperty("Oracle","oracle");
        properties.setProperty("MySQL","mysql");
        properties.setProperty("DB2","db2");
        properties.setProperty("Derby","derby");
        properties.setProperty("H2","h2");
        properties.setProperty("HSQL","hsql");
        properties.setProperty("Informix","informix");
        properties.setProperty("MS-SQL","ms-sql");
        properties.setProperty("PostgreSQL","postgresql");
        properties.setProperty("Sybase","sybase");
        properties.setProperty("Hana","hana");
        properties.setProperty("DM DBMS","dm");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

3) 多类型数据库使用方式

map.xml文件中,需要语法适配的,则复制一份相同ID值的节点,保持ID值一样,
指定databaseId="dm",在该语句块中改造达梦专属语法

对于注解形式,例如@Select,则复制原有@select的,
新增一个@select注解,指定databaseId="dm"即可

4)驱动替换,shcema相当于mysql中的数据库 

spring.datasource.url=jdbc:dm://127.0.0.1:5236?schema=模式名&compatibleMode=mysql&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
spring.datasource.username=XXX
spring.datasource.password=XXX
spring.datasource.driver-class-name=dm.jdbc.driver.DmDriver

2.代码改造步骤:

1. 首先处理列名是否包含关键字,对关键字转义处理
2. 批量搜索是否包含mysql特殊单引号`,批量替换为空
3. 检查是否用到特殊的语法,例如 insert ignore,replace into, insert into ... on duplicate update
4. 检查mybatis中insert语句的表是否包含自增主键的插入,查库表ID字段看看值是否为雪花算法生成,这种情况需要特殊处理
5. 检查批量插入中返回自增主键的写法,useGeneratedKeys="true" keyProperty="id"中的keyProperty改为"list.id"
6. 检查是否用了group_concat
7. 检查group by的语句,是否有select的字段不在group by中,处理ONLY_FULL_GROUP_BY的问题
8. 其他函数检查,date_format(),CONVERT()函数,FIND_IN_SET函数,date_add函数,UNIX_TIMESTAMP函数,anyvalue()函数
9. 检查mybatis xml中jdbcType="OTHER"的字段对应达梦的类型,大部分需要改成longvarchar类型,否则会出现返回的类型为DmdbClob等内置类型,而且jackjson转换会出现栈溢出问题
10. 检查join,right join ,left join等语句是否缺失on作为匹配条件

3.达梦数据库问题总结

官方迁移文档：DM_DBA手记之MySQL移植到DM.pdf (dameng.com)

1） 水平分区表不支持自增主键问题

在达梦数据库中,不支持分区表添加自增主键,可使用序列来替代或更改为非自增主键

#查询mysql分区表语法

SELECT sc.* FROM `information_schema`.`TABLES` sc
 WHERE sc.`TABLE_SCHEMA`='数据库名' AND sc.create_options LIKE '%partitioned%';

#查询达梦数据库中已有的所有序列,也可以在数据库单个模式中序列的目录查看相关序列

select * from "SYS"."ALL_SEQUENCES";

#创建序列，根据每个自增序列id值不一样，需要设置不同的开始值,获取序列下一个值,使用序列名.NEXTVAL,获取当前序列.CURRVAL,注意:CURRVAL获取当前序列值时的前提是在当前会话中使用了.NEXTVAL产生序列值否则报-7147: 序列当前值尚未在此会话中定义

CREATE SEQUENCE 模式名.序列名 INCREMENT BY 1 START WITH 1 NOCYCLE

#（方案一）设置 ID默认值为序列下一个自增值，例如

alter table "模式名"."分区表名" alter column "ID" set default 序列名.NEXTVAL;

#（方案二）创建每行insert前的触发器，指定ID为序列ID值

CREATE OR REPLACE TRIGGER 触发器名称

BEFORE INSERT ON 模式名.表名

FOR EACH ROW

BEGIN

:NEW.ID:=序列名.nextval;

END;

将mysql分区中的表批量生成序列和触发器的语句,然后在达梦中执行

-- 有自增主键的分区表创建序列来作为自增主键

SELECT UPPER(CONCAT('CREATE SEQUENCE ',sc.TABLE_SCHEMA,'.',sc.TABLE_NAME,
'_SEQUENCE INCREMENT BY 1 START WITH ',sc.AUTO_INCREMENT,' NOCYCLE;'))
 FROM `information_schema`.`TABLES` sc WHERE

sc.`TABLE_SCHEMA`='数据库名'

-- and sc.table_name='表名'

AND sc.create_options LIKE '%partitioned%'

AND sc.`AUTO_INCREMENT` IS NOT NULL;

--（方案一）设置默认值

SELECT UPPER(CONCAT('ALTER TABLE ',sc.TABLE_SCHEMA,'.',
sc.TABLE_NAME,' ALTER COLUMN "ID" SET DEFAULT ',
sc.TABLE_NAME,'_SEQUENCE.NEXTVAL')) FROM `information_schema`.`TABLES` sc WHERE

sc.`TABLE_SCHEMA`='数据库名'
AND sc.create_options LIKE '%partitioned%'

AND sc.`AUTO_INCREMENT` IS NOT NULL;

-- （方案二）有自增主键的分区表创建触发器来填充序列值作为自增主键，解决分区表不自持自增主键问题

SELECT UPPER(CONCAT('CREATE OR REPLACE TRIGGER ',sc.TABLE_NAME,
'_INSERT_BEFORE BEFORE INSERT ON ',sc.TABLE_SCHEMA,'.',sc.TABLE_NAME,
' FOR EACH ROW BEGIN :NEW.ID:=',sc.TABLE_NAME,'_SEQUENCE.NEXTVAL; END;')) 
FROM `information_schema`.`TABLES` sc WHERE

sc.`TABLE_SCHEMA`='数据库名'

AND sc.create_options LIKE '%partitioned%'

AND sc.`AUTO_INCREMENT` IS NOT NULL;

2）字段和表区分大小写，如果忽略大小写需要创建实例的时候指定配置项

VARCHAR类型以字符为单位是英文，汉字都算1个字符吗 | 达梦技术社区

3）达梦不支持的函数问题

达梦不支持关键字CURRENT_TIMESTAMP，想做到更新某条数据更新时间交由数据库维护，需要通过新建触发器来做适配

4）关键字 列名为系统内置关键字的问题

数据字段不支持mysql中的`，需要去掉，部分关键字必须使用双引号处理，或者在连接URL中增加&keywords=(ref,versions,list)来屏蔽关键字（此种方案部分关键字会有问题，

例如 end关键字，配置后驱动会把语句中的end自动加双引号会导致case when中的end被双引号从而出现语法错误）

查询达梦中的关键字,查询后使用find_in_set在mysql中执行输出包含关键字的列

select * from v$reserved_words

解决方案，可以在专属达梦的xml中增加双引号处理（由于mysql加双引号会识别成字符串值而不是列名，所以xml要写两份，达梦的指定databaseid="dm"），

#在达梦数据库中查询出所有关键字，复制字符串

select LISTAGG(keyword,',') from v$reserved_words

#复制上面语句值在mysql中使用find_in_set查询那些字段名用到关键字，根据结果对应的表和字段进行修改

SELECT c.COLUMN_NAME,c.TABLE_NAME,c.TABLE_SCHEMA FROM `information_schema`.`COLUMNS` c WHERE c.TABLE_SCHEMA NOT IN('information_schema')

AND FIND_IN_SET(UPPER(c.COLUMN_NAME),

'IFNULL,ABORT,ABSOLUTE,ACROSS,ACTION,ADD,ADVANCED,AFTER,ALL,ALTER,AND,ANY,ARRAYLEN,ARCHIVEDIR,ARCHIVELOG,ARCHIVESTYLE,AS,ASC,ASSIGN,AT,ATTACH,AUDIT,AUTHID,AUTHORIZATION,AUTO,AVG,BACKUP,BACKUPDIR,BACKUPINFO,BACKUPSET,MAXPIECESIZE,DEVICE,DELIMITED,PARMS,TRACE,FILE,BAKFILE,PARALLEL,BEFORE,BEGIN,BETWEEN,BIGDATEDIFF,BIGINT,BINARY,BIT,BITMAP,BLOB,BLOCK,BOOL,BOOLEAN,BOTH,BSTRING,BTREE,BY,BYTE,CACHE,CALCULATE,CALL,CASCADE,CASCADED,CASE,CAST,CATALOG,CHAIN,CHAR,CHARACTER,CUMULATIVE,NCHAR,NCHARACTER,NATIONAL,CHECK,CIPHER,CLOB,CLOSE,CLUSTER,COLUMN,COMMIT,COMMITTED,COMMITWORK,COMMENT,COMPILE,DUMP,JOB,COMPRESS,COMPRESSED,CONNECT,CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE,CONNECT_BY_ROOT,CONSTANT,CONSTRAINT,CONSTRAINTS,CONSTRUCTOR,CONTAINS,CONTEXT,DICTIONARY,PRIVILEGE,REPLAY,BACKED,BUFFER,LEXER,CONVERT,COPY,CORRESPONDING,COUNT,CREATE,CROSS,CRYPTO,CTLFILE,CURRENT,CURRENT_USER,CURRENT_SCHEMA,CURSOR,CYCLE,SEARCH,DEPTH,BREADTH,DATABASE,DATA,DATAFILE,DATE,DATEADD,DATEDIFF,DATETIME,DAY,DATEPART,DBFILE,DEBUG,DDL,DEC,DECIMAL,DECLARE,DECODE,DEFAULT,DEFERRABLE,DEFERRED,DEFINER,DELETE,DELETING,DELTA,DESC,DETACH,DETERMINISTIC,DIRECTORY,DISCONNECT,DISABLE,DISTINCT,DISTRIBUTED,DOUBLE,DOWN,DROP,EACH,ELSE,ELSEIF,ELSIF,ENABLE,ENCRYPT,ENCRYPTION,END,ESCAPE,EVENTS,EXCEPT,EXCEPTION,EXCLUSIVE,EXEC,EXECUTE,EXISTS,EXIT,EXPLAIN,EXTERNAL,EXTRACT,FETCH,FINAL,FILLFACTOR,FILEGROUP,FIRST,FLOAT,FOR,FORCE,FOREIGN,FREQUENCE,FROM,FULL,FULLY,FIELDS,FUNCTION,GLOBAL,GLOBALLY,GOTO,GRANT,GREAT,GROUP,HASH,HASHPARTMAP,HAVING,HEXTORAW,HOUR,IDENTIFIED,IDENTITY_INSERT,IDENTITY,IF,IMAGE,IMMEDIATE,IN,INCREASE,INCREMENT,INDEX,INITIAL,INITIALLY,INNER,INNERID,INSERT,INSERTING,INSTANTIABLE,OVERRIDING,INSTEAD,INT,PLS_INTEGER,INTEGER,INTENT,INTERSECT,INTERVAL,INTO,IS,ISOLATION,JOIN,KEY,LAST,LEADING,LEFT,LEVEL,LIKE,LINK,LOCAL,LOCALLY,LOCATION,LOCK,UNLOCK,ACCOUNT,LOCKED,LOGFILE,LOGIN,LOGON,LONGVARBINARY,LONGVARCHAR,LOOP,MANUAL,MAP,MATCH,MATCHED,MAX,MAXSIZE,MAXVALUE,MEMBER,MERGE,MICRO,MIN,MINEXTENTS,MINUS,MINUTE,MINVALUE,MIRROR,MODE,MODIFY,MONEY,MONTH,MOUNT,MATERIALIZED,MOVE,MOVEMENT,MAPPED,REFRESH,FAST,COMPLETE,DEMAND,NEVER,BUILD,PURGE,SYNCHRONOUS,ASYNCHRONOUS,NATURAL,NEW,NEXT,NO,NOARCHIVELOG,NOAUDIT,NOROWDEPENDENCIES,NOT,NOCACHE,NOCYCLE,NOMAXVALUE,NOMINVALUE,NOORDER,NOWAIT,NULL,NULLS,NUMBER,NUMERIC,NOSORT,OBJECT,OF,OFF,OFFLINE,OFFSET,OLD,ON,ONCE,ONLINE,ONLY,OPEN,OPTIMIZE,OPTION,OR,ORDER,OUT,OUTER,OVERLAPS,PACKAGE,PAGE,PARTIAL,PARTITION,PARTITIONS,PENDANT,PERCENT,PRECISION,PRESERVE,PRIMARY,PRINT,PRIOR,PRIVILEGES,PROCEDURE,PUBLIC,RAISE,RANDOMLY,RANGE,RAWTOHEX,READ,REAL,REBUILD,RECORD,RECORDS,REF,REFERENCES,REFERENCE,REFERENCING,RELATIVE,RENAME,REPEAT,REPEATABLE,REPLACE,RESIZE,RESTORE,RESTRICT,RESULT,RETURN,REVERSE,REVOKE,RIGHT,ROLE,ROLLBACK,ROLLFILE,ROOT,ROW,ROWCOUNT,ROWDEPENDENCIES,ROWNUM,ROWS,SALT,SAVEPOINT,SCHEMA,SCN,SECOND,SELECT,SELF,SEQUENCE,SERIALIZABLE,SERVER,SET,SHARE,SIBLINGS,SIZE,SMALLINT,SNAPSHOT,SOME,SOUND,SPATIAL,SPLIT,SQL,STATEMENT,STYLE,STORAGE,SUBPARTITION,SUBPARTITIONS,SUBSTRING,SUCCESSFUL,SUM,SWITCH,SYNC,SYNONYM,SYS_CONNECT_BY_PATH,TABLESPACE,TABLE,TEMPLATE,TEMPORARY,TEXT,THEN,TIES,TIME,TIMER,TIMESTAMP,TIMESTAMPADD,TIMESTAMPDIFF,TINYINT,TO,TOP,TRAILING,TRANSACTION,TRIGGER,TRIGGERS,TRIM,THROUGH,TRUNCATE,TRUNCSIZE,TYPE,UNCOMMITTED,UNDER,UNION,UNIQUE,UNTIL,UP,UPDATE,UPDATING,USER,USING,USE_HASH,USE_MERGE,USE_NL,USE_NL_WITH_INDEX,VALUE,VALUES,VARBINARY,VARCHAR,VARCHAR2,VARRAY,VARYING,VARIANCE,SINCE,SKIP,STDDEV,VIEW,VSIZE,WAIT,WEEK,WHEN,WHENEVER,WHERE,WHILE,WITH,WORK,WRITE,YEAR,ANALYZE,SERERR,SUSPEND,LOGOUT,LOGOFF,RELATED,LIMIT,UNLIMITED,EXTERNALLY,SESSION_PER_USER,CONNECT_IDLE_TIME,FAILED_LOGIN_ATTEMPS,PASSWORD_LIFE_TIME,PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX,PASSWORD_LOCK_TIME,PASSWORD_GRACE_TIME,CPU_PER_CALL,CPU_PER_SESSION,MEM_SPACE,READ_PER_CALL,READ_PER_SESSION,RULE,STARTUP,LABEL,SHUTDOWN,TIMES,ALLOW_IP,NOT_ALLOW_IP,ALLOW_DATETIME,NOT_ALLOW_DATETIME,PASSWORD_POLICY,EVENTINFO,DISKSPACE,DEREF,DANGLING,RETURNING,SCOPE,STRING,SBYTE,SHORT,USHORT,UINT,LONG,ULONG,VOID,CONST,DO,BREAK,CONTINUE,THROW,FINALLY,TRY,CATCH,PROTECTED,PRIVATE,ABSTRACT,SEALED,STATIC,READONLY,VIRTUAL,VISIBLE,OVERRIDE,EXTENDS,NODE,EXTERN,JAVA,CLASS,BASE,STRUCT,GET,SIZEOF,TYPEOF,ADMIN,REPLICATE,VERIFY,ZONE,VERTICAL,LOG,NONE,LOB,ERROR,LESS,THAN,EQU,EXCHANGE,STORE,NOBRANCH,BRANCH,CLUSTERBTR,LIST,NORMAL,STANDBY,TRANSACTIONAL,ARRAY,ROLLUP,CUBE,GROUPING,OVER,ROWID,SECTION,STAT,UNBOUNDED,PRECEDING,FOLLOWING,AUTOEXTEND,SETS,WRAPPED,CONNECT_TIME,TRXID,VERSIONS,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_STARTTRXID,VERSIONS_ENDTRXID,VERSIONS_OPERATION,HUGE,PATH,FILESIZE,SESSION,QUERY_REWRITE_INTEGRITY,PRAGMA,AUTONOMOUS_TRANSACTION,EXCEPTION_INIT,SUBTYPE,BULK,COLLECT,FORALL,INDICES,SAVE,EXCEPTIONS,DOMAIN,USAGE,COLLATION,COLLATE,TIME_ZONE,OVERLAY,PLACING,LARGE,WITHOUT,DIAGNOSTICS,CHARACTERISTICS,SIMPLE,PAD,SPACE,SENSITIVE,ASENSITIVE,INSENSITIVE,SCROLL,HOLD,PIPELINED,PIPE,KEEP,DENSE_RANK,COUNTER,WITHIN,SYSTEM,SPFILE,MEMORY,ACCESSED,INITIALIZED,LOGGING,NOLOGGING,LNNVL,MOD,COLUMNS,SAMPLE,XML,PIVOT,UNPIVOT,SEED,PARALLEL_ENABLE,AGGREGATE,NOCOPY,INCLUDING,EXCLUDING,INDEXES,INVISIBLE,UNUSABLE,DDL_CLONE,ARCHIVE,INLINE,TYPEDEF,INCLUDE,EXCLUDE,LSN,INPUT,RESULT_CACHE,MONITORING,NOMONITORING,CORRUPT,STRICT,LAX,FORMAT,JSON,KEYS,ASCII,PRETTY,WRAPPER,CONDITIONAL,UNCONDITIONAL,EMPTY,TASK,THREAD,ERRORS,BADFILE,MAX_RUN_DURATION,FREQ,BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY,BYHOUR,BYMINUTE,BYSECOND,SPAN,YEARLY,MONTHLY,WEEKLY,DAILY,HOURLY,MINUTELY,SECONDLY,MON,TUE,WED,THU,FRI,SAT,SUN,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,XMLTABLE,PASSING,MULTISET,XMLPARSE,XMLAGG,LOCAL_OBJECT,RESTRICT_REFERENCES,REJECT,PROFILE,SPEED,STOP,INSTANCE,IGNORE_ROW_ON_DUPKEY_INDEX,EDITIONABLE,NONEDITIONABLE,BATCH,JSON_TABLE,ORDINALITY,AUTO_INCREMENT'

) ;

注意并非所有关键字都有问题，需要生成sql后调试看看

SELECT CONCAT('select ',GROUP_CONCAT(COLUMN_NAME),' from ',c.TABLE_SCHEMA,'.',c.TABLE_NAME,';') FROM `information_schema`.`COLUMNS` c WHERE c.TABLE_SCHEMA NOT IN('information_schema')

AND FIND_IN_SET(UPPER(c.COLUMN_NAME),

'IFNULL,ABORT,ABSOLUTE,ACROSS,ACTION,ADD,ADVANCED,AFTER,ALL,ALTER,AND,ANY,ARRAYLEN,ARCHIVEDIR,ARCHIVELOG,ARCHIVESTYLE,AS,ASC,ASSIGN,AT,ATTACH,AUDIT,AUTHID,AUTHORIZATION,AUTO,AVG,BACKUP,BACKUPDIR,BACKUPINFO,BACKUPSET,MAXPIECESIZE,DEVICE,DELIMITED,PARMS,TRACE,FILE,BAKFILE,PARALLEL,BEFORE,BEGIN,BETWEEN,BIGDATEDIFF,BIGINT,BINARY,BIT,BITMAP,BLOB,BLOCK,BOOL,BOOLEAN,BOTH,BSTRING,BTREE,BY,BYTE,CACHE,CALCULATE,CALL,CASCADE,CASCADED,CASE,CAST,CATALOG,CHAIN,CHAR,CHARACTER,CUMULATIVE,NCHAR,NCHARACTER,NATIONAL,CHECK,CIPHER,CLOB,CLOSE,CLUSTER,COLUMN,COMMIT,COMMITTED,COMMITWORK,COMMENT,COMPILE,DUMP,JOB,COMPRESS,COMPRESSED,CONNECT,CONNECT_BY_ISLEAF,CONNECT_BY_ISCYCLE,CONNECT_BY_ROOT,CONSTANT,CONSTRAINT,CONSTRAINTS,CONSTRUCTOR,CONTAINS,CONTEXT,DICTIONARY,PRIVILEGE,REPLAY,BACKED,BUFFER,LEXER,CONVERT,COPY,CORRESPONDING,COUNT,CREATE,CROSS,CRYPTO,CTLFILE,CURRENT,CURRENT_USER,CURRENT_SCHEMA,CURSOR,CYCLE,SEARCH,DEPTH,BREADTH,DATABASE,DATA,DATAFILE,DATE,DATEADD,DATEDIFF,DATETIME,DAY,DATEPART,DBFILE,DEBUG,DDL,DEC,DECIMAL,DECLARE,DECODE,DEFAULT,DEFERRABLE,DEFERRED,DEFINER,DELETE,DELETING,DELTA,DESC,DETACH,DETERMINISTIC,DIRECTORY,DISCONNECT,DISABLE,DISTINCT,DISTRIBUTED,DOUBLE,DOWN,DROP,EACH,ELSE,ELSEIF,ELSIF,ENABLE,ENCRYPT,ENCRYPTION,END,ESCAPE,EVENTS,EXCEPT,EXCEPTION,EXCLUSIVE,EXEC,EXECUTE,EXISTS,EXIT,EXPLAIN,EXTERNAL,EXTRACT,FETCH,FINAL,FILLFACTOR,FILEGROUP,FIRST,FLOAT,FOR,FORCE,FOREIGN,FREQUENCE,FROM,FULL,FULLY,FIELDS,FUNCTION,GLOBAL,GLOBALLY,GOTO,GRANT,GREAT,GROUP,HASH,HASHPARTMAP,HAVING,HEXTORAW,HOUR,IDENTIFIED,IDENTITY_INSERT,IDENTITY,IF,IMAGE,IMMEDIATE,IN,INCREASE,INCREMENT,INDEX,INITIAL,INITIALLY,INNER,INNERID,INSERT,INSERTING,INSTANTIABLE,OVERRIDING,INSTEAD,INT,PLS_INTEGER,INTEGER,INTENT,INTERSECT,INTERVAL,INTO,IS,ISOLATION,JOIN,KEY,LAST,LEADING,LEFT,LEVEL,LIKE,LINK,LOCAL,LOCALLY,LOCATION,LOCK,UNLOCK,ACCOUNT,LOCKED,LOGFILE,LOGIN,LOGON,LONGVARBINARY,LONGVARCHAR,LOOP,MANUAL,MAP,MATCH,MATCHED,MAX,MAXSIZE,MAXVALUE,MEMBER,MERGE,MICRO,MIN,MINEXTENTS,MINUS,MINUTE,MINVALUE,MIRROR,MODE,MODIFY,MONEY,MONTH,MOUNT,MATERIALIZED,MOVE,MOVEMENT,MAPPED,REFRESH,FAST,COMPLETE,DEMAND,NEVER,BUILD,PURGE,SYNCHRONOUS,ASYNCHRONOUS,NATURAL,NEW,NEXT,NO,NOARCHIVELOG,NOAUDIT,NOROWDEPENDENCIES,NOT,NOCACHE,NOCYCLE,NOMAXVALUE,NOMINVALUE,NOORDER,NOWAIT,NULL,NULLS,NUMBER,NUMERIC,NOSORT,OBJECT,OF,OFF,OFFLINE,OFFSET,OLD,ON,ONCE,ONLINE,ONLY,OPEN,OPTIMIZE,OPTION,OR,ORDER,OUT,OUTER,OVERLAPS,PACKAGE,PAGE,PARTIAL,PARTITION,PARTITIONS,PENDANT,PERCENT,PRECISION,PRESERVE,PRIMARY,PRINT,PRIOR,PRIVILEGES,PROCEDURE,PUBLIC,RAISE,RANDOMLY,RANGE,RAWTOHEX,READ,REAL,REBUILD,RECORD,RECORDS,REF,REFERENCES,REFERENCE,REFERENCING,RELATIVE,RENAME,REPEAT,REPEATABLE,REPLACE,RESIZE,RESTORE,RESTRICT,RESULT,RETURN,REVERSE,REVOKE,RIGHT,ROLE,ROLLBACK,ROLLFILE,ROOT,ROW,ROWCOUNT,ROWDEPENDENCIES,ROWNUM,ROWS,SALT,SAVEPOINT,SCHEMA,SCN,SECOND,SELECT,SELF,SEQUENCE,SERIALIZABLE,SERVER,SET,SHARE,SIBLINGS,SIZE,SMALLINT,SNAPSHOT,SOME,SOUND,SPATIAL,SPLIT,SQL,STATEMENT,STYLE,STORAGE,SUBPARTITION,SUBPARTITIONS,SUBSTRING,SUCCESSFUL,SUM,SWITCH,SYNC,SYNONYM,SYS_CONNECT_BY_PATH,TABLESPACE,TABLE,TEMPLATE,TEMPORARY,TEXT,THEN,TIES,TIME,TIMER,TIMESTAMP,TIMESTAMPADD,TIMESTAMPDIFF,TINYINT,TO,TOP,TRAILING,TRANSACTION,TRIGGER,TRIGGERS,TRIM,THROUGH,TRUNCATE,TRUNCSIZE,TYPE,UNCOMMITTED,UNDER,UNION,UNIQUE,UNTIL,UP,UPDATE,UPDATING,USER,USING,USE_HASH,USE_MERGE,USE_NL,USE_NL_WITH_INDEX,VALUE,VALUES,VARBINARY,VARCHAR,VARCHAR2,VARRAY,VARYING,VARIANCE,SINCE,SKIP,STDDEV,VIEW,VSIZE,WAIT,WEEK,WHEN,WHENEVER,WHERE,WHILE,WITH,WORK,WRITE,YEAR,ANALYZE,SERERR,SUSPEND,LOGOUT,LOGOFF,RELATED,LIMIT,UNLIMITED,EXTERNALLY,SESSION_PER_USER,CONNECT_IDLE_TIME,FAILED_LOGIN_ATTEMPS,PASSWORD_LIFE_TIME,PASSWORD_REUSE_TIME,PASSWORD_REUSE_MAX,PASSWORD_LOCK_TIME,PASSWORD_GRACE_TIME,CPU_PER_CALL,CPU_PER_SESSION,MEM_SPACE,READ_PER_CALL,READ_PER_SESSION,RULE,STARTUP,LABEL,SHUTDOWN,TIMES,ALLOW_IP,NOT_ALLOW_IP,ALLOW_DATETIME,NOT_ALLOW_DATETIME,PASSWORD_POLICY,EVENTINFO,DISKSPACE,DEREF,DANGLING,RETURNING,SCOPE,STRING,SBYTE,SHORT,USHORT,UINT,LONG,ULONG,VOID,CONST,DO,BREAK,CONTINUE,THROW,FINALLY,TRY,CATCH,PROTECTED,PRIVATE,ABSTRACT,SEALED,STATIC,READONLY,VIRTUAL,VISIBLE,OVERRIDE,EXTENDS,NODE,EXTERN,JAVA,CLASS,BASE,STRUCT,GET,SIZEOF,TYPEOF,ADMIN,REPLICATE,VERIFY,ZONE,VERTICAL,LOG,NONE,LOB,ERROR,LESS,THAN,EQU,EXCHANGE,STORE,NOBRANCH,BRANCH,CLUSTERBTR,LIST,NORMAL,STANDBY,TRANSACTIONAL,ARRAY,ROLLUP,CUBE,GROUPING,OVER,ROWID,SECTION,STAT,UNBOUNDED,PRECEDING,FOLLOWING,AUTOEXTEND,SETS,WRAPPED,CONNECT_TIME,TRXID,VERSIONS,VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_STARTTRXID,VERSIONS_ENDTRXID,VERSIONS_OPERATION,HUGE,PATH,FILESIZE,SESSION,QUERY_REWRITE_INTEGRITY,PRAGMA,AUTONOMOUS_TRANSACTION,EXCEPTION_INIT,SUBTYPE,BULK,COLLECT,FORALL,INDICES,SAVE,EXCEPTIONS,DOMAIN,USAGE,COLLATION,COLLATE,TIME_ZONE,OVERLAY,PLACING,LARGE,WITHOUT,DIAGNOSTICS,CHARACTERISTICS,SIMPLE,PAD,SPACE,SENSITIVE,ASENSITIVE,INSENSITIVE,SCROLL,HOLD,PIPELINED,PIPE,KEEP,DENSE_RANK,COUNTER,WITHIN,SYSTEM,SPFILE,MEMORY,ACCESSED,INITIALIZED,LOGGING,NOLOGGING,LNNVL,MOD,COLUMNS,SAMPLE,XML,PIVOT,UNPIVOT,SEED,PARALLEL_ENABLE,AGGREGATE,NOCOPY,INCLUDING,EXCLUDING,INDEXES,INVISIBLE,UNUSABLE,DDL_CLONE,ARCHIVE,INLINE,TYPEDEF,INCLUDE,EXCLUDE,LSN,INPUT,RESULT_CACHE,MONITORING,NOMONITORING,CORRUPT,STRICT,LAX,FORMAT,JSON,KEYS,ASCII,PRETTY,WRAPPER,CONDITIONAL,UNCONDITIONAL,EMPTY,TASK,THREAD,ERRORS,BADFILE,MAX_RUN_DURATION,FREQ,BYMONTH,BYWEEKNO,BYYEARDAY,BYMONTHDAY,BYDAY,BYHOUR,BYMINUTE,BYSECOND,SPAN,YEARLY,MONTHLY,WEEKLY,DAILY,HOURLY,MINUTELY,SECONDLY,MON,TUE,WED,THU,FRI,SAT,SUN,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,XMLTABLE,PASSING,MULTISET,XMLPARSE,XMLAGG,LOCAL_OBJECT,RESTRICT_REFERENCES,REJECT,PROFILE,SPEED,STOP,INSTANCE,IGNORE_ROW_ON_DUPKEY_INDEX,EDITIONABLE,NONEDITIONABLE,BATCH,JSON_TABLE,ORDINALITY,AUTO_INCREMENT'

) AND c.TABLE_SCHEMA='数据库名' GROUP BY c.TABLE_NAME

5）表字段长度问题

字符大小问题，在达梦中utf-8,中文占三个字节，所以mysql中定义varchar(2)的话，在Oracle中只能存一个中文汉字，需要改成三倍，查询语句中如果涉及字符长度判断可能有问题，

在初始化实例的时候设置参数LENGTH_IN_CHAR=0（varchar字节为单位）。Utf-8一个汉字占用三个字节，一个汉字占三个字节，一个英文字母占一个字节。varchar以字节为单位。一个varchar等于一个字节。

VARCHAR类型以字符为单位是英文，汉字都算1个字符吗 | 达梦技术社区

 以下为将包含中文字段的字段生成更改字段长度的代码:

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MysqlChangeChineseLenExecutor {
    // MySQL 8.0 以下版本选择
    //static final String JdbcDriver = "com.mysql.jdbc.Driver";
    // MySQL 8.0 以上版本选择
    static final String JdbcDriver = "com.mysql.cj.jdbc.Driver";
    //要适配的达梦数据库
    static final String database = "test";
    static final String Url = "jdbc:mysql://127.0.0.1:3306/"+database+"?useSSL=false&serverTimezone=UTC";

    //检查类型,0-中文超过最大值后,默认加大为最大中文字符数的长度*3,不超过不扩容,1-中文超过最大值后,原始字段扩容三倍,不超过的表不扩容,2-所有包含中文的字段都扩大为最大中文字符数*3,3-所有包含中文的字段扩大表空间三倍,
    static final int checkType = 1;

    //输入连接数据库的用户名与密码
    static final String User = "root";//输入你的数据库库名
    static final String PassWord = "root";//输入你的数据库连接密码

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;

        try {
            // 注册 MysqlChangeChineseLenExecutor 驱动
            Class.forName(JdbcDriver);

            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(Url, User, PassWord);

            // 执行查询
            System.out.println("输入sql语句后并执行...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM information_schema.`COLUMNS` c WHERE c.`TABLE_SCHEMA`='" + database + "' AND data_type='varchar'";// 这里填写需要的sql语句
            //查询最大值
            String maxSql = "SELECT MAX(mxlen) as mxlens FROM (SELECT LENGTH(`[columnName]`) AS  mxlen FROM [tableSchema].`[tableName]` WHERE  LENGTH(`[columnName]`)>[columnLen]) t";
            //执行sql语句
            ResultSet rs = stmt.executeQuery(sql);
            List<String> waitAdjustSqlList = new ArrayList<>(128);
            String modifyBaseSql = "alter table [tableSchema].[tableName] modify [columnName] VARCHAR([columnLen]);";
            // 展开结果集数据库SNC_COMPONENT
            while (rs.next()) {
                // 通过字段检索
                String tableSchema = rs.getString("TABLE_SCHEMA");//获取id值
                String tableName = rs.getString("TABLE_NAME");//获取id值
                String columnName = rs.getString("COLUMN_NAME");//获取id值
                String characterMaximumLength = rs.getString("CHARACTER_MAXIMUM_LENGTH");//获取id值
                String excuteSql = maxSql.replace("[tableSchema]", tableSchema).replace("[tableName]", tableName).replace("[columnName]", columnName);
                String modifySql = modifyBaseSql.replace("[tableSchema]", tableSchema).replace("[tableName]", tableName)
                        .replace("[columnName]", columnName);
                Statement stmt2 = conn.createStatement();
                //检查类型,0-默认加大为最大中文字符数的长度*3,不超过不扩容,1-所有包含中文的字段扩大表空间三倍,2-中文超过最大值后,原始字段扩容三倍,不超过的表不扩容
                if (checkType == 0 || checkType == 1) {

                    //执行sql语句
                    ResultSet rs2 = stmt2.executeQuery(excuteSql.replace("[columnLen]", characterMaximumLength));
                    //有结果的话
                    if (rs2.next()) {
                        String chLen = rs2.getString("mxlens");
                        if (chLen != null) {
                            waitAdjustSqlList.add(modifySql.replace("[columnLen]", (checkType == 0 ? chLen : Integer.valueOf(characterMaximumLength) * 3) + ""));
                        }
                    }
                    stmt2.close();
                } else if (checkType == 2 || checkType == 3) {
                    //执行sql语句
                    ResultSet rs2 = stmt2.executeQuery(excuteSql.replace("[columnLen]", "CHAR_LENGTH(" + columnName + ")"));
                    //有结果的话
                    if (rs2.next()) {
                        String chLen = rs2.getString("mxlens");
                        if (chLen != null) {
                            waitAdjustSqlList.add(modifySql.replace("[columnLen]", (checkType == 2 ? chLen : Integer.valueOf(characterMaximumLength) * 3) + ""));

                        }
                    }
                    stmt2.close();
                }


            }
            if (!waitAdjustSqlList.isEmpty()) {
                System.out.println("======================================");
                for (String s : waitAdjustSqlList) {
                    System.out.println(s);
                }
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // 处理 MysqlChangeChineseLenExecutor 错误
            se.printStackTrace();
        } catch (Exception e) {
            // 处理 Class.forName 错误
            e.printStackTrace();
        } finally {
            // 关闭资源
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        System.out.println("\n执行成功！");
    }
}

6）达梦自增主键不能插入null值和指定主键值插入的问题

达梦设置自增主键后，插入ID有值会报错，需要设置，SET IDENTITY_INSERT 【数据库名】.【表名】 ON WITH REPLACE NULL; 有值的插入可以不要后面的WITH REPLACE NULL;

方法一，在执行insert前调用以下语句 

 SET IDENTITY_INSERT 【表名】 ON WITH REPLACE NULL;

方法二，创建表插入触发器：

-- 创建触发器
  SET SCHEMA 【数据库名】;
  CREATE OR REPLACE TRIGGER 【表名】_INSERT_CHECK
  BEFORE INSERT ON 【数据库名】.【表名】
  BEGIN
  SET IDENTITY_INSERT 【数据库名】.【表名】 ON WITH REPLACE NULL;
  END;

 

7)使用小数精度问题

使用float,double等类型，数据会出现精度问题，例如8.500，存储不能为8.5，后面的0无法去除

8）存储过程语法不兼容，需要改造

达梦中使用的变量必须提前声明，系统内置表和MySQL不一样，时间日期处理函数不一样，其他内置函数也有差异，例如获取当前数据库，在mysql中使用

database(),在达梦中，需要改为 SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),

使用游标遍历时，必须指定EXIT WHEN XXX%NOTFOUND; 否则会出现死循环

9）列为varchar类型的字段和数字比较大小问题

达梦中字符串列不能和数字之间比较大小，需要将字符串列to_number后才可以比较大小，而且to_number会出现非数字字段语法报错问题,需要判断是否为数字后操作

10）分区表使用maxvalue后无法新增分区问题

分区表使用maxvalue后无法新增分区，需要删除maxvalue后才可以新增分区，此过程数据会出现maxvalue分区数据丢失，

除非新建一个表将分区数据迁移过去，删除后重建，或者考虑采用间隔分区自动生成分区

管理分区表和分区索引 | 达梦技术文档

CREATE TABLE "TEST" ( "ID" BIGINT NOT NULL, "NAME" VARCHAR(300), 
"CREATE_TIME" BIGINT NOT NULL, NOT CLUSTER PRIMARY KEY("ID")) 
PARTITION BY RANGE (CREATE_TIME) -- 一天的毫秒数=24*60*60*1000=86400000 
INTERVAL (86400000) (
 PARTITION TEST4_20070101 VALUES LESS THAN (1167580800000)
 )

-- 达梦查询分区

select * FROM dba_tab_partitions p
			WHERE  p.table_owner=SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
			AND p.table_name='TEST'

间隔分区自动生成的分区名无法管理，如果需要更新分区名，需要用到下面的语句

alter TABLE TEST rename partition SYS_P3183_3187 to TEST_20230103

mysql查询分区数据分布

SELECT TABLE_NAME,partition_name part, partition_expression expr, 
partition_description descr, table_rows
 FROM  INFORMATION_SCHEMA.partitions 
 WHERE TABLE_SCHEMA="数据库名" AND table_rows >0;

select table_name,partition_name,num_rows,high_value,partition_position 
FROM dba_tab_partitions p
			WHERE  p.table_owner='数据库名' 
			-- AND p.table_name='表名'
			order by p.table_name,p.partition_position;

11）value中包含特殊符号问题

达梦使用单引号作为字符串字的插入，内容中包含单引号时，使用\'无效，需要再加个单引号才能转义，录入\''，测试使用Java代码插入不受影响，但是用工具导入时或者复制sql执行都有这个问题

12）mybatis 中批量插入id填充问题

批量插入时，使用自动生成的key,例如insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id" keyColumn="id"返回的ID有问题，返回了序列ID最大值，非自增ID的值

需要把所有xml中批量插入的keyProperty="id" 改为keyProperty="list.id" 才正常，此次list为集合对象名

13)group_concat函数不支持改造

对于 group_concat 函数，可以采用 DM 的 LISTAGG/LISTAGG2 函数进行替换，LISTAGG和group_concat用法不一样，必须指定分隔符，否则直接拼接一起，而mysql中默认逗号分隔

mysql GROUP_CONCAT函数dm怎么用 | 达梦技术社区

14)group by 全字段问题

达梦只支持类似mysql的ONLY_FULL_GROUP_BY,group by 中的select 列名,除了聚合函数外的列必须包含在group by中,达梦也不支持any_value函数

解决方案

     1.把select的列去掉

     2.把select的列都加入group by中

     3. 使用非group by的列更改使用聚合函数,例如使用max,FIRST_VALUE, 建议使用FIRST_VALUE(列名) 列名,使用 FIRST_VALUE(列名) 列名 来修饰除了聚合函数外不需要group by的字段

     4.any_value替换为FIRST_VALUE,且不支持修饰聚合函数

   注意:如果字段为longvarchar,text,clob等类型时,使用first_value修饰会报错,需要更改为子查询改好的字段sql需要在达梦数据库上执行,没报错方可使用

15) insert ignore,repalce into和 insert on duplicate key update适配

达梦不支持此语法,有以下解决方案

1. 使用merge into替代(如何需要update其他值的情况下,建议使用merge into)

【达梦数据库】MySQL 的ON DUPLICATE KEY UPDATE语句在达梦数据库中使用的方式 - aaacarrot - 博客园

可使用下面语句指定模式名和表名生成替换的关键字(注意表中有没有自增主键,联合主键等情况调整是否传入ID字段)

select concat('MERGE INTO ',dbt.table_name,' T1
  USING (
  <foreach collection="list" item="item" index="index" separator="UNION ALL">
  SELECT ',
              (select LISTAGG2('#{item.'||
                               replace(replace(replace(replace(replace(replace(replace(
                                                                                       replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                               replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                                                                                       replace(replace(replace(replace(replace(
                                                                                                                                                                                                                                               LOWER(dtc.COLUMN_NAME),
                                                                                                                                                                                                                                               '_a','A'),'_b','B'),'_c','C'),'_d','D'),'_e','E'),'_f','F'),'_g','G'),
                                                                                                                                                                                       '_h','H'),'_i','I'),'_j','J'),'_k','K'),'_l','L'),'_m','M'),'_n','N'),
                                                                                                                               '_o','O'),'_p','P'),'_q','Q'), '_r','R'),'_s','S'),'_t','T'),'_u','U'),
                                                                       '_v','V'),'_w','W'),'_x','X'), '_y','Y'),'_z','Z')
                                   ||'} '||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name ),
              '
               FROM dual
               </foreach>
                ) T2 ON (
                ',

              (select LISTAGG2('T1.'||dic.column_name||' = T2.'||dic.column_name,' AND ') from dba_ind_columns dic
                                                                                                   left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
               where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
                 and ac.CONSTRAINT_TYPE='P'),
              ')
                WHEN NOT MATCHED THEN INSERT( ',

              (select LISTAGG2(dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name),

  ') VALUES
  (
  ',
  (select LISTAGG2('T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name),
  '
  ) WHEN MATCHED THEN UPDATE
  SET ',

  (select LISTAGG2(' T1.'||dtc.COLUMN_NAME||' =T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name
   and  dtc.COLUMN_NAME NOT IN(
   select dic.column_name from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='P'
   )
   )


  ) as 主键_批量插入或更新语句,

       concat('MERGE INTO ',dbt.table_name,' T1
  USING (
  SELECT ',
              (select LISTAGG2('#{'||
                               replace(replace(replace(replace(replace(replace(replace(
                                                                                       replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                               replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                                                                                       replace(replace(replace(replace(replace(
                                                                                                                                                                                                                                               LOWER(dtc.COLUMN_NAME),
                                                                                                                                                                                                                                               '_a','A'),'_b','B'),'_c','C'),'_d','D'),'_e','E'),'_f','F'),'_g','G'),
                                                                                                                                                                                       '_h','H'),'_i','I'),'_j','J'),'_k','K'),'_l','L'),'_m','M'),'_n','N'),
                                                                                                                               '_o','O'),'_p','P'),'_q','Q'), '_r','R'),'_s','S'),'_t','T'),'_u','U'),
                                                                       '_v','V'),'_w','W'),'_x','X'), '_y','Y'),'_z','Z')
                                   ||'} '||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name),
  '
   FROM dual
    ) T2 ON (
    ',

    (select LISTAGG2('T1.'||dic.column_name||' = T2.'||dic.column_name,' AND ') from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='P'),
')
  WHEN NOT MATCHED THEN INSERT( ',

  (select LISTAGG2(dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name ),

  ') VALUES
  (
  ',
  (select LISTAGG2('T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name),
  '
  ) WHEN MATCHED THEN UPDATE
  SET ',

  (select LISTAGG2(' T1.'||dtc.COLUMN_NAME||' =T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name
   and dtc.COLUMN_NAME!='ID' and dtc.COLUMN_NAME NOT IN(
   select dic.column_name from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='P'
   )
   )


  )
    as 主键_插入或更新语句,
       concat('MERGE INTO ',dbt.table_name,' T1
  USING (
  <foreach collection="list" item="item" index="index" separator="UNION ALL">
  SELECT ',
              (select LISTAGG2('#{item.'||
                               replace(replace(replace(replace(replace(replace(replace(
                                                                                       replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                               replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                                                                                       replace(replace(replace(replace(replace(
                                                                                                                                                                                                                                               LOWER(dtc.COLUMN_NAME),
                                                                                                                                                                                                                                               '_a','A'),'_b','B'),'_c','C'),'_d','D'),'_e','E'),'_f','F'),'_g','G'),
                                                                                                                                                                                       '_h','H'),'_i','I'),'_j','J'),'_k','K'),'_l','L'),'_m','M'),'_n','N'),
                                                                                                                               '_o','O'),'_p','P'),'_q','Q'), '_r','R'),'_s','S'),'_t','T'),'_u','U'),
                                                                       '_v','V'),'_w','W'),'_x','X'), '_y','Y'),'_z','Z')
                                   ||'} '||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name),
  '
   FROM dual
   </foreach>
    ) T2 ON (
    ',

    (select LISTAGG2('T1.'||dic.column_name||' = T2.'||dic.column_name,' AND ') from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='U'),
')
  WHEN NOT MATCHED THEN INSERT( ',

  (select LISTAGG2(dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name and dtc.COLUMN_NAME!='ID'),

  ') VALUES
  (
  ',
  (select LISTAGG2('T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name and dtc.COLUMN_NAME!='ID'),
  '
  ) WHEN MATCHED THEN UPDATE
  SET ',

  (select LISTAGG2(' T1.'||dtc.COLUMN_NAME||' =T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name
   and dtc.COLUMN_NAME!='ID' and dtc.COLUMN_NAME NOT IN(
   select dic.column_name from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='P'
   )
   )


  ) as 联合主键_批量插入或更新语句,

       concat('MERGE INTO ',dbt.table_name,' T1
  USING (
  SELECT ',
              (select LISTAGG2('#{'||
                               replace(replace(replace(replace(replace(replace(replace(
                                                                                       replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                               replace(replace(replace(replace(replace(replace(replace(
                                                                                                                                                                                                       replace(replace(replace(replace(replace(
                                                                                                                                                                                                                                               LOWER(dtc.COLUMN_NAME),
                                                                                                                                                                                                                                               '_a','A'),'_b','B'),'_c','C'),'_d','D'),'_e','E'),'_f','F'),'_g','G'),
                                                                                                                                                                                       '_h','H'),'_i','I'),'_j','J'),'_k','K'),'_l','L'),'_m','M'),'_n','N'),
                                                                                                                               '_o','O'),'_p','P'),'_q','Q'), '_r','R'),'_s','S'),'_t','T'),'_u','U'),
                                                                       '_v','V'),'_w','W'),'_x','X'), '_y','Y'),'_z','Z')
                                   ||'} '||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name),
  '
   FROM dual
    ) T2 ON (
    ',

    (select LISTAGG2('T1.'||dic.column_name||' = T2.'||dic.column_name,' AND ') from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='U'),
')
  WHEN NOT MATCHED THEN INSERT( ',

  (select LISTAGG2(dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name and dtc.COLUMN_NAME!='ID'),

  ') VALUES
  (
  ',
  (select LISTAGG2('T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name and dtc.COLUMN_NAME!='ID'),
  '
  ) WHEN MATCHED THEN UPDATE
  SET ',

  (select LISTAGG2(' T1.'||dtc.COLUMN_NAME||' =T2.'||dtc.COLUMN_NAME,',') from DBA_TAB_COLUMNS dtc where dtc.owner=dbt.owner and dtc.table_name=dbt.table_name
   and dtc.COLUMN_NAME!='ID' and dtc.COLUMN_NAME NOT IN(
   select dic.column_name from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='U'
   )
   )


  )
           as 联合主键_插入或更新语句
from dba_tables dbt
where dbt.owner='模式名' and dbt.table_name=upper('表名');

2.使用hint注释(没有其他需要update的值情况下,使用这个)

使用IGNORE_ROW_ON_DUPKEY_INDEX还是报[23000][-6602] 违反表[xxx]唯一性约束 | 达梦技术社区

可使用下面语句指定模式名和表名生成替换的关键字

select  concat('/*+ IGNORE_ROW_ON_DUPKEY_INDEX( ',dbt.table_name,' ( ',
    (select LISTAGG2( dic.column_name,',') WITHIN GROUP(order by dic.COLUMN_POSITION asc) from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='U' ),
' ))*/'

  )
  as 联合唯一索引,
  concat('/*+ IGNORE_ROW_ON_DUPKEY_INDEX( ',dbt.table_name,' ( ',
    (select LISTAGG2( dic.column_name,',') WITHIN GROUP(order by dic.COLUMN_POSITION asc) from dba_ind_columns dic
left join ALL_CONSTRAINTS ac on ac.owner=dic.table_owner and  ac.table_name=dic.table_name and dic.index_name=ac.index_name
where dic.table_owner=dbt.owner and dic.table_name=dbt.table_name
and ac.CONSTRAINT_TYPE='P' ),
' ))*/'

  ) as 主键
   from dba_tables dbt
 where dbt.owner='模式名' and dbt.table_name=upper('对应表名');

3.自增主键且没有其他唯一主键或联合唯一主键的,可以直接去掉 on duplicate key update,或改成标准的insert into语句

16)UNIX_TIMESTAMP 语法不一样

在达梦中UNIX_TIMESTAMP()会报错,需要改成UNIX_TIMESTAMP(NOW()),而且如果*1000后计算会出现数据溢出问题

例如

错误语法:select (UNIX_TIMESTAMP(NOW())*1000-30*24*60*60*1000) 出现数据溢出

正确改为:

select (UNIX_TIMESTAMP(NOW())-30*24*60*60)*1000

17) 注意达梦中CLOB,TEXT等大字段在java中可能无法序列化的问题

在mybatis中如果xml上映射包含有TEXT,CLOB等大字段的类型时,对应Java Type为OTHER类型,则返回的字段为达梦DmdbClob等内置类型,

如果不正确的解析手动处理,而是直接返回则会在json序列化中出现栈溢出问题,而且返回的结果不是我们所需的内容,因为在DmdbClob对象中,

data字段才是数据本身,而这个对象中有个connection对象包含着当前连接信息,数据库用户名密码属性等值,

其中connect中的epGroup和props对象相互依赖引用会导致jackjson不断的链式循环解析结构从而导致栈溢出

所以建议

官方说连接url上加clobAsString=1可以自动转换为string,但是测试text类型还是有问题,建议改成longvarchar类型,或者更改语法使用cast as varchar来强转,但是这个转换如果内容超长则会报错(不推荐)

方法1.修改xml中resultMap对应列的 jdbcType="OTHER" 新增 javaType="java.lang.String"来映射

方法2.或者把数据库列类型更改为longvarchar处理

alter table "模式名"."表名" modify "列名" LONGVARCHAR;

18) 达梦数据库中left join,join 等连接必须包含on匹配,不允许没有on进行连接的情况下使用where进行匹配,否则报语法错误

4.迁移数据对比

表迁移数据时，需要注意有些联合主键工具处理有bug，会把主键搞错，需要手工处理

#建议使用迁移工具，新建对比，填写mysql和达梦连接后，选择数据库对比，对比可能有缓存，修改数据后，需要关闭迁移工具后重新对比

查询mysql中表数据量

#查询mysql中表数据行数（数据可能不准确，查看 https://www.cnblogs.com/gina11/p/15478811.html ）
SELECT sc.TABLE_NAME,sc.TABLE_ROWS FROM  information_schema.TABLES sc WHERE 
sc.TABLE_SCHEMA='数据库名' ORDER BY sc.table_rows DESC

达梦中查询表数据量

-- 执行全表数据统计，有性能问题，耗时比较长
DBMS_STATS.GATHER_SCHEMA_STATS(
'ATM_PRODUCT', --HNSIMIS 为模式名
100, FALSE,'FOR ALL COLUMNS SIZE AUTO');

-- 查询表数据行
select owner,table_name，num_rows from dba_tables 
where owner='模式名' order by num_rows desc;

异常导入数据处理

1.使用工具导入数据时，异常的数据表记录起来，在mysql中导出对应表的数据
2.批量替换点`,mysql上的`(单引号)在达梦中不适用
3.主键自增的表，需要先设置表允许插入主键
SET IDENTITY_INSERT 表名 on;
4.如果导入有部分失败的数据，可以删除后操作
truncate table 表名;
5.插入时，报记录过长，则需要表上右键->存储选项->启用超长记录或者建表数据上指定
STORAGE(USING LONG ROW, ON "MAIN", CLUSTERBTR)

注意项.使用达梦管理工具导入数据时，记得点击上面的√提交事务，
或者调用commit提交，否则数据会不生效
手工导入数据中\`要批量替换为\``,\"直接替换为"

注意项.

使用达梦管理工具导入数据时，记得点击上面的√提交事务，或者调用commit提交，否则数据会不生效