Mysql to Oracle Program Migration
2001-09 Yu Feng
There are a lot of application projects, just starting, using the MySQL database basically implement various functional needs, with the increase of application users,
The increase in the amount of data, the case of mysql gradually appears to be overwhelmed: the connection is very slow or even downtime, so there will be data from MySQL to
Oracle's needs, applications should also be modified accordingly. I have summed up the following precautions, I hope to help everyone.
1. Automatic growth data type processing
MySQL has automatic data types, and does not operate this field when inserting records, the data value is automatically obtained.
Oracle does not have automatic growth data types, you need to create an automatic sequence number, insert the next one of the serial numbers when inserting records
The value is worthy of this field.
Create sequence serial number name (best is a table name serial number tag) Increment by 1 start with 1
MaxValue 99999 Cycle Nocache;
The largest value is set by the length of the field, and if the defined automatic growth serial number Number (6), the maximum is 99999
Insert statement inserts this field value: the name of the serial number. NexTVal
2. Processing of single quotes
In mysql, you can use a double quotation to clasp strings, and you can only use single quarters in the Oracle. Insert and modify strings
Before you have to do a single quotation number: replace all single quotes that appear into two single quotes.
3. Processing of paged SQL statements
MySQL handle the SQL statement of the page is relatively simple, use the LIMIT start position, record the number; PHP can also be positioned to the result with SEEK
The location of the set.
Oracle handles the SQL statement that flips the page is more cumbersome. Each result set has only one ROWNUM field indicates its location and can only
With ROWNUM <100, ROWNUM> 80 cannot be used.
The following is a better two oracle flipping SQL statement (ID is the field name of the only keyword):
Statement 1:
SELECT ID, [Field_Name, ...] from table_name where id in (SELECT ID "
Rownum as nuMrow, ID from table_name where condition 1 Order BY Conditions 2) Where Numrow> 80 and
Numrow <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 <100) Order BY Conditions 3;
4. Handling of long strings
Handling Oracle also has special places. The maximum operable string length is less than or equal to INSERT and UPDATE
4000 single-byte, if you want to insert a longer string, consider the field with a clob type, and use the DBMS_LOB program in Oracle.
package. Be sure to do non-empty and length judgments before inserting a modified record, can't make a warning of field values and exceeding length field values,
Returns the last operation.
5. Processing of the date field
MySQL date field points Date and Time two, the Oracle date field is only Date, including the annual day time division second information, with the current database
The system time is sysdate, accurate to second, or converted into a date function to_date ('2001-08-01', 'YYYY-MM-DD') with a string.
Year - Month - Day 24 hours: Mini-mm-DD HH24: MI: SS to_Date (), there are many date formats, you can see Oracle DOC.
Date field conversion into a string function to_Char ('2001-08-01', 'YYYY-MM-DD HH24: MI: SS')
The mathematical operation formula for the date field is very different.
MySQL finds 7 days from the current time
Date_field_name> Subdate (now (), Interval 7 Day)
Oracle found 7 days from the current time
Date_field_name> sysdate - 7;
6. Treatment of empty characters
MySQL's non-empty fields also have empty content, and the non-empty fields defined in Oracle do not allow empty content.
Press MySQL's Not Null to define an Oracle table structure, and errors will occur when the data is changed. Therefore, the empty characters should be judged when the data is transferred.
Broken, if you are null or empty characters, you need to change it into a string of a space.
7. Available in strings
Field name LIKE '% string%' in mysql
Oracle can also use the field name LIKE '% string%' but this method cannot use an index, the speed is unhappy.
Compare the function INSTR (field name, 'string')> 0 will get more accurate lookup results
8. In the program and function, please pay attention to the release of the result set and pointer after the operation of the operation database is completed.
