Differences between Database and Schema using different databases?

Question

What are the differences in database terminology between MS SQL and MySQL?

Can a MySQL instance have more than one database? It appears that it can only create different schemas. However, the SQL command is create database.

In MS SQL, you can create multiple databases… each have a default schema of dbo?… but multiple schemas in a database is still possible?

Solution 1

From this link, we see that MS SQL schemas are no longer tied to users, here's the relevant quote:

The behavior of schemas changed in SQL Server 2005. Schemas are no longer equivalent to database users; each schema is now a distinct namespace that exists independently of the database user who created it. In other words, a schema is simply a container of objects. A schema can be owned by any user, and its ownership is transferable.

In MySQL, databases and schemas are exactly the same thing, you can even interchange the word in the commands, i.e. CREATE DATABASE has the synonym CREATE SCHEMA.

MySQL supports multiple databases (schemas) and MS SQL supports multiple databases and multiple schemas.

Solution 2

In general, I found the following article on Wikipedia to be useful.

At the bottom of the article is the following:

The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot ".". This seems to be a universal amongst all of the implementations. A true fully (database, schema, and table) qualified query is exemplified as such: select * from database.schema.table

Now, the issue, both a schema and a database can be used to isolate one table, foo from another like named table foo. The following is pseudo code: select * from db1.foo vs. select * from db2.foo (no explicit schema between db and table) select * from [db1.]default.foo vs. select * from [db1.]alternate.foo (no explicit db prefix) The problem that arises is that former MySQL users will create multiple databases for one project. In this context MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, Postgres has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality. MySQL aliases behind the scenes, schema with database, such that create schema, and create database are analogs.

It can be said, that MySQL therefore, has implemented cross-table functionality, skipped schema functionality entirely and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas, but lacks some functionality MySQL has with databases, while MySQL doesn't even attempt to support true schemas.

Solution 3

I believe by saying 'schema' for MS SQL you are meaning 'owner'.

From my understand, in MySQL when you do a

SELECT * from world.city;

This query is selecting from the world database the table city.

In MsSQL you will have to do a

SELECT * from world.dbo.city;

Where 'dbo' is the default owner of the table.

To make life easier define the default database by typing

USE world
SELECT * from city;

In MySQL there is no way to declare the owner of the table. ie. 'dbo'.

Solution 4

MS SQL Definately supports multiple databases, each with 1 to many schemas, dbo is merely the default for backwards compatibility purposes.

View more solutions

Comments

[sujaisd]

Table name prefix is used in MySQL as a practice to cover schema feature, though its not a proper fix, it is common practice in MySQL. wp_config - where wp is for wordpress.

[lmat - Reinstate Monica]

"both a schema and a database can be used to isolate one table" Wow, that's really confusing if you already understand that in MySQL, a schema is a database. But I guess the point of the article is to teach folks who are not familiar. Thanks for the quote.

[Fredrik Johansson]

For me, it helps to think of schemas in MS SQL Server as namespaces in c# or packages in Java.