What is the difference between oracle and mysql

Difference: 1. Oracle database is a heavyweight object-relational database management system, and mysql is a lightweight open source relational database management system; 2. Oracle has many parameters to verify users and has strong security. Safety, mysql only has three parameters to authenticate users, and the security is weak.

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

What is the difference between oracle and mysql

1. The difference between type and cost

oracle database is an object relationship Database management system (ORDBMS), a heavy-duty database. It is often called Oracle RDBMS or simply Oracle and is a database.

MySQL is an open source relational database management system (RDBMS) and a lightweight database. It is the most used RDBMS in the world and runs as a server providing multi-user access to multiple databases. It is an open source, free database.

2. Storage differences

Compared with Oracle, MySQL does not have table spaces, role management, snapshots, synonyms and packages, and automatic storage management.

3. Differences in security

MySQL uses three parameters to authenticate users, namely username, password and location; Oracle uses many security functions, such as username, password, configuration files, local authentication, external authentication, advanced security enhancements, and more.

4. Support for transactions

MySQL can only support transactions under the row-level lock of the innodb storage engine, while Oracle fully supports transactions

5. Performance Differences in diagnosis

MySQL has fewer diagnostic 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.

6. Differences in management tools

MySQL has fewer management tools. The installation of management tools under Linux sometimes requires the installation of additional packages (phpmyadmin , etc), there is a 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.

7. Differences in 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 session cannot update data in this table. Although InnoDB engine tables can use row-level locks, this row-level locking 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.

8. Persistence of saving data

MySQL will lose data when the database is updated or restarted. Oracle writes the submitted sql operation line into the online log file and keeps it until On the disk, it can be restored at any time

9. The difference in 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. Rowized transaction isolation level, which 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 the session updates data, an exclusive lock must be added so that other sessions cannot access the data.

10. Some operational differences

①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 a record, There is no need to specify the primary key value of the record, Mysql will automatically grow;

Oracle does not have an automatic growth type. The primary key generally uses the sequence. When inserting the record, just pay 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

You can use double quotes to wrap a string in MYSQL, but you can only use single quotes to wrap a string in ORACLE. 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

MYSQL is relatively simple to process page-turning SQL statements. Use LIMIT to set the starting position and record number; ORACLE is more complicated to process page-turning SQL statements. .

④ Handling of null characters

MYSQL's non-empty fields can also have empty content. If non-empty fields are defined in ORACLE, they will not allow empty content.

⑤Fuzzy comparison of strings

In MYSQL, field names like '% string%' are used; in ORACLE, field names like '% string%' can also be used, but this method cannot Using indexes is not fast.

Recommended learning: mysql video tutorial

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