Precautions for migrating data from Oracle to MySQL _ MySQL

Notes for migrating Oracle to MySQL Alex Kochis, senior product manager of Microsoft "Windows Genuine value-added Program", wrote in his blog that this was caused by human error. Alex said, the new software was accidentally loaded to the server running WGA, causing the server to fail to run properly. Therefore, the reasonable application of genuine software users cannot be handled correctly.

The database used by the customer is mysql, and the developed product supports oracle. to make the customer pay for it, we must switch the database environment from oracle to mysql. We encountered the following problems during the conversion process, hoping to give some reference to colleagues who encountered the same problem. If we pay attention to database portability during the initial design and coding process, no additional work is required.

I. problems encountered when the database environment switched from oracle to mysql.

Because the logic remains unchanged, the principle is not to change the application code, but to change the database table creation/initialization SQL. The following are our problems and solutions.

1. Differences in case sensitivity (if the server OS is linux ).

In oracle, case insensitive. Sometimes oracle is not case-sensitive. table names and field names are case-insensitive without double quotation marks. for example, insert into tableName and insert into TABLENAME are the same, use a tool to export the creation/data initialization script. Generally, the table name and field name are converted to uppercase.

However, in MySQL, the case sensitivity of the operating system determines the case sensitivity of the database name and table name. The database corresponds to the directory in the data directory. each table in the database corresponds to at least one file in the Database Directory (or multiple, depending on the storage engine ). Therefore, using databases or tables is actually manipulating these files (folders). The case sensitivity of operating systems determines the case sensitivity of database names and table names. Linux is case sensitive.

The solution is to keep the mysql database name and oracle in the same case. the table name must be consistent with the table name in the SQL string in the application. if the field name in the application uses double quotation marks, make sure that the field names in SQL are in the same case as those in double quotation marks. If the table names and fields referenced by your application are not case-insensitive, it will be too much trouble.

2. differences between reserved words.

Function names such as inteval and show in SQL are reserved words. Reserved words in Oracle can be used as table names and field names without affecting usage. however, reserved words in mysql cannot be used as table names and field names. if used, a syntax error is reported.

Solution: Use the '''' symbol for the reserved words in the SQL statement. This symbol is located on 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. differences in data types.

In mysql, there is no varchar2 or number in oracle. mysql has varchar or numeric corresponding to it. of course, there is no mysql time type in oracle.

The solution is to replace.

4. differences between automatic growth types.

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

The solution is to convert the sequence in Oracle to the auto_increment attribute. in some cases, there may be another way to solve the problem. create an independent table to specifically record the automatically growing data.

5. Differences in index length restrictions.

From MySQL 4.1.2, the index length of MyISAM and InnoDB tables can be 1000 bytes, that is, the index field length cannot exceed 1000 bytes. if the index length exceeds 1071 bytes, the following ERROR is reported: ERROR 42000 ): specified key was too long; max key length is 1000 bytes. For UTF-8 encoding, it is equivalent to the length of 333 characters (because UTF8 occupies 3 bytes ). The index length limit of Oracle is much looser than that of mysql.

The solution does not need to be discussed. either the index definition or the field definition length should be changed.

2. what should we pay attention to for database compatibility?

Database compatibility should be a matter of importance in database design, because sometimes the customer has a database that is already in use and does not want to maintain both databases at the same time, in this way, compatibility with multiple databases can also become a selling point of the product.

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

1. follow the standard usage and try not to use a database-specific usage.

For example, the usage of the msyql ''' symbol,

For example, many people use this method to create sequence during oracle development and SELECT seq before inserting data into the table. nextval from dual;, and then insert the queried value into the table as the value. this method cannot adapt to databases without sequence. each database has an automatic growth usage, if necessary, it should be fully used.

For another example, different databases have extended paging queries. postgresql has offset, limit, and oracle does not.

2. avoid case sensitivity issues.

Select the database table name and field name in upper or lower case, and the database design and encoding process are completely unified.

3. Reserved words.

Database designers are required to avoid using reserved words as table names and field names. Many people also use this method by adding '_' before the table name and field name, like this: create table _ tablename (_ id integer ). This will never cause problems caused by reserved words.