What are the differences between MySQL and Oracle?

1. Oracle is a large database and Mysql is a small and medium-sized database. Oracle's market share reaches 40%, while Mysql only has about 20%. At the same time, Mysql is open source and Oracle's price is very high.

2. Oracle supports large concurrency and large access volume, and is the best tool for OLTP.

3. The space used for installation is also very different. Mysql is only 152M after installation, while Oracle is about 3G. Oracle occupies a particularly large memory space and other machine performance when used.

4. Oracle also has some differences in Mysql operations

①The primary key Mysql generally uses the automatic growth type. When creating a table, you only need to specify the primary key of the table as auto increment. When inserting records, no need Then specify the primary key value of the record, and Mysql will automatically grow; Oracle does not have an automatic growth type. The primary key generally uses a sequence. When inserting the record, the next value of the sequence number is paid to the field; but the ORM framework only needs to be native. The primary key generation strategy is enough.

②Handling of single quotes In MYSQL, you can use double quotes to wrap a string, but in ORACLE, you can only use single quotes to wrap a string. Before inserting or modifying a string, you must replace single quotes: replace all occurrences of a single quote with two single quotes.

③ Processing of page-turning SQL statements It is relatively simple for MYSQL to process page-turning SQL statements, using LIMIT as the starting position and the number of records; ORACLE's processing of page-turning SQL statements is more cumbersome. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM<100 can be used, not ROWNUM>80

④ Processing of long strings ORACLE also has its special features in processing long strings. The maximum operable string length during INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to insert a longer string, please consider using the CLOB type for the field and borrow the method from the DBMS_LOB package that comes with ORACLE. Before inserting a modified record, you must make non-empty and length judgments. Field values ​​that cannot be empty and field values ​​that exceed the length should issue a warning and return to the last operation. ⑤Handling of empty characters. MYSQL's non-empty fields also have empty content. ORACLE defines non-empty fields and does not allow empty content. Define the ORACLE table structure according to MYSQL's NOT NULL, and an error will occur when importing data. Therefore, when importing data, you need to judge the null character. If it is NULL or a null character, you need to change it into a space string.

⑥Fuzzy comparison of strings. In MYSQL, field names like '% string%' are used. In ORACLE, field names like '% string%' can also be used. However, this method cannot use indexes and is not fast.

⑦Oracle implements most of the functions in ANSII SQL, such as transaction isolation level, propagation characteristics, etc. Mysql is still relatively good in this regard

The above is the detailed content of What are the differences between MySQL and Oracle?. For more information, please follow other related articles on the PHP Chinese website!