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.
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.
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
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
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!