Is there any difference between mysql and oracle?

There are differences, which are: 1. MySQL automatically submits transactions and Oracle manually submits them; 2. At the isolation level, MySQL is "read committed" and Oracle is "repeatable read"; 3. MySQL is lightweight , and is free, while Oracle is heavy-duty and charges a fee.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The difference between mysql and oracle

(1) Submission of transactions

MySQL default It is submitted automatically, but Oracle does not submit automatically by default. Users need to submit manually. They need to write the commit; command or click the commit button

(2) Paging query

MySQL It is to write "select... from ...where...limit x, y" directly in the SQL statement. With limit, paging can be achieved

, while Oracle needs to use pseudo columns ROWNUM and embedded Set query

(3) Transaction isolation level

MySQL is the isolation level of read committed, while Oracle is the isolation level of repeatable read, and both support serializable strings The row-based transaction isolation level can achieve the highest level of

read consistency. Only after each session is submitted can other sessions see the submitted changes. Oracle achieves read consistency by constructing multi-version data blocks in the undo table space. During each session

query, if the corresponding data block changes, Oracle will construct it in the undo table space for this session. Old data blocks during query

MySQL does not have a mechanism similar to Oracle for constructing multi-version data blocks, and only supports the isolation level of read committed. When one session reads data, other sessions cannot change the data, but they can insert data at the end of the table. When a session updates data, an exclusive lock must be added, and other sessions cannot access the data

(4) Support for transactions

MySQL is at the row level of the innodb storage engine Transactions can only be supported under lock conditions, and Oracle fully supports transactions

(5) Durability of saving data

MySQL is updated or restarted when the database is updated. Data will be lost. Oracle writes the submitted sql operation line into the online log file and saves it on the disk. It can be restored at any time

(6) Concurrency

MySQL mainly uses table-level locks, and the granularity of resource locking is very large. If a session locks a table for too long, other sessions will be unable to update the data in this table.

Although InnoDB engine tables can use row-level locks, this row-level lock mechanism relies on the index of the table. If the table does not have an index, or the SQL statement does not use an index, table-level locks are still used.

Oracle uses row-level locks, and 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 rely on indexes. So Oracle's support for concurrency is much better.

(7) Logical backup
The data must be locked during MySQL logical backup to ensure that the backed up data is consistent, which affects the normal use of dml in the business. Oracle does not lock during logical backup. data, and the backup data is consistent

(8) Replication

MySQL: The replication server configuration is simple, but when there is a problem with the main database, the cluster database may lose a certain amount The data. And you need to manually switch the plex library to the main library.

Oracle: There are both push or pull traditional data replication and dataguard’s dual-machine or multi-machine disaster recovery mechanism. If there is a problem with the main database, the standby database can be automatically switched to the main database, but the configuration management is complicated. complex.

(9) Performance Diagnosis

MySQL has few diagnostic and tuning methods, mainly slow query logs.

Oracle has various mature performance diagnosis and tuning tools, which can realize many automatic analysis and diagnosis functions. For example, awr, addm, sqltrace, tkproof, etc.

(10) Permissions and Security

MySQL users are related to the host, which feels meaningless, and it is easier to be counterfeited There are opportunities for the host and IP.

Oracle’s permissions and security concepts are relatively traditional and quite satisfactory.

(11) Partitioned table and partitioned index

MySQL’s partitioned table is not yet mature and stable.

Oracle’s partition table and partition index functions are very mature and can improve the user experience of accessing the db.

(12) Management tools

There are few MySQL management tools. The installation of management tools under Linux sometimes requires the installation of additional packages (phpmyadmin, etc). Certain complexity.

Oracle has a variety of mature command lines, graphical interfaces, web management tools, and many third-party management tools, making management extremely convenient and efficient.

(13) The most important difference

MySQL is a lightweight database and is free. There is no service to restore data.

Oracle is a heavy-duty database, and there is a fee. Oracle has any services for the Oracle database.

【Related recommendations: mysql video tutorial】

The above is the detailed content of Is there any difference between mysql and oracle?. For more information, please follow other related articles on the PHP Chinese website!