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

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

I would like to summarize the following points for attention, hoping to be helpful to you.

1. Auto-growing data types processing MySQL has auto-growing data types. You don't have to manipulate this field when you insert a record, and you will automatically get the data value. Oracle does not have an auto-growing data type, so you need to create an auto-growing serial number, and when you insert a record, you assign the next value of the serial number to this field.

The name of the CREATE SEQUENCE serial number (preferably the table name serial number tag) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE; where the maximum value is determined by the length of the field, if the auto-growing serial number NUMBER (6) is defined, the maximum value is 999999 INSERT statement insert this field value is: the name of the serial number. NEXTVAL

two。 Dealing with single quotation marks you can wrap strings in double quotes in MySQL and only in single quotation marks in Oracle. Single quotation marks must be replaced before inserting and modifying strings: replace all occurrences of one single quote with two single quotation marks.

3. The processing of page turning SQL statement MySQL handles page turning SQL statement is relatively simple, using the LIMIT start position, record the number; PHP can also use SEEK to locate the result set. It is more tedious for Oracle to deal with SQL statements that turn pages. Each result set has only one ROWNUM field to indicate its location, and can only use ROWNUM<100, not ROWNUM > 80. Here are two better Oracle paging SQL statements after analysis (ID is the field name of the only keyword):

Statement 1:

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE

ID IN (SELECT ID FROM (SELECT ROWNUM AS NUMROW)

ID FROM TABLE_NAME WHERE condition 1 ORDER BY condition 2)

WHERE NUMROW > 80 AND NUMROW & lt; 100) ORDER BY condition 3

Statement 2:

SELECT * FROM ((SELECT ROWNUM AS NUMROW

C. * from (select [FIELD_NAME,...]

FROM TABLE_NAME WHERE condition 1 ORDER BY condition 2) c)

WHERE NUMROW > 80 AND NUMROW & lt; 100) ORDER BY condition 3

4. Dealing with long strings Oracle also has its special features. The maximum operable string length for INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to insert a longer string, consider using the CLOB type for the field, which is borrowed from the DBMS_LOB package included in Oracle. Be sure to make a non-empty and length judgment before inserting the modification record. Field values that cannot be empty and values that exceed the length field should be warned to return to the last operation.

5. There are two types of MySQL date fields: DATE and TIME. There are only DATE in Oracle date field, which contains the information of year, month, day, hour, minute and second. The system time of the current database is SYSDATE, accurate to seconds. Or use the string to convert the date function TO_DATE ('2001-08-01) year-month-day 24 hours: minute: second format YYYY-MM-DD HH24:MI:SS TO_DATE () there are many date formats, see Oracle DOC. The mathematical operation formula of date field is very different when it is converted into string function TO_CHAR ('2001-08-01). The mathematical formula of date field is very different. MySQL finds 7 days from current time with DATE_FIELD_NAME > SUBDATE ((NOW (), INTERVAL 7 DAY) Oracle finds 7 days from current time with DATE_FIELD_NAME > SYSDATE-7

6. Dealing with null characters the non-empty field of MySQL also has empty content. If non-empty field is defined in Oracle, empty content is not allowed. Define the structure of the Oracle table according to the NOT NULL of MySQL, and errors will occur when importing data. Therefore, it is necessary to judge the empty character when importing the data, and if it is a NULL or empty character, you need to change it to a space string.

7. Fuzzy comparison of strings using field names in MySQL, field names like can also be used in like Oracle, but this method cannot use indexes. If you use the string comparison function instr (field name, string) > 0, you will get more accurate search results 8. In programs and functions, please pay attention to the release of result sets and pointers after the work of manipulating the database is completed.

At this point, the study on "what to pay attention to when moving data from MySQL to Oracle" 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.