The main differences between Oracle and Mysql

Oracle and MySQL are common relational database management systems (RDBMS), but they are very different in architecture, functionality, performance, usage scenarios, etc. Here are their main differences:

1. Basic differences

2. Architecture and Storage

3. SQL syntax and functions

4. Performance and Extension

5. Use scenarios

Summarize

• OracleSuitableLarge enterprise-level applications,likeBanking, telecommunications, government, financeand have stronger transaction processing capabilities and high availability.
• MySQLSuitableSmall and medium-sized applications, Internet companies,likeWebsite backend, data storage, log analysis, easy deployment and good performance.

If your project isHigh concurrency, high transaction volume, complex SQL computing,chooseOracleMore suitable; if soWeb development, lightweight data storage, MySQL is a more economical choice.

Differences in SQL syntax

Here are some specific differences between Oracle and MySQL in SQL syntax and features, covering common SQL statements and features.

1. Data Type

2. Automatic growth field

Oracle: Not supportedAUTO_INCREMENT, usually usedSEQUENCEandTRIGGERTo simulate.

CREATE SEQUENCE seq_name;
CREATE TABLE test_table (
  id NUMBER DEFAULT seq_name.NEXTVAL
);

MySQL:useAUTO_INCREMENTto define self-increment columns.

CREATE TABLE test_table (
  id INT AUTO_INCREMENT PRIMARY KEY
);

3. String concatenation

Oracle:use||to concatenate strings.

SELECT 'Hello ' || 'World' FROM dual;

MySQL:useCONCAT()Function to concatenate strings.

SELECT CONCAT('Hello ', 'World');

4. Sequence and self-increase

Oracle:useSEQUENCEto generate self-value-added.

CREATE SEQUENCE seq_name;
SELECT seq_name.NEXTVAL FROM dual;

MySQL:passAUTO_INCREMENTDirectly generate self-value-added.

CREATE TABLE test_table (
  id INT AUTO_INCREMENT PRIMARY KEY
);

5. Subquery

Oracle:supportWITHSubqueries (common table expressions) and complex subqueries.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id
)
SELECT , 
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE  > d.avg_salary;

MySQL: SupportedWITHSubqueries (MySQL 8.0 and above), but not supported in older versions.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id
)
SELECT , 
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE  > d.avg_salary;

6. Functions and stored procedures

Oracle:usePL/SQLWrite stored procedures, functions, and triggers.

CREATE OR REPLACE PROCEDURE increase_salary(p_id IN NUMBER, p_amount IN NUMBER) AS
BEGIN
  UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_id;
END;

MySQL:useMySQL stored procedures, the syntax is simpler.

DELIMITER //
CREATE PROCEDURE increase_salary(IN p_id INT, IN p_amount DECIMAL)
BEGIN
  UPDATE employees SET salary = salary + p_amount WHERE employee_id = p_id;
END //
DELIMITER ;

7. Foreign key constraints

Oracle: Fully support for foreign key constraints and can use composite foreign keys.

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  customer_id NUMBER,
  CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

MySQL：InnoDBThe engine supports foreign keys, butMyISAMForeign keys are not supported.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

8. Aggregation function

Oracle:supplyLISTAGGetc. to handle string aggregation.

SELECT department_id, LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_names
FROM employees
GROUP BY department_id;

MySQL:useGROUP_CONCAT()to perform similar string aggregation.

SELECT department_id, GROUP_CONCAT(employee_name ORDER BY employee_name) AS employee_names
FROM employees
GROUP BY department_id;

9. Pagination query

Oracle:useROWNUMorFETCH FIRSTCome to paginate.

SELECT * FROM (SELECT employees.*, ROWNUM rnum FROM employees) WHERE rnum BETWEEN 10 AND 20;

OR

SELECT * FROM employees FETCH FIRST 10 ROWS ONLY;

MySQL:useLIMITto paginate.

SELECT * FROM employees LIMIT 10, 20;

10. Missing SQL Features

Oracle：

• supportComposite index、Bitmap index、External tableAdvanced features.
• supportRAC（Real Application Clusters）、Data GuardHigh availability technologies.

MySQL：

• Full text index(Only inInnoDBandMyISAMvalid).
• Supports simpler cluster solutions, such asMaster-slave copyandPartition table, but there is no native support for complex distributed clustering and fault tolerance mechanisms.

11. Error handling

Oracle:useEXCEPTIONBlocks perform error handling.

BEGIN
  -- some operations
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- handle error
END;

• MySQL: Error handling is relatively concise, usually throughDECLAREandHANDLERconduct
• DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
•   -- handle error

This is the end of this article about the difference between Oracle and Mysql. For more information about the differences between Oracle and Mysql, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!