Differences between MySQL and Oracle

Time:2022-5-24

1. MySQL and Oracle are both relational databases, Oracle is a large database, and MySQL is a small and medium-sized database. However, MySQL is open source, but Oracle charges and is more expensive.

MySQL default port: 3306, default user: root
Oracle default port: 1521, default user: System

2. Oracle occupies a large amount of memory, while MySQL is very small. After MySQL is installed, it is almost one or two hundred megabytes, while Oracle has about 3G, and Oracle occupies a large amount of memory space and other machine performance.

MySQL login: MySQL - hlocalhost - uroot - P password (H: host, u: user, P: password)
Oracle login: sqlplus user_ name/ [email protected] :port/instance_ name; (you can write IP address, port number and instance name in a TNS file, take an alias, and enter this alias when logging in)

Oracle supports large concurrency and large traffic. It is the best tool of OLTP.

3. Hierarchy of database:

MySQL: the default user is root. Many databases can be created under the user. There are many tables under each database. Generally, the default user is used and multiple users will not be created;
Oracle: create a database. There are many users under the database: sys, system, Scott, etc. there are many tables under different users. Generally, only one database is created.

4. Table field type in database:

MySQL: int, float, double and other numeric types, varchar, char character types, date, datetime, time, year, timestamp and other date types.
Oracle: number (numeric type), VARCHAR2, varchar, char (character type), date (date type), etc

Char (2) is defined as two bytes in Oracle and two characters in MySQL.

The length of varchar in MySQL must be given, such as varchar (10), otherwise an error will occur during insertion.

5. Primary key

MySQL supports primary key self growth. Specify the primary key as auto increment, and it will grow automatically when inserted. Oracle primary keys generally use sequences.

6. Quotation mark handling:

MySQL strings can be enclosed in double quotation marks, while Oracle can only use single quotation marks

7. Paging:

MySQL paging uses the limit keyword, while Oracle uses the rownum field to indicate the location, and can only use less than, not greater than.

8. Submit to:

MySQL is submitted automatically by default and can be modified to manual submission.
Oracle does not submit automatically by default. It needs to submit manually. You need to write a commit instruction or click the commit button.

9. Transaction isolation level:

MySQL is the isolation level of repeatable read, while Oracle is the isolation level of read committed;
At the same time, both support serializable serialization transaction isolation level, which can achieve the highest level of read consistency. After each session is submitted, other sessions can see the submitted changes;
Oracle realizes read consistency by constructing multi version data blocks in the undo table space. If the corresponding data block changes during each session query, Oracle will construct the old data block for this session in the undo space;
MySQL does not have a mechanism similar to Oracle to construct multi version data. It only supports the isolation level of read committed. When a session reads data, other sessions cannot change the data, but data can be inserted at the end of the table. When a session updates data, an exclusive lock should be added, and other sessions cannot access the data.

10. Concurrency:

MySQL is dominated by table level locking, and the granularity of resource locking is very large. If a session locks a table for too long, other sessions will not be able to update the data in this table. Although InnoDB engine tables can use row level locks, the mechanism of this row level lock depends on the index of the table. If the table does not have an index or the SQL statement does not use an index, the table level lock is still used;
Oracle uses row level locks. The granularity of resource locking is much smaller. It only locks the resources required by SQL, and the locking is on the data rows in the database and does not depend on the index. Therefore, Oracle supports concurrency much better.

11. Logical backup:

Data must be locked during MySQL logical backup to ensure that the backed up data is consistent and affect the normal use of DML.
Oracle logical backup does not lock data, and the backed up data is consistent.

12. Judge true and false:

0 and 1 in MySQL judge true and false, and true and false in Oracle

13. Case sensitive:

MySQL is case insensitive in Windows environments and case sensitive in UNIX and Linux environments.
Oracle does not differentiate

14. Date conversion:

Dateformat() function is used for date conversion in MySQL;
Oracle to_ Date () and to_ Char() two functions.