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

user_87361 0
Viewed 195864

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!

jdbc oracle mysql sql database
7 Answers

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.

user_1384750

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

user_993430
user_1489520

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.

user_60405440

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

user_81058560
Related