Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "MySQL how to migrate KingbaseESV8R2", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how MySQL migrates KingbaseESV8R2" article.

1. Use Oracle to transfer

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

Migration to Oracle questions:

1.oracle has a maximum limit of 30 bits for table names

2.invalid hex number, invalid hexadecimal

3. Table data is empty, report to cannot insert NULL into

4. The index name is too long

There are a lot of problems with MySQL migrating Oracle, and the adjustment is large and troublesome; when Oracle migrates Kingbase, sometimes the table migration is not successful, but it can succeed again after many attempts.

two。 Use KingbaseESV8R3 to transfer

After contacting the gold warehouse technician, I learned that there is a new version of V8R3, but it cannot be used in a production environment, it can be used for testing, and it supports MySQL to migrate directly to V8R3.

So reinstall a virtual machine (R2 and R3 cannot coexist), and then use the R3 migration tool to migrate. Remember to select all the source database settings in the migration so that views, indexes, foreign keys, default values, etc., will be migrated.

Migration to V8R3 questions:

1.tinyint (1) is mapped to boolean after migration, even though it is already mapped in the migration tool.

Therefore, you need to change the field type of the table after migration, and change the default value if you have a default value.

two。 Although comments are selected for migration, comments for fields are not visible using the default object manager. Mouse over the table name to view the comments of the table. There is no way to view it at this time.

After correcting the tinyint (1) problem, you can select the database in the object manager of R3 for logical backup.

Then use the object manager of R2 to restore logically.

3. Other questions 1. Self-increasing

MySQL auto-increment is different from Kingbase auto-increment; Kingbase auto-increment is the same as Oracle, which is realized by defining a sequence sequence.

Currently, if you use kingbaseV8R3 as a transit, mysql migrates to R3, which will help us create a sequence and fill in the default values of the fields to achieve self-increment.

Here is how to use self-increment manually:

First create a SEQUENCE, named test_id_SEQ, where the starting value is set to 101,

CREATE SEQUENCE test_id_SEQ START 101

Then you need to add the default value of the self-added field.

NEXTVAL ('test_id_SEQ'::REGCLASS)

After that, using the insert statement, the field id of the test table begins to increment from 101,

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

Before deleting a sequence, you need to delete the default value in the field before you can delete the sequence.

DROP SEQUENCE test_id_SEQ2.uuid

Kingbase does not have uuid function, execute error report

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

Use it for the time being

Select SYS_GUID_NAME () as id from dual

Instead, after all, mysql and kingbase have different uuid generation rules.

4. Discovered SQL issu

1. Cannot use `to distinguish between keywords

two。 Change the function IFNULL to NVL

All the fields need to be displayed in 3.group by.

4.sql cannot appear! =''and will not report an error, but execute the result null. In Kingbase, an empty string is equivalent to null

5. Changing the function IF to NVL2,Kingbase can only judge whether it is null or not.

6. The field type string can save the time and want to format the time. You need to convert the time to a timestamp and then format it into a string

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

7. You can't use double quotation marks. Replace them with single quotation marks.

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 go back to time before you can 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. Table name and system view may have the same name, and the schema name should be added. Table name distinction

11.is_delete = false is changed to is_delete = 0, and a field value of 0 cannot be filtered using false

The above is about the content of this article on "how MySQL migrates KingbaseESV8R2". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more related knowledge, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.