MySQL 使用不同数据库的数据库和模式之间的差异？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1869522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Differences between Database and Schema using different databases?
提问by user48545
What are the differences in database terminology between MS SQL and MySQL?
MS SQL 和 MySQL 在数据库术语上有什么区别？
Can a MySQL instance have more than one database? It appears that it can only create different schemas. However, the SQL command is create database.
一个 MySQL 实例可以有多个数据库吗？看来它只能创建不同的模式。但是，SQL 命令是create database.
In MS SQL, you can create multiple databases… each have a default schema of dbo?… but multiple schemas in a database is still possible?
在 MS SQL 中，您可以创建多个数据库……每个数据库都有一个默认的 dbo 架构？……但是一个数据库中的多个架构仍然可能吗？
回答by Lance Roberts
From this link, we see that MS SQL schemas are no longer tied to users, here's the relevant quote:
从这个链接，我们看到 MS SQL 模式不再与用户绑定，这是相关的引用：
The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.
SQL Server 2005 中架构的行为发生了变化。架构不再等同于数据库用户；每个模式现在都是一个独特的命名空间，独立于创建它的数据库用户存在。换句话说，模式只是对象的容器。模式可以由任何用户拥有，并且其所有权是可转让的。
In MySQL, databases and schemas are exactly the same thing, you can even interchange the word in the commands, i.e. CREATE DATABASE has the synonym CREATE SCHEMA.
在 MySQL 中，数据库和模式是完全一样的东西，你甚至可以在命令中互换这个词，即 CREATE DATABASE 有同义词 CREATE SCHEMA。
MySQL supports multiple databases (schemas) and MS SQL supports multiple databases and multiple schemas.
MySQL 支持多个数据库（架构），MS SQL 支持多个数据库和多个架构。
回答by Greg Roberts
In general, I found the following article on Wikipediato be useful.
总的来说，我发现维基百科上的以下文章 很有用。
At the bottom of the article is the following:
文章底部有以下内容：
The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot ".". This seems to be a universal amongst all of the implementations. A true fully (database, schema, and table) qualified query is exemplified as such:
select * from database.schema.tableNow, the issue, both a schema and a database can be used to isolate one table,
foofrom another like named tablefoo. The following is pseudo code:select * from db1.foovs.select * from db2.foo(no explicit schema between db and table)select * from [db1.]default.foovs.select * from [db1.]alternate.foo(no explicit db prefix) The problem that arises is that former MySQL users will create multiple databases for one project. In this context MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, Postgres has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality. MySQL aliases behind the scenes, schema with database, such that create schema, and create database are analogs.It can be said, that MySQL therefore, has implemented cross-table functionality, skipped schema functionality entirely and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas, but lacks some functionality MySQL has with databases, while MySQL doesn't even attempt to support true schemas.
SQL 规范明确了“SQL 模式”是什么；然而，不同的数据库错误地实现了它。为了使这种混淆更加复杂，当不正确实现时，该功能可能与父数据库的功能重叠。SQL 模式只是数据库中的一个命名空间，该命名空间内的事物使用成员运算符点“.”进行寻址。这似乎是所有实现中的普遍性。一个真正的完全（数据库、模式和表）限定查询示例如下：
select * from database.schema.table现在，问题是，模式和数据库都可用于将一个表
foo与另一个表（如 named table ）隔离foo。下面是伪代码：select * from db1.foovs.select * from db2.foo（db和table之间没有显式schema）select * from [db1.]default.foovs.select * from [db1.]alternate.foo（没有显式db前缀） 出现的问题是以前的MySQL用户会为一个项目创建多个数据库。在这种情况下，MySQL 数据库在功能上类似于 Postgres 模式，因为 Postgres 缺少 MySQL 具有的现成的跨数据库功能。相反，Postgres 应用了更多实现跨表、跨模式的规范，然后为未来的跨数据库功能留出了空间。MySQL 别名在后台，schema 和 database，例如 create schema 和 create database 是类似的。可以说，因此 MySQL 实现了跨表功能，完全跳过了模式功能，并在其数据库实现中提供了类似的功能。总之，Postgres 完全支持模式，但缺少 MySQL 对数据库的一些功能，而 MySQL 甚至不尝试支持真正的模式。
回答by Yada
I believe by saying 'schema' for MS SQL you are meaning 'owner'.
我相信通过对 MS SQL 说“模式”，您的意思是“所有者”。
From my understand, in MySQL when you do a
据我了解，在 MySQL 中，当您执行
SELECT * from world.city;
This query is selecting from the world database the table city.
此查询从世界数据库中选择表城市。
In MsSQL you will have to do a
在 MsSQL 中，您必须执行以下操作
SELECT * from world.dbo.city;
Where 'dbo' is the default owner of the table.
其中“dbo”是表的默认所有者。
To make life easier define the default database by typing
为了让生活更轻松，通过键入定义默认数据库
USE world
SELECT * from city;
In MySQL there is no way to declare the owner of the table. ie. 'dbo'.
在 MySQL 中，无法声明表的所有者。IE。'dbo'。
回答by Andrew
MS SQL Definately supports multiple databases, each with 1 to many schemas, dbo is merely the default for backwards compatibility purposes.
MS SQL 绝对支持多个数据库，每个数据库有 1 到多个模式，dbo 只是为了向后兼容目的的默认设置。
