Tutorial on Migrating Website Data from ORACLE to MYSQL _Shang ping China focuses on high quality website construction

Website production (www.sino web. net). In order to reduce the operation cost of the website, we should consider changing the database from the original enterprise database ORACLE to a smaller MYSQL. An important process in the database change is the data migration of the website. In the process of data migration, I encountered some problems and took a long time to solve them. Today, I will analyze and summarize the problems that need attention in the background database construction of the website.

1. Different databases have different definitions of individual data types

In the process of testing database changes, we found that there was a problem with the publication time displayed on the previous article details page of the website, which was in an abnormal format of year month day hour minute.

After checking the file code and database records retrieved from the page, it is found that there is only one definition type of time 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 the year, month and day, not the 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 import this type of data again.

Summary: Different databases have different definitions of specific types. Therefore, after completing the structural components of the new database table, it is better to migrate a small number of data record bars for data testing, so that the problem can be found in time, the data type can be modified, and then all data can be migrated, so as to avoid repeated data import and increased workload.

2. Different databases have different case sensitivity

During the database migration test, it is found that the articles added through the website background management system are abnormal, and error messages will appear. It was found that the record bar corresponding to the new article could not be inserted normally by calling the record of the article SiteArticle table in the database.

Through code test and inspection, it is found that the inconsistency between the case and case of the table name of the web page file code and the database operation is the cause. ORACLE database has a good tolerance and supports the insertion of new records of the web page file about the insert into Sitearticle () code to the SiteArticle table; The MYSQL database is case sensitive and only supports the insert into SiteArticle() code to insert new records into the SiteArticle table.

To solve this problem, you can only view the source code of the web page file carefully, change the case of the characters of the database name, table name, field name, etc. of all statements operating on the database, and keep the case consistent with the case of the name created in the database.

Summary: From the perspective of compatibility, the case problem in the code related to database operation should be standardized during website construction. In the process of replacing or upgrading the database in the future, case sensitivity should not be concerned, and the workload of changing code should be reduced.

3. Different databases have different requirements for reserved words

In the process of database migration, it was found that after unifying the case, the record insertion of the keyword tag still failed when the article was submitted, and the error message was prompted as a syntax error.

After investigation, it was found that reserved characters were playing tricks. In ORACLE database, the attitude towards reserved words is relatively tolerant, and reserved words can be used for table names and field names; The MYSQL database stipulates that reserved words cannot be used as table names and field names. One of the fields in the Sitearticle table of the website database is called show, which is a reserved word. Therefore, it is also established in the MYSQL database, and a syntax error is inserted in the new record.

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

Summary: Different databases have different requirements for reserved words, so no matter whether the current database type version supports it or not, reserved words should not be used as field names and table names as far as possible during the construction of the website database to ensure normal operation after the upgrade and replacement of later database versions.
This article was published by Beijing website production company Shangpin China //ihucc.com/

Tutorial of website data migration from ORACLE to MYSQL

Date：2013-05-24 Source: Shangpin China Type: website encyclopedia
Word Size： small medium big

+86 10-60259772