Things to consider when migrating data from MySQL to Oracle

Notes on migrating data from MySQL to Oracle

1. Handling of automatically growing data types

MYSQL has an automatically growing data type. There is no need to operate this field when inserting records, and the data value will be obtained automatically. ORACLE does not have an auto-increasing data type. It is necessary to create an auto-increasing sequence number. When inserting a record, the next value of the sequence number must be assigned to this field.

CREATE SEQUENCE The name of the serial number (preferably the table name + the serial number mark)

INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

INSERT statement inserts this field value as: The name of the serial number.NEXTVAL

2. Handling of single quotes

You can use double quotes to wrap a string in MYSQL, but you can only use single quotes to wrap a string in ORACLE. Before inserting or modifying a string, you must replace single quotes: replace all occurrences of a single quote with two single quotes. Of course, if you use the Convert Mysql to Oracle tool, you don’t need to consider this problem

3. Processing of long strings

In ORACLE, the maximum operable string length during INSERT and UPDATE is less than or equal to 4000 single bytes. If you want To insert a longer string, please consider using the CLOB type for the field and borrow the method from the DBMS_LOB package that comes with ORACLE. Before inserting a modified record, you must make non-empty and length judgments. Field values ​​that cannot be empty and field values ​​that exceed the length should issue a warning and return to the last operation.

4. Processing of page-turning SQL statements

MYSQL processing page-turning SQL statements is relatively simple. Use LIMIT to start the position and record the number. ORACLE's processing of paging SQL statements is more cumbersome. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM<100 can be used, but ROWNUM>80 cannot be used.

The following are two better ORACLE paging SQL statements after analysis (ID is the field name of the unique keyword):

Statement 1:

SELECT ID, FIELD_NAME,... .

FROM TABLE_NAME

WHERE ID IN (SELECT ID

FROM (SELECT ROWNUM AS NUMROW, ID

FROM TABLE_NAME

WHERE condition 1

ORDER BY condition 2)

WHERE NUMROW > 80

AND NUMROW < 100)

ORDER BY Condition 3;

Statement 2:

SELECT *

FROM ((SELECT ROWNUM AS NUMROW, c.*

from (select FIELD_NAME,.. .

FROM TABLE_NAME

WHERE Condition 1

ORDER BY Condition 2 ) c) WHERE NUMROW > 80 AND NUMROW < 100)

ORDER BY condition 3;

5. Processing of date fields

MYSQL date fields are divided into DATE and TIME. ORACLE date fields only have DATE, including year and month. For day, hour, minute and second information, use the system time of the current database as SYSDATE, accurate to the second.

The mathematical formulas for date fields are very different.

MYSQL uses: DATE_FIELD_NAME > SUBDATE(NOW(),INTERVAL 7 DAY)

ORACLE uses: DATE_FIELD_NAME >SYSDATE - 7;

6. String ambiguity Comparison

In MYSQL, use field names like '% string%'. In ORACLE, you can also use field names like '% string%'. However, this method cannot use indexes and is not fast. Use the string comparison function instr(field name ,'String')>0 will get more precise search results.

7. Handling of null characters

The non-empty fields of MYSQL also have empty content. ORACLE defines non-empty fields and does not allow empty content. Define the ORACLE table structure according to MYSQL's NOT NULL, and an error will occur when importing data. Therefore, when importing data, you need to judge the null character. If it is NULL or a null character, you need to change it into a space string.

Notes on porting Oracle to mysql

The database used by the customer is mysql, and the developed product supports oracle, we must port the database environment from oracle to mysql. During the migration process, we encountered the following problems. If we pay attention to the portability of the database during the initial design and coding process, there is no need to do any additional work in this case.

1. Problems encountered when transplanting the database environment from Oracle to MySQL

1. Case-sensitive differences (if the server OS is Linux)

Generally, Oracle is not case-sensitive. Sometimes we don't pay attention to the case problem when using Oracle. Table names and field names are not case-sensitive without double quotes. Like this: insert into tableName and insert into TABLENAME have the same effect. Use tools to export creation/data initialization. Script, the results obtained are generally converted to uppercase table names and field names.

But in MySQL, the case sensitivity of the operating system used determines the case sensitivity of the database name and table name. The database corresponds to a directory in the data directory, and each table in the database corresponds to at least one file in the database directory (maybe multiple, depending on the storage engine). Therefore, using a database or table is actually manipulating these files (folders), so the case sensitivity of the operating system determines the case sensitivity of the database name and table name. It is case-sensitive in operating systems with Linux as the kernel.

The solution is to keep the database name of MySQL consistent with the case of Oracle, and the table name consistent with the table name in the SQL string in the application. If the field name in the application uses double quotes, please put it in the SQL The case of the field name should be consistent with the characters in double quotes. If the table names and fields referenced by your application do not have uniform case, you will be in big trouble.

2. The difference between reserved words

For example, function names in SQL language (such as inteval, show) are reserved words. Reserved words in Oracle can be used as table names and field names, and do not affect their use. However, reserved words in MySQL cannot be used as table names and field names. If used, a syntax error will be reported.

The solution is to quote the reserved words in the SQL statement with the ‘`’ symbol, which is located above the tab key of the keyboard; if it is a field name, there is another method tablename.field name. Like this:

insert into tablename (id, `interval`) value(…..

or

insert into tablename (id, tablename.inteval) value(…..

3. The difference between automatic growth types

Oracle has sequence, but mysql does not, but it has the auto_increment attribute.

The solution is to convert the sequence in Oracle to use the auto_increment attribute. In some cases, there may be a way to solve the problem. Create a separate table for dedicated recording. Automatically growing data.

4. Differences in data types

In mysql there is no varchar2 and number like in oracle. Mysql has corresponding varchar and numeric. Of course, there is no mysql time type in oracle. The solution is to replace.

5. The difference in index length limit

Starting from MySQL 4.1.2, MyISAM and InnoDB table index length supports 1000 bytes, which means that the length of the index field cannot exceed 1000

bytes. If it exceeds, an error like this will be reported:

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

If it is UTF-8 encoding, it is equivalent to a length of 333 characters (because UTF8 takes up one character. 3 bytes). Oracle's index length limit is much looser than that of MySQL.

There is no need to say more about the solution, either change the definition of the index or change the definition length of the field.

Second, for the sake of the database. Compatibility What should we pay attention to? Database compatibility should be an issue that should be paid attention to in database design, because sometimes customers have databases that are already in use and do not want to maintain two databases at the same time. In this case, it is still compatible with multiple databases. It can become a selling point of the product.

The key to achieving database compatibility is to comply with standard usage.

1. Follow standard usage and try not to use certain database-specific usage

such as the usage of msyql's '`' symbol. For another example, many people have this usage. When using Oracle to create a sequence, SELECT seq.nextval FROM DUAL; before inserting data into the table, and then insert the value obtained from the query into the table as value. This usage does not work. It is not suitable for databases without sequence. Each database has automatic growth usage. If you need to use it, you should use it completely.

For another example, different databases have expanded paging queries. Postgresql has offset, limit, Oracle does not.

2. Reserved words

requires database designers to try not to use reserved words for table names and field names. There are also many people who use this method, adding ‘_’ before the table name and field name,

like this: create table _tablename (_id integer). This way you never have problems caused by reserved words.

3. Avoid the problem of database case sensitivity

Choose whether database table names and field names should be in uppercase or lowercase, and be completely unified in the design and coding process of the database.

When using the Convert Oracle to Mysql tool, please pay attention to the control of the "Convert name to uppercase" option