Oracle porting to MySQL considerations

The customer database is MySQL, and the developed product supports Oracle, and we have to turn the database environment from Oracle to MySQL in order for our customers to pay. We have encountered some of the following problems in the process of conversion, hoping to give the same experience to the colleagues for some reference. If we were to focus on the porting of the database in the initial design and coding process, there would be no need for additional work at all.

One, the database environment from Oracle to MySQL encountered problems.

Because the logic does not change, so the principle is not to change the application code, only the database table creation/initialization of SQL. Here are the problems we encountered and the solutions.

1, Case sensitive difference (if the server OS is Linux).

In Oracle, it is typically case-insensitive. Sometimes we use Oracle's problem of not paying attention to case, the table name and field name without double quotes are case-insensitive, like this: INSERT INTO TableName and insert into TableName effects are the same, using tools to export creation/data initialization scripts, The resulting general table name and field name are converted to uppercase.

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

The solution is to keep the MySQL database name consistent with Oracle's case, the table name is consistent with the table name in the SQL string in the application, and if the field name in the application has double quotes, keep the field name in SQL consistent with the characters in the double quotes. If your application references a table name or field that doesn't have a uniform case, then it's a lot of trouble.

2, the difference between reserved words.

function names like 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 use, but reserved words in MySQL cannot be used as table names and field names, if they are reported with syntax errors.

Workaround, which causes the reserved word in the SQL statement to be used as the "' symbol, which is positioned above the keyboard's tab key, and another way to tablename the field name. Field name. Like this: INSERT INTO tablename (ID, ' interval ') value (... or INSERT INTO TableName (ID, tablename.inteval) value (...).

3, the data type difference.

There is no MySQL-like varchar2, Number,mysql in MySQL, and the corresponding varchar, numeric, of course, there is no time type in Oracle.

The solution is to replace.

4, the automatic growth type difference.

Oracle has Sequence,mysql, but has auto_increment properties.

The solution is to convert sequence in Oracle to use the Auto_increment attribute, and in some cases there may be a way to solve the problem by creating a separate table to specifically record automatically-growing data.

5. The difference of index length limit.

Starting with the MySQL 4.1.2, the MyISAM and InnoDB table index lengths support 1000 bytes, which means the length of the indexed field cannot exceed 1000 bytes, if it exceeds the error that is reported: Error 1071 (42000): Specified Key was too Long Max key length is 1000 bytes. If it is a UTF-8 encoding, the equivalent of 333 characters (because UTF8 is 3 bytes). Oracle's index length limit is much looser than MySQL.

The solution is not to say more, or change the definition of the index, or change the definition of the length of the field.