the recent knock on the project accidentally contacted MySQL, but due to some differences between Oracle and some of the differences between SQL statements, let me go a lot of detours, so here to summarize the differences between their two. in general, Oracle is a large database and MySQL is a small-to-medium database, although MySQL is open source but occupies only 20% of the market, and the very high Oracle market share is twice times that of MySQL. In terms of usage, Oracle supports large concurrency, large traffic, and is the best tool for OLTP (on-line Transaction processing online transaction processing system), but Oracle installation takes up a lot of space and MySQL installs only around 150M. from the perspective of practical application, there are mainly the following aspects:
PS: The first two are currently in the DRP project through the Detour, please everyone warning! "
① the
processing of SQL statements for paged queries (the code for paging queries using nested statements is being studied, and later I will share them with you!). ）。 mysql processing of the page of the SQL statement is relatively simple, using the limit start position, record number; Oracle's SQL statement for paging is cumbersome. Each result set has only one rownum field indicating its location, for example:Select RowNum from T_user where RowNum < 3However, to use MySQL to query line numbers, you must use the "@rowNum: [email protected] + 0".
② Processing of a SELECT query statement nesting :
The use of Oracle can be nested directly, but in MySQL must give each layer of the query statement an alias, the code is as follows:
Select user_id, user_name, PASSWORD, Contact_tel, email, create_date from ( (select user_id, user_name, PASSWORD , Contact_tel, email, create_date from ((SELECT user_id, user_name, PASSWORD, Contact_tel, e-mail, create_date from T_ User where user_id <> ' root ') as a) where user_id <> ") as B ) WHERE user_name <> '
Otherwise, this error will occur: Every derived table must has its own alias. (Each derived table must have its own alias!) As in the code above, as a and as B are the processes that alias the derived table.
③ primary key MySQL generally uses the automatic growth type, when the table is created as long as the primary key of the specified table is auto increment, when inserting a record, no need to specify the primary key value of the record, MySQL will automatically grow; Oracle does not have an auto-grow type, the primary key is generally used by the sequence, The next value of the sequence number is paid to the field when the record is inserted, but only if the ORM framework is a native primary key generation strategy.
processing of ④ long stringsthe 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.
processing of ⑤ null charactersMySQL'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.
fuzzy comparison of ⑥ stringsMySQL 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.
⑦Oracle implements
most of the functionality in ANSII SQL , such as the isolation level of transactions, propagation characteristics, and so on, which MySQL is still relatively weak.
The above is my collection and collation of the differences between them, for the MySQL query line number is further research, the results will be the first time to share to everyone.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Exposing the secrets between MySQL and Oracle
