Detailed explanation of the misunderstanding between MySQL and Oracle

Essential Difference

• Oracle Database is an object-relational database management system (chargeable)
• MySQL is an open source relational database management system (free)

Database security

• MySQL uses three parameters to authenticate a user, namely username, password and location
• Oracle uses more security features such as username, password, profile, local authentication, external authentication, advanced security enhancements, etc.

Permissions

The MySQL permission system is a hierarchical structure formed by inheritance. When permissions are granted to a higher level, other lower levels implicitly inherit the permissions granted. Of course, the lower levels can also rewrite these permissions.

Depending on the authorization scope, MySQL has the following authorization methods:

1. Global;

2. Based on each host;

3. Table-based;

4. Based on table columns.

Each level has an authorization table in the database. When performing permission checks, MySQL checks each table from high to low, and lower-range authorizations take precedence over higher-range authorizations.

Unlike Oracle, MySQL does not have the concept of roles. That is to say, if the same permissions are granted to a group of users, each user needs to be authorized separately.

Schema Migration

A schema contains tables, views, indexes, users, constraints, stored procedures, triggers, and other database-related concepts. Most relational databases have similar concepts.

The following contents are included:

1. Similarity of pattern objects;

2. The name of the schema object;

3. Focus on table design;

4.Multiple database integration;

5. Concerns about MySQL schema integration.

Similarity of pattern objects

In terms of schema objects, Oracle and MySQL store many similarities, but there are also some differences.

The name of the schema object

Oracle is case-insensitive, and schema objects are stored in-line on write. In the Oracle world, columns, indexes, stored procedures, triggers, and column aliases are all case-insensitive, and this is true on all platforms. MySQL is case sensitive, such as the storage path relative to the database and the files corresponding to the table.

Both Oracle and MySQL allow you to use keywords with schema objects when you enclose them in quotes. But for some keywords in MySQL, it is OK without quotes.

Table design concerns

1. The type of character data;

2. Column default value.

3. Character Data Type

(1) Oracle supports four font types: CHAR, NCHAR, NVARCHAR2, and VARCHAR2. The maximum length of CHAR and NCHAR is 2000 bytes.

The maximum length of NVARCHAR2 and VARCHAR2 is 4000 bytes.

(2) There are some differences between MySQL and Oracle in saving and reading character data. The length of MySQL character types such as CHAR and VARCHAR is less than 65535 bytes. Oracle supports four font types: CHAR, NCHAR, NVARCHAR2, and VARCHAR2. The maximum length of CHAR and NCHAR is 2000 bytes, and the maximum length of NVARCHAR2 and VARCHAR2 is 4000 bytes.

MySQL handles column default values ​​and does not allow them to be empty, which is different from Oracle. In Oracle, if you insert data into a table, you need to have values ​​for all columns that do not allow NULL.

Multi-database migration

If multiple MySQL databases are located on the same database service, migration is supported.

Data storage concepts

The MySQL database corresponds to a directory within the data directory on the server. This data storage method is different from that of many databases, including Oracle. The table in the database corresponds to one or more files in the database directory and uses the storage engine when storing the table.

An Oracle database contains one or more tablespaces. Tablespaces correspond to the physical storage of data on disk. A tablespace is constructed from one or more data files. A data file is a file in the file system or a piece of raw storage space.

Grammatical Differences

Primary Key:

• MySQL generally uses the automatic growth type. When creating a table, specify the primary key of the table as auto increment, and the primary key will automatically grow.
• There is no automatic growth in Oracle. The primary key generally uses a sequence, and the values ​​can be assigned sequentially during interpolation.

Quote mark problem:

• Oracle does not use double quotes and will report an error
• MySQL has no restrictions on quotation marks

Pagination query:

• MySQL paging query uses the keyword limit to implement
• Oracle does not implement keywords for paging queries, so the implementation is more complicated. In each result set, there is only one rownum field to indicate its position, and you can only use rownum<= a certain number, not rownum>= a certain number, because ROWNUM is a pseudo column. When using it, you need to give ROWNUM an alias to turn it into a logical column and then operate on it.

Data Type:

• Integer type in MySQL: int(), string type: varchar()
• Integer in Oracle: number(), string type: varchar2()

The above is a detailed explanation of the misunderstandings between Mysql and Oracle. For more information about the misunderstandings between Mysql and Oracle, please pay attention to other related articles on 123WORDPRESS.COM!