Compatibility migration of website data from ORACLE to MYSQL

The overall revision and upgrade of the website, because you want to reduce the website operation cost and consider changing the database, from the original enterprise-level database ORACLE to a relatively small MYSQL, an important process in database changes is website data migration. Some problems encountered during the data migration process and took a long time to solve them. Today, we will analyze and summarize the problems that need to be paid attention to during the construction of the website's background database.

The overall revision and upgrade of the website, because you want to reduce the website operation cost and consider changing the database, from the original enterprise-level database ORACLE to a relatively small MYSQL, an important process in database changes is website data migration. Some problems encountered during the data migration process and took a long time to solve them. Today, we will analyze and summarize the problems that need to be paid attention to during the construction of the website's background database.

1. different databases are case sensitive

During the database migration test, an error is reported when an exception occurs in the document added by the website background management system. We found that the record corresponding to the new article could not be inserted normally by retrieving the record of the database article SiteArticle table.

The code test shows that the code in the webpage file is caused by inconsistent case sensitivity of the table name for database operations, and the ORACLE database has a good volume, supports insert into Sitearticle () code in webpage files to insert new records to SiteArticle tables. MYSQL databases are case sensitive and only support insert into SiteArticle () the code inserts a new record into the SiteArticle table.

To solve this problem, you can only carefully check the source code of the webpage file and change the case sensitivity of the database name, table name, and field name of all statements for database operations, make sure that the name is in the same case as the name created in the database.

Summary: From the perspective of compatibility, the case sensitivity issue in the code related to database operations should be standardized During website construction. in the future, you do not have to worry about case sensitivity during Database replacement or database upgrade, it also reduces the workload of changing code.

2. the definitions of individual data types in different databases are different.

During the testing of database changes, we found that there was a problem in the posting time on the previous article details page of the website. The format of this document is incorrect: Year-Month-Day: Minute.

After checking the file code and database records on the page, it is found that only one Date type is defined in ORACLE, and the format of this definition type includes year, month, day, and time; in MYSQL, the definition types of Time include Date, Time, and Datetime. in MYSQL, the definition of Date type only includes year, month, and day, but not Time.

To solve this problem, you can only change the field type created in the MYSQL database table from the original Date to Datetime, and then re-import the data.

Summary: different databases have different definitions for specific types. Therefore, after completing the structure component of the new database table, it is best to perform a migration of a small number of data records for data testing, you can promptly locate the problem and modify the data type before migrating all the data to avoid repeated data import and increase the workload.

3. different databases have different requirements for reserved words.

During database migration, after the case sensitivity problem is fixed, the keyword tag record insertion still fails when the document is submitted. the error message is a syntax error.

After investigation, it is found that the reserved words are playing tricks. In ORACLE databases, reserved words are more tolerant. you can use reserved words for both table names and field names. in MYSQL databases, reserved words cannot be used as table names and field names. One of the fields in the Sitearticle table of the website database is show, which is a reserved word. Therefore, after a new record is inserted in the MYSQL database, a syntax error is returned.

To solve this problem, you need to use the ''symbol (that is, the symbol entered by the key before 1) for the show field name of the Sitearticle table in the code statement in the webpage code.

Summary: different databases have different requirements for reserved words. Therefore, during Website database construction, no matter whether the current database type version is supported or not, do not use reserved words as field names or table names, to ensure that the database version can run normally after upgrade and replacement.

To sum up, the above problems encountered in the process of changing the website database and the technical methods to solve, share hope to help everyone, this article by the sunshine diet network http://www.jianfei1234.com original, please keep the author's link when reprinting. thank you.

Note:For more information about website construction skills, go to the website creation tutorial channel.