MySQL-to-Oracle Program Migration Considerations (reprint) _php

MySQL-to-Oracle program migration Considerations

2001-09


There are many applications, just start with MySQL database can basically achieve a variety of functional requirements, with the increase in application users,
As the amount of data increases, MySQL gradually becomes overwhelmed: the connection is slow or even down, so the data is moved from MySQL to
Oracle's requirements, the application should make some changes accordingly. I summed up the following points to note, I hope to help you.

1. Automatic growth of data type processing
MySQL has an auto-growing data type that does not manipulate this field when inserting records and automatically obtains data values.
Oracle does not have an auto-growing data type, it needs to create an auto-growing serial number, insert a record to put the serial number of the next
Value is assigned 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;
Where the maximum value is determined by the length of the field, if the defined auto-grow serial number is no. (6), the maximum value is 999999
Insert statement inserts this field value as: The name of the serial number. Nextval

2. Handling of single quotes
In MySQL, you can use double quotes to wrap strings, and Oracle can only wrap strings with single quotes. When inserting and modifying strings
Must be replaced by single quotes: Replace all occurrences of a single quotation mark with two single quotes.

3. Handling of page-turn SQL statements
MySQL processing page of the SQL statement is relatively simple, with limit start position, record number; PHP can also use seek to locate the results
The location of the set.
Oracle's SQL statements that handle paging are cumbersome. Each result set has only one rownum field indicating its location, and only
Use rownum<100, can not use rownum>80.
The following are two of the better-parsed Oracle page-flipping SQL statements (the field name with the unique keyword ID):
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 Numrow >
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 > B and Numrow < 3) Order by conditions;

4. Processing of long strings
The processing of long strings Oracle also has its special place. The maximum operand length at insert and update is less than or equal to
4,000 single byte, if you want to insert a longer string, consider the field with the Clob type, method borrowed from the Oracle Dbms_lob program
Package. Be sure to do non-null and length judgments before inserting a modified record, and you should warn about field values that cannot be empty and values beyond the length field.
Returns the last action.

5. Processing of date fields
The MySQL date field is dated and time two, the Oracle Date field is only date, contains the date of the month and the minute, and the current database
System time is sysdate, accurate to seconds, or converted to a date-type function to_date (' 2001-08-01 ', ' yyyy-mm-dd ')
Year-month-day 24 hours: minutes: The format of the second YYYY-MM-DD HH24:MI:SS to_date () There are a number of date formats that you can see
ORACLE DOC.
Date field converted to String function to_char (' 2001-08-01 ', ' yyyy-mm-dd HH24:MI:SS ')

The mathematical formula for a date field differs greatly.
MySQL found 7 days from current time
Date_field_name > Subdate (Now (), INTERVAL 7 day)
Oracle found 7 days from current time
Date_field_name >SYSDATE-7;

6. Handling of NULL characters
MySQL's non-empty fields also have empty content, and Oracle defines non-empty fields that are not allowed to have empty content.
The Oracle table structure is defined by not NULL for MySQL, and errors are generated when the data is being directed. Therefore, the null character is to be judged when the data is directed
broken, if NULL or null character, you need to change it to a space string.

7. Fuzzy Comparison of strings
MySQL with field name like '% string% '
Oracle can also use the field name like '% string% ' but this method cannot use the index, the speed is not fast
Use string comparison function InStr (field name, ' string ') >0 to get more accurate results

8. In programs and functions, after the work of the database is completed, be aware of the result set and the release of the pointer.


Interested to see MySQL Administrator's Guide

