Oracle and Mysql 9-point difference _mysql

1. Group function Usage Rules

Group functions in MySQL can be used at will in a SELECT statement, but in Oracle if there is a group function in the query, the other column names must be processed by the group function, or the column in the GROUP BY clause is otherwise an error
Eg:select name,count from user; This is not a problem in MySQL. There's a problem in Oracle.

2. Automatic growth of data type processing

MySQL has an automatically growing data type, and the data value is automatically obtained when you insert a record without manipulating this field. Oracle does not have an automatically growing data type, an automatic growth sequence number is required, and the next value of the serial number is assigned to the field when the record is inserted.
The name of the CREATE sequence serial number (preferably the table name + serial number tag) INCREMENT by 1 START with 1 MAXVALUE 99999 CYCLE nocache;
The maximum value is determined by the length of the field, and if the defined automatically-growing serial number (6), the maximum is 999999
Insert statement inserts this field value as: The name of the serial number. Nextval

3. Single quotation mark processing

MySQL can be used in double quotes wrap strings, Oracle can only use single quotes wrap strings. A single quotation mark must be replaced before inserting and modifying a string: Replace all occurrences of one single quote with two single quotes.

4. The processing of the SQL statement page

MySQL processing pages of the SQL statement is relatively simple, with limit start position, record number; PHP can also use seek to locate the result set. Oracle handles page-flipping SQL statements is cumbersome. Each result set has only one rownum field that indicates its position, and can only be used rownum<100, not rownum>80.
The following are two of the better Oracle page-Flipping SQL statements (field names with IDs as unique keywords):
Statement one:
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 NU Mrow > Numrow <) Order BY condition 3;
Statement two:
SELECT * FROM (select RowNum as Numrow, c.* from (SELECT [Field_name,...] From table_name WHERE Condition 1 order by Condition 2 c) WHERE Numrow > Numrow < (m) Order by condition 3;

5. Processing of long strings

Processing of long strings Oracle also has its own special place. The maximum operable string length for insert and update is less than 4,000 single-byte, and if you want to insert a longer string, consider using the CLOB type for the field to borrow the Dbms_lob package from Oracle. Be sure to do non-null and length judgments before inserting a change record, and you should warn about field values that are not empty and values that exceed the length field, and return to the last action.

6. Processing of date fields

The MySQL date field is divided into date and time two, and the Oracle Date field is only date, containing the minutes and seconds of the year, the system time of the current database is sysdate, accurate to the second, or the string is converted to a date-type function to_date (' 2001-08-01 '), ' YYYY-MM-DD ') year-month-day 24 hours: minutes: SEC format yyyy-mm-dd HH24:MI:SS to_date () There are many different date formats, see Oracle DOC. Date field converted to String function To_char (' 2001-08-01 ', ' yyyy-mm-dd HH24:MI:SS ')
The mathematical formula for the date field is very different. MySQL finds 7 days away from current time with Date_field_name > Subdate (Now (), INTERVAL 7 day) Oracle finds 7 days with Date_field_name >SYSDATE–7;
Several functions that insert the current time in MySQL are: the Now () function returns the current date time with ' Yyyy-mm-dd HH:MM:SS ', and can be saved directly to the DateTime field. Curdate () returns today's date in ' YYYY-MM-DD ' format and can be stored directly in the Date field. Curtime () returns the current time in ' HH:MM:SS ' format, which can be saved directly to the Duration field. Example: INSERT INTO TableName (fieldname) VALUES (now ())
And the current time in Oracle is Sysdate

7. Processing of NULL characters

MySQL's Non-empty field also has empty content, Oracle defined a NON-EMPTY field does not allow empty content. The Oracle table structure is defined according to the NOT null of MySQL, and errors are generated when data is directed. So when you guide the data, you have to judge the null character, if it is null or empty, you need to change it to a blank string.

8. Fuzzy Comparison of strings

MySQL in 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, with the string comparison function InStr (field name, ' string ') >0 will get more accurate search results.

9. Procedures and functions, the operation of the database after the completion of the work of the result set and the release of the pointer.