Is there any difference between mysql and oracle?

There are differences between mysql and oracle: 1. Oracle is an object-relational database management system (ORDBMS), while MySQL is a relational database management system (RDBMS); 2. Oracle is closed source (charged), and MySQL It is open source (free); 3. Oracle is a large database, while MySQL is a small and medium-sized database; 4. Oracle can set user permissions, access permissions, read and write permissions, etc., but MySQL cannot; 5. Oracle has table spaces, but MySQL does not .

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

MySQL and Oracle are both popular relational database management systems (RDBMS) widely used around the world; most databases work in a similar way, but MySQL and Oracle are always present here and there Some differences.

The specific difference between mysql and oracle

1. The essential difference

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

MySQL is an open source relational database management system (RDBMS). 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. Oracle database products are closed source and chargeable, while MySQL is an open source project (free);

3. Oracle is a large database, and Mysql is a small and medium-sized database;

4. Oracle can set user permissions, access permissions, read and write permissions, etc., but MySQL does not;

5. Oracle has the concept of table space, but MySQL does not;

6. Oracle defaults It is not submitted automatically and needs to be submitted manually by the user. Mysql defaults to automatic submission.

7. Oracle does not lock data during logical backup, and the backed up data is consistent. The data needs to be locked during Mysql logical backup to ensure that the backed up data is consistent and affects the normal use of the business.

8. 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.; Mysql has few diagnostic and tuning methods, mainly slow query logs.

9. Oracle has unlimited capacity, which is determined by configuration; while MySQL uses the MyISAM storage engine, and the maximum table size is 65536TB. The maximum effective table size of a MySQL database is usually determined by the operating system's file size limitations, rather than by MySQL internal limitations.

10. Oracle's performance under Linux is lower than MySQL when a small amount of data is used, and faster than MySQL when using tens of millions of data.

11. Oracle is comprehensive, complete, and stable, but generally has a large amount of data and has high hardware requirements; while MySQL uses very little CPU and memory and has high performance, but poor scalability.

12. It is relatively simple for MySQL to process the SQL statement for page turning. Use LIMIT to start the position and record the number. Oracle's processing of page turning SQL statements is more cumbersome. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM<100 can be used, but ROWNUM>80 cannot be used.

13. MySQL date field is divided into DATE and TIME. Oracle date field only has DATE, which contains year, month, day, hour, minute and second information. Use the system time of the current database as SYSDATE, accurate to the second.

14. The difference between SQL statements:

• mysql does not need from, Oracle must have it, and can be written as from dual;

• mysql uses the concat() function to connect strings. Oracle not only has the concat() function, but you can also use ||;

• mysql does not have a full outer connection, use a set connection instead, oracle has Full outer joins, and the left and right outer joins have their own syntax: ( );

• Mysql uses limit for paging, and Oracle paging relies on the rownum keyword;

• mysql automatically increases auto_increment, oracle uses sequences instead;

• mysql directly writes loop judgment statements, oracle has to use PLSQL statements;

• Mysql uses elseif for judgment, and oracle uses elsif;

• mysql can directly add, delete, and modify, oracle needs to commit;

• group by, use it under oracle For group by, the fields after group by must appear after select, otherwise an error will be reported, but mysql will not;

[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!