Briefly describe the difference between MySQL and Oracle

1. Oracle is a large database while MySQL is a small to medium-sized database. Oracle has a market share of 40% while MySQL only has about 20%. In addition, MySQL is open source while Oracle is very expensive.

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. After installation, MySQL only takes up 152M while Oracle takes up about 3G. In addition, Oracle takes up a particularly large amount of memory space and other machine performance when in use.

4. Some differences between Oracle and MySQL operations

①Mysql generally uses the auto-increment type for the primary key. When creating a table, you only need to specify the primary key of the table as auto increment. When inserting a record, you do not need to specify the primary key value of the record, and Mysql will automatically increase it. Oracle does not have an auto-increment type. The primary key generally uses a sequence. When inserting a record, just assign the next value of the sequence number to the field. It's just that the ORM framework only needs a native primary key generation strategy.

②Handling of single quotes In MYSQL, strings can be enclosed in double quotes, but in ORACLE, strings can only be enclosed in single quotes. Before inserting and modifying a string, single quotes must be replaced: all occurrences of a single quote are replaced with two single quotes.

③Processing of SQL statements for page turning: MYSQL's processing of SQL statements for page turning is relatively simple, using LIMIT to set the starting position and record the number; ORACLE's processing of SQL statements for page turning is relatively complicated. Each result set has only one ROWNUM field to indicate its position, and can only use ROWNUM<100, not ROWNUM>80

④ Processing of long strings ORACLE also has its special features in processing long strings. The maximum operable string length for 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 using the DBMS_LOB package that comes with ORACLE. Before inserting or modifying records, you must perform non-empty and length checks. 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 contents, while ORACLE does not allow empty contents in non-empty fields. Defining the ORACLE table structure according to MYSQL's NOT NULL will result in errors when importing data. Therefore, when importing data, you need to judge the empty character. If it is NULL or an empty character, you need to change it to a string of spaces.

⑥ Fuzzy comparison of strings uses field name like '% string%' in MYSQL, and can also be used in ORACLE. 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., while MySQL is still relatively new in this regard.

The above is a brief introduction of the differences between MySQL and Oracle. I hope it will be helpful to everyone. If you have any questions, please leave me a message and I will reply to you in time!