2

I have a modular system that is being migrated to MySQL.

In SQL Server, there is this concept of the schema for grouping tables and database objects together. For example, all user-related tables can be put in the auth schema and all blog tables in the blog schema. This means we can see these table names in object explorer:

  • auth.Users
  • auth.Roles
  • auth.UserRoles
  • auth.Token
  • blog.Posts
  • blog.Comments
  • blog.Authors

It is a neat way to keep things together and also sort them together which is very helpful in understanding the database and modular structure and helps reduce maintenance costs.

I know MySQL does not support this feature.

I also know that we can prefix the names of tables to mimic this behavior. But I wonder if that's the best way?

Is there a known method already to achieve this that I don't know? I'm fairly new to MySQL.

1
  • MySQL has 2 levels: DATABASE (aka SCHEMA) and table. So, if auth is a DATABASE and Users is TABLE. If you need to "prefix" either, that is manual.
    – Rick James
    Jun 4 at 20:11
3

In MySQL, SCHEMA and DATABASE are synonyms. They refer to the same thing. You can use statements like CREATE SCHEMA or CREATE DATABASE. You can SHOW SCHEMAS or SHOW DATABASES.

This is a structural namespace for tables, so you can have two tables with the same name as long as they are in different schemas.

mysql> create table test.mytable( ... );
mysql> create table test2.mytable( ... );

Internally, MySQL Server implements schemas simply as subdirectories under the server's data directory. MySQL supports only one level of subdirectory in this way.

It has been said that you can't have inter-schema transactions. This is not true. You can reference tables of different schemas in a single transaction. You can even reference tables from different schemas in the same query:

SELECT * FROM test.mytable JOIN test2.mytable ON ...

You can make a table with a foreign key referencing another table in a different schema. It's just a kind of namespace for the tables, so you need to reference tables with the schema qualifier if the table isn't in your current default database. Read about qualified table names here: https://dev.mysql.com/doc/refman/8.0/en/identifier-qualifiers.html

MySQL also supports schema-level privileges in GRANT statements:

GRANT SELECT ON test2.* TO 'user'@'%';

Other SQL implementations like Microsoft's have further levels of namespacing. I've seen terms "schema," "database," and "catalog" used differently in some brands of SQL database. These things are not implemented in a consistent manner, so you need to learn how the product you are using treats them.

MySQL does not have any implementation for a "catalog", but you still see references to catalog in INFORMATION_SCHEMA tables, because the columns of those tables are standardized in the SQL spec. In MySQL, the catalog is always NULL in the info-schema tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.