Precautions for migration from MySQL to Oracle

Precautions for migration from MySQL to Oracle

2001-09 Yu Feng has many application projects. At the beginning, MySQL Databases can basically meet various functional requirements. As the number of application users increases, the amount of data increases, mySQL gradually became overwhelmed: the connection was slow or even down, so there was a need to migrate data from MySQL to Oracle, and the application had to make some modifications accordingly. I have summarized the following considerations and hope to help you. 1. Automatically increasing data types process Mysql Data Types Automatically increasing. This field is not required when you insert a record, and the data value is automatically obtained. Oracle does not have an auto-increasing data type. You need to create an auto-increasing serial number. When inserting a record, you need to assign the next value of the serial number to this field. Create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1 maxvalue 99999 cycle nocache; the maximum value is determined by the length of the field, if the defined auto-increment serial number (6) is used, the maximum value is 999999 insert. insert this field value as the serial number name. nextval2. MySQL can use double quotation marks to enclose strings. Oracle can only use single quotation marks to enclose strings. You must replace single quotes before inserting and modifying strings: replace all the existing single quotes with two single quotes. 3. Processing of paging SQL statements MySQL SQL statements that process paging is relatively simple. Use Limit to start the position and record the number. In PHP, you can also use seek to locate the result set. It is complicated for Oracle to process paging SQL statements. Each result set has only one rownum field to indicate its position, and only rownum <100, not rownum> 80 can be used. The following are two SQL statements (ID is the field name of the unique keyword) that have been analyzed: 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 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 Condition 3; 4. Processing of long strings in Oracle also has its special features. The maximum length of a string that can be operated during insert and update is less than or equal to 4000 single bytes. If you want to insert a longer string, use the clob type for the field to use the dbms_lob package that comes with Oracle. Before inserting a modification record, you must make a non-null and length judgment. If the field value cannot be blank or the field value beyond the length is exceeded, a warning should be given, and the last operation is returned. 5. the Date Field Processing MySQL Date Field is divided into two types: Date and time. The Oracle Date Field only contains date, which contains the information of year, month, day, hour, minute, and second. the system time of the current database is sysdate, accurate to seconds, or use a string to convert to the date type function to_date ('1970-08-01 ', 'yyyy-MM-DD') year-month-day 24 hours: minute: Second format YYYY-MM-DD hh24: MI: SS to_date () has many date formats. For more information, see Oracle Doc. the mathematical formula for converting a datetime field to a string function to_char ('1970-08-01 ', 'yyyy-MM-DD hh24: MI: ss') is very different. MySQL uses date_field_name> subdate (now (), interval 7 day) to locate the current time seven days from the current time with date_field_name> sysdate-7; 6. empty Character Processing MySQL non-empty fields are also empty content, Oracle defines non-empty fields are not allowed to be empty content. The Oracle table structure is defined based on the not null value of MySQL. errors may occur when data is imported. Therefore, when importing data, you need to judge the null character. If it is null or null, you need to change it to a space string. 7. fuzzy comparison of strings MySQL can also use field names like '% string %' in Oracle, but this method cannot use indexes, if the speed is not fast, use the string to compare the function instr (field name, 'string')> 0 to get more accurate search result 8. in programs and functions, pay attention to the release of result sets and pointers after database operations are completed.