MySQL MySQL中架构/数据库之间的区别

Difference Between Schema / Database in MySQL

提问by Lloyd Banks

Is there a difference between a schema and a database in MySQL? In SQL Server, a database is a higher level container in relation to a schema.

MySQL中的模式和数据库之间有区别吗？在 SQL Server 中，数据库是与架构相关的更高级别的容器。

I read that Create Schemaand Create Databasedo essentially the same thing in MySQL, which leads me to believe that schemas and databases are different words for the same objects.

我读了它，Create Schema并Create Database在 MySQL 中做了基本相同的事情，这让我相信模式和数据库是相同对象的不同词。

回答by eggyal

As defined in the MySQL Glossary:

如MySQL 词汇表中所定义：

In MySQL, physically, a schemais synonymous with a database. You can substitute the keyword SCHEMAinstead of DATABASEin MySQL SQL syntax, for example using CREATE SCHEMAinstead of CREATE DATABASE.

Some other database products draw a distinction. For example, in the Oracle Database product, a schemarepresents only a part of a database: the tables and other objects owned by a single user.

在 MySQL 中，在物理上，模式与数据库是同义词。您可以在 MySQL SQL 语法中替换关键字SCHEMA而不是DATABASE，例如使用CREATE SCHEMA代替CREATE DATABASE。

其他一些数据库产品有所区别。例如，在 Oracle 数据库产品中，模式仅代表数据库的一部分：单个用户拥有的表和其他对象。

回答by user2631022

Depends on the database server. MySQLdoesn't care, its basically the same thing.

取决于数据库服务器。MySQL无所谓，基本一样。

Oracle, DB2, and other enterprise level database solutions make a distinction. Usually a schema is a collection of tablesand a Database is a collection of schemas.

Oracle、DB2和其他企业级数据库解决方案有所区别。通常一个模式是表的集合和一个数据库是一个模式的集合。

回答by Akash KC

Refering to MySql documentation,

参考MySql 文档，

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE as of MySQL 5.0.2.

CREATE DATABASE 创建具有给定名称的数据库。要使用此语句，您需要对数据库具有 CREATE 权限。从 MySQL 5.0.2 开始，CREATE SCHEMA 是 CREATE DATABASE 的同义词。

回答by Jean-Michel P.

PostgreSQL supports schemas, which is a subset of a database: https://www.postgresql.org/docs/current/static/ddl-schemas.html

PostgreSQL 支持模式，它是数据库的一个子集：https: //www.postgresql.org/docs/current/static/ddl-schemas.html

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.

一个数据库包含一个或多个命名模式，这些模式又包含表。模式还包含其他类型的命名对象，包括数据类型、函数和运算符。相同的对象名称可以在不同的模式中使用而不会发生冲突；例如，schema1 和 myschema 都可以包含名为 mytable 的表。与数据库不同，模式不是严格分开的：用户可以访问他们所连接的数据库中任何模式中的对象，前提是他们有权限这样做。

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

模式类似于操作系统级别的目录，只是模式不能嵌套。

In my humble opinion, MySQL is not a reference database. You should never quote MySQL for an explanation. MySQL implements non-standard SQL and sometimes claims features that it does not support. For example, in MySQL, CREATE schema will only create a DATABASE. It is truely misleading users.

在我看来，MySQL 不是参考数据库。你永远不应该引用 MySQL 作为解释。MySQL 实现了非标准 SQL，有时会声称它不支持的功能。例如，在 MySQL 中，CREATE 模式只会创建一个 DATABASE。这确实是在误导用户。

This kind of vocabulary is called "MySQLism" by DBAs.

这种词汇被 DBA 称为“MySQLism”。

回答by Riz

in MySQL schemais synonym of database. Its quite confusing for beginner people who jump to MySQL and very first day find the word schema, so guys nothing to worry as both are same.

在 MySQL模式中是database 的同义词。对于跳到 MySQL 并且第一天就找到这个词schema 的初学者来说，这非常令人困惑，所以大家不用担心，因为两者都是一样的。

When you are starting MySQL for the first time you need to create a database (like any other database system) to work with so you can CREATE SCHEMAwhich is nothing but CREATE DATABASE

当你第一次启动 MySQL 时，你需要创建一个数据库（像任何其他数据库系统一样）来使用，这样你就可以创建 SCHEMA，它只是CREATE DATABASE

In some other database system schema represents a part of database or a collection of Tables, and collection of schema is a database.

在其他一些数据库系统中，模式代表数据库的一部分或表的集合，模式的集合就是一个数据库。

回答by Mike Brant

Yes, people use these terms interchangeably with regard to MySQL. Though oftentimes you will hear people inappropriately refer to the entire database server as the database.

是的，人们在 MySQL 中可以互换使用这些术语。尽管您经常会听到人们不恰当地将整个数据库服务器称为数据库。

回答by EliD

Microsoft SQL Server for instance, Schemas refer to a single user and is another level of a container in the order of indicating the server, database, schema, tables, and objects.

以Microsoft SQL Server为例，Schemas是指单个用户，是容器的另一个层次，按照服务器、数据库、架构、表、对象的顺序排列。

For example, when you are intending to update dbo.table_a and the syntax isn't full qualified such as UPDATE table.a the DBMS can't decide to use the intended table. Essentially by default the DBMS will utilize myuser.table_a

例如，当您打算更新 dbo.table_a 并且语法不是完全限定的（例如 UPDATE table.a）时，DBMS 无法决定使用预期的表。基本上默认情况下，DBMS 将使用 myuser.table_a