136

Taking a peek into the information_schema database and peeking at the metadata for one of my pet projects, I'm having a hard time understanding what (if any) differences there are between the create schema command and the create database command for MySQL.

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).

Thanks!

PartialOrder
  • 2,870
  • 3
  • 36
  • 44
Bob
  • 2,002
  • 3
  • 16
  • 18

7 Answers7

182

The documentation of MySQL says :

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.

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

Pascal MARTIN
  • 395,085
  • 80
  • 655
  • 663
34

Mysql documentation says : CREATE SCHEMA is a synonym for CREATE DATABASE as of MySQL 5.0.2.

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

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.

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".

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".

Sadegh
  • 6,654
  • 4
  • 34
  • 44
12

Strictly speaking, the difference between Database and Schema is inexisting in MySql.

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

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)

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:

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.

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).

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 dbo schema).

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

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

1) dbo, the default schema.

2) guest contains 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

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

Doing this way, the schema lookup mentioned above could be made available to any standard user in the database (e.g. SELECT permissions only), whereas a table called supplierbankaccountdetails may 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).

Finally, and quoting the same book again:

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

Nicolas
  • 1,320
  • 2
  • 16
  • 28
8

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

UdayKiran Pulipati
  • 6,579
  • 7
  • 67
  • 92
Sergey Olontsev
  • 1,084
  • 7
  • 19
7

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

Arun Raja
  • 1,554
  • 16
  • 26
2

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.

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.

Oleg Khaykin
  • 51
  • 3
2

there is no difference between MySQL "database" and MySQL "schema": these are two names for the same thing

rpagrawal
  • 17
  • 2
  • 4