Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference Between Schema / Database in MySQL

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.

like image 531
Lloyd Banks Avatar asked Jul 23 '12 18:07

Lloyd Banks


People also ask

What is different between schema and database?

The main difference between schema and database is that schema is a logical definition of a database that defines the tables, columns and types of columns, while the database is a collection of related data stored in tables. Database refers to a set of data.

What are schemas in MySQL?

The mysql schema is the system schema. It contains tables that store information required by the MySQL server as it runs. A broad categorization is that the mysql schema contains data dictionary tables that store database object metadata, and system tables used for other operational purposes.

What are the 3 types of database schema?

Schema is of three types: Logical Schema, Physical Schema and view Schema.

What is the difference between a schema and a table?

What is the difference between Schema and Table? A database schema describes the structure and organization of data in a database system, while a table is a data set in which the data is organized in to a set of vertical columns and horizontal rows.


4 Answers

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.

like image 57
eggyal Avatar answered Oct 22 '22 20:10

eggyal


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.

like image 25
user2631022 Avatar answered Oct 22 '22 20:10

user2631022


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.

like image 20
Akash KC Avatar answered Oct 22 '22 21:10

Akash KC


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.

like image 17
Jean-Michel P. Avatar answered Oct 22 '22 21:10

Jean-Michel P.