MySQL“创建架构”和“创建数据库”-有什么区别

MySQL 'create schema' and 'create database' - Is there any difference

提问by Bob

Taking a peak into the information_schemadatabase and peaking at the metadatafor one of my pet projects, I'm having a hard time understanding what (if any) differences there are between the create schemacommand and the create databasecommand for MySQL.

information_schema在我的一个宠物项目的数据库中达到峰值并在元数据中达到峰值，我很难理解create schema命令和create databaseMySQL 命令之间有什么（如果有的话）差异。

Are there any differences? If not, is this a rather typical pattern of behavior for relational databases (I've heard that for other databases, such as Oracle, a schema exists in a database, rather than being on the same level as a database).

有什么区别吗？如果不是，这是否是关系数据库的典型行为模式（我听说对于其他数据库，例如 Oracle，数据库中存在模式，而不是与数据库处于同一级别）。

Thanks!

谢谢！

回答by Pascal MARTIN

The documentation of MySQL says:

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 的同义词。

So, it would seem normal that those two instruction do the same.

因此，这两条指令执行相同操作似乎很正常。

回答by Sadegh

Mysql documentationsays : CREATE SCHEMA is a synonym for CREATE DATABASEas of MySQL 5.0.2.

Mysql 文档说：从 MySQL 5.0.2 开始，CREATE SCHEMA 是 CREATE DATABASE 的同义词。

---

---

this all goes back to an ANSI standard for SQL in the mid-80s.

这一切都可以追溯到 80 年代中期的 SQL ANSI 标准。

That standard had a "CREATE SCHEMA" command, and it served to introduce multiple name spaces for table and view names. All tables and views were created within a "schema". I do not know whether that version defined some cross-schema access to tables and views, but I assume it did. AFAIR, no product (at least back then) really implemented it, that whole concept was more theory than practice.

该标准有一个“CREATE SCHEMA”命令，它用于为表和视图名称引入多个名称空间。所有表和视图都是在“模式”中创建的。我不知道那个版本是否定义了一些对表和视图的跨模式访问，但我认为它确实如此。AFAIR，没有产品（至少在当时）真正实现了它，整个概念更多的是理论而不是实践。

OTOH, ISTR this version of the standard did not have the concept of a "user" or a "CREATE USER" command, so there were products that used the concept of a "user" (who then had his own name space for tables and views) to implement their equivalent of "schema".

OTOH，ISTR 这个版本的标准没有“用户”或“创建用户”命令的概念，所以有些产品使用了“用户”的概念（然后他有自己的表和名称空间）视图）来实现它们相当于“模式”。

This is an area where systems differ.

这是一个系统不同的领域。

As far as administration is concerned, this should not matter too much, because here you have differences anyway.

就管理而言，这应该无关紧要，因为无论如何您都存在差异。

As far as you look at application code, you "only" have to care about cases where one application accesses tables from multiple name spaces. AFAIK, all systems support a syntax ".", and for this it should not matter whether the name space is that of a user, a "schema", or a "database".

就您查看应用程序代码而言，您“仅”需要关心一个应用程序从多个名称空间访问表的情况。AFAIK，所有系统都支持语法“.”，为此，名称空间是用户、“模式”还是“数据库”的名称空间无关紧要。

回答by Nicolas

Strictly speaking, the difference between Databaseand Schemais inexisting in MySql.

严格来说，Database和Schema的区别在 MySql 中是不存在的。

However, this is not the case in other database engines such as SQL Server. In SQL server:,

但是，在 SQL Server 等其他数据库引擎中并非如此。在 SQL 服务器中：，

Every table belongs to a grouping of objects in the database called database schema. It's a container or namespace (Querying Microsoft SQL Server 2012)

每个表都属于称为数据库架构的数据库中的一组对象。它是一个容器或命名空间（查询 Microsoft SQL Server 2012）

By default, all the tables in SQL Server belong to a default schema called dbo. When you query a table that hasn't been allocated to any particular schema, you can do something like:

默认情况下，SQL Server 中的所有表都属于名为dbo的默认架构。当您查询尚未分配给任何特定架构的表时，您可以执行以下操作：

SELECT *
FROM your_table

which is equivalent to:

这相当于：

SELECT *
FROM dbo.your_table

Now, SQL server allows the creation of different schema, which gives you the possibility of grouping tables that share a similar purpose. That helps to organize the database.

现在，SQL Server 允许创建不同的模式，这使您可以对具有相似目的的表进行分组。这有助于组织数据库。

For example, you can create an schema called sales, with tables such as invoices, creditorders(and any other related with sales), and another schema called lookup, with tables such as countries, currencies, subscriptiontypes(and any other table used as look up table).

例如，您可以创建一个名为sales的模式，其中包含诸如invoices、creditorders（以及任何其他与销售相关的）表，以及另一个名为lookup 的模式，其中包含诸如country、currency、subscriptiontypes等表（以及用作 look 的任何其他表）。上桌）。

The tables that are allocated to a specific domain are displayed in SQL Server Studio Manager with the schema name prepended to the table name (exactly the same as the tables that belong to the default dboschema).

分配给特定域的表显示在 SQL Server Studio 管理器中，表名称前面带有架构名称（与属于默认dbo架构的表完全相同）。

There are special schemas in SQL Server. To quote the same book:

SQL Server 中有特殊的架构。引用同一本书：

There are several built-in database schemas, and they can't be dropped or altered:

1) dbo, the default schema.

2) guestcontains objects available to a guest user("guest user" is a special role in SQL Server lingo, with some default and highly restricted permissions). Rarely used.

3) INFORMATION_SCHEMA, used by the Information Schema Views

4) sys, reserved for SQL Server internal use exclusively

有几个内置的数据库模式，它们不能被删除或更改：

1) dbo，默认架构。

2)来宾包含对来宾用户可用的对象（“来宾用户”是 SQL Server 行话中的特殊角色，具有一些默认和高度受限的权限）。很少使用。

3) INFORMATION_SCHEMA，由信息模式视图使用

4) sys，仅供SQL Server内部使用

Schemas are not only for grouping. It is actually possible to give different permissions for each schema to different users, as described MSDN.

模式不仅用于分组。正如MSDN所述，实际上可以为不同的用户授予每个架构的不同权限。

Doing this way, the schema lookupmentioned above could be made available to any standard user in the database (e.g. SELECTpermissions only), whereas a table called supplierbankaccountdetailsmay be allocated in a different schema called financial, and to give only access to the users in the group accounts(just an example, you get the idea).

这样一来，架构查找上述数据库可以提供给任何标准的用户（例如SELECT只有权限），而表称为supplierbankaccountdetails可以在一个名为不同的模式来分配资金，并给予在只对用户的访问组accounts（只是一个例子，你明白了）。

Finally, and quoting the same book again:

最后，再次引用同一本书：

It isn't the same Database Schemaand Table Schema. The former is the namespace of a table, whereas the latter refers to the table definition

它不是相同的Database Schema和Table Schema。前者是表的命名空间，而后者是指表定义

回答by Sergey Olontsev

CREATE SCHEMA is a synonym for CREATE DATABASE. CREATE DATABASE Syntax

CREATE SCHEMA 是 CREATE DATABASE 的同义词。创建数据库语法

回答by Arun Raja

Database is a collection of schemas and schema is a collection of tables. But in MySQL they use it the same way.

数据库是模式的集合，模式是表的集合。但是在 MySQL 中，他们以相同的方式使用它。

回答by Oleg Khaykin

So, there is no difference between MySQL "database" and MySQL "schema": these are two names for the same thing - a namespace for tables and other DB objects.

因此，MySQL“数据库”和 MySQL“模式”之间没有区别：它们是同一事物的两个名称 - 表和其他 DB 对象的命名空间。

For people with Oracle background: MySQL "database" a.k.a. MySQL "schema" corresponds to Oracle schema. The difference between MySQL and Oracle CREATE SCHEMA commands is that in Oracle the CREATE SCHEMA command does not actually create a schema but rather populates it with tables and views. And Oracle's CREATE DATABASE command does a very different thing than its MySQL counterpart.

对于具有 Oracle 背景的人：MySQL“数据库”又名 MySQL“架构”对应于 Oracle 架构。MySQL 和 Oracle CREATE SCHEMA 命令之间的区别在于，在 Oracle 中，CREATE SCHEMA 命令实际上并不创建模式，而是用表和视图填充它。而 Oracle 的 CREATE DATABASE 命令与它的 MySQL 对应的功能非常不同。