Summary of the differences between MySQL and Oracle (comparison of functional performance, selection, SQL when using them, etc.)

1. Concurrency

Concurrency is the most important feature of OLTP database, but concurrency involves the acquisition, sharing and locking of resources.

mysql:
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, the mechanism of this row-level lock depends on the index of the table. If the table has no index or the SQL statement does not use the index, table-level locks are still used.

oracle:
Oracle uses row-level locks, which have a much smaller granularity for resource locking. It only locks the resources required by SQL, and the lock is on the data row in the database, not dependent on the index. Therefore, Oracle's support for concurrency is much better.

2. Consistency

oracle:
Oracle supports the serializable isolation level, which can achieve the highest level of read consistency. After each session is submitted, other sessions can see the submitted changes. Oracle achieves read consistency by constructing multi-version data blocks in the undo tablespace.
When each session queries, if the corresponding data block changes, Oracle will construct the old data block for this session in the undo tablespace.

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

3. Affairs

Oracle has fully supported transactions for a long time.

MySQL supports transactions only with row-level locks in the InnoDB storage engine.

4. Data persistence

oracle
Ensure that submitted data can be recovered, because Oracle writes the submitted SQL operation lines into the online log file and saves them on disk.
If the database or host restarts abnormally, Oracle can use the online log to recover the data submitted by the customer after the restart.
mysql:
By default, SQL statements are submitted, but if there is a problem with the db or host restart during the update process, data may be lost.

5. Submission Method

Oracle does not automatically submit by default and requires the user to submit manually.
The default for mysql is auto-commit.

6. Logical backup

Oracle does not lock data during logical backup, and the backed up data is consistent.

When performing a logical backup of MySQL, the data must be locked to ensure that the backed-up data is consistent, which affects the normal use of DML in the business.

7. Hot backup

Oracle has a mature hot standby tool, RMAN, which does not affect users' use of the database during hot standby. Even if the backed up database is inconsistent, it can be restored consistently through archive logs and online redo logs.
mysql:
When using the mysqlhostcopy hot standby function of mysql with the myisam engine, you need to add a read lock to the table, which affects the dml operation.
Innodb engine, it will back up innodb tables and indexes, but will not back up .frm files. When backing up with ibbackup, a log file will be created to record data changes during the backup period, so there is no need to lock the table and it will not affect other users' use of the database. But this tool is charged.
innobackup is a script used in conjunction with ibbackup that assists in backing up .frm files.

8. Extension and flexibility of SQL statements

MySQL has many very practical and convenient extensions to SQL statements, such as the limit function, the insert function that can insert multiple rows of data at a time, and the select function that can select certain management data without adding from.
Oracle feels more stable and traditional in this regard.

IX. Copy

Oracle: It has 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 backup database can be automatically switched to the main database, but the configuration management is more complicated.
mysql: The replication server configuration is simple, but when the main database has problems, the cluster database may lose some data. And you need to manually switch the cluster database to the main database.

10. Performance Diagnosis

Oracle has a variety of mature performance diagnosis and tuning tools that can implement many automatic analysis and diagnosis functions. Such as awr, addm, sqltrace, tkproof, etc.
There are few diagnostic and tuning methods for MySQL, mainly slow query logs.

11. Permissions and Security

The mysql user is related to the host, which seems meaningless. In addition, it is easier for the host and IP to be counterfeited.
Oracle's permissions and security concepts are relatively traditional and standard.

12. Partition tables and partition indexes
Oracle's partition table and partition index functions are very mature and can improve the user experience of accessing the DB.
MySQL's partition table is not yet mature and stable.

13. Management Tools
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.
There are fewer MySQL management tools. The installation of management tools under Linux sometimes requires the installation of additional packages (phpmyadmin, etc.), which is somewhat complicated.

14. Technical Support

If there is a problem with Oracle, you can contact customer service

Solve mysq problems yourself

15. Authorization

Oracle charges

mysq open source - free

16. Choice

If you have money, I suggest you use Oracle.

If you don’t have money but can meet your needs, I recommend using mysq. (Alibaba, Wikipedia and other large projects also use MySQL, and they mainly use distributed storage, caching, table and database sharding and other technologies)

Here is another article, mainly comparing from the sql aspect:

The difference between Oracle database and MySQL database is the main content of this article. I hope it can be helpful to you.

1. Group function usage rules

In MySQL, group functions can be used freely in select statements, but in Oracle, if there is a group function in the query statement, the other column names must be processed by the group function or be columns in the group by clause, otherwise an error will be reported.

For example:

select name, count(money) from user; This works fine in MySQL but has problems in Oracle.
And select name, count(money) from user group by name or select max(name), count(money) from user;
There will be no error in Oracle, and there will be no error in MySQL in the same way.

2. Automatic growth data type processing

MYSQL has an automatically growing data type. When inserting records, you do not need to operate this field, and the data value will be automatically obtained. ORACLE does not have an automatically increasing data type, so you need to create an automatically increasing sequence number and assign the next value of the sequence number to this field when inserting a record.

CREATE SEQUENCE sequence number name (preferably table name + sequence number tag) INCREMENT BY 1 START WITH 1 MAXVALUE 99999 CYCLE NOCACHE;

The maximum value is determined by the length of the field. If the automatically increasing sequence number NUMBER(6) is defined, the maximum value is 999999

The INSERT statement inserts this field value: the name of the serial number.NEXTVAL

3. Processing of single quotes

In MYSQL, you can use double quotes to enclose strings, but in ORACLE, you can only use single quotes to enclose strings. Before inserting and modifying a string, single quotes must be replaced: all occurrences of a single quote are replaced with two single quotes.

4. Processing of SQL statements for page turning

The SQL statement for MYSQL to process page turning is relatively simple. Use LIMIT to set the starting position and record the number. In PHP, you can also use SEEK to locate the position of the result set. The SQL statements for ORACLE to process page turning are rather complicated. Each result set has only one ROWNUM field to indicate its position, and only ROWNUM<100 can be used, not ROWNUM>80.

The following are two better ORACLE page turning SQL statements after analysis (ID is the field name of the unique keyword):

Statement 1:

SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM (SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE condition1 ORDER BY condition2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY condition3;

Statement 2:

SELECT * FROM (( SELECT ROWNUM AS NUMROW, c.* from (select [FIELD_NAME,...] FROM TABLE_NAME WHERE condition1 ORDER BY condition2) c) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY condition3;

5. Processing of long strings

ORACLE also has its own special features in processing long strings. The maximum operable string length for INSERT and UPDATE is less than or equal to 4000 single bytes. If you want to insert a longer string, consider using the CLOB type for the field, using the DBMS_LOB package that comes with ORACLE. Before inserting or modifying records, you must perform non-empty and length checks. Field values ​​that cannot be empty and field values ​​that exceed the length should issue a warning and return to the last operation.

6. Processing of date fields

MYSQL date fields are divided into DATE and TIME. ORACLE date fields only have DATE, which contains year, month, day, hour, minute, and second information. The system time of the current database is SYSDATE, accurate to seconds, or the string is converted into a date type function TO_DATE('2001-08-01','YYYY-MM-DD') Year-month-day 24 hours: minutes: seconds format YYYY-MM-DD HH24:MI:SS TO_DATE() There are many other date formats, you can refer to ORACLE DOC. Date field conversion string function TO_CHAR('2001-08-01','YYYY-MM-DD HH24:MI:SS')

Mathematical formulas for date fields are very different. MYSQL finds the date 7 days from the current time using DATE_FIELD_NAME > SUBDATE(NOW(), INTERVAL 7 DAY) ORACLE finds the date 7 days from the current time using DATE_FIELD_NAME > SYSDATE - 7;

Several functions in MYSQL that insert the current time are: The NOW() function returns the current date and time as `'YYYY-MM-DD HH:MM:SS', which can be directly stored in the DATETIME field. CURDATE() returns today's date in the format of 'YYYY-MM-DD' and can be stored directly in a DATE field. CURTIME() returns the current time in the format of 'HH:MM:SS' and can be stored directly in the TIME field. Example: insert into tablename (fieldname) values ​​(now())

The current time in Oracle is sysdate

7. Handling of Null Characters

MYSQL's non-empty fields also have empty contents, but ORACLE does not allow empty contents in non-empty fields. Defining the ORACLE table structure according to MYSQL's NOT NULL will result in errors when importing data. Therefore, when importing data, you need to judge the empty character. If it is NULL or an empty character, you need to change it to a string of spaces.

8. Fuzzy comparison of strings

In MYSQL, use field name like%' string %'. In ORACLE, you can also use field name like%' string %', but this method cannot use indexes and is not fast. Using the string comparison function instr(field name, ' string ')>0 will get more accurate search results.

9. In programs and functions , please pay attention to releasing result sets and pointers after completing database operations.