Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the essential difference between MySQL and Oracle". In daily operation, I believe that many people have doubts about the essential difference between MySQL and Oracle. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the essential difference between MySQL and Oracle". Next, please follow the editor to study!

Essential difference:

Oracle database is an object-relational database management system (fee)

MySQL is an open source relational database management system (free)

Security of the database:

Mysql uses three parameters to authenticate the user, namely, the user name, password, and location

Oracle uses more security features, such as user names, passwords, profiles, local authentication, external authentication, advanced security enhancements, etc.

Authority

The permission system of MySQL is a hierarchical structure formed by inheritance. When permissions are granted at a higher level, other lower levels implicitly inherit the granted permissions, and of course, the lower levels can overwrite these permissions.

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

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

Overall situation

On a per host basis

Based on table

Based on table columns.

Each level has an authorization table in the database. When doing permission checks, MySQL checks every table from high to low, and low-scope authorization takes precedence over high-scope authorization.

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

Pattern migration

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

The contents are as follows:

Similarity of pattern objects

The name of the schema object

The focus of table design

Multi-database integration

The focus of MySQL schema integration.

Similarity of pattern objects

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

The name of the schema object

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

When keywords are enclosed in quotation marks, both Oracle and MySQL allow these keywords to be used for schema objects. But MySQL can do without quotation marks for some keywords.

The focus of table design

1. The type of character data

2. Column default value.

3.2.3.1 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 character types of MySQL, such as CHAR and VARCHAR, are less than 65535 bytes long. 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.

(3.2.3.2 column defaults)

Unlike Oracle, MySQL handles column defaults and does not allow them to be empty. If you insert data into a table in Oracle, you need to have all values that are not allowed to be NULL columns.

Multi-database migration

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

Data storage concept

The database of MySQL corresponds to the directory in the data directory on the server, which is different from the multi-data database, including Oracle. The tables in the database correspond to the files in one or more database directories and fetch the storage engine when the tables are stored.

An Oracle database contains one or more tablespaces. The tablespace corresponds to the physical storage of data on disk. Tablespaces are built from one or more data files. A data file is a file in a file system or a piece of space in the original storage.

Grammatical differences:

Primary key:

Mysql generally uses the auto-grow type. If you specify the primary key of the table as auto increment when you create the table, the primary key will grow automatically.

There is no automatic growth in Oracle. Primary keys generally use sequences and can be assigned in turn during interpolation.

Quotation mark question:

Oracle does not use double quotation marks and will report an error

Mysql has no restrictions on quotation marks.

Paging query:

MySQL paging query is implemented using the keyword limit

Oracle does not implement the keyword of paging query, so it is complicated to implement. In each result set, there is only one rownum field indicating its location, and only a certain number of rownum= can be used. Because ROWNUM is a pseudo column, you need to take an alias for ROWNUM when using it, change it into a logical column, and then operate it.

Data type:

Integer in mysql: int (), string type: varchar ()

Shaping in Oracle: number (), string type: varchar2 ()

At this point, the study of "the essential difference between MySQL and Oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.