Daily Learning points--------Sixth day (2017-10-10) Summary: Differences between MySQL and Oracle

1. Processing of automatic growth data types

MySQL has an auto-growing data type that automatically gets data values when inserting records without manipulating this field. 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

CREATE SEQUENCE Serial Number name (indicates + serial number tag) increment by 1 start with 1 MaxValue 99999 cycle NoCache

The maximum value is determined by the length of the field, if the auto-growing serial number (6) is defined, and the maximum value is 999999. Insert statement inserts this field as. Nextval

2, single-quote processing

MySQL can wrap strings in double quotes, and Oracle can only wrap strings with single quotes. Single quotation marks must be converted before inserting and modifying strings; change all occurrences of a single quotation mark to double quotation marks

3, the processing of page-turn SQL statements

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 roqnum > 80

The following are the two better-parsed statements for Oracle paging (ID is the field name of the unique keyword)

4. Processing of long strings

The processing of long strings Oracle also has its special place. Insert and update the maximum action string 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. Make sure to do non-empty and length judgments before inserting the modified record, and the empty field and the value beyond the length field should be warned to return the last action.

5. Processing of date fields

The MySQL date field is divided into two types. The Oracle Date field is only date, containing the date and time of the month and minute information, with the current database current system time of sysdate, accurate to the second, or string 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: Format yyyy-mm-dd HH24:MI:SS to_date () There are many different date formats that can be found in Oracle DOC. The date field is converted to a 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 date fields is very different, and MySQL finds 7 days away from the current time with Date_field > Subdate (Now (), INTERVAL 7days) Oracle found 7 day from current time with Date_ Field_name >SYSDATE-7;

6. Processing of empty strings

　MySQL's non-empty fields also have empty content, Oracle defines a non-empty field is not allowed to empty content, according to MySQL not NULL to define the Oracle table structure, the data back to generate errors. Therefore, the data should be judged by an empty string, if it is empty or an empty string. A string that needs to be changed to a space.

7, the fuzzy comparison of the string

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

8, programs and functions in the operation of the database after the completion of the work, please note the result set and the release of the pointer

Daily Learning points--------Sixth day (2017-10-10) Summary: Differences between MySQL and Oracle