323

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.

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

AakashM
  • 59,217
  • 16
  • 147
  • 181
Lloyd Banks
  • 32,108
  • 50
  • 143
  • 228
  • Actually I would say `schema` is better than `database`, as MySQL is a kind of database, and it contains several `database`, and sometimes you `use` a `database`, which makes many searches to miss target. – theaws.blog Dec 10 '19 at 13:42

8 Answers8

378

As defined in the MySQL Glossary:

In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

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

eggyal
  • 113,121
  • 18
  • 188
  • 221
  • 87
    I think "some other database products" is really a euphemism for pretty much every other database (perhaps except SQLite, but that's understandable). – Stefan Steiger Nov 12 '13 at 06:30
  • 6
    Making schema synonymous with database is interesting -- I had always associated it with user objects.. And a friendly mention of SQLServer which separated the user/schema concept allowing for a boatload of flexibility and some interesting ways to do dynamic queries. – Gerard ONeill Feb 04 '15 at 20:49
  • The annoying thing about defining "schema" as a grouping of database objects is that it overloads the older meaning of the word, e.g. https://en.wikipedia.org/wiki/Database_schema. – Mark E. Haase Mar 02 '16 at 03:28
  • 1
    @mehaase both are the old meaning of the word. Oracle had schemas before even SQL3 (1999), although it treated them as other databases would tread full-blown databases. In fact, Oracle calls "database" what others call a server or server instance - different binaries, different executables, different ports. – Panagiotis Kanavos Feb 06 '17 at 12:46
72

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

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

Arpit Aggarwal
  • 21,748
  • 13
  • 80
  • 99
user2631022
  • 731
  • 5
  • 2
  • 7
    Even Microsoft SQL Server has very similar distinction as `Oracle` and `DB2` for this case. – RBT Dec 27 '16 at 06:12
  • 5
    In MySQL "database" and "schema" is not basically the same thing; it is exactly the same thing (for example, `create database` and `create schema` are synonyms). In Oracle schema is synonymous with user (perhaps more accurately, schema is a list of all objects created under a user). MSSQL distinguishes all three (and as a result, when accessing a table from a different database, the name must be qualified by both database and schema, e.g. `database1.dbo.table2`) – Mike Rosoft Aug 26 '19 at 14:48
19

Refering to MySql documentation,

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.

Akash KC
  • 15,121
  • 5
  • 33
  • 56
11

PostgreSQL supports schemas, which is a subset of a database: 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.

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.

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

Jean-Michel P.
  • 119
  • 1
  • 2
  • 3
    And then there is "schemata" to add to confusion for beginners :) – Zeni Mar 28 '18 at 14:43
  • @Zeni what exactly is a schemata in MySQL? All I could find are references to the information_schema schemata table but no concise definition as to what it actually means – Alf47 Apr 06 '20 at 21:00
  • @Alf47 "schemata" is just the plural form of the word "schema". "schemas" is also acceptable, but not as fancy :) – jobo3208 Jun 11 '20 at 21:17
9

in MySQL schema is 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.

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 SCHEMA which is nothing but 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.

Riz
  • 91
  • 1
  • 2
4

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.

Mike Brant
  • 66,858
  • 9
  • 86
  • 97
0

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.

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

EliD
  • 1
  • 1
  • Schema's are *owned* by a single user. By default, a user created in the database uses the dbo schema as their default schema. The default schema used for a non fully qualified table reference is what is set for that user. – Gerard ONeill Feb 04 '15 at 20:43
  • In older versions of SQL Server, deleting a user would render the schema that was associated with it unusable. These days the mapping between user and schema is less strict. Also, it is considered best practice to make a single fake account to owner of the database and all of the schemas. – Jonathan Allen May 08 '16 at 05:17
0

Simply if you are thinking or discussing about Mysql. Then take a simple answer

"SCHEMA & DATABASE are exactly the same thing, just a synthetic sugar in mysql."

Md. Sahaib Mridha
  • 1
  • 2