Some of the differences between MySQL and Oracle

Original: http://www.cnblogs.com/wujin/archive/2012/02/02/2336055.html

There are many application projects, just start with MySQL database can basically achieve a variety of functional requirements, with the increase in application users, data volume increases, MySQL gradually appeared overwhelmed: the connection is slow or even down, so there is the need to move data from MySQL to Oracle, The application should also make some changes accordingly. Summarizes the following points of attention.

1.  fromMovingIncreaseLongof Dataclasstypeprocessing
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 autogrow serial number, and the next value of the sequence number is assigned to this field when inserting the record.

The 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.singleof quotation marksprocessing
In MySQL, you can use double quotes to wrap strings, and Oracle can only wrap strings with single quotes. Single quotation marks must be replaced before inserting and modifying strings: Replace all occurrences of a single quotation mark with two single quotes.

3.Turnpageof theSQLlanguagesentence ofprocessing
MySQL processing the page of the SQL statement is relatively simple, with the limit start position, record the number of, PHP can also use seek to locate the result set location. Oracle's SQL statements that handle paging are cumbersome. Each result set has only one rownum field indicating its location and can only be used with rownum<100, not 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:
Statement two:

SELECT ID, [Field_name,...]
From table_name
where ID in (select-ROWNUM as Numrow, ID from table_name where condition 1 ORDER by condition 2) wher E numrow > Numrow <) ORDER by condition 3;

+ View Code

4. of long Stringsprocessing
The processing of long strings Oracle also has its special place. Insert and update when the maximum operand length is less than or equal to 4,000 single bytes, if you want to insert a longer string, consider the field with the Clob type, the method borrows the Dbms_lob package from the Oracle. Be sure to do non-null and length judgments before inserting a modified record, and the field values that cannot be empty and the value beyond the length field should be warned to return the last action.

5. The Date fieldprocessing
The MySQL date field is dated and time two, the Oracle Date field is only date, contains the date of the month and minute information, the system time of the current database is sysdate, accurate to the second, or the string is converted into a date type function to_date (' <st1: Chsdate isrocdate= "false" Islunardate= "false" day= "1" month= "8" year= "2001" >2001-08-01</st1:chsdate> ', ' Yyyy-mm-dd ') year-month-day 24 hours: minutes: The format of the second YYYY-MM-DD HH24:MI:SS to_date () There are many different date formats that can be found in Oracle DOC. Date field converted to String function To_char (' < St1:chsdate isrocdate= "false" Islunardate= "false" day= "1" month= "8" year= "2001" >2001-08-01</st1:chsdate> ", ' Yyyy-mm-dd HH24:MI:SS ')

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

6. The null characterprocessing
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. As a result, null characters are judged on the data, and if NULL or NULL, it is necessary to change it to a space string.

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

8. Procedures and functions, OperationDatabasebe aware of the result set and the release of the pointer after the work is completed.