9 differences between Oracle and mysql

1. Rules for function usage

In mysql, group functions can be used freely in select statements, but in oracle if there is a group of functions in the query, the other column names must be handled by the group function or the columns in the group by clause otherwise an error is reported
eg: select name,count(money) from user; This is not a problem in mysql, it's a problem in oracle

2. Automatic growth of data type processing

MYSQL has an auto-growing data type, which is not manipulated when inserting records and automatically gets data values. ORACLE does not have an auto-incremented data type, and an auto-incremented sequence number needs to be established to which the next value of the sequence number is assigned when the record is inserted.
Name of 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, the maximum value is 999999 if the automatic increment sequence number NUMBER(6) is defined
The INSERT statement inserts this field with a value of: name of the serial number.NEXTVAL

3. Handling of single quotes

Double quotes are allowed in MYSQL and single quotes are allowed in ORACLE. Single quotation marks must be replaced before inserting or modifying a string: replace all 1 single quotation marks that appear with two single quotation marks.

4. Processing of page turning SQL statements

MYSQL is relatively simple to handle the page turning SQL statement. It USES LIMIT to start the position and record the number. SEEK can also be used to locate the result set. ORACLE is a bit more cumbersome to handle paging SQL statements. Each result set has only 1 ROWNUM field indicating its location and can only be used with ROWNUM < 100, you can't use ROWNUM > 80.
The following are the two better ORACLE page-turning SQL statements after analysis (ID is the field name with only 1 keyword) :
Statement 1:
SELECT ID, FIELD_NAME,... FROM TABLE_NAME ID (SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE condition 1 ORDER BY condition 2 > 80 AND NUMROW < 100) ORDER BY conditions 3;
Statement 2:
SELECT * FROM ((SELECT ROWNUM AS, c.* from (select [FIELD_NAME...]) FROM TABLE_NAME WHERE Condition 1 ORDER BY condition 2) c) WHERE NUMROW > 80 AND NUMROW < 100) ORDER BY Conditions 3;

5. Processing of long strings

The processing of long strings ORACLE also has its special features. The maximum string length for INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to insert a longer string, consider using the CLOB type for the fields, which borrow the DBMS_LOB package that comes with ORACLE. Non-null and length judgments must be made 1 before inserting the modification record. The field value that cannot be null and the field value that exceeds the length should be warned, returning the last operation.

6. Processing of date fields

The date field of MYSQL is divided into DATE and TIME. The date field of ORACLE is only DATE, which contains the minute and second information of year, month and day. The system time of the current database is SYSDATE, accurate to seconds. Or use a string into a function type date TO_DATE (' 2001-08-01 ', 'YYYY MM - DD') - month - day 24 hours: minutes: seconds format YYYY MM - DD HH24: MI: SS TO_DATE () there are a lot of kinds of date format, See ORACLE DOC. Date field into a string function TO_CHAR (' 2001-08-01 ', 'YYYY MM - DD HH24: MI: SS')
The mathematical formulas for date fields are quite different. MYSQL find 7 days from the current time using DATE_FIELD_NAME > SUBDATE(NOW(), INTERVAL 7 DAY)ORACLE Find 7 days from the current time using DATE_FIELD_NAME > SYSDATE � 7;
The NOW() function returns the current date and time as' YYYY-ES190en-ES191en HH:MM:SS', which can be saved directly into the DATETIME field. CURDATE() returns today's date in the format 'ES197en-ES198en-ES199en' and can be saved directly into the DATE field. CURTIME() returns the current time as 'HH:MM:SS' and can be saved directly into the TIME field. Example: insert into tablename (fieldname) values (now())
The current time in oracle is sysdate

7. Processing of null characters

Non-empty fields in MYSQL also have empty contents. In ORACLE, non-empty fields are not allowed to have empty contents. Define ORACLE table structure according to NOT NULL, error will occur when derivative data. Therefore, the derivative data is used to judge the null character. If it is NULL or the null character, it needs to be changed to a 1-space string

8. Fuzzy comparison of strings

MYSQL USES the field name like% 'string %', like can also use the field name like%' string %', but this method cannot use index, it is not fast, it USES string comparison function instr(field name, 'string ') > Zero will give you a more accurate result.

9. In programs and functions, please pay attention to the release of the result set and pointer after the operation of the database.