MySQL迁移到达梦数据库FAQ

1、列[user]附近出现错误
示例：

 /* 任务失败: 创建表"T" */ 
 /* 第 6 行, 第 40 列[user]附近出现错误: 
语法分析出错 */ 
 /* 相关SQL语句: */ 
CREATE TABLE "TEST_SNAPSHOT"
(
 "GROUPGUID" VARCHAR(400) NULL,
 "OBJECTGUID" VARCHAR(400) NULL,
 "OBJECTNAME" VARCHAR(400) NULL,
 "OBJECTTYPE" VARCHAR(400) DEFAULT '( 'user' )'
 NULL,
 "ORDERNUMBER" INT NULL,
 "ROWGUID" VARCHAR(200) NOT NULL,
 "CLIENTIP" VARCHAR(200) NULL,
 "APPKEY" VARCHAR(400) NULL,
 "BOOKGUID" VARCHAR(200) NOT NULL,
 "ROW_ID" INT NULL
);

修正方法：默认值字段用单引号包围如下

 "OBJECTTYPE" VARCHAR(400) DEFAULT 'user' 

2、列[using]附近出现错误
示例：

/* 相关SQL语句: */
/* 第 N 行, 第 709 列[using]附近出现错误:
语法分析出错 */

/* 相关SQL语句: */
CREATE
        VIEW "VIE_A_MYOLE"
        (
                "ROWGUID"        ,
                "MODULEGUID"     ,
                "MYDESKGUID"     ,
                "BELONGUSERGUID" ,
                "ORDERNUMBER"    ,
                "ISDISABLE"      ,
                "UIGUID"         ,
                "APPGUID"        ,
                "ICON"           ,
                "ICONCONTENT"    ,
                "ICONCONTENTTYPE",
                "ICONUPDATETIME" ,
                "MODULENAME"     ,
                "MODULEMENUNAME" ,
                "MODULEURL"      ,
                "ISBLANK"        ,
                "MESSAGECOUNTURL"
        ) AS
select
        "PM"."ROWGUID"        AS "ROWGUID"        ,
        "PM"."MODULEGUID"     AS "MODULEGUID"     ,
        "PM"."BELONGDESKGUID" AS "MYDESKGUID"     ,
        "PM"."BELONGUSERGUID" AS "BELONGUSERGUID" ,
        "PM"."ORDERNUMBER"    AS "ORDERNUMBER"    ,
        "PM"."ISDISABLE"      AS "ISDISABLE"      ,
        "PM"."UIGUID"         AS "UIGUID"         ,
        "M"."APPGUID"         AS "APPGUID"        ,
        "M"."ICON"            AS "ICON"           ,
        "M"."ICONCONTENT"     AS "ICONCONTENT"    ,
        "M"."ICONCONTENTTYPE" AS "ICONCONTENTTYPE",
        "M"."ICONUPDATETIME"  AS "ICONUPDATETIME" ,
        "M"."MODULENAME"      AS "MODULENAME"     ,
        "M"."MODULEMENUNAME"  AS "MODULEMENUNAME" ,
        "M"."MODULEURL"       AS "MODULEURL"      ,
        "M"."ISBLANK"         AS "ISBLANK"        ,
        "M"."MESSAGECOUNTURL" AS "MESSAGECOUNTURL"
from
        (app_pers pm join app_o m on((pm.moduleguid = convert(m.moduleguid using utf8mb4)))) where (m.isdisable = 0)        

修正方法：

去掉convert以及utf8mb4关键字
(app_personalmodule pm join app_module m on((pm.moduleguid = m.moduleguid))) where (m.isdisable = 0) 

3、列[)]附近出现错误

/* 任务失败: 创建视图"VIEW_COM" */
/* 第 3 行, 第 463 列[)]附近出现错误:
语法分析出错 */
/* 相关SQL语句: */
CREATE
        VIEW "VIEW_COMPFO"
        (
                "PRJNAME"    ,
                "CORPNAME"   ,
                "DWGUID"     ,
                "DWTYPE"     ,
                "PROJECTGUID",
                "CORPCODE"   ,
                "LZYGUID"    ,
                "LWYGUID"
        ) AS
select
        "PROJECTINFO"."NAME"               AS "PRJNAME"    ,
        "PROJECTINFO"."CONTRACTORCORPNAME" AS "CORPNAME"   ,
        "PROJECTINFO"."DANWEIGUID"         AS "DWGUID"     ,
        '总'                                         AS "DWTYPE"     ,
        "PROJECTINFO"."ROWGUID"            AS "PROJECTGUID",
        "PROJECTINFO"."CONTRACTORCORPCODE" AS "CORPCODE"   ,
        "SMZ_PROJECTLZYRELATION"."LZYGUID" AS "LZYGUID"    ,
        "PROJECTINFO"."LWYGUID"            AS "LWYGUID"
from
        ("PROJE"
join "SMZ_PROJON")
where
        (
                "SMZ_PROJECTLZYRELATION"."PROJECTGUID" = "PROJECTINFO"."ROWGUID"
        );
        
  
  修正方法：
  MySQL中的join既可以使用on也可以使用where，在达梦中请使用标准写法，修改成如下
from "PROJECTINFO"
join "SMZ_PROJECTLZYRELATION"
on
        (
                "SMZ_PROJECTLZYRELATION"."PROJECTGUID" = "PROJECTINFO"."ROWGUID"
        );       

4、无效的列名[ROWID]
示例：

/* 第8 行附近出现错误:
无效的列名[ROWID] */
/* 相关SQL语句: */

CREATE
        TABLE "COE_MAXNU"
        (
                "CODEID"       INT IDENTITY(1, 1) NOT NULL,
                "ROWID"       INT NULL                   ,
                "NUMBERNAME"   VARCHAR(150) NULL          ,
                "NUMBERFLAG"   VARCHAR(200) NULL          ,
                "CURRENTVALUE" VARCHAR(200) NULL
        );        

修正方法：
"ROWID"为数据库保留字，请更换其他列名

CREATE
        TABLE "COE_MAXNU"
        (
                "CODEID"       INT IDENTITY(1, 1) NOT NULL,
                "ROW_ID"       INT NULL                   ,
                "NUMBERNAME"   VARCHAR(150) NULL          ,
                "NUMBERFLAG"   VARCHAR(200) NULL          ,
                "CURRENTVALUE" VARCHAR(200) NULL
        );   

5、REFERENCE: 标识符无效

 /* ORA-00904: S.REFERENCE: 标识符无效
CREATE VIEW VIEW_AIT_ENTE_JL (BELONGXIAQUCODE,OPERATEUSERNAME,OPERATEDATE,ROW_ID,
YEARFLAG,ROWGUID,PERSONGUID,STARTDATE,ENDDATE,COMPANYNAME,DOSITION,REFERENCE,REFERENCECONTACT,
SXGUID,POSITION,JOB,PERSONCATEGORY)  
AS 
select S.BELONGXIAQUCODE  AS BELONGXIAQUCODE,
       S.OPERATEUSERNAME  AS OPERATEUSERNAME,
       S.OPERATEDATE      AS OPERATEDATE,
       S.ROW_ID           AS ROW_ID,
       S.YEARFLAG         AS YEARFLAG,
       S.ROWGUID          AS ROWGUID,
       S.PERSONGUID       AS PERSONGUID,
       S.STARTDATE        AS STARTDATE,
       S.ENDDATE          AS ENDDATE,
       S.COMPANYNAME      AS COMPANYNAME,
       S.DOSITION         AS DOSITION,
       S.REFERENCE        AS REFERENCE,
       S.REFERENCECONTACT AS REFERENCECONTACT,
       S.SXGUID           AS SXGUID,
       S.POSITION         AS POSITION,
       S.JOB              AS JOB,
       P.PERSONCATEGORY   AS PERSONCATEGORY
  from AUDITENTPERINFO P, AUDITSTAFFRESUME S
 where P.ROWGUID = S.PERSONGUID;

修正方法：REFERENCE为数据库保留字无法使用，请使用其他列名

6、报错：第75 行附近出现错误[-6116]，数据类型不匹配

修正方法：
1、达梦视图的union all连接多张表列字段，字段类型需要一致，如果包含clob、text类型，与varchar类型，需要显式转换
如下将"WORKFLOW_WORKITEM_HISTORY"."ACTIVITYNAME"的字段转为varchar
将clob字段转为varchar dbms_lob.substr(“WORKFLOW_WORKITEM_HISTORY”.“ACTIVITYNAME”,4000)

7、MySQL的concat_ws函数可以用达梦的concat函数替换
原始sql：

 select "TEST_SCHEDULE_MAIN"."SCHEDULEGUID" AS "SCHEDULEGUID",
        (case when (isnull("TEST_SCHEDULE_SHARE"."OBJGUID") or ("TEST_SCHEDULE_SHARE"."OBJGUID" = '')
) then "TEST_SCHEDULE_MAIN"."BELONGGUID" else 
concat_ws(';',TEST_schedule_main.BelongGuid,TEST_schedule_share.ObjGuid) end) AS userguid,

修正为：

 select
        "TEST_SCHEDULE_MAIN"."SCHEDULEGUID" AS "SCHEDULEGUID",
        (case when (isnull("TEST_SCHEDULE_SHARE"."OBJGUID") or ("TEST_SCHEDULE_SHARE"."OBJGUID" = '')
) then "TEST_SCHEDULE_MAIN"."BELONGGUID" else 
concat(concat(TEST_SCHEDULE_MAIN.BELONGGUID, ';'), TEST_SCHEDULE_SHARE.OBJGUID) end) AS "USERGUID" ,
               

8、第93行附近出现错误[-2685]:试图在blob或者clob列上排序或比较

修正方法：将clob、blob转为varchar

dbms_lob.substr("TEST_OU"."OUCODELEVEL",4000) 

9、无法解析的成员访问表达式[date_format]

date_format("TEST_MEETING_TABLE"."TXTDATE", '%h') AS "F_HOUR",
date_format("TEST_MEETING_TABLE"."TXTDATE", '%i') AS "F_MINUTE" 

修正如下：

to_date("TEST_MEETING_TABLE"."TXTDATE", 'hh24')
to_date("TEST_MEETING_TABLE"."TXTDATE", 'mi')

10、数据类型不匹配
修正方法：
如果有clob，text，blob等大字段对象，可使用dbms_lob.substr函数包截断成varchar类型。
示例：

dbms_lob.substr("WOR_WORKM_HIY"."ACTIVITYNAME",4000)

11、违反列非空约束

MySQL可以插入空字符串’’,而达梦在插入空字符串之后会变成null值，如果源库有空字符串的行，达梦在遇到非空约束的列字段中该行无法被插入，可以在源库将空字符串用其他值替代或者删除

12、列长度超出定义
MySQL的字符串以字符个数存储，达梦按照字符集大小存储，中文字符串gbk占2个字节，utf8占3个字节。
例如MySQL中varchar(150),可以存最多150个中文
在达梦最大只能存75个中文（GBK）或者50个中文（UTF8）

13、记录超长

alter table CHECKRESULT   enable using LONG ROW ;

批量开启超长字段
select 'alter table '||table_name||' enable using LONG ROW;' from user_tables where table_name like 'EJG%'