Some differences between Oracle and MySQL

Source: Internet
Author: User

1. Group function Usage Rules

The group functions in MySQL are freely available in the SELECT statement, but in Oracle if there is a group function in the query statement, the other column names must be processed by the group function, or the columns in the GROUP BY clause will otherwise be error-

eg

Select Name,count from user; This is not a problem in MySQL. There is a problem in Oracle.

2. Automatic growth of data type processing

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 the field value: The name of the serial number. Nextval

3. Handling of single quotes

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.

4. Handling 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, 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:

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 > B and Numrow < 3) Order by conditions;

5. Processing of long strings

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.

6. Processing of date fields

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 (' 2001-08-01 ', ' 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 kinds of date formats that can be found in the Oracle DOC. Date fields are converted to String functions To_char (' 2001-08-01 ', ' 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;

Several functions for inserting the current time in MySQL are: the Now () function returns the current datetime with ' Yyyy-mm-dd HH:MM:SS ' and can be stored directly in the DateTime field. Curdate () returns today's date in the format ' Yyyy-mm-dd ', which can be stored directly in the Date field. Curtime () returns the current time in the format ' HH:MM:SS ', which can be stored directly in the Date field. Example: INSERT INTO TableName (fieldname) VALUES (now ())

And the current time in Oracle is Sysdate

7. Handling of NULL characters

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.

8. Fuzzy Comparison of strings

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, with a string comparison function InStr (field name, ' string ') >0 will be more accurate results found.

9. In programs and functions, after the work of the database is completed, be aware of the result set and the release of the pointer.

Some differences between Oracle and MySQL

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.