[Selected] A comprehensive analysis of the different SQL commands, syntax, and transactions between Oracle and MySQL

[Selected Article] SQL commands and syntax differences between Oracle and MySQL

    • 1. Connect to the database
    • 2. Get the current date and time
    • 3. Date and time functions
    • 4. String concatenation
    • 5. Auto-increment primary key
    • 6. Differences in table field types
    • 7. LIMIT and ROWNUM (for paging)
    • 8. Get the last inserted ID
    • 9. String comparison
    • 10. Foreign key constraints
    • 11. Transaction submission
    • 12. Transaction isolation level
    • 13. Transaction support
    • 14. Concurrency support

1. Connect to the database

  • Oracle
# sqlplus username/password@host IP:port number/instance name
# sqlplus oracle/[email protected]:1521/db_test
sqlplus username/password@database_name
  • MySQL
# Local connection
mysql -u username -p password
# remote connection
mysql -u username -p password -h mysql_server_ip_address

2. Get the current date and time

  • Oracle
 SELECT SYSDATE FROM DUAL;

The above code query will return the current date and time, as well as other relevant information.

DUAL is a virtual table used to execute queries without an actual table

This query can be nested within other queries or assigned to a variable for use in stored procedures or triggers.
For example, if you want to insert the current date and time into a table, you can do the following:

INSERT INTO table_name(date_column) VALUES (SYSDATE);

This will insert the current date and time in the date_column column of the table named table_name.

Note: SYSDATE returns a date and time, typically year, month, day, hour, minute, second, and millisecond precision, depending on the database settings. It can be formatted or otherwise manipulated as needed.

  • MySQL
SELECT NOW();

3. Date and time functions

Both Oracle and MySQL support date and time functions, but the function names and syntax may differ slightly.

-----------------------Orcale----------------------- ----
# Get the current date and time
SELECT SYSDATE FROM DUAL;

# Get the current date
SELECT TRUNC(SYSDATE) FROM DUAL;

# Get the current time
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;

-----------------------MySQL-------------------------- -
# Get the current date and time
SELECT CURRENT_TIMESTAMP;

# Get the current date
SELECT CURRENT_DATE;

# Get the current time
SELECT CURRENT_TIME;

4. String concatenation

  • Oracle: Use the || operator to concatenate strings, as follows:
SELECT 'Hello ' || 'World' FROM DUAL;
  • MySQL: Use the CONCAT() function as follows: 
 SELECT CONCAT('Hello ', 'World');

5. Auto-increment primary key

  • Oracle:

(1) Use Sequence and Triggers to implement auto-incrementing primary keys.

① Create Sequence:

In Oracle, a sequence is an object used to generate unique increasing or decreasing numbers. Sequences can be created using the following syntax:

# Create sequence (Sequence)
CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE min_value
MAXVALUE max_value
NOCACHE;

# sequence_name: The name of the sequence.
# initial_value: The initial value of the sequence.
# increment_value: The increment value of the sequence.
# min_value: The minimum value of the sequence.
# max_value: The maximum value of the sequence.
# NOCACHE: Do not cache sequence values.

② Create a trigger (Trigger):

A trigger is a database object used to define automated actions on a table. Triggers can be created using the following syntax:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
SELECT sequence_name.NEXTVAL INTO :new.column_name FROM dual;
END;

# trigger_name: The name of the trigger.
# table_name: The name of the table.
# column_name: The name of the auto-incremented primary key column.

(2) Starting from Oracle 12c, the IDENTITY column is introduced, which can be used for automatically incrementing primary key fields. Using IDENTITY columns simplifies the implementation of auto-increment fields.

CREATE TABLE table_name (
  primary_key_column NUMBER GENERATED ALWAYS AS IDENTITY,
  --Other column definitions
);

The above code uses GENERATED ALWAYS AS IDENTITY in the table’s column definition to create an auto-incrementing primary key field.

  • MySQL: Use the AUTO_INCREMENT keyword to define an auto-incrementing primary key column.
CREATE TABLE table_name (
  `id` BIGINT(19) UNSIGNED NOT NULL AUTO_INCREMENT,
  --Other class definitions
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

6. Differences in table field types

  • MySQL supports numerical types such as int, float, double, and varchar, char characters Type, date, datetime, time, year, timestamp and other date types.
  • Oracle supports number numeric type, varchar2, varchar, char character type, date Date type etc.

Among them, char(2) is defined like this. This unit represents two bytes in Oracle and two characters in mysql. The length of Varchar must be given in mysql. For example: varchar(10) .

7. LIMIT and ROWNUM (for paging)

  • Oracle: Use ROWNUM or ROWID for paging operations, usually requiring subqueries.
    You need to use shoddy rownum and nested queries. Oracle uses the rownum field to indicate the position, and you can only use less than, not greater than.

Suppose there is a table named employees and we want to paginate by employees' names in alphabetical order. Here is sample code:

SELECT * FROM (
    SELECT e.*, ROWNUM AS rnum
    FROM employees e
    ORDER BY e.last_name
)
WHERE rnum BETWEEN 11 AND 20;

The steps for this query are as follows:

The inner subquery sorts employees alphabetically by last name and assigns each row a ROWNUM.
The outer query selects ROWNUM rows from 11 to 20, which implements paging and returns the data from rows 11 to 20.

  • MySQL: Use the LIMIT clause to limit the number of rows in the result set, as follows:
SELECT * FROM table_name LIMIT 10;

8. Get the last inserted ID

  • Oracle: Use the RETURNING INTO clause to get the last inserted ID.
# Create a table
CREATE TABLE table_name (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    data VARCHAR2(50)
);

#Insert a row of data and get the inserted ID
DECLARE
    last_id NUMBER;
BEGIN
    INSERT INTO table_name (data) VALUES ('Some data')
    RETURNING id INTO last_id;
    DBMS_OUTPUT.PUT_LINE('Last Inserted ID: ' || last_id);
END;
/ # Used to execute previously defined PL/SQL code blocks

The above example code explanation:

① Create a table table_name, in which the id column uses GENERATED ALWAYS AS IDENTITY to specify an automatically generated unique ID.
② Insert a row of data and use the RETURNING INTO clause to store the inserted ID in the last_id variable.

  • MySQL: Use the LAST_INSERT_ID() function to get the last inserted ID.
# Create a table
CREATE TABLE table_name(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(50)
);

#Insert a row of data and get the inserted ID
INSERT INTO example_table (data) VALUES ('Some data');

# Get the last inserted ID
SELECT LAST_INSERT_ID();

The above example code explanation:

① Create a table table_name, in which the id column uses AUTO_INCREMENT and PRIMARY KEY to specify an automatically generated unique ID. .
② When inserting data, MySQL will automatically assign the next available ID. To get the last inserted ID, use the LAST_INSERT_ID() function.

9. String comparison

  • Oracle is case sensitive by default, you need to use the COLLATE clause to perform case-insensitive comparisons.
--case-sensitive comparison (default)
SELECT name
FROM table_name
WHERE name = 'test';

-- Case-insensitive comparison (use COLLATE)
SELECT name
FROM table_name
WHERE name COLLATE BINARY_CI = 'test'; # BINARY_CI represents case-insensitive comparison
  • MySQL is case insensitive by default, but you can use the COLLATE clause to perform case-sensitive comparisons.
--case-insensitive comparison (default)
SELECT name
FROM table_name
WHERE name = 'test';

-- Case-sensitive comparison (using COLLATE)
SELECT name
FROM table_name
WHERE name COLLATE utf8_bin = 'test'; # utf8_bin is a case-sensitive comparison

To perform a case-insensitive comparison, use the COLLATE clause and specify the appropriate COLLATION
For example:

  • BINARY_CI means case-insensitive comparison
  • utf8_bin is a case-sensitive comparison

10. Foreign key constraints

Both Oracle and MySQL support foreign key constraints, but the specific syntax and operations may be different.

  • Oracle:
--Create parent table
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(50)
);

--Create a subtable, including foreign key constraints
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

In Oracle, foreign key constraints are usually defined using the CONSTRAINT keyword when creating a table. You can specify the name of the foreign key (for example, fk_dept) and the foreign key reference. Parent table and columns.
In the above example, the department_id column of the employees table is a foreign key that references the department_id of the departments table List.

  • MySQL
--Create parent table
CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(50)
);

--Create a subtable, including foreign key constraints
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

In MySQL, foreign key constraints can be inlined into column definitions when creating the table, without the need for additional CONSTRAINT keywords.
In the above example, the department_id column of the employees table is a foreign key that references the department_id of the departments table List.

11. Transaction submission

  • Oracle does not automatically submit by default. You need to submit manually. You need to write the commit command or click the commit button.
  • MySQL defaults to automatic submission, which can be modified to manual submission.

Here is sample code on how to perform manual commit in these two databases and set MySQL to manual commit:

  • Oracle
--Create a sample table
CREATE TABLE table_name(
    id NUMBER PRIMARY KEY,
    data VARCHAR2(50)
);

-- Insert data and submit manually
BEGIN
    INSERT INTO table_name(id, data) VALUES (1, 'Data 1');
    -- Manually commit the transaction
    COMMIT;
END;
/

In Oracle, it is not automatically committed by default, you need to use the COMMIT command to manually commit the transaction. In the above example, we use COMMIT; to manually commit the transaction after inserting the data.

  • MySQL
# Switch to manual submission mode
SET autocommit = 0;

#Create a sample table
CREATE TABLE table_name(
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(50)
);

#Insert data
INSERT INTO table_name(data) VALUES ('Data 1');

# Manually commit the transaction
COMMIT;

In MySQL, it is autocommitted by default, which means that each SQL statement is immediately committed as a separate transaction. To switch to manual commit mode, use the SET autocommit = 0; command. You can then use the COMMIT; command to manually commit the transaction.

12. Transaction isolation level

  • Oracle’s default isolation level is repeatable read (repeatable read)
  • The default isolation level of MySQL is read committed (read committed)

The following is sample code on how to set and demonstrate the default isolation level in Oracle and MySQL:

  • Oracle
# Query the isolation level of the current database
SELECT s.sid, s.serial#,
       CASE t.xidusn
           WHEN 0 THEN 'Read Committed'
           ELSE 'Serializable'
       END AS isolation_level
  FROM v$session s
       JOIN v$transaction t
       ON s.saddr = t.ses_addr
 WHERE s.sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

In Oracle, the default isolation level is "Serializable" (serializable). The above example code queries the isolation level of the current session. If it is 0, then the isolation level is "Read Committed". Otherwise, it is "Serializable".

  • MySQL
# Query the isolation level of the current database
SELECT @@global.tx_isolation AS isolation_level;

In MySQL, the default isolation level is "REPEATABLE READ" (repeatable read). The above example code queries the isolation level of the current database, and its default value should be "REPEATABLE READ".

13. Transaction support

  • Oracle fully supports transactions
# Start transaction
BEGIN
    #Insert data
    INSERT INTO example_table (id, data) VALUES (1, 'Data 1');
    INSERT INTO example_table (id, data) VALUES (2, 'Data 2');

    # Submit transaction
    COMMIT;
END;

In Oracle, transactions are supported by default. You can use BEGIN and COMMIT blocks to define and submit transactions to ensure that a set of operations either all succeed or all fail.
The above example demonstrates a transaction containing insert operations. If one of the inserts fails, the entire transaction will be rolled back.

  • MySQL can only support transactions with row-level locks in the Innodb storage engine.
# Switch to InnoDB storage engine
ALTER TABLE table_name ENGINE = InnoDB;

# Start transaction
START TRANSACTION;

#Insert data
INSERT INTO table_name (id, data) VALUES (1, 'Data 1');
INSERT INTO table_name (id, data) VALUES (2, 'Data 2');

# Submit transaction
COMMIT;

**In MySQL, transaction support depends on the storage engine. **By default, the InnoDB storage engine supports transactions, while the MyISAM storage engine does not. The above example demonstrates how to use the InnoDB storage engine’s row-level locks to perform transactions.
Transactions can be defined and committed using START TRANSACTION and COMMIT.

14. Concurrency support

  • 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 depend on the index.
  • MySQL mainly uses table-level locks, and the granularity of resource locking is very large. Although the tables of the InnoDB engine can use row-level locks, this row-level locking mechanism depends on the index of the table. If the table does not index, then table-level locks are still used.
    Therefore, in order to implement row-level locking, MySQL usually needs to ensure that the table has appropriate indexes.

So Oracle’s support for concurrency is much better.