MySQL and Oracle (i)

Beginners SSH Framework, in the online search for resources for reference study, found that in addition to the learning of MySQL, most also use Oracle. In order to better use the database in the future, specifically on the Internet to find some relevant information. Let's review and study.

MySQL differs from Oracle:

1. Oracle is a large database and MySQL is a small to medium database

2. Oracle supports high concurrency, large traffic, and is the best tool for OLTP.

3. mysql is only around 150M after installation and Oracle has about 3G, and Oracle occupies particularly large memory space and other machine performance when used.

Some differences between the 4.Oracle and MySQL operations

① 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.

② Single-Quote processing

In MySQL, you can use double quotes to wrap strings, and Oracle can only wrap strings with single quotes.

③ the processing of SQL statements with paging

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 and can only be used with rownum<100, not rownum>80

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.

Processing 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.

Fuzzy comparison of ⑥ strings

MySQL 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, while MySQL compares less.

MySQL and Oracle (i)