When handling databases in MySQL, many of you have probably come across the phrase “create a schema.”
A schema represents the structure or blueprint of a database and exists as a collection of tables, views, indexes, triggers, etc. In MySQL, “schema” and “database” are treated almost synonymously, but when compared to other RDBMS (Relational Database Management Systems), there may be differences in meaning. In this article, we will systematically explain how to create a schema in MySQL, points to note during the process, and practical best practices. Especially for beginners, this content will clearly resolve questions like “What is a schema?” and “How do you create one?”
Simple Summary of the Differences Between Schema and Database
In MySQL, it is fine to understand that “creating a schema = creating a database.” However, in other database systems like Oracle or PostgreSQL, a “schema” is a logical group (namespace) that exists within a database, and it is not necessarily synonymous. Understanding this difference can help avoid confusion when migrating to or integrating with other RDBMS.
Target Readers and Goals of This Article
This article is written for the following types of people.
Beginners using MySQL for the first time
People who want to understand the basics of schemas and creation procedures
Engineers or students planning to use MySQL in their work
By the end of reading this article, the goal is for you to be able to appropriately create schemas in MySQL, with awareness of character encoding, management methods, and other design considerations.
2. What is a Schema?
Basic Concepts of Schema
Schema refers to a framework that defines the structure or blueprint of a database.
Specifically, it includes a group of objects such as tables, views, indexes, stored procedures, triggers, etc., for managing and operating data. In MySQL, it is treated as “schema = database,” and schemas are created with the CREATE DATABASE command. In other words, in MySQL, when you hear the word schema, you can think of it as the database itself without issue.
CREATE DATABASE sample_db;
In this way, you can create a schema (database) with a simple command.
Differences in Schema in Other RDBMS
In MySQL, schema and database are almost synonymous, but in other RDBMS, the meaning may differ.
Database System
Definition of Schema
MySQL
Refers to the entire database (synonymous)
PostgreSQL
Namespace within the database (multiple schemas possible)
Oracle
Data storage unit corresponding to each user (user = schema)
For example, in PostgreSQL, a single database can have multiple schemas, each functioning as an independent namespace. On the other hand, in MySQL, the design is one schema per database, so understanding this difference is important when considering configuration and portability.
Roles and Benefits of Schema
Schemas have the following benefits:
Structure Organization: By logically grouping tables and views, management becomes easier
Access Control: By setting permissions at the schema level, security can be enhanced
Clarification of Data Modeling: By clarifying the logical structure from the design stage, team development proceeds smoothly
In MySQL, many of these benefits are realized at the “database” level, making it a very important concept in practice as well.
3. How to Create a Schema in MySQL
Basics of Schema Creation: The CREATE DATABASE Command
The most basic way to create a schema (= database) in MySQL is to use the CREATE DATABASE statement. The following is its basic syntax.
CREATE DATABASE schema_name;
For example, to create a schema named “sample_db”, write it as follows.
CREATE DATABASE sample_db;
Executing this command creates an empty database (schema) named sample_db. This is the starting point for schema creation in MySQL.
Using IF NOT EXISTS: Preventing Duplicate Errors
If you try to create a schema with a name that already exists, an error occurs, but by adding the IF NOT EXISTS option, you can prevent such errors.
CREATE DATABASE IF NOT EXISTS sample_db;
This syntax is particularly useful in development environments where scripts may be executed repeatedly.
Setting Character Encoding and Collation
When handling Japanese data, specifying character encoding (CHARACTER SET) and collation (COLLATE) is very important. If not set properly, garbled characters or sorting issues may occur.
CREATE DATABASE sample_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;
Recommended Settings:
CHARACTER SET: utf8mb4 (supports multilingual including Japanese)
COLLATE: utf8mb4_general_ci or utf8mb4_unicode_ci (there are differences in strictness, but both are effective for general purposes)
By making this setting, handling Japanese data on MySQL becomes smooth, and you can prevent future troubles.
What is the Difference with CREATE SCHEMA?
MySQL also supports the CREATE SCHEMA syntax, but it behaves exactly the same as CREATE DATABASE. Either can be used without issue, but by convention in MySQL, CREATE DATABASE is more commonly used.
CREATE SCHEMA IF NOT EXISTS sample_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
You can choose based on preference, but it is recommended to unify within the project or team.
4. Best Practices for Schema Creation
Creating a schema in MySQL itself is simple, but in practice, design and management with long-term operation in mind are extremely important. Here, we introduce several best practices to keep in mind when creating a schema.
Set Consistent Naming Conventions
Apply clear and consistent naming rules to schema names, table names, column names, etc. If naming is not unified, it becomes easy to cause confusion when maintaining and extending the database later.
Example: Basic Naming Rule Proposal
Adopt snake_case (sample_table)
Name tables based on nouns (e.g., users, orders)
Do not use prefixes (as it often becomes redundant)
Naming rules may differ by team or project, but it is extremely important to “document and share them.”
Specify Appropriate Character Encoding
As mentioned in the previous chapter, character encoding is the foundation of database design. For projects handling Japanese, it is recommended to explicitly specify utf8mb4.
CREATE DATABASE example_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
utf8 only supports up to 3 bytes, and there is a possibility of issues with emojis or some kanji. In new developments, always choose utf8mb4.
Plan and Set Permissions
After creating the schema, it is necessary to grant appropriate access permissions to users. Granting all permissions to all users is dangerous from a security perspective.
GRANT ALL PRIVILEGES ON example_db.* TO 'app_user'@'localhost';
At minimum, consider permission designs by roles like the following:
Role
Example Permissions
Administrator
All permissions (CREATE, DROP, GRANT, etc.)
Application
SELECT, INSERT, UPDATE, etc.
Read-Only
SELECT only
In MySQL, you can check and manage the current permission status with REVOKE or SHOW GRANTS.
Take a Backup of the Initial State
Even after schema creation, when there is no data yet, exporting and saving the initial design structure will be very useful in the future.
By doing so, it becomes easy to apply only the structure to another environment or restore it.
5. Schema Management and Operations
After creating a schema in MySQL, you will need skills to properly manage and operate it. This section explains basic operations on schemas and commonly used commands.
5.1 Displaying a List of Schemas
If you want to check the list of schemas (databases) currently existing in MySQL, use the SHOW DATABASES command.
SHOW DATABASES;
Executing this command will list all schema names, including system databases such as information_schema and mysql. Schemas created by the user are also included here. It can also be used as needed to check if a specific schema exists.
5.2 Using a Schema (Switching)
In MySQL, you need to explicitly specify the target schema for operations. Use the USE command for this.
USE sample_db;
Executing this command switches the schema context for that session to sample_db, and subsequent operations will be performed within this schema.
5.3 Deleting a Schema
To delete an unnecessary schema, use the DROP DATABASE command.
DROP DATABASE sample_db;
Note:
This operation cannot be undone. Since all tables, views, and data contained in the target schema will be deleted, it is essential to take a backup beforehand. For safety, it is also recommended to execute it with IF EXISTS.
DROP DATABASE IF EXISTS sample_db;
5.4 Managing Tables and Views in a Schema
A schema by itself has no meaning; the main elements are the tables and views and other objects within it. Below are representative object operations within a schema.
Creating a Table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255)
);
Checking the List of Tables
SHOW TABLES;
Creating a View
Views are convenient when you want to reuse complex queries.
CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE active = 1;
Checking the List of Views
SHOW FULL TABLES WHERE Table_type = 'VIEW';
Deleting Tables or Views
DROP TABLE users;
DROP VIEW active_users;
In this way, not only creating the schema but also how to manage its contents is extremely important. Proper operation greatly improves the system’s scalability and maintainability.
6. Precautions When Handling Japanese Data
When using MySQL in a Japanese environment, the most common problem that many beginners face is “garbled characters”.
This occurs because the database’s character encoding and the settings between the client and server do not match. In this section, we explain the basic precautions and setup methods for handling Japanese data safely and accurately.
Causes of Garbled Characters and Preventive Measures
In MySQL, the character code settings are affected by the following three points.
Encoding of the database (schema) itself
Encoding per table and column
Encoding during communication with the client
If these do not match, for example, phenomena such as “Japanese text that should have been INSERTed becoming \”???\” ” can occur.
Recommended Encoding Settings
To handle Japanese correctly in MySQL, it is recommended to use utf8mb4 instead of UTF-8.
The reason is that while utf8 supports a maximum of 3 bytes, utf8mb4 supports up to 4 bytes, allowing emojis and some old characters to be saved without issues.
When Creating the Database:
CREATE DATABASE japanese_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
When Creating the Table:
CREATE TABLE messages (
id INT PRIMARY KEY AUTO_INCREMENT,
content TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Checking Settings at Connection Time (Client Side):
SHOW VARIABLES LIKE 'character_set%';
You can check the current character set settings with this command. If the client is using a different character set, you need to specify it explicitly.
SET NAMES utf8mb4;
Measures in Server Settings (my.cnf)
In a production environment, by editing MySQL’s configuration file (my.cnf or my.ini) and specifying the default character code, you can minimize the risk of garbled characters.
By making this setting, the entire MySQL will operate assuming utf8mb4. After setting, a restart of MySQL is required.
Response Measures When Garbled Characters Occur
If garbled characters have already occurred, identify and fix the cause using the following steps.
Check the encoding with SHOW CREATE TABLE table_name;
Change the character set at client connection time to utf8mb4
Dump the data and restore it specifying the character code (e.g., --default-character-set=utf8mb4)
7. Frequently Asked Questions (FAQ)
Here, we have compiled frequently asked questions about creating and managing schemas in MySQL in a Q&A format. We focus on points that beginners often stumble over.
Q1. In MySQL, do “schema” and “database” mean the same thing?
A1. Yes, in MySQL, “schema” and “database” are essentially synonymous.CREATE DATABASE and CREATE SCHEMA perform the same operation. The difference appears when using other RDBMS like Oracle or PostgreSQL. In MySQL, it’s conventional to refer to it as a “database,” but the term “schema” is also frequently used in documentation.
Q2. What happens if I don’t specify the character encoding when creating a schema?
A2. If not explicitly specified, the MySQL server’s default encoding is applied.
Depending on the environment, it might be set to latin1 or utf8, which can cause garbled characters with Japanese data.
Therefore, it’s safer to explicitly specify utf8mb4 and utf8mb4_unicode_ci when creating a schema.
Q3. Can I change the character encoding of an existing schema later?
A3. Yes, you can change it using the ALTER DATABASE statement, but it does not directly affect existing tables or data.
ALTER DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
However, the encoding of existing tables or columns needs to be changed separately with ALTER TABLE. Be sure to take a backup before performing the operation.
Q4. How can I copy or back up a schema?
A4. You can export (backup) on a schema-by-schema basis using the mysqldump command.
