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

This article mainly explains "how to migrate from MySQL to Oracle". The content in the article is simple and clear, easy to learn and understand. Please follow the editor's train of thought to study and learn "how to migrate from MySQL to Oracle".

1. Automatic growth of data type 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.

Name of the CREATE SEQUENCE serial number (preferably table name + serial number tag) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE

The value of this field inserted by the INSERT statement is: the name of the serial number. NEXTVAL

two。 The treatment of single quotation marks

Strings can be wrapped in double quotation marks 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. Of course, if you use Convert Mysql to Oracle tools, you don't have to think about it.

3. Processing of long strings

In ORACLE, 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. The method borrows 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.

4. The processing of the SQL statement of turning pages

It is relatively simple for MYSQL to deal with the SQL statement of page turning, using the LIMIT starting position to record the number. 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 ROWNUM80.

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

< 100 ) ORDER BY 条件3; 语句二： SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE 条件1 ORDER BY 条件2) c) WHERE NUMROW >

80 AND NUMROW

< 100 ) ORDER BY 条件3; 5. 日期字段的处理 MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前的系统时间为SYSDATE, 精确到秒。 日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用 DATE_FIELD_NAME >

SUBDATE (NOW (), INTERVAL 7 DAY) ORACLE found 7 days from the current time with DATE_FIELD_NAME > SYSDATE-7

6. Fuzzy comparison of strings

The field name like'% string%'is used in MYSQL, and the field name like'% string% 'can also be used in ORACLE, but this method cannot use index and is not fast. Using the string comparison function instr (field name,' string') > 0 will get more accurate search results.

7. Processing of null characters

MYSQL's non-empty fields also have empty content, and ORACLE defines non-empty fields that do not allow empty content. 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.

Thank you for your reading, the above is the content of "how to migrate from MySQL to Oracle". After the study of this article, I believe you have a deeper understanding of how to migrate from MySQL to Oracle, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.