Shulou(Shulou.com)06/01 Report--

Editor to share with you what is the difference between mysql and oracle, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Both MySQL and Oracle are popular database management systems that are widely used around the world; most databases work in a similar way, but with some differences.

1. The difference between type and cost

Oracle database is an object-relational database management system (ORDBMS), a heavy database. It is often called Oracle RDBMS or Oracle for short, and it is a paid database.

MySQL is an open source relational database management system (RDBMS), one is a lightweight database. It is the most widely 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. Differences in storage

Compared to Oracle, MySQL has no tablespaces, role management, snapshots, synonyms and packages, and automatic storage management.

3. Differences in security

MySQL uses three parameters to authenticate users, namely, user name, password, and location; Oracle uses many security features, such as user name, password, profile, local authentication, external authentication, advanced security enhancements, and so on.

4. Support for transactions

MySQL supports transactions only in the case of row-level locks of innodb storage engine, while Oracle fully supports transactions

5. Differences in performance diagnosis

There are few diagnostic and tuning methods for MySQL, mainly slow query logs.

Oracle has a variety of mature performance diagnosis and tuning tools, which can achieve many automatic analysis and diagnosis functions. Such as awr, addm, sqltrace, tkproof, etc.

6. Differences in management tools

MySQL management tools are few, and the installation of management tools under linux sometimes requires the installation of additional packages (phpmyadmin, etc), which is somewhat complicated.

Oracle has a variety of mature command line, graphical interface, web management tools, as well as many third-party management tools, management is extremely convenient and efficient.

7. The difference in concurrency

MySQL is mainly table-level locking, and the granularity of resource locking is very large. If a session locks a table for too long, it will prevent other session from updating the data in this table. Although InnoDB engine tables can use row-level locks, this row-level locking mechanism depends on the index of the table, and table-level locks are still used if the table does not have an index, or if the sql statement does not use an index.

Oracle uses row-level locks, locking resources with much smaller granularity, only locking resources needed by sql, and locking on data rows in the database, independent of and index. So Oracle's support for concurrency is much better.

8. Keep the persistence of the data

When MySQL is updated or restarted in the database, the data will be lost. Oracle writes the submitted sql operation line to the online log file, which is kept on disk and can be recovered at any time.

9. Differences in transaction isolation levels

MySQL is the isolation level of read commited, while Oracle is the isolation level of repeatable read, and both support the serializable serialization transaction isolation level to achieve the highest level of read consistency. The committed changes are not seen by other session until each session commits.

Oracle achieves read consistency by constructing multiple versions of data blocks in the undo table space. If the corresponding data block changes in each session query, Oracle will construct the old data block it queried for the session in the undo table space.

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

10. Some differences in operation

① primary key

Mysql generally uses the automatic growth type. When creating a table, as long as the primary key of the table is specified as auto_increment, and when inserting a record, there is no need to specify the primary key value of the record, and Mysql will grow automatically.

Oracle does not have an auto-growth type, and primary keys are commonly used in the sequence. When inserting a record, you can pay the next value of the serial number to this field; it's just that the ORM framework is as long as it is a native primary key generation strategy.

Processing of ② single quotation marks

Strings can be wrapped in double quotation marks in MYSQL and only in single quotation marks in ORACLE. Single quotation marks must be replaced before inserting and modifying strings: replace all occurrences of one single quote with two single quotation marks.

The processing of SQL statement of ③ page turning

It is relatively simple for MYSQL to deal with the SQL statement of page turning, using the starting position of LIMIT to record the number, while ORACLE is more cumbersome to deal with the SQL statement of page turning.

Processing of ④ Null characters

Non-empty fields in MYSQL can also have empty content, and non-empty fields defined in ORACLE do not allow empty content.

Fuzzy comparison of ⑤ strings

The field name like'% string%'is used in MYSQL; the field name like'% string% 'can also be used in ORACLE, but this method does not use indexing and is not fast.

These are all the contents of the difference between mysql and oracle. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.