Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what problems need to be paid attention to when transplanting Oracle to MySQL". In daily operation, I believe that many people have doubts about what problems they need to pay attention to when transplanting Oracle to MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "what problems need to be paid attention to when transplanting Oracle to MySQL". Next, please follow the editor to study!

The database used by customers is MySQL (the best combination with PHP), and the developed products support Oracle (large website database platform). In order to make customers pay, we have to change the database environment from Oracle (large website database platform) to MySQL database.

What should be paid attention to when porting Oracle to MySQL?

First, the problems encountered in the database environment from Oracle (database platform for large websites) to MySQL (the best combination with PHP).

Because the logic remains the same, the principle is not to change the application code, but only to change the database table creation / initialization sql. Here are the problems we have encountered and the solutions.

1. Case-sensitive difference (if the server OS is linux).

In Oracle (large website database platform), it is generally not case-sensitive. Sometimes we do not pay attention to case when using Oracle (database platform for large websites). Table names and field names are case-insensitive without double quotation marks, like this: insertintotableName and insertintoTABLENAME effect is the same, export the creation / data initialization script with the tool, and the resulting table and field names are converted to uppercase.

However, in MySQL (the best combination with PHP), the case sensitivity of the operating system used determines the case sensitivity of database and table names. 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 (or multiple, depending on the storage engine). Therefore, using a database or table actually manipulates these files (folders), so the case sensitivity of using the operating system determines the case sensitivity of database and table names. It is case-sensitive in operating systems with linux as the kernel.

The solution is to match the case of the MySQL database name with that of the Oracle (large website database platform), and the table name with the table name in the sql string in the application. If the field name in the application uses double quotation marks, please match the case of the field name in sql with the characters in the double quotation marks. If the table names and fields referenced by your application are not uniform in case, you will be in big trouble.

2. The difference between reserved words.

Function names such as sql (e.g. inteval,show) are reserved words. Reserved words in Oracle (large website database platform) can be used as table names and field names, and do not affect their use, but reserved words in MySQL (the best combination with PHP) cannot be used as table names and field names, and syntax errors will be reported if used.

The solution is to enclose the reserved word in the sql statement with the symbol'', which is above the tab key on the keyboard; if it is the field name, there is another method, tablename. Field name. Like this: insertintotablename (id, `interval`) value (… .. Or insertintotablename (id,tablename.inteval) value (… .. .

3. The difference of data type.

In MySQL (the best combination with PHP), there is no corresponding varchar and numeric like varchar2 and number,MySQL (the best combination with PHP) in Oracle (large website database platform). Of course, there is no time type of MySQL (the best combination with PHP in Oracle).

The solution is to replace.

4. the difference between the types of automatic growth.

Oracle (database platform for large websites) is not found in sequence,MySQL (the best combination with PHP), but it has the auto_increment attribute.

The solution is to convert sequence in Oracle (database platform for large websites) to use the auto_increment attribute, and in some cases there may be a way to solve the problem by creating a new separate table to record auto-growing data.

5. The difference of index length limit.

Starting with MySQL (the best combination with PHP) 4.1.2, the index length of MyISAM and InnoDB tables supports 1000 bytes, which means that the length of the index field cannot exceed 1000 bytes. An error like this will be reported if it exceeds: ERROR1071 (42000): Specifiedkeywastoolong;maxkeylengthis1000bytes. In the case of UTF-8 encoding, it is equivalent to 333 characters (because one character takes up 3 bytes in UTF8). The index length limit of Oracle (large website database platform) is much looser than that of MySQL database.

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.

What should we pay attention to for the compatibility of the database?

Database compatibility should be an issue that database designers should pay attention to, because sometimes customers have databases that are already in use and do not want to maintain two 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 certain database-specific usage.

Such as the use of the''symbol of msyql, for example, many people have this usage. When developing with Oracle (database platform for large websites), create sequence, SELECTseq.nextvalFROMDUAL;, before inserting data into the table, and then insert the value of the query into the table as value. This usage is not suitable for databases without sequence. Every database has auto-growth usage, which should be fully used if you need to use it.

For example, paging queries are extended in different databases, but postgresql does not have offset,limit,Oracle (database platform for large websites).

2. Avoid the problem of database case sensitivity.

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

3. Keep the word.

Database designers are required not to use reserved words as table and field names as far as possible. There are also a lot of people who use this by adding'_ 'before table and field names, like this: createtable_tablename (_ idinteger). In this way, there will never be problems caused by reserved words.

At this point, the study on "what problems should be paid attention to when transplanting Oracle to MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.