Considerations for changing the database environment from Oracle to MySQL

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

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

The customer uses mysql, and the developed products support oracle. In order to make the customer pay for it, we must switch the 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 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.