MySQL: MySQL Database Operations and Character Set…

Q: How do I check the database size?

Run the query information_schema : SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema;

Q: How do I correct an incorrect character set?

Use ALTER DATABASE to change the database character set, use ALTER TABLE to change the table character set, and use CONVERT TO to convert existing data.

Last updated: 2026-08-26

A database is a container for storing data—working with a database is the first step in using MySQL.

This lesson will teach you how to perform CRUD operations on databases and configure character sets.

graph TB
    A[Database Operations] --> B[CREATE DATABASE<br/>Create a Database]
    A --> C[SHOW DATABASES<br/>View Database]
    A --> D[USE<br/>Select a Database]
    A --> E[ALTER DATABASE<br/>Modify the character set]
    A --> F[DROP DATABASE<br/>Delete the database]
    B --> B1[Specify a character set utf8mb4]
    E --> E1[Modify the character set/Sorting Rules]

1. What You'll Learn

Create a database and specify the character set
View and select a database
Change the database character set
Delete the database
A Detailed Explanation of Character Sets and Collation Rules

2. A True Story About a Multilingual Website

(1) Pain Point: Garbled Chinese characters

A developer created an e-commerce database and inserted Chinese product names:

SQL

INSERT INTO products (name) VALUES ('iPhone');
SELECT * FROM products;

Result: name is displayed as ??? or garbled characters.

Reason: The database's default character set is latin1, which does not support Chinese.

(2) Solution for utf8mb4

SQL

-- Specify utf8mb4 character set when creating the database
CREATE DATABASE ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Benefits: Supports all Unicode characters, including Chinese, Japanese, Korean, and emojis.

Character Set	Supported Range	Recommendation Level
latin1	Western European characters	❌
utf8	Basic Multilingual (does not support 4-byte characters)	⚠️
utf8mb4	Full Unicode (including Emoji)	⭐⭐⭐

3. Create a Database

(1) Basic Syntax

SQL

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

▶ Example: Creating a Database

Output:

TEXT 📖 Display only

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

Query OK, 1 row affected

SQL

-- The Simplest Way to Create
CREATE DATABASE mydb;

-- Create it if it doesn't exist (Recommended)
CREATE DATABASE IF NOT EXISTS mydb;

-- Specify a character set (Recommended)
CREATE DATABASE IF NOT EXISTS mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Creating an E-commerce Database
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Output:

TEXT 📖 Display only

Query OK, 1 row affected (0.01 sec)

Output:

TEXT 📖 Display only

Output displayed

4. View the Database

▶ Example: View all databases

SQL

-- View All Databases
SHOW DATABASES;

-- View the statement used to create the database
SHOW CREATE DATABASE mydb;

-- View the currently used database
SELECT DATABASE();

▶ Try it Yourself

Output:

TEXT 📖 Display only

+--------------------+
| Database           |
+--------------------+
| ecommerce          |
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

5. Select a Database

▶ Example: The USE command

SQL

-- Select a Database
USE ecommerce;

-- Verify the current database
SELECT DATABASE();

▶ Try it Yourself

Output:

TEXT 📖 Display only

+------------+
| DATABASE() |
+------------+
| ecommerce  |
+------------+

6. Modify the Database

▶ Example: Changing the character set

SQL

-- Modify the Database Character Set
ALTER DATABASE mydb
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Verify Changes
SHOW CREATE DATABASE mydb;

▶ Try it Yourself

Output:

TEXT 📖 Display only

+----------+------------------------------------------------------------------------------------------+
| Database | Create Database                                                                          |
+----------+------------------------------------------------------------------------------------------+
| mydb     | CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+------------------------------------------------------------------------------------------+

7. Delete the Database

▶ Example: Deleting a Database

SQL

-- Delete the database
DROP DATABASE mydb;

-- Secure Deletion (If it exists)
DROP DATABASE IF EXISTS mydb;

▶ Try it Yourself

Output:

TEXT 📖 Display only

Output displayed

⚠️ Note: Deleting a database will permanently delete all tables and data within it, and this action cannot be undone! Be sure to confirm or back up your data before proceeding.

8. Character Sets and Sorting Rules

(1) Common Character Sets

Character Set	Byte Length	Supported Characters	Use Cases
ASCII	1	English + Numbers	All-English scenarios
latin1	1	Western European characters	Legacy system compatibility
utf8	1-3	Basic Multilingual	Backward Compatibility
utf8mb4	1-4	Full Unicode	Recommended default

(2) Sorting Rules

Sorting rules determine how strings are compared and sorted.

Sorting Rule	Description	Case-Sensitive
utf8mb4_general_ci	General-purpose, fast	Case-insensitive
utf8mb4_unicode_ci	Accurate, supports multiple languages	Case-insensitive
utf8mb4_bin	Binary Comparison	Case-Sensitive

💡 Tip: _ci indicates case-insensitive comparison, and _bin indicates binary comparison (case-sensitive).

▶ Example: Viewing character sets

SQL

-- View All Character Sets
SHOW CHARACTER SET;

-- View All Sorting Rules
SHOW COLLATION LIKE 'utf8mb4%';

-- View the server's default character set
SHOW VARIABLES LIKE 'character_set_server';

-- View the database character set
SHOW VARIABLES LIKE 'character_set_database';

▶ Try it Yourself

Output:

TEXT 📖 Display only

+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_server     | utf8mb4                                       |
+--------------------------+-----------------------------------------------+

Output:

TEXT 📖 Display only

Output displayed

9. Database Backup and Recovery

❓ FAQ

Q What is the difference between utf8 and utf8mb4?

A utf8 uses a maximum of 3 bytes and does not support 4-byte characters such as emojis. utf8mb4 is full UTF-8; it is recommended to always use utf8mb4.

Q Can I change the database name?

A MySQL does not support directly renaming a database. You can create a new database, migrate the tables to it, and then delete the old database.

Q How do I check the database size?

A Run the query information_schema:

SELECT table_schema, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb FROM information_schema.tables GROUP BY table_schema;

Q What is the difference between DROP DATABASE and TRUNCATE DATABASE?

A DROP deletes the entire database (schema and data), while TRUNCATE only clears the data but retains the schema. (MySQL does not have a TRUNCATE DATABASE command; you must use TRUNCATE on each table individually.)

Q How do I correct an incorrect character set?

A Use ALTER DATABASE to change the database character set, use ALTER TABLE to change the table character set, and use CONVERT TO to convert existing data.

📖 Summary

CREATE DATABASE: Create a database; we recommend using IF NOT EXISTS and utf8mb4
USE Select the current database; all subsequent operations will be performed in that database.
SHOW DATABASES to view all databases, SHOW CREATE DATABASE to view the creation statement
ALTER DATABASE Modifies database properties (character set, collation)
DROP DATABASE Deletes the database. Be sure to verify that a backup has been made before proceeding.
utf8mb4 is the recommended character set; it supports the full Unicode character set and emojis
mysqldump is used for database backups, and mysql < file.sql is used for restoration

📝 Exercises

Basic Problem (Difficulty ⭐): Create a database named school with the character set utf8mb4 and the collation utf8mb4_unicode_ci.
Advanced Exercise (Difficulty ⭐⭐): Review the creation statement for the school database to confirm that the character set is configured correctly. Then, create a test table and insert data containing emojis to verify support.
Challenge (Difficulty: ⭐⭐⭐): Write a backup script to back up the school database to the backup/ directory, with filenames that include the date (e.g., school_20260703.sql).

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: MySQL Database Operations and Character Set…

1. What You'll Learn

2. A True Story About a Multilingual Website

(1) Pain Point: Garbled Chinese characters

(2) Solution for utf8mb4

3. Create a Database

(1) Basic Syntax

▶ Example: Creating a Database

4. View the Database

▶ Example: View all databases

5. Select a Database

▶ Example: The USE command

6. Modify the Database

▶ Example: Changing the character set

7. Delete the Database

▶ Example: Deleting a Database

8. Character Sets and Sorting Rules

(1) Common Character Sets

(2) Sorting Rules

▶ Example: Viewing character sets

9. Database Backup and Recovery

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好