华为云用户手册

日志概述 日志文件是DSC所有操作和状态的存储库。支持以下日志文件： SQL迁移日志 DSC.log：SQL迁移的所有活动。 DSCError.log：SQL迁移错误。 successRead.log：SQL迁移中对输入文件的成功读次数。 successWrite.log：SQL迁移中对输入文件的成功写次数。 Perl迁移日志 perlDSC.log：Perl迁移中所有的活动、预警和错误。 Apache Log4j用于指定DSC记录日志的框架。用户可使用以下Log4j配置文件，也可以根据需要进行自定义： Teradata/Oracle/Netezza/DB2 : config/log4j2.xml MySQL : config/log4j2_mysql.xml 父主题： 日志参考

Oracle PACKAGE迁移注意事项 1. 应将包规范(即包头)与包体分置于不同文件、相同输入路径中进行迁移。 2. 应先使用Bulk模式迁移普通DDL语句（包含PACKAGE脚本中引用到的全部表结构信息），以在config/create-types-UDT.properties文件中形成字典信息。之后再使用Blogic模式迁移包规范（即包头）与包体。具体解释如下： 在部分Oracle PACKAGE定义包规范时，使用了"tbName.colName%TYPE"语法以基于其他表对象声明自定义的记录类型。 1 2 3 4 5 6 7 8 9 10 例如 CREATE OR REPLACE PACKAGE p_emp AS --定义RECORD类型 TYPE re_emp IS RECORD( rno emp.empno%TYPE, rname emp.empname%TYPE ); END; GaussDB 暂不支持通过"tbName.colName%TYPE"语法在CREATE TYPE命令中指定列数据类型，DSC工具在迁移时需要构建含有诸如emp表信息的数据库上下文环境。由此需要先使用DSC工具迁移所有的建表脚本(即使用Bulk模式迁移普通DDL语句)，DSC内部会自动生成相应的数据字典。当含有各种表信息的上下文环境构建完成后，可以使用Blogic模式迁移 Oracle PACKAGE，此时re_emp记录类型会根据emp表的列类型完成迁移。 1 2 3 4 5 期望输出 CREATE TYPE p_emp.re_emp AS ( rno NUMBER(4), rname VARCHAR2(10) );

Netezza配置 设置Netezza配置参数可在迁移Netezza数据库脚本时自定义迁移工具的行为。 打开config文件夹中的features-netezza.properties文件，并根据实际需要设置表1中的参数。 表1 features-netezza.properties文件中的配置参数 参数 说明 取值范围 默认值 样例 rowstoreToColumnstore 是否将rowstore迁移为columnstore。 true false false rowstoreToColumnstore=false cstore_blob cstore_blob取值如下： bytea none bytea none bytea cstore_blob=bytea keywords_addressed_using_as 关键字“addressed_using_as”的取值如下： OWNER ATTRIBUTE SOURCE FREEZE OWNER ATTRIBUTE SOURCE FREEZE OWNER keywords_addressed_using_as=OWNER keywords_addressed_using_doublequote 关键字“addressed_using_doublequote”的可能取值。 FREEZE FREEZE keywords_addressed_using_doublequote=FREEZE 父主题： 配置DSC

术语表 下表包含缩略语、术语及其说明。 术语 描述 C 公用表表达式(CTE) 公用表表达式是一个在查询中定义的临时命名结果集，仅可用于更大的查询范围。 D 数据库(DB) 数据库是一组相关信息的集合，通常是为了使通用的检索变得简单和高效而组织起来的。 数据库属性： 数据库名称。 Endian文件格式（BIG_ENDIAN大端或LITTLE_ENDIAN小端）。 关系。 不存在无关系的数据库。 数据库管理员(DBA) 数据库管理员是负责组织中数据库的安装、配置、升级、管理、监控和维护的人员。 该角色包括开发和设计数据库策略、监控和优化数据库性能和容量，以及规划未来的扩展需求。数据库管理员亦可计划、协调和实施安全措施，以保障资料库的安全。 E 编码 在信息处理中，编码是一种规则系统，它把字母、单词、声音、图像或手势等信息转换成另一种规则。有时，它以缩短或秘密的形式通过通道进行通讯或存储在介质中。 I 索引 数据库管理系统中的一种有序数据结构，可加速表内数据的查询和更新。 M 迁移 迁移是指将源数据库（如Teradata）中的脚本、查询、模式、数据等迁移到目标数据库（如GaussDB (DWS)）。 元数据 元数据是关于数据的数据。元数据定义了数据的属性，用于指定数据的存储位置、历史数据、检索资源数据、记录信息等。 O 操作系统(OS) 操作系统是管理计算机中所有其他程序的程序，这些程序最初通过引导程序加载到计算机中。 Q 查询 查询是向数据库发出的信息请求。查询执行SQL语句，并返回该语句定义的结果集。 S 结构化查询语言(SQL) 一种编程语言，广泛用于访问、更新、管理和查询关系数据库中的数据。 模式 模式是数据库管理系统支持的正式语言中描述的结构。它是指数据的组织，描述数据库是如何构建的。（在关系数据库中，它描述了如何将数据库划分为表。） T Teradata Teradata是一种关系数据库管理系统。它可用于同时运行多个复杂查询。它支持使用SQL的即席查询，并广泛用于管理大型仓储操作。 表 紧密相关的列的集合。表由包含相同列的不同值的行组成。 V 视图 视图限制对表的特定行或列的访问。视图可以从一个或多个表中创建，并且由用于创建视图的查询决定。 父主题： DSC

错误码 表2 错误码 错误码 错误信息 Teradata DSC_ERR_003_001 Query/statement is not supported since the Teradata view "dbc.indices" is supported only for the indextype P and Q. DSC_ERR_003_002 Error in Bteq processing. Something went wrong while processing the BTEQ commands. DSC_ERR_003_003 Query/statement is not supported in ddl DSC. Please check the same and refer user manual for the supported feature list. DSC_ERR_003_004 Unsupported format decimal format like ZZZ99Z, ZZZ.ZZ9. DSC_ERR_003_005 The tool does not support the "IN/NOT IN to EXISTS/NOT EXISTS conversion" for the query in which its outer query refers multiple tables and the column(s) specified with IN / NOT IN operator do not have table reference. DSC_ERR_003_006 The tool does not support the query in which its outer query refers multiple tables and the column(s) specified with IN / NOT IN operator do not have table reference. DSC_ERR_003_007 Primary Index without column is not supported. DSC_ERR_003_008 TeradataQuerySplitter config file contains list is not supported. DSC_ERR_003_009 Gauss does not support WITH CHECK OPTION in CREATE VIEW. Please enable the config_param tdMigrateVIEWCHECKOPTION to comment the WITH CHECK OPTION syntax in the statement. DSC_ERR_003_010 Gauss does not have an equivalent syntax for CHARACTER SET & CASE SPECIFIC option in column-level. Please enable the config_param tdMigrateCharsetCase to comment the CHARACTER SET & CASE SPECIFIC option syntax in the statement. DSC_ERR_003_011 Gauss does not have equivalent syntax for LOCK option in CREATE VIEW and INSERT statement. You can rewrite this statement or set the configuration parameter tdMigrateLOCKOption to TRUE to comment the LOCK syntax in this statement. DSC_ERR_003_012 Invalid width (Number of rows) parameter in MDIFF function. DSC_ERR_003_013 First 2 parameters should be present in MDIFF function. DSC_ERR_003_014 Query/statement is not supported as ORDER BY clause is not present in TOP WITH TIES. Please check the same and refer user manual. DSC_ERR_003_015 Column mismatch for the TITLE conversion. DSC_ERR_003_016 Query/statement is not supported as same Table alias is addressed in both inner and outer query. Please check the same and refer user manual for the supported feature list. DSC_ERR_003_017 Sub query list does not have columns. DSC_ERR_003_018 Number of expressions specified in the outer query does not match with inner query. DSC_ERR_003_019 Error while loading the .RUN FILE from given location. DSC_ERR_003_020 Unable to delete the file, file not found. DSC_ERR_003_021 Unable to delete the file, failed with IOEXception. DSC_ERR_003_022 Please specify the value for environment_file_path parameter in features-teradata.properties. Application DSC_ERR_004_001 Application has timed out, exceeded the hours specified in the config file. Please configure the Timeout parameter in the application.properties to higher value. DSC_ERR_004_002 Error while loading the property files from config directory. DSC_ERR_004_003 Error while loading the property files from config directory, directory is not readable. DSC_ERR_004_004 Error while loading the property file. DSC_ERR_004_005 Unable to load the JSON file. DSC_ERR_004_006 DSC tool does not support this Conversion type provided. DSC_ERR_004_007 Error occurred while framing output replacement query. DSC_ERR_004_008 Invalid index value while parsing the script. DSC_ERR_004_009 Error in conversion process, unable to convert the script. DSC_ERR_004_010 No SQL files found in the input directory with the extension specified in the fileExtension property in application.properties. DSC_ERR_004_011 The query length parameter (MaxSqlLen) value is not valid. DSC_ERR_004_012 Since the input folder has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_013 Since the output directory has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_014 Disk is almost full. Please clear the space and re-run the tool. DSC_ERR_004_015 DSC has been cancelled as configured by the user. DSC_ERR_004_016 Error occurred while formatting the sql scripts. DSC_ERR_004_017 Invalid index specified for fetching the element from list while formatting the scripts DSC_ERR_004_018 Error occurred while converting from string to integer. DSC_ERR_004_019 Input File is modified while DSC is in progress. DSC_ERR_004_020 Process is null, unable to read encoding format. DSC_ERR_004_021 Target File does not have write permissions. DSC_ERR_004_022 The target directory does not have write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_023 PL/SQL object contains incorrect DDL/Query. Please check the script for the query position specified in the log. DSC_ERR_004_024 PreQueryValidation failed due to bracket mismatch or invalid terminator. DSC_ERR_004_025 Conversion task name is not valid. DSC_ERR_004_026 Database entered by the user is not supported by the DSC tool. DSC_ERR_004_027 Gauss db password should not be empty. DSC_ERR_004_028 Gauss db password should not be empty. DSC_ERR_004_029 Target db entered in the Gaussdb.properties is not valid. DSC_ERR_004_030 User name entered in the Gaussdb.properties is empty. DSC_ERR_004_031 Port entered in the Gaussdb.properties is not valid. DSC_ERR_004_032 IP entered in the Gaussdb.properties is not valid. DSC_ERR_004_033 Database name entered in the Gaussdb.properties is empty. DSC_ERR_004_034 DSC Application failed to start. DSC_ERR_004_035 Since the environment variable path has write privileges to Group and/or Others, process is stopped due to security reason. DSC_ERR_004_036 Error while loading environment parameter File. DSC_ERR_004_037 Invalid input (empty/space/string value) for the parameter NoOfThreads in application.properties. Hence taking the default processes. DSC_ERR_004_038 Input for the parameter NoOfThreads in application.properties is less than 1. Hence taking the default processes. DSC_ERR_004_039 Error in processing the DDL query. DSC_ERR_004_040 Error in processing the PL/SQL query. DSC_ERR_004_041 Error in post processing the query. DSC_ERR_004_042 Invalid application timeout value, default to 4 hours. DSC_ERR_004_043 Error in writing the output file. DSC_ERR_004_044 Error in reading the input file. DSC_ERR_004_045 No valid files found in the input directory for migration. DSC_ERR_004_046 Query is not converted as it contains unsupported keyword. DSC_ERR_004_047 Error while reading the property. DSC_ERR_004_048 PreQueryValidation failed due to query exceeds maximum length (MaxSqlLen config parameter). DSC_ERR_004_049 Thread count entered in the Gaussdb.properties is not valid. Wrapper DSC_ERR_005_003 Reading file Failed with error: File not found Exception. DSC_ERR_005_004 Reading file Failed with error: IOException. DSC_ERR_005_005 Root privileged users are not allowed to execute the DSC tool. DSC_ERR_005_006 Error while getting the id of os user used to execute the DSC tool. DSC_ERR_005_007 Arguments specified is not valid, please check the user manual for the command line arguments. DSC_ERR_005_008 File name is not specified for reading the encoding type. DSC_ERR_005_009 Invalid argument specified for the encoding parameter. DSC_ERR_005_010 Source database is not set. Please enter a valid source db and refer the user manual for syntax. DSC_ERR_005_011 Commandline database specified for source to target is not supported by the DSC tool. DSC_ERR_005_012 Error in loading config file with IOException. DSC_ERR_005_013 Initial JVM memory is greater than maximum JVM memory. DSC_ERR_005_014 Invalid value specified for configValue. DSC_ERR_005_015 Invalid source database specified for source-db option. DSC_ERR_005_016 Invalid target database specified for target-db option. DSC_ERR_005_017 Invalid conversion type specified for dsc-type option. DSC_ERR_005_018 Invalid application language specified for application-lang option. DSC_ERR_005_019 Conversion-type should be DDL for application-lang type as perl. DSC_ERR_005_020 Source-db should be teradata for application-lang type as perl. DSC_ERR_005_021 Please use "-VN [V1R7 | V1R8_330]" or "--version-number [V1R7 | V1R8_330]" to specify the kernel version which can be either V1R7 or V1R8_330. DSC_ERR_005_022 Input directory does not exist. DSC_ERR_005_023 Getting path for input directory failed with IOException. DSC_ERR_005_024 Getting path for output directory failed with IOException. DSC_ERR_005_025 Setting file permission for output directory failed with IOException. DSC_ERR_005_026 Creating output directory failed. DSC_ERR_005_027 Setting file permissions for log directory/file failed with FileException. DSC_ERR_005_028 Error while connecting to GaussDB, Failed with error. DSC_ERR_005_029 Error occurred due to file permission while creating or executing the file. DSC_ERR_005_030 No arguments specified in the commandline. DSC_ERR_005_031 Error occurred in creating output directory.

数据库管理 DSC工具迁移时会将MySQL SET CHARACTER SET语句迁移为SET SESSION NAMES。字符集对照如下表： 表1 字符集对照表 MySQL CHARACTER SET GaussDB(DWS) SESSION NAMES ASCII SQL_ASCII BIG5 BIG5 CP1250 WIN1250 CP1251 WIN1251 CP1256 WIN1256 CP1257 WIN1257 CP932 SJIS EUCJPMS EUC_JP EUCKR EUC_KR GB2312 GB18030 GBK GBK GREEK ISO_8859_7 HEBREW ISO_8859_8 KOI8R KOI8R KOI8U KOI8U LATIN1 LATIN1 LATIN2 LATIN2 LATIN5 LATIN5 LATIN7 LATIN7 SJIS SJIS SWE7 UTF8 TIS620 WIN874 UTF8 UTF8 UTF8MB4 UTF8 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 SET CHARACTER SET 'ASCII'; SET CHARACTER SET 'BIG5'; SET CHARACTER SET 'CP1250'; SET CHARACTER SET 'CP1251'; SET CHARACTER SET 'CP1256'; SET CHARACTER SET 'CP1257'; SET CHARACTER SET 'CP932'; SET CHARACTER SET 'EUCJPMS'; SET CHARACTER SET 'EUCKR'; SET CHARACTER SET 'GB2312'; SET CHARACTER SET 'GBK'; SET CHARACTER SET 'GREEK'; SET CHARACTER SET 'HEBREW'; SET CHARACTER SET 'KOI8R'; SET CHARACTER SET 'KOI8U'; SET CHARACTER SET 'LATIN1'; SET CHARACTER SET 'LATIN2'; SET CHARACTER SET 'LATIN5'; SET CHARACTER SET 'LATIN7'; SET CHARACTER SET 'SJIS'; SET CHARACTER SET 'SWE7'; SET CHARACTER SET 'TIS620'; SET CHARACTER SET 'UTF8'; SET CHARACTER SET 'UTF8MB4'; ##mysql中不支持 SET CHARACTER SET 'U CS 2'; ##mysql中不支持SET CHARACTER SET 'UTF16'; ##mysql中不支持SET CHARACTER SET 'UTF16LE'; ##mysql中不支持SET CHARACTER SET 'UTF32'; 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 SET SESSION NAMES 'SQL_ASCII'; SET SESSION NAMES 'BIG5'; SET SESSION NAMES 'WIN1250'; SET SESSION NAMES 'WIN1251'; SET SESSION NAMES 'WIN1256'; SET SESSION NAMES 'WIN1257'; SET SESSION NAMES 'SJIS'; SET SESSION NAMES 'EUC_JP'; SET SESSION NAMES 'EUC_KR'; SET SESSION NAMES 'GB18030'; SET SESSION NAMES 'GBK'; SET SESSION NAMES 'ISO_8859_7'; SET SESSION NAMES 'ISO_8859_8'; SET SESSION NAMES 'KOI8R'; SET SESSION NAMES 'KOI8U'; SET SESSION NAMES 'LATIN1'; SET SESSION NAMES 'LATIN2'; SET SESSION NAMES 'LATIN5'; SET SESSION NAMES 'LATIN7'; SET SESSION NAMES 'SJIS'; SET SESSION NAMES 'UTF8'; SET SESSION NAMES 'WIN874'; SET SESSION NAMES 'UTF8'; SET SESSION NAMES 'UTF8'; --mysql中不支持 SET CHARACTER SET 'UCS2'; --mysql中不支持SET CHARACTER SET 'UTF16'; --mysql中不支持SET CHARACTER SET 'UTF16LE'; --mysql中不支持SET CHARACTER SET 'UTF32'; 父主题： 事务管理和数据库管理

IGNORE MySQL UPDATE语句如果使用IGNORE修饰符，即使更新期间发生错误，UPDATE语句也不会中止。 输入示例 1 2 #测试 IGNORE 语法点 UPDATE IGNORE employees SET department_id=3; 输出示例 1 2 --测试 IGNORE 语法点 UPDATE "public"."employees" SET "department_id" = 3;

ORDER BY 如果一个MySQL UPDATE语句包含一个 ORDER BY子句，则这些行将按照该子句指定的顺序更新。 输入示例 1 2 # 测试 ORDER BY 语法点 UPDATE employees SET department_id=department_id+1 ORDER BY id; 输出示例 1 2 -- 测试 ORDER BY 语法点 UPDATE "public"."employees" SET "department_id" = department_id+1;

LOW_PRIORITY MySQL UPDATE语句如果使用LOW_PRIORITY修饰符，则执行UPDATE延迟。 输入示例 1 2 #测试 LOW_PRIORITY 语法点 UPDATE LOW_PRIORITY employees SET department_id=2; 输出示例 1 2 --测试 LOW_PRIORITY 语法点 UPDATE "public"."employees" SET "department_id" = 2;

IGNORE MySQL INSERT语句如果使用IGNORE修饰符，则执行INSERT语句时发生的错误将被忽略。 输入示例 1 2 3 4 5 6 7 8 9 10 # 如果表中已经存在相同的记录,则忽略当前新数据 INSERT IGNORE INTO exmp_tb2 VALUES(189, '189.23','nice','2017-11-12'); INSERT IGNORE INTO exmp_tb2 VALUES(130,'189.23','nice','2017-11-12'); INSERT IGNORE INTO exmp_tb2 VALUES(120,15.68,'good','2018-11-12'); INSERT IGNORE INTO exmp_tb2 VALUES(DEFAULT,128.23,'nice','2018-10-11'); INSERT IGNORE INTO exmp_tb2 VALUES(DEFAULT,DEFAULT,'nice','2018-12-14'); INSERT IGNORE INTO exmp_tb2 VALUES(DEFAULT,DEFAULT,'nice',DEFAULT); INSERT IGNORE INTO exmp_tb2 (tb2_id,tb2_price) VALUES(DEFAULT,DEFAULT); INSERT IGNORE INTO exmp_tb2 (tb2_id,tb2_price,tb2_note) VALUES(DEFAULT,DEFAULT,DEFAULT); INSERT IGNORE INTO exmp_tb2 (tb2_id,tb2_price,tb2_note,tb2_date) VALUES(DEFAULT,DEFAULT,DEFAULT,DEFAULT); 输出示例 1 2 3 4 5 6 7 8 9 10 -- 如果表中已经存在相同的记录,则忽略当前新数据 INSERT INTO "public"."exmp_tb2" VALUES (101,'189.23','nice','2017-11-12'); INSERT INTO "public"."exmp_tb2" VALUES (130,'189.23','nice','2017-11-12'); INSERT INTO "public"."exmp_tb2" VALUES (120,15.68,'good','2018-11-12'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,128.23,'nice','2018-10-11'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);

VALUES INSERT使用 VALUES语法的语句可以插入多行，以逗号分隔。 输入示例 1 INSERT INTO exmp_tb1 (tb1_name,tb1_gender,tb1_address,tb1_number) VALUES('David','male','NewYork','01015827875'),('Rachel','female','NewYork','01015827749'),('Monica','female','NewYork','010158996743'); 输出示例 1 2 3 INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_gender","tb1_address","tb1_number") VALUES ('David','male','NewYork','01015827875'); INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_gender","tb1_address","tb1_number") VALUES ('Rachel','female','NewYork','01015827749'); INSERT INTO "public"."exmp_tb1" ("tb1_name","tb1_gender","tb1_address","tb1_number") VALUES ('Monica','female','NewYork','010158996743');

SET MySQL INSERT...SET语句的形式插入基于明确指定的值的行。 输入示例 1 2 3 4 # INSERT INTO SET 可以针对性的执行插入操作，但是一次只能插入一行数据，不能批量添加数据 INSERT INTO exmp_tb2 SET tb2_price=56.1,tb2_note='unbelievable',tb2_date='2018-11-13'; INSERT INTO exmp_tb2 SET tb2_price=99.9,tb2_note='perfect',tb2_date='2018-10-13'; INSERT INTO exmp_tb2 SET tb2_id=9,tb2_price=99.9,tb2_note='perfect',tb2_date='2018-10-13'; 输出示例 1 2 3 4 -- INSERT INTO SET 可以针对性的执行插入操作，但是一次只能插入一行数据，不能批量添加数据 INSERT INTO "public"."exmp_tb2" ("tb2_price","tb2_note","tb2_date") VALUES (56.1,'unbelievable','2018-11-13'); INSERT INTO "public"."exmp_tb2" ("tb2_price","tb2_note","tb2_date") VALUES (99.9,'perfect','2018-10-13'); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (9,99.9,'perfect','2018-10-13');

ON DUPLICATE KEY UPDATE INSERT使用ON DUPLICATE KEY UPDATE子句可以使现有行更新。 输入示例 1 2 3 4 5 #ON DUPLICATE KEY UPDATE 若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE;否则执行插入操作 INSERT INTO exmp_tb2(tb2_id,tb2_price) VALUES(3,12.3) ON DUPLICATE KEY UPDATE tb2_price=12.3; INSERT INTO exmp_tb2(tb2_id,tb2_price) VALUES(4,12.3) ON DUPLICATE KEY UPDATE tb2_price=12.3; INSERT INTO exmp_tb2(tb2_id,tb2_price,tb2_note) VALUES(10,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE tb2_price=66.6; INSERT INTO exmp_tb2(tb2_id,tb2_price,tb2_note,tb2_date) VALUES(11,DEFAULT,DEFAULT,DEFAULT) ON DUPLICATE KEY UPDATE tb2_price=66.6; 输出示例 1 2 3 4 5 --ON DUPLICATE KEY UPDATE 若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE;否则执行插入操作 INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (3,12.3); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (4,12.3); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (10,DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (11,DEFAULT,DEFAULT,DEFAULT);

LOW_PRIORITY MySQL INSERT插入语句使用LOW_PRIORITY修饰符时，则执行该INSERT延迟。 输入示例 1 2 3 4 5 6 # LOW_PRIORITY 低优先级 INSERT LOW_PRIORITY INTO exmp_tb2 VALUES( DEFAULT, '128.23', 'nice', '2018-10-11'); INSERT LOW_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14' ); INSERT LOW_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT); INSERT LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT); INSERT LOW_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT); 输出示例 1 2 3 4 5 6 -- LOW_PRIORITY 低优先级 INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,'128.23','nice','2018-10-11'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT);

PARTITION 当插入到分区表中时，可以控制哪些分区和子分区接受新行。 输入示例 1 2 3 4 5 INSERT INTO employees PARTITION(p3) VALUES (19, 'Frank1', 'Williams', 1, 2); INSERT INTO employees PARTITION(p0) VALUES (4, 'Frank1', 'Williams', 1, 2); INSERT INTO employees PARTITION(p1) VALUES (9, 'Frank1', 'Williams', 1, 2); INSERT INTO employees PARTITION(p2) VALUES (10, 'Frank1', 'Williams', 1, 2); INSERT INTO employees PARTITION(p2) VALUES (11, 'Frank1', 'Williams', 1, 2); 输出示例 1 2 3 4 5 INSERT INTO "public"."employees" VALUES (19,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (4,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (9,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (10,'Frank1','Williams',1,2); INSERT INTO "public"."employees" VALUES (11,'Frank1','Williams',1,2);

DELAYED 在MySQL 5.7中，DELAYED关键字被接受，但被服务器忽略。 输入示例 1 2 3 4 5 6 7 8 9 # DELAYED 延迟 INSERT DELAYED INTO exmp_tb2 VALUES(99, 15.68, 'good', '2018-11-12'); INSERT DELAYED INTO exmp_tb2 VALUES(80, 12.3, 'cheap', '2018-11-11'); INSERT DELAYED INTO exmp_tb2 VALUES(DEFAULT, 128.23, 'nice', '2018-10-11'); INSERT DELAYED INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14'); INSERT DELAYED INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT); INSERT DELAYED INTO exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT); INSERT DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT); INSERT DELAYED INTO exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(DEFAULT, DEFAULT, DEFAULT, DEFAULT); 输出示例 1 2 3 4 5 6 7 8 9 -- DELAYED 延迟 INSERT INTO "public"."exmp_tb2" VALUES (99,15.68,'good','2018-11-12'); INSERT INTO "public"."exmp_tb2" VALUES (80,12.3,'cheap','2018-11-11'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,128.23,'nice','2018-10-11'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);

HIGH_PRIORITY MySQL中如果指定HIGH_PRIORITY，则会覆盖LOW_PRIORITY选项的效果。 输入示例 1 2 3 4 5 6 7 8 9 # HIGH_PRIORITY 高优先级 INSERT HIGH_PRIORITY INTO exmp_tb2 VALUES(100, 12.3, 'cheap', '2018-11-11'); INSERT HIGH_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, 128.23, 'nice', '2018-10-11'); INSERT HIGH_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', '2018-12-14'); INSERT HIGH_PRIORITY INTO exmp_tb2 VALUES(DEFAULT, DEFAULT, 'nice', DEFAULT); INSERT HIGH_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price) VALUES(DEFAULT, DEFAULT); INSERT HIGH_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price, tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT); INSERT HIGH_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price , tb2_note) VALUES(DEFAULT, DEFAULT, DEFAULT); INSERT HIGH_PRIORITY INTO exmp_tb2 (tb2_id, tb2_price, tb2_note, tb2_date) VALUES(DEFAULT, DEFAULT, DEFAULT, DEFAULT); 输出示例 1 2 3 4 5 6 7 8 9 -- HIGH_PRIORITY 高优先级 INSERT INTO "public"."exmp_tb2" VALUES (100,12.3,'cheap','2018-11-11'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,128.23,'nice','2018-10-11'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice','2018-12-14'); INSERT INTO "public"."exmp_tb2" VALUES (DEFAULT,DEFAULT,'nice',DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price") VALUES (DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note") VALUES (DEFAULT,DEFAULT,DEFAULT); INSERT INTO "public"."exmp_tb2" ("tb2_id","tb2_price","tb2_note","tb2_date") VALUES (DEFAULT,DEFAULT,DEFAULT,DEFAULT);

GROUP BY转换 MySQL/ADB分组查询的时候允许查询非分组字段，不报错；DWS分组查询时只能查询分组字段和聚集函数，报错。因此补齐没有group by的查询分组字段。 输入示例 1 SELECT e.department_id, department_name, ROUND(AVG(salary), 0) avg_salary FROM employees e JOIN departments d on e.department_id = d.department_id GROUP BY department_name ORDER BY department_name; 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 SELECT e.department_id, department_name, ROUND (AVG(salary), 0) AS "avg_salary" FROM employees "e" JOIN departments "d" ON e.department_id = d.department_id GROUP BY department_name, 1 ORDER BY department_name; 父主题： 数据操作语句（DML）

除法表达式 MySQL中，除法表达式中，当除数为0时，会返回null值。DWS会报错，因此对除法表达式进行转换，增加一个if条件表达式。 输入示例 1 2 select sum(c1) / c2 as result from table_t1; select sum(c1) / count (c3/c4) as result from table_t1; 输出示例 1 2 SELECT (if (c2 = 0, null, sum(c1) / c2)) AS "result" FROM table_t1; SELECT (if (count(if (c4 = 0, null, c3 / c4)) = 0, null, sum(c1) / count(if (c4 = 0, null, c3 / c4)))) AS "result" FROM table_t1; 父主题： 数据操作语句（DML）

INTERVAL MySQL中使用interval表达式格式为INTERVAL N时间单位，DWS不支持，需要转换为INTERVAL 'N'时间单位。 输入示例 1 2 3 4 5 6 SELECT CURRENT_TIME() - INTERVAL 4 DAY; SELECT NOW() - INTERVAL 5 HOUR; SELECT CURRENT_TIME() - INTERVAL '4' DAY; SELECT NOW() - INTERVAL '5' HOUR; SELECT CURRENT_TIME() - INTERVAL "4" DAY; SELECT NOW() - INTERVAL "5" HOUR; 输出示例 1 2 3 4 5 6 SELECT (CURRENT_TIME () - INTERVAL '4' DAY); SELECT (NOW () - INTERVAL '5' HOUR); SELECT (CURRENT_TIME () - INTERVAL '4' DAY); SELECT (NOW () - INTERVAL '5' HOUR); SELECT (CURRENT_TIME () - INTERVAL '4' DAY); SELECT (NOW () - INTERVAL '5' HOUR); 父主题： 数据操作语句（DML）

数据库 在MySQL中，DATABASE 是一种模式对象，等同于Oracle、GaussDB(DWS)数据库的SCHEMA概念。DSC工具迁移时考虑了以下两个场景。 创建数据库 输入示例 create database IF NOT EXISTS dbname1 CHARACTER SET=utf8 COLLATE=utf8_unicode_ci; create database IF NOT EXISTS dbname2; drop database if exists dbname1; drop database if exists dbname2; 输出示例 CREATE SCHEMA "dbname1"; CREATE SCHEMA "dbname2"; DROP SCHEMA IF EXISTS "dbname1"; DROP SCHEMA IF EXISTS "dbname2"; 使用数据库 输入示例 drop database if exists test; create database if not exists test; use test; 输出示例 DROP SCHEMA IF EXISTS "test"; CREATE SCHEMA "test"; SET CURRENT_SCHEMA = "test"; 父主题： MySQL语法迁移

注释 MySQL支持由 '#' 或 '--' 字符引起的单行注释，而GaussDB(DWS)仅支持由双破折号 '--' 字符引起的单行注释。DSC工具迁移时会将 '#' 转化为 '--' 注释。 输入示例 1 2 3 4 5 6 7 ## comment sample create a table CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl`( `runoob_id` VARCHAR, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR )ENGINE=InnoDB DEFAULT CHARSET=utf8; 输出示例 1 2 3 4 5 6 7 8 9 10 11 -- comment sample create a table CREATE TABLE IF NOT EXISTS "public"."runoob_tbl" ( "runoob_id" VARCHAR, "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("runoob_id"); 父主题： MySQL语法迁移

索引重命名 DSC工具支持索引重命名，对索引名前加表名前缀防止索引名冲突（只支持创建有具体索引名的DDL语句，目前不支持删除索引的重命名，修改该参数需慎重）。 修改配置 打开表1 features-mysql.properties文件中的配置参数配置文件，修改如下参数为true。(默认false：不进行重命名) 1 2 # 创建索引时，是否重新命名索引名 table.index.rename=true 输入示例 1 2 3 4 5 6 CREATE TABLE IF NOT EXISTS `CUSTOMER`( `NAME` VARCHAR(64) PRIMARY KEY, ID INTEGER, ID2 INTEGER); CREATE INDEX ID_INDEX USING BTREE ON CUSTOMER (ID); ALTER TABLE CUSTOMER ADD INDEX ID3_INDEX(ID2); 输出示例 1 2 3 4 5 6 CREATE TABLE IF NOT EXISTS "public"."customer" ( "name" VARCHAR(256) PRIMARY KEY, "id" INTEGER, "id2" INTEGER) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("name"); CREATE INDEX customer_id_index ON "public"."customer" USING BTREE ("id"); CREATE INDEX customer_id3_index ON "public"."customer" ("id2"); 父主题： 索引

BTREE索引 GaussDB(DWS)支持BTREE索引，但USING BTREE关键字在语句中的位置与MySQL存在差异。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 内联BTREE索引 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE `public`.`test_create_table03` ( `DEMAND_ID` INT(11) NOT NULL AUTO_INCREMENT, `DEMAND_NAME` CHAR(100) NOT NULL, `THEME` VARCHAR(200) NULL DEFAULT NULL, `SEND_ID` INT(11) NULL DEFAULT NULL, `SEND_NAME` CHAR(20) NULL DEFAULT NULL, `SEND_TIME` DATETIME NULL DEFAULT NULL, `DEMAND_CONTENT` TEXT NOT NULL, PRIMARY KEY(`DEMAND_ID`), INDEX THEME_INDEX(THEME) USING BTREE, INDEX NAME_INDEX USING BTREE (SEND_NAME(10)) ); 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE "public"."test_create_table03" ( "demand_id" SERIAL NOT NULL, "demand_name" CHAR(400) NOT NULL, "theme" VARCHAR(800) DEFAULT NULL, "send_id" INTEGER DEFAULT NULL, "send_name" CHAR(80) DEFAULT NULL, "send_time" TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL, "demand_content" TEXT NOT NULL, PRIMARY KEY ("demand_id") ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("demand_id"); CREATE INDEX "theme_index" ON "public"."test_create_table03" USING BTREE ("theme"); CREATE INDEX "name_index" ON "public"."test_create_table03" USING BTREE ("send_name"); ALTER TABLE创建BTREE索引。 输入示例 1 2 3 4 5 6 7 CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` FLOAT(10,2), PRIMARY KEY(`dataType1`) ); ALTER TABLE runoob_alter_test ADD KEY alterTable_addKey_indexType (dataType1) USING BTREE; 输出示例 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test" ( "datatype1" SERIAL NOT NULL, "datatype2" REAL, PRIMARY KEY ("datatype1") ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); CREATE INDEX "altertable_addkey_indextype" ON "public"."runoob_alter_test" ("datatype1"); CREATE INDEX创建BTREE索引。 输入示例 1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE `public`.`test_index_table05` ( `ID` INT(11) NOT NULL AUTO_INCREMENT, `USER_ID` INT(20) NOT NULL, `USER_NAME` CHAR(20) NULL DEFAULT NULL, `DETAIL` VARCHAR(100) NULL DEFAULT NULL, PRIMARY KEY (`ID`) ); CREATE UNIQUE INDEX USER_ID_INDEX USING BTREE ON TEST_INDEX_TABLE05(USER_ID); CREATE INDEX USER_NAME_INDEX USING BTREE ON TEST_INDEX_TABLE05(USER_NAME(10)); CREATE INDEX DETAIL_INDEX ON TEST_INDEX_TABLE05(DETAIL(50)) USING BTREE; CREATE INDEX USER_INFO_INDEX USING BTREE ON TEST_INDEX_TABLE05(USER_ID,USER_NAME(10)); 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE "public"."test_index_table05" ( "id" SERIAL NOT NULL, "user_id" INTEGER NOT NULL, "user_name" CHAR(80) DEFAULT NULL, "detail" VARCHAR(400) DEFAULT NULL, PRIMARY KEY ("id") ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id"); CREATE INDEX "user_id_index" ON "public"."test_index_table05" ("user_id"); CREATE INDEX "user_name_index" ON "public"."test_index_table05" USING BTREE ("user_name"); CREATE INDEX "detail_index" ON "public"."test_index_table05" USING BTREE ("detail"); CREATE INDEX "user_info_index" ON "public"."test_index_table05" USING BTREE ("user_id","user_name"); 父主题： 索引

唯一索引 GaussDB(DWS)不支持唯一索引(约束)与主键约束联合使用。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 MySQL唯一索引(约束)与主键约束联合使用的场景在工具迁移时会与OLAP场景下的分布键构成复杂的关系。工具暂不支持唯一索引(约束)与主键约束联合使用的场景。 内联唯一索引，如存在主键索引与唯一索引是相同列，DSC工具迁移时会将唯一索引移除。 输入示例 1 2 3 4 5 6 CREATE TABLE IF NOT EXISTS `public`.`runoob_dataType_test` ( `id` INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(128) NOT NULL, UNIQUE (id ASC) ); 输出示例 1 2 3 4 5 6 7 8 CREATE TABLE IF NOT EXISTS "public"."runoob_datatype_test" ( "id" SERIAL PRIMARY KEY, "name" VARCHAR(128) NOT NULL ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("id"); ALTER TABLE创建唯一索引，DSC工具迁移时会根据GaussDB(DWS)的特性创建普通索引。 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`( `dataType1` int, `dataType2` FLOAT(10,2), `dataType3` DOUBLE(20,8) )ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE runoob_alter_test ADD UNIQUE idx_runoob_alter_test_datatype1(dataType1); ALTER TABLE runoob_alter_test ADD UNIQUE INDEX idx_runoob_alter_test_datatype1(dataType2); ALTER TABLE runoob_alter_test ADD UNIQUE KEY idx_runoob_alter_test_datatype1(dataType3); CREATE TABLE IF NOT EXISTS `public`.`runoob_alter_test`( `dataType1` int, `dataType2` FLOAT(10,2), `dataType3` DOUBLE(20,8), `dataType4` TEXT NOT NULL, `dataType5` YEAR NOT NULL DEFAULT '2018', `dataType6` DATETIME NOT NULL DEFAULT '2018-10-12 15:27:33.999999' )ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE idx_runoob_alter_test_datatype1(dataType1); ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE INDEX idx_runoob_alter_test_datatype2(dataType2); ALTER TABLE runoob_alter_test ADD CONSTRAINT UNIQUE KEY idx_runoob_alter_test_datatype3(dataType3); ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE idx_runoob_alter_test_datatype4(dataType4); ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE INDEX idx_runoob_alter_test_datatype5(dataType5); ALTER TABLE runoob_alter_test ADD CONSTRAINT constraint_dataType UNIQUE KEY idx_runoob_alter_test_datatype6(dataType6); 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test" ( "datatype1" INTEGER, "datatype2" REAL, "datatype3" DOUBLE PRECISION ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype1"); CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype2"); CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype3"); CREATE TABLE IF NOT EXISTS "public"."runoob_alter_test" ( "datatype1" INTEGER, "datatype2" REAL, "datatype3" DOUBLE PRECISION, "datatype4" TEXT NOT NULL, "datatype5" SMALLINT NOT NULL DEFAULT '2018', "datatype6" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT '2018-10-12 15:27:33.999999' ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); CREATE INDEX "idx_runoob_alter_test_datatype1" ON "public"."runoob_alter_test" ("datatype1"); CREATE INDEX "idx_runoob_alter_test_datatype2" ON "public"."runoob_alter_test" ("datatype2"); CREATE INDEX "idx_runoob_alter_test_datatype3" ON "public"."runoob_alter_test" ("datatype3"); CREATE INDEX "idx_runoob_alter_test_datatype4" ON "public"."runoob_alter_test" ("datatype4"); CREATE INDEX "idx_runoob_alter_test_datatype5" ON "public"."runoob_alter_test" ("datatype5"); CREATE INDEX "idx_runoob_alter_test_datatype6" ON "public"."runoob_alter_test" ("datatype6"); CREATE INDEX创建唯一索引，DSC工具迁移时会根据GaussDB(DWS)的特性创建普通索引。 输入示例 1 2 3 4 5 6 7 8 9 CREATE TABLE `public`.`test_index_table01` ( `TABLE01_ID` INT(11) NOT NULL, `TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL, `AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL, `AUTHOR_ID` INT(11) NULL DEFAULT NULL, `CREATE_TIME` INT NULL DEFAULT NULL, PRIMARY KEY(`TABLE01_ID`) ); CREATE UNIQUE INDEX AUTHOR_INDEX ON `test_index_table01`(AUTHOR_ID); 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL, PRIMARY KEY ("table01_id") ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id"); CREATE INDEX "author_index" ON "public"."test_index_table01" ("author_id"); CREATE TABLE中存在多个唯一索引，DSC工具迁移时会根据GaussDB(DWS)的特性将所有唯一索引创建为普通索引。 输入示例 1 2 3 4 5 6 7 8 9 CREATE TABLE `public`.`test_index_table01` ( `TABLE01_ID` INT(11) NOT NULL, `TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL, `AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL, `AUTHOR_ID` INT(11) NULL DEFAULT NULL, `CREATE_TIME` INT NULL DEFAULT NULL, UNIQUE(`TABLE01_ID`), UNIQUE(`AUTHOR_ID`) ); 输出示例 1 2 3 4 5 6 7 8 9 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id"); CREATE INDEX "idx_test_index_table01_table01_id" ON "public"."test_index_table01"("TABLE01_ID"); CREATE INDEX "idx_test_index_table01_author_id" ON "public"."test_index_table01"("AUTHOR_ID"); CREATE TABLE中存在一个唯一索引，并不存在主键索引时，DSC工具迁移时会根据GaussDB(DWS)的特性保留该唯一索引。 输入示例 1 2 3 4 5 6 7 8 CREATE TABLE `public`.`test_index_table01` ( `TABLE01_ID` INT(11) NOT NULL, `TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL, `AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL, `AUTHOR_ID` INT(11) NULL DEFAULT NULL, `CREATE_TIME` INT NULL DEFAULT NULL, UNIQUE(`AUTHOR_ID`) ); 输出示例 1 2 3 4 5 6 7 8 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL, UNIQUE ("author_id") ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("author_id"); CREATE TABLE中存在主键索引时，DSC工具迁移时会根据GaussDB(DWS)的特性将所有的唯一索引创建为普通索引。 输入示例 1 2 3 4 5 6 7 8 9 CREATE TABLE `public`.`test_index_table01` ( `TABLE01_ID` INT(11) NOT NULL, `TABLE01_THEME` VARCHAR(100) NULL DEFAULT NULL, `AUTHOR_NAME` CHAR(10) NULL DEFAULT NULL, `AUTHOR_ID` INT(11) NULL DEFAULT NULL, `CREATE_TIME` INT NULL DEFAULT NULL, PRIMARY KEY(`TABLE01_ID`), UNIQUE(`AUTHOR_ID`) ); 输出示例 1 2 3 4 5 6 7 8 9 CREATE TABLE "public"."test_index_table01" ( "table01_id" INTEGER NOT NULL, "table01_theme" VARCHAR(400) DEFAULT NULL, "author_name" CHAR(40) DEFAULT NULL, "author_id" INTEGER DEFAULT NULL, "create_time" INTEGER DEFAULT NULL, PRIMARY KEY ("table01_id") ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY HASH ("table01_id"); CREATE INDEX "idx_test_index_table01_author_id" ON "public"."test_index_table01"("AUTHOR_ID"); 父主题： 索引

添加与删除列 MySQL添加、删除列语句与GaussDB(DWS)存在差异。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 GaussDB不支持表定义中列序数的变更，工具暂不支持FRIST，AFTER特性的完整迁移。基于当前的临时方案，工具仅仅移除该关键字。 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 CREATE TABLE IF NOT EXISTS `runoob_alter_test`( `dataType1` int NOT NULL AUTO_INCREMENT, `dataType2` FLOAT(10,2), `dataType3` DOUBLE(20,8), `dataType4` TEXT NOT NULL, `dataType5` YEAR NOT NULL DEFAULT '2018', `dataType6` DATETIME NOT NULL DEFAULT '2018-10-12 15:27:33.999999', `dataType7` CHAR NOT NULL DEFAULT '', `dataType8` VARCHAR(50), `dataType9` VARCHAR(50) NOT NULL DEFAULT '', `dataType10` TIME NOT NULL DEFAULT '10:20:59', PRIMARY KEY(`dataType1`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; ## A. ALTER TABLE runoob_alter_test ADD dataType1_1 INT NOT NULL AFTER dataType1; ALTER TABLE runoob_alter_test DROP dataType1_1; ## B. ALTER TABLE runoob_alter_test ADD dataType1_1 INT NOT NULL FIRST; ALTER TABLE runoob_alter_test DROP dataType1_1; ## C. ALTER TABLE runoob_alter_test ADD COLUMN dataType1_1 INT NOT NULL AFTER dataType2; ALTER TABLE runoob_alter_test DROP COLUMN dataType1_1; ## D. ALTER TABLE runoob_alter_test ADD COLUMN dataType1_1 INT NOT NULL FIRST; ALTER TABLE runoob_alter_test DROP COLUMN dataType1_1; ## E. ALTER TABLE runoob_alter_test ADD COLUMN(dataType1_1 INT NOT NULL, dataType1_2 VARCHAR(200) NOT NULL); ALTER TABLE runoob_alter_test DROP COLUMN dataType1_1, DROP COLUMN dataType1_2; 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 CREATE TABLE "public"."runoob_alter_test" ( "datatype1" SERIAL NOT NULL, "datatype2" REAL, "datatype3" DOUBLE PRECISION, "datatype4" TEXT NOT NULL, "datatype5" SMALLINT NOT NULL DEFAULT '2018', "datatype6" TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT '2018-10-12 15:27:33.999999', "datatype7" CHAR(4) NOT NULL DEFAULT '', "datatype8" VARCHAR(200), "datatype9" VARCHAR(200) NOT NULL DEFAULT '', "datatype10" TIME WITHOUT TIME ZONE NOT NULL DEFAULT '10:20:59', PRIMARY KEY ("datatype1") ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype1"); -- A. ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL DEFAULT 0; ALTER TABLE "public"."runoob_alter_test" DROP COLUMN "datatype1_1" RESTRICT; -- B. ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL DEFAULT 0; ALTER TABLE "public"."runoob_alter_test" DROP COLUMN "datatype1_1" RESTRICT; -- C. ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL DEFAULT 0; ALTER TABLE "public"."runoob_alter_test" DROP COLUMN "datatype1_1" RESTRICT; -- D. ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL DEFAULT 0; ALTER TABLE "public"."runoob_alter_test" DROP COLUMN "datatype1_1" RESTRICT; -- E. ALTER TABLE "public"."runoob_alter_test" ADD COLUMN "datatype1_1" INTEGER NOT NULL, ADD COLUMN "datatype1_2" VARCHAR(800) NOT NULL DEFAULT ''; ALTER TABLE "public"."runoob_alter_test" DROP COLUMN "datatype1_1" RESTRICT, DROP COLUMN "datatype1_2" RESTRICT; 父主题： 表（可选参数、操作）

行列存压缩 GaussDB(DWS)中，只支持列存表压缩功能，暂不支持行存表压缩功能。优化行列存压缩机制，DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 压缩参数 : table.compress.mode创建新表时，需要在CREATE TABLE语句中指定关键字COMPRESS，这样，当对该表进行批量插入时就会触发压缩特性。该特性会在页范围内扫描所有元组数据，生成字典、压缩元组数据并进行存储。指定关键字NOCOMPRESS则不对表进行压缩。 table.compress.row和table.compress.column指定表数据的压缩级别，它决定了表数据的压缩比以及压缩时间。一般来讲，压缩级别越高，压缩比也越大，压缩时间也越长；反之亦然。实际压缩比取决于加载的表数据的分布特征。 table.compress.level指定表数据同一压缩级别下的不同压缩水平，它决定了同一压缩级别下表数据的压缩比以及压缩时间。对同一压缩级别进行了更加详细的划分，为用户选择压缩比和压缩时间提供了更多的空间。总体来讲，此值越大，表示同一压缩级别下压缩比越大，压缩时间越长；反之亦然。 行存表输入示例 1 2 3 4 5 6 7 DROP TABLE IF EXISTS `public`.`runoob_tbl`; CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl`( `runoob_id` VARCHAR, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR )ENGINE=InnoDB DEFAULT CHARSET=utf8; 行存表输出示例 1 2 3 4 5 6 7 DROP TABLE IF EXISTS "public"."runoob_tbl"; CREATE TABLE IF NOT EXISTS "public"."runoob_tbl" ( "runoob_id" VARCHAR, "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR ) WITH (ORIENTATION = ROW, COMPRESSION = YES) COMPRESS DISTRIBUTE BY HASH ("runoob_id"); 列存表输入示例 1 2 3 4 5 6 7 DROP TABLE IF EXISTS `public`.`runoob_tbl`; CREATE TABLE IF NOT EXISTS `public`.`runoob_tbl`( `runoob_id` VARCHAR, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` VARCHAR )ENGINE=InnoDB DEFAULT CHARSET=utf8; 列存表输出示例 1 2 3 4 5 6 7 8 9 10 11 DROP TABLE IF EXISTS "public"."runoob_tbl"; CREATE TABLE IF NOT EXISTS "public"."runoob_tbl" ( "runoob_id" VARCHAR, "runoob_title" VARCHAR(400) NOT NULL, "runoob_author" VARCHAR(160) NOT NULL, "submission_date" VARCHAR ) WITH ( COMPRESSLEVEL = 1, ORIENTATION = COLUMN, COMPRESSION = LOW ) DISTRIBUTE BY HASH ("runoob_id"); 父主题： 表（可选参数、操作）

RENAME 重命名表名 MySQL重命名表名的语句与GaussDB(DWS)有一些差异。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 工具暂不支持原表名附有DATABASE(SCHEMA)的场景。 MySQL通过RENAME TABLE语句修改表名。 输入示例 1 2 3 4 5 # 单表重命名 RENAME TABLE DEPARTMENT TO NEWDEPT; # 多表重命名 RENAME TABLE NEWDEPT TO NEWDEPT_02,PEOPLE TO PEOPLE_02; 输出示例 1 2 3 4 5 6 --单表重命名 ALTER TABLE "public"."department" RENAME TO "newdept"; --多表重命名 ALTER TABLE "public"."newdept" RENAME TO "newdept_02"; ALTER TABLE "public"."people" RENAME TO "people_02"; MySQL通过ALTER TABLE RENAME 语句修改表名，DSC工具迁移该语句时会将“AS”关键字迁移为“TO”。 输入示例 1 2 3 4 5 ## A. ALTER TABLE runoob_alter_test RENAME TO runoob_alter_testnew; ## B. ALTER TABLE runoob_alter_testnew RENAME AS runoob_alter_testnewnew; 输出示例 1 2 3 4 5 -- A. ALTER TABLE "public"."runoob_alter_test" RENAME TO "runoob_alter_testnew"; -- B. ALTER TABLE "public"."runoob_alter_testnew" RENAME TO "runoob_alter_testnewnew"; 父主题： 表（可选参数、操作）

ROUNDROBIN表 GaussDB(DWS)支持建立roundrobin表，根据实际需要设置表1中的参数table.type进行配置。设置table.type=ROUND-ROBIN。 输入示例 1 2 3 4 5 CREATE TABLE charge_snapshot ( id bigint NOT NULL, profit_model integer, ladder_rebate_rule text ); 输出示例 1 2 3 4 5 CREATE TABLE "public"."charge_snapshot" ( "id" BIGINT NOT NULL, "profit_model" INTEGER, "ladder_rebate_rule" TEXT ) WITH (ORIENTATION = ROW, COMPRESSION = NO) NOCOMPRESS DISTRIBUTE BY ROUNDROBIN; 父主题： 表（可选参数、操作）

MODIFY修改列 MySQL使用MODIFY关键字修改列数据类型、设置非空约束。DSC工具迁移时会根据GaussDB(DWS)的特性进行相应适配。 输入示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE TABLE IF NOT EXISTS `runoob_alter_test`( `dataType0` varchar(100), `dataType1` bigint, `dataType2` bigint, `dataType3` bigint )ENGINE=InnoDB DEFAULT CHARSET=utf8; ## A. ALTER TABLE runoob_alter_test MODIFY dataType1 smallint; ## B. ALTER TABLE runoob_alter_test MODIFY dataType1 smallint NOT NULL; ## C. ALTER TABLE runoob_alter_test MODIFY dataType1 smallint NOT NULL FIRST; ## D. ALTER TABLE runoob_alter_test MODIFY dataType1 smallint NOT NULL AFTER dataType3; 输出示例 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 CREATE TABLE "public"."runoob_alter_test" ( "datatype0" VARCHAR(400), "datatype1" BIGINT, "datatype2" BIGINT, "datatype3" BIGINT ) WITH ( ORIENTATION = ROW, COMPRESSION = NO ) NOCOMPRESS DISTRIBUTE BY HASH ("datatype0"); -- A. ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NULL DEFAULT NULL; -- B. ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NOT NULL; -- C. ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NOT NULL; -- D. ALTER TABLE "public"."runoob_alter_test" MODIFY "datatype1" SMALLINT NOT NULL; 父主题： 表（可选参数、操作）