Difference Between Schema / Database in MySQL
MysqlDatabaseSchemaDatabase SchemaMysql Problem Overview
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.
Mysql Solutions
Solution 1 - Mysql
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.
Solution 2 - Mysql
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.
Solution 3 - Mysql
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.
Solution 4 - Mysql
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.
Solution 5 - Mysql
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.
Solution 6 - Mysql
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.
Solution 7 - Mysql
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."
Solution 8 - Mysql
Just add some more info:
MongoDB also distinguish schema from database.
schema represent the tables, which means the structure of database.
Solution 9 - Mysql
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
Solution 10 - Mysql
not like Postgres, SQL server schema is set of database have same thing but in mysql schema and database it is the same MySQL does not support the concept of schema. In MySQL, schema and schemas are synonyms for database and databases.
When a user connects to MySQL, they don't connect to a specific database. Instead, they can access any table they have permissions for