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
In MS SQL, you can create multiple databases… each have a default schema of dbo?… but multiple schemas in a database is still possible?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
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.
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,
foofrom another like named table
foo. The following is pseudo code:
select * from db1.foovs.
select * from db2.foo(no explicit schema between db and table)
select * from [db1.]default.foovs.
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.
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’.
MS SQL Definately supports multiple databases, each with 1 to many schemas, dbo is merely the default for backwards compatibility purposes.