HomeMogDBMogDB StackUqbar

Documentation:v2.0

Supported Versions:

Migrating Data from MySQL to openGauss/MogDB Using MTK

Supported Versions

  • 5.5
  • 5.6
  • 5.7
  • 8.0

Precautions

Empty string problem

  • openGauss A mode is different from MySQL, and data needs to be processed. When the attribute of the column is ``NOT NULL, it is converted to" "`, and when it is allowed to be NULL, it is converted to NULL
  • openGauss PG mode is the same as MySQL, NULL != '' No need to deal with
  • openGauss B mode is the same as MySQL, NULL != '' No need to deal with

Database compatible types. Value range: A, B, C, and PG

  • A indicates that MogDB is compatible with Oracle.
  • B indicates that MogDB is compatible with MySQL.
  • C indicates that MogDB is compatible with Teradata.
  • PG indicates that MogDB is compatible with PostgreSQL.

However, C is not supported currently. Therefore, the values A, B, and PG are commonly used.

Note:

  • For the compatibility type of A, the database considers an empty string as NULL and replace the data type DATE with TIMESTAMP(0) WITHOUT TIME ZONE.
  • For the compatibility type of B, when a string is converted to an integer type, if an illegal value is entered, it will be converted to 0. However, for other compatibility types, an error will be reported.
  • For the compatibility type of PG, CHAR and VARCHAR use character as the string unit. For other compatibility types, byte is used as the string unit. For example, as for UTF-8 character set, CHAR(3) can hold 3 Chinese characters in the compatibility type of PG but only one Chinese character in the other compatibility types.

COLLATE

MySQL's COLLATE is divided into the following levels

  • Instance level
  • database level
  • table level
  • column level
  • Connection level

MogDB's COLLATE in B mode dolphin is the following level

  • Instance level
  • database level
  • Schema
  • table level
  • column level

database

MTK migrates the MySQL database to MogDB/openGauss schema. Be sure to pay attention to the character set and COLLATE.

MySQL view COLLATE

select schema_name,default_character_set_name,DEFAULT_COLLATION_NAME from information_schema.schemata;

MTK does not migrate the COLLATE attribute for the time being. Manual intervention is required to confirm and create the schema. Whether migration will be considered in later versions is to be determined.

Table

  • rewrite the auto-increment column as the default sequence
  • CHARSET property remove
  • ENGINE property remove

Partition Table

  • LINEAR Hash partition convert Hash partition

  • LINEAR Key/Key partition convert Hash partition

  • range partition

    • days function not support

    • to_days

      MySQL

      CREATE TABLE mtk.range_part_t1 (
          prof_history_id BIGINT(20),
          person_id BIGINT(20) NOT NULL,
          organization_id BIGINT(20) NOT NULL,
          record_date DATETIME NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (to_days(record_date))
      (
          PARTITION yr0 VALUES LESS THAN(736695),
          PARTITION yr7 VALUES LESS THAN(737060),
          PARTITION yr8 VALUES LESS THAN(737425),
          PARTITION yr9 VALUES LESS THAN(MAXVALUE)
      )

      openGauss

      CREATE TABLE mtk.range_part_t2 (
          prof_history_id BIGINT,
          person_id BIGINT NOT NULL,
          organization_id BIGINT NOT NULL,
          record_date TIMESTAMP NOT NULL
      ) PARTITION BY RANGE (record_date)
      (
          PARTITION yr0 VALUES LESS THAN('2017-01-01'),
          PARTITION yr7 VALUES LESS THAN('2018-01-01'),
          PARTITION yr8 VALUES LESS THAN('2019-01-01'),
          PARTITION yr9 VALUES LESS THAN(MAXVALUE)
      )
    • year

      MySQL

      CREATE TABLE mtk.range_part_t2 (
          prof_history_id BIGINT(20),
          person_id BIGINT(20) NOT NULL,
          organization_id BIGINT(20) NOT NULL,
          record_date DATETIME NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (year(record_date))
      (
          PARTITION yr0 VALUES LESS THAN(2018),
          PARTITION yr7 VALUES LESS THAN(2019),
          PARTITION yr8 VALUES LESS THAN(2020),
          PARTITION yr9 VALUES LESS THAN(MAXVALUE)
      )

      openGauss

      CREATE TABLE mtk.range_part_t1 (
          prof_history_id BIGINT,
          person_id BIGINT NOT NULL,
          organization_id BIGINT NOT NULL,
          record_date TIMESTAMP NOT NULL
      ) PARTITION BY RANGE (record_date)
      (
          PARTITION yr0 VALUES LESS THAN('2018-01-01'),
          PARTITION yr7 VALUES LESS THAN('2019-01-01'),
          PARTITION yr8 VALUES LESS THAN('2020-01-01'),
          PARTITION yr9 VALUES LESS THAN(MAXVALUE)
      )
    • unix_timestamp

      MySQL

      CREATE TABLE mtk1.range_part_t3 (
          report_id INT(11) NOT NULL,
          report_status VARCHAR(20) NOT NULL,
          report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 PARTITION BY RANGE (unix_timestamp(report_updated))
      (
          PARTITION p0 VALUES LESS THAN(1293811200),
          PARTITION p1 VALUES LESS THAN(1296489600),
          PARTITION p2 VALUES LESS THAN(1298908800),
          PARTITION p3 VALUES LESS THAN(1301587200),
          PARTITION p4 VALUES LESS THAN(1304179200),
          PARTITION p5 VALUES LESS THAN(1306857600),
          PARTITION p6 VALUES LESS THAN(1309449600),
          PARTITION p7 VALUES LESS THAN(1312128000),
          PARTITION p8 VALUES LESS THAN(1314806400),
          PARTITION p9 VALUES LESS THAN(MAXVALUE)
      )

      openGauss

      CREATE TABLE mtk1.range_part_t3 (
          report_id INTEGER NOT NULL,
          report_status VARCHAR(60) NOT NULL,
          report_updated TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP
      ) PARTITION BY RANGE (report_updated)
      (
          -- 1293811200 ---> '2011-01-01 00:00:00'
          PARTITION p0 VALUES LESS THAN('2011-01-01 00:00:00'),
          -- 1296489600 ---> '2011-02-01 00:00:00'
          PARTITION p1 VALUES LESS THAN('2011-02-01 00:00:00'),
          -- 1298908800 ---> '2011-03-01 00:00:00'
          PARTITION p2 VALUES LESS THAN('2011-03-01 00:00:00'),
          -- 1301587200 ---> '2011-04-01 00:00:00'
          PARTITION p3 VALUES LESS THAN('2011-04-01 00:00:00'),
          -- 1304179200 ---> '2011-05-01 00:00:00'
          PARTITION p4 VALUES LESS THAN('2011-05-01 00:00:00'),
          -- 1306857600 ---> '2011-06-01 00:00:00'
          PARTITION p5 VALUES LESS THAN('2011-06-01 00:00:00'),
          -- 1309449600 ---> '2011-07-01 00:00:00'
          PARTITION p6 VALUES LESS THAN('2011-07-01 00:00:00'),
          -- 1312128000 ---> '2011-08-01 00:00:00'
          PARTITION p7 VALUES LESS THAN('2011-08-01 00:00:00'),
          -- 1314806400 ---> '2011-09-01 00:00:00'
          PARTITION p8 VALUES LESS THAN('2011-09-01 00:00:00'),
          PARTITION p9 VALUES LESS THAN(MAXVALUE)
      )

Column

Column Type Map Between MySQL and openGauss/MogDB

MySQL COLUMN_TYPE openGauss data_type data_length data_precision data_scale openGauss ex_data_type Comment
bit(1) bool bool v2.9.6
bit(1) bit(1) bit(1) v2.9.5
bit(2+) bit(2+) 2+ 0 bit(2+)
bigint(20) bigint 64 0 bigint
char(1) character 1 character(1)
varchar(20) character varying 20 character varying(20)
varchar(4000) character varying 4000 character varying(4000)
time time time
date date date
year int int
datetime timestamp without time zone timestamp(0) without time zone
timestamp(6) timestamp with time zone timestamp with time zone
decimal(10,0) numeric 10 0 numeric(10,0)
decimal(38,0) numeric 38 0 numeric(38,0)
decimal(65,30) numeric 65 30 numeric(65,30)
double DOUBLE PRECISION DOUBLE PRECISION
double(10) numeric 10 5 numeric(10)
double(10,5) numeric 10 5 numeric(10,5)
blob bytea/blob bytea/blob
tinyblob bytea/blob bytea/blob
mediumblob bytea/blob bytea/blob
longblob bytea/blob bytea/blob
tinytext text text
text text text
mediumtext text text
longtext text text
binary(10) bytea 10 bytea
varbinary(10) bytea 40 bytea
enum enum type enum type
set varchar varchar
Geometry varchar/Geometry varchar/Geometry
GeometryCollection varchar/Geometry varchar/Geometry
GemoCollection varchar/Geometry varchar/Geometry
LineString varchar/Geometry varchar/Geometry
MultiPoint varchar/Geometry varchar/Geometry
MultiPolygon varchar/Geometry varchar/Geometry
Point varchar/Geometry varchar/Geometry
Polygon varchar/Geometry varchar/Geometry
MultilineString varchar/Geometry varchar/Geometry
  • For MySql bigint unsigned auto increment columns migrated to openGauss, pay attention to the size of the sequence.
  • The field length will be expanded according to the compatibility mode migrated to openGauss
  • Timestamp column Default value 0000-00-00 00:00:00 rewrite 1970-01-01
Spatial Data Type

MTK 2.5.3 Support. Migrate to geomotry if Postgis is installed, varchar otherwise

The following types are supported + Geometry + GeometryCollection + LineString + MultiPoint + MultiPolygon + Point + Polygon + MultilineString

Set

MySQL Set type can insert multiple values, temporarily migrated to openGauss varchar type

Table Data

Constraint

  • The name is automatically changed in openGauss.

  • not supported Foreign keys point to non-unique indexes of the parent table

    CREATE TABLE `t1` (
    `id` int(11) NOT NULL,
    `uuid` binary(16) NOT NULL COMMENT 'UUID',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `uuid` (`uuid`) USING BTREE
    );
    CREATE TABLE `t2` (
    `c1_uuid` binary(16) NOT NUL,
    `c2_uuid` binary(16) NOT NULL,
    PRIMARY KEY (`c1_uuid`,`c2_uuid`),
    CONSTRAINT `FK_T1` FOREIGN KEY (`c2_uuid`) REFERENCES `t1` (`uuid`) ON DELETE RESTRICT ON UPDATE RESTRICT
    );
    --  FK_T1 constraints are not migrated. The dependency it points to is a normal index

Index

  • The name is automatically changed in openGauss.
  • Index partitions are not supported currently.

Syntax

  • year(col3)
  • to_days(t)
Copyright © 2011-2024 www.enmotech.com All rights reserved.