Mysql to Oracle Program Migration
Author: Yu Feng
There are many application projects, and when you start, use the MySQL database to basically implement various functional requirements. As the application user increases, the amount of data increases, MySQL gradually appears, and the connection is very slow or even downtime. If you move the data from MySQL to Oracle, the application must do some modifications 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 an automatic growth data type, you need to create an automatic growth number, and assign the next value of the serial number to this field when the record is inserted.
Create sequence serial number (best is a table name serial number tag) increment by 1 start with 1 maxvalue 99999 cycle noCache; where the largest value is set by the length, if the defined automatic growth serial number Number (6 ), The maximum value of 99999 INSERT statement is inserted into this field value: the name of the serial number. NexTVal
2. Processing of single quotes Mysql can use dual quotation to clasp strings, and only single quarters in the Oracle are available. Alternative to single quotation marks must be made before inserting and modifying the string: replace all the single quotes that appear into two single quotes.
3. Processing the SQL statement of the SQL statement MySQL handling the SQL statement of the page is relatively simple, using the LIMIT start position, record the number; PHP can also be positioned to the result set. Oracle handles the SQL statement that flips the page is more cumbersome. Each result set only has a ROWNUM field indicating its location and can only use rownum <100, and cannot use ROWNUM> 80. The following is a better two oracle flipping SQL statement (ID is 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 [spec [field_name , ...] from table_name where conditions 1 Order BY Conditions 2) c) WHERE Numrow> 80 and Numrow <100) Order BY Conditions 3;
4. Treatment of long strings of long strings Oracle also has special places. Insert and Update, the maximum operable string length is less than or equal to 4000 single-bytes. If you want to insert a longer string, consider the field with a Clob type, and use the DBMS_LOB package from Oracle. Before inserting a modified record, you must do non-empty and length judgments, and the field value that cannot be empty and the exceeding length field value should be warned, and the last operation is returned.
5. Date field processing MySQL Date Field Date Date and Time Two, Oracle Date Field Only Date, including the annual monthly daytime second information, using the current database system time is sysdate, accurate to second, or use strings to convert to date type function TO_DATE ( '2001-08-01', 'YYYY-MM-DD') Year - month - day 24 hours: minutes: seconds format YYYY-MM-DD HH24: MI: SS TO_DATE () there are many Date format, you can see the Oracle Doc. Date field conversion into a string function to_char ('2001-08-01', 'YYYY-MM-DD HH24: MI: SS') Date field mathematical operation formula has a big difference . MySQL finds 7 days from the current time with Date_field_name> Subdate ((now (), Interval 7 Day) Oracle finds 7 days from the current time with Date_field_name> sysdate - 7;
6. Empty characters' handling MySQL's non-empty fields also have empty content, and the non-empty fields are defined in Oracle not allowed to have 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 character is judged when the data is transferred. If a null or empty character, you need to change it into a string of a space.
7. String Fuke Compare Mysql With Field Name Like '% String%' Oracle can also use field name LIKE '% string%' but this method cannot use indexes, speed unhappy comparison function INSTR Field name, 'string')> 0 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.
