Briefly describe the difference between MySQL and Oracle

  • 2021-08-21 21:42:38
  • OfStack

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

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

3. The space used for installation varies greatly. After Mysql is installed, it is only 152M, while Oracle is about 3G, and Oracle takes up a lot of memory space and other machine performance when used.

4. Some differences between Oracle and Mysql operation

(1) The primary key Mysql1 uses the automatic growth type. When creating a table, as long as the primary key of the table is specified as auto increment, when inserting a record, it is not necessary to specify the primary key value of the record, and Mysql will automatically grow; Oracle has no automatic growth type, and the sequence used like primary key 1 can pay the next 1 value of serial number to this field when inserting records; Only the ORM framework is as long as it is the native primary key generation strategy.

② Processing of single quotation marks MYSQL can wrap strings with double quotation marks, while ORACLE can only wrap strings with single quotation marks. You must replace single quotation marks before inserting and modifying strings: replace all one single quotation mark with two single quotation marks.

The processing of SQL statement of page turning MYSQL is relatively simple to process SQL statement of page turning, and the number is recorded by starting position of LIMIT; ORACLE Handling page-turning SQL statements is more cumbersome. Each result set has only one ROWNUM field indicating its location, and only ROWNUM can be used < 100, cannot use ROWNUM > 80

Long string processing ORACLE also has its special place. For INSERT and UPDATE, the maximum operable string length is less than or equal to 4000 single bytes. If you want to insert a longer string, please consider using CLOB type for the field, and borrow DBMS_LOB package from ORACLE. Before inserting the modified record, it is necessary to judge the non-null and length. If the field value cannot be null and the field value exceeds the length, it should be warned and return to the last operation. ⑤ Processing of null characters the non-null fields of MYSQL also have empty contents, and the non-null fields defined in ORACLE do not allow empty contents. ORACLE table structure is defined according to NOT NULL of MYSQL, and errors will occur when leading data. Therefore, when leading data, null characters should be judged. If it is NULL or null characters, it needs to be changed into a string of 1 space.

⑥ Fuzzy comparison of strings the field name like '% string%' is used in MYSQL, and the field name like '% string%' can also be used in ORACLE, but this method can not use index, and its speed is not fast.

⑦ Oracle realizes most of the functions of ANSII SQL, such as the isolation level of transactions, propagation characteristics, etc., while Mysql is relatively similar in this respect

The above is this site to give you a brief introduction to the difference between MySQL and Oracle, I hope to help you, if you have any questions welcome to leave me a message, this site will reply to you in time!


Related articles: