Issues needing attention in MySQL database conversion to Oracle

There are many applications, just started with the MySQL database can basically achieve a variety of functional requirements, with the increase in the number of users, data, MySQL gradually become overwhelmed by the situation: the connection is slow or even downtime, so there is the need to move data from MySQL to Oracle, The application should also make some changes accordingly.

I have summed up the following points of attention, I hope to help you.

1. Automatic growth of data types processing MySQL has an automatically growing data type, the data value is automatically obtained when inserting records without manipulating this field. Oracle does not have an automatically growing data type, an automatic growth sequence number is required, and the next value of the serial number is assigned to the field when the record is inserted. 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; The largest value is determined by the length of the field, and if the defined automatically-growing serial number (6), the maximum value for the 999999 INSERT statement inserts the field value: The name of the serial number. Nextval

2. Single quote processing mysql can be wrap strings in double quotes, Oracle can only use single quotes wrap strings. A single quotation mark must be replaced before inserting and modifying a string: Replace all occurrences of one single quote with two single quotes.

3. The processing of the page-turning SQL statements mysql processing of the SQL statement page is relatively simple, with limit start position, record number; PHP can also use seek to locate the result set. Oracle handles page-flipping SQL statements is cumbersome. Each result set has only one rownum field that indicates its position, and can only be used rownum<100, not rownum>80. The following are two of the better Oracle page-Flipping SQL statements (field names with IDs as unique keywords):

Statement one:

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 NU Mrow > Numrow <) Order BY condition 3;

Statement two:

SELECT * FROM (select RowNum as Numrow, c.* from (SELECT [Field_name,...] From table_name WHERE Condition 1 order by Condition 2 c) WHERE Numrow > Numrow < (m) Order by condition 3;

4. Long string processing the processing of long strings Oracle also has its special place. The maximum operable string length for insert and update is less than 4,000 single-byte, and if you want to insert a longer string, consider using the CLOB type for the field to borrow the Dbms_lob package from Oracle. Be sure to do non-null and length judgments before inserting a change record, and you should warn about field values that are not empty and values that exceed the length field, and return to the last action.

5. Date field processing the MySQL date field is dated and time two, the Oracle Date field is only date, contains the time and minutes of the year, the current database is sysdate, accurate to the second, or a string converted into a date-type function to_date (' 2001-08-01 ', ' yyyy-mm-dd ') year-month-day 24 hours: minutes: SEC format yyyy-mm-dd HH24:MI:SS to_date () There are many different date formats, see ORACLE DOC. Date field converted to string function to The mathematical formula for the _char (' 2001-08-01 ', ' yyyy-mm-dd HH24:MI:SS ') Date field varies greatly. MySQL finds 7 days away from current time with Date_field_name > Subdate (Now (), INTERVAL 7 day) Oracle finds 7 days with Date_field_name >SYSDATE-7;

6. Null character processing MySQL's Non-empty field also has empty content, Oracle defined a NON-EMPTY field does not allow empty content. The Oracle table structure is defined according to the NOT null of MySQL, and errors are generated when data is directed. Therefore, to guide the data to judge the null character, if null or empty characters, you need to change it to a space string.

7. Fuzzy comparison of Strings MySQL in the field name like '% string% ' Oracle can also be used with the field name "% string%" but this method cannot use the index, the speed is not fast with string comparison function InStr (field name, ' string ') >0 will get more accurate Find Results 8. In programs and functions, be aware of the result set and the release of the pointer when you are done working on the database.