How to migrate MySQL to KingbaseESV8R2

1. Use Oracle transfer

KingbaseESV8R2 only supports migration from Oracle, so migrate MySQL to Oracle first.

Problems in migrating to Oracle:
1.Oracle has a maximum 30-digit limit on table names
2.invalid hex number, invalid hexadecimal
3.Table The data is empty and it reports "cannot insert NULL into
4. The index name is too long

There are many problems when migrating MySQL to Oracle, and the adjustment is very troublesome; when Oracle migrates to Kingbase, sometimes the table migration will not work. Successful, but can be successful again after multiple attempts.

2. Use KingbaseESV8R3 transfer

After contacting Jincang technical staff, I learned that there is a new version V8R3, but it cannot be used in the production environment, it can be used for testing, and supports MySQL direct migration to V8R3.

Therefore, you need to reinstall a virtual machine. During the installation process, you cannot install R2 and R3 at the same time, and use the R3 migration tool for data migration. Please select Select All when migrating the source database to migrate settings such as views, indexes, foreign keys, and default values.

Problems in migrating to V8R3:
1.tinyint(1) is mapped to boolean after migration, even if it has been mapped in the migration tool, but still the same.
So after migration, you need to change the table field type. If there is a default value, you must also change the default value.

Although the annotation has been selected during the migration process, the default value is used. Object Manager still cannot display comments for fields. Hover the mouse on the table name to view the comments on the table. No way to view it yet.

After modifying the tinyint(1) problem, you can select the database in the R3 object manager for logical backup.
Then use R2’s object manager to perform logical restoration.

3. Other questions

1.Auto-increment

MySQL auto-increment is different from Kingbase auto-increment; Kingbase auto-increment is the same as Oracle, both are implemented by defining a sequence. .

If we currently use kingbaseV8R3 for transfer, migrating mysql to r3 will help us create the sequence and fill in the default values ​​of the fields to achieve auto-increment.

The following is the manual way to use auto-increment:

Create a SEQUENCE first, name it test_id_SEQ, set the starting value here to 101

CREATE SEQUENCE test_id_SEQ START 101;

and then add fields that require auto-increment After adding

NEXTVAL('test_id_SEQ'::REGCLASS)

to the default value and using the insert statement, the field ID of the test table will start to increase from 101

INSERT INTO test (name) values('1');

Before deleting the sequence, you need to delete the default value in the field. Delete the sequence

DROP SEQUENCE test_id_SEQ

2.uuid

kingbase does not have a uuid function, and the execution error

select replace(uuid(), '-', '') as id from dual

is replaced by

select SYS_GUID_NAME() as id from dual;

for the time being. After all, the uuid of mysql and kingbase Different generation rules

4. Discovered SQL problems

1.Cannot use `distinguishing keyword

2.Function IFNULL changes to NVL

3. All fields need to be displayed in group by

4.sql cannot appear!='', no error will be reported, but the execution result is null. In Kingbase, an empty string is equivalent to null

5. The function IF is changed to NVL2. Kingbase can only judge whether it is null

6. The field type is string but the time is stored, and I want to format the time. You need to convert the time to a timestamp first, and then format it into a string

SELECT to_char(to_timestamp('2020-02-20 15:35:44', 'YYYY-MM-DD HH24:MI:SS'),'MM-DD')

7. You cannot use double quotes "", use single quotes '' instead of

8. Cannot use count('')

9. Time formatting, addition and subtraction

limit_time = limit_time (now() - apply_time)

MySQL:

limit_time = date_add( limit_time, INTERVAL ( SELECT TimeStampDiff( DAY, now(), apply_time ) ) + 1 DAY )

Kingbase: The trouble lies in the conversion of time and string. First format the timestamp into a string, and then convert the time back to add or subtract

limit_time = limit_time + (to_date(to_char(now(),'YYYY-MM-DD'),'YYYY-MM-DD') - to_date(to_char(apply_time,'YYYY-MM-DD'),'YYYY-MM-DD') + integer '1')

10. The table name and the system view may have the same name, so add schema name. Table name to distinguish

11. Change is_delete = false to is_delete = 0. If the field value is 0, false cannot be used. filter

The above is the detailed content of How to migrate MySQL to KingbaseESV8R2. For more information, please follow other related articles on the PHP Chinese website!