Differences between Oracle and MySQL: oraclemysql

Differences between Oracle and MySQL: oraclemysql
Common function differences

1. Lock differences:

• Add Oracle locks to data blocks

• InnoDB locks the index, so the granularity of MySQL locks is not as fine as that of Oracle.

2. Import and export:

• Oracle adopts EXP/IMP, EXPDP/IMPDP for import and export.

• MySQL uses mysqldump for export and can use pipelines or sources for import.

3. commit:

• Oracle manual submission by default

• MySQL is automatically submitted by default.

4. SQL cache: MySQL can only cache result sets, but cannot cache SQL parsing results

5. database objects:

• Oracle compiles and stores database objects and directly executes binary codes

• MySQL only stores code and performs temporary parsing. Therefore, when creating MySQL triggers, stored procedures, functions, and other objects, it only checks the syntax and does not check the logic.

6. transactions:

• MySQL does not support all engines. We recommend that you use InnoDB first, which provides better concurrency than other engines.

Differences in SQL writing

1. Empty string processing:

• Oracle only has the concept of NULL.

• MySQL Empty strings ''and NULL. The empty strings are not equal to NULL.

2. limit on the number of result sets:

• Oracle uses rownum

• MySQL uses limit;

3. Execute SQL:

• Oracle requires select func () from dual;

• MySQL can directly select func ();

4. Modify the field type or length:

• Oracle: alter table tablename modify column ...;

• MySQL: alter table tablename modify (...);

5. Copy Data:

• Oracle as is required, create table table1 as select * from table2;

• MySQL's as is optional. create table table1 select * from table2;

6. string processing:

• Enclose strings in single quotes in Oracle

• MySQL uses double quotation marks to enclose strings