What is the difference between mysql and oracle

MySQL and Oracle are both popular database management systems that are widely used around the world; most databases work in a similar way, but there are some differences. The following article will introduce to you the differences between MySQL and Oracle. I hope it will be helpful to you.

The difference between mysql and oracle:

1. The difference between type and cost

Oracle database is an object-relational 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 features, such as Usernames, passwords, profiles, 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. Differences in performance diagnosis

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

6. Differences in management tools

MySQL has fewer management tools. The installation of management tools under Linux sometimes requires Installing additional packages (phpmyadmin, etc) has certain complications.

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 a long time If it is 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 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 online. The log file is saved to the disk and can be restored at any time

9. The difference in transaction isolation level

MySQL is the isolation level of read committed, while Oracle is repeatable. The isolation level of read, and both support serializable serialized 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 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. 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 sequence generally used for the primary key will be the next value of the sequence number when inserting the record. Just pay this 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

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

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