Differences between Database and Schema using different databases?

mysql sql-server database schema
user48545 picture user48545 · Dec 8, 2009 · Viewed 45.5k times · Source

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?

Answer

Lance Roberts picture Lance Roberts · Dec 14, 2009

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.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
Storing JSON in database vs. having a new column for each key

I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format. uid | meta -------------------------------------------------- 1 | {name:[…

Read More
A powerful management tool for MySQL with similar features to SQL Server Management studio

I am currently working with a developer who is experienced at Ms-SQL, but not much at MySQL. He has been cursing MySQL for having Bugs, and also being far harder to use. Is is because his experience has been so …

Read More