Practical MySQL & MariaDB

如何在 MySQL 中创建模式：数据库创建、管理与最佳实践完整指南

基本操作
目次

1. 引言

为什么在 MySQL 中创建 Schema？

在 MySQL 中处理数据库时，您可能遇到过“创建 schema”的表述。
Schema 表示数据库的结构或蓝图，以表格、视图、索引和触发器等对象的集合形式存在。在 MySQL 中，“schema”和“database”被视为几乎同义的术语。然而，与其他 RDBMS（关系数据库管理系统）相比，它们的含义可能不同。

在本文中，我们系统地解释了如何在 MySQL 中创建 schema、重要注意事项以及实际最佳实践。对于初学者来说，本指南澄清了诸如“什么是 schema？”和“如何创建它？”等问题。

快速概述：Schema 和 Database 的区别

在 MySQL 中，完全可以将“创建 schema”理解为“创建 database”。

然而，在其他数据库系统如 Oracle 和 PostgreSQL 中，“schema”是一个数据库内的逻辑组（命名空间），并不一定与数据库本身同义。理解这种区别有助于在迁移到其他 RDBMS 平台或与之集成时避免混淆。

本文的目标读者和目标

本文是为以下读者撰写的：

  • 首次使用 MySQL 的初学者
  • 希望了解 schema 基础知识和创建过程的人
  • 计划在实际项目中使用 MySQL 的工程师或学生

读完本文后，您将能够正确地在 MySQL 中创建 schema，并以字符编码和管理实践的意识来设计它们。

2. 什么是 Schema？

Schema 的基本概念

Schema 指的是定义数据库结构或蓝图的框架。

具体来说，Schema 包括用于管理和操作数据的对象，如表格、视图、索引、存储过程和触发器。

在 MySQL 中，“schema = database”，您使用 CREATE DATABASE 命令创建 schema。换句话说，在 MySQL 中听到“schema”一词时，可以将其视为数据库本身。

CREATE DATABASE sample_db;

使用这个简单的命令，您就可以创建 schema（database）。

与其他 RDBMS 中 Schema 的区别

在 MySQL 中，schema 和 database 几乎同义，但在其他 RDBMS 中，含义可能不同。

Database SystemDefinition of Schema
MySQLRefers to the entire database (synonymous)
PostgreSQLA namespace within a database (multiple schemas allowed)
OracleA data storage unit corresponding to a user (user = schema)

例如，PostgreSQL 允许在单个数据库中存在多个 schema，每个 schema 作为独立的命名空间起作用。相比之下，MySQL 每个数据库使用一个 schema。理解这种区别对于系统设计和可移植性很重要。

Schema 的作用和益处

Schema 提供了以下优势：

  • 组织结构：逻辑分组表格和视图，提高可管理性
  • 访问控制：可以按 schema 设置权限，提升安全性
  • 清晰的数据建模：在设计阶段定义逻辑结构，提高团队开发效率

在 MySQL 中，这些益处的大部分在数据库级别实现，这使其在实际环境中成为一个关键概念。

3. 如何在 MySQL 中创建 Schema

基本 Schema 创建：CREATE DATABASE 命令

在 MySQL 中创建 schema（= database）的最基本方法是使用 CREATE DATABASE 语句。其基本语法为：

CREATE DATABASE schema_name;

例如，要创建名为“sample_db”的 schema：

CREATE DATABASE sample_db;

执行此命令将创建一个名为 sample_db 的空数据库（schema）。这是 MySQL 中 schema 创建的起点。

使用 IF NOT EXISTS 防止重复错误

尝试创建已存在的 schema 将导致错误。您可以使用 IF NOT EXISTS 选项来防止这种情况：

CREATE DATABASE IF NOT EXISTS sample_db;

这种语法在开发环境中特别有用，在那里脚本可能被重复执行。

设置字符编码和排序规则

在处理多语言数据（包括日文文本）时，指定字符集和排序规则极其重要。设置不当可能导致乱码（mojibake）或排序问题。

CREATE DATABASE sample_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_general_ci;

推荐设置：

  • CHARACTER SET : utf8mb4（支持包括表情符号在内的多语言数据）
  • COLLATE : utf8mb4_general_ci（更快但不够严格），utf8mb4_unicode_ci，或 utf8mb4_0900_ai_ci（推荐用于 MySQL 8.0+）

这些设置有助于防止将来的文本编码问题。

关于 CREATE SCHEMA？

MySQL 也支持 CREATE SCHEMA 语法，其行为与 CREATE DATABASE 完全相同。两者均可使用，但在 MySQL 实践中更常使用 CREATE DATABASE。

CREATE SCHEMA IF NOT EXISTS sample_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

您可以根据偏好进行选择，但建议在团队或项目内部保持一致性。

4. 创建模式时的最佳实践

在 MySQL 中创建模式很简单，但在实际环境中，长期的运营设计和管理至关重要。

建立一致的命名约定

为模式名、表名和列名制定清晰且一致的命名规则。命名不一致会在维护和扩展时造成混淆。

示例命名规则

  • 使用 snake_case（sample_table）
  • 表名使用名词（例如 users、orders）
  • 避免不必要的前缀

在团队内部记录并共享命名约定。

明确指定字符编码

正如前文所述，字符编码是基础。对于多语言项目，明确指定 utf8mb4。

CREATE DATABASE example_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

旧的 utf8 仅支持最多 3 字节，可能在表情符号或某些字符上出现问题。新项目请始终使用 utf8mb4。

仔细规划权限设置

创建模式后，需要分配适当的用户权限。将所有权限授予所有用户是一种安全风险。

GRANT ALL PRIVILEGES ON example_db.* TO 'app_user'@'localhost';

考虑基于角色的权限设计：

RoleExample Privileges
AdministratorAll privileges (CREATE, DROP, GRANT, etc.)
ApplicationSELECT, INSERT, UPDATE, etc.
Read-onlySELECT only

您可以使用 REVOKE 和 SHOW GRANTS 来管理权限。

备份初始模式结构

即使没有数据，导出并保存初始模式结构也非常有用。

mysqldump -u root -p --no-data example_db > schema_structure.sql

这使您能够轻松将结构应用到其他环境。

5. 管理和操作模式

在 MySQL 中创建模式后，您需要具备正确管理和操作它的技能。

5.1 显示模式列表

查看已有的模式（数据库）：

SHOW DATABASES;

此命令会显示所有模式，包括 information_schema、mysql 等系统数据库。

5.2 使用（切换）模式

指定要使用的工作模式：

USE sample_db;

此操作会将会话上下文切换到 sample_db。

5.3 删除模式

删除不再使用的模式：

DROP DATABASE sample_db;

警告：

此操作不可撤销。模式内的所有表、视图和数据都会被删除。

为安全起见：

DROP DATABASE IF EXISTS sample_db;

5.4 管理表和视图

创建表

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

列出表

SHOW TABLES;

创建视图

CREATE VIEW active_users AS
SELECT id, name
FROM users
WHERE active = 1;

列出视图

SHOW FULL TABLES WHERE Table_type = 'VIEW';

删除表或视图

DROP TABLE users;
DROP VIEW active_users;

适当的模式管理显著提升系统的可扩展性和可维护性。

6. 安全处理多语言数据

在多语言环境中，初学者最常遇到的问题是“mojibake”（乱码），其原因是字符编码设置不匹配。

乱码的原因

编码设置必须在以下三个方面保持一致：

  1. 数据库（模式）编码
  2. 表/列编码
  3. 客户端与服务器通信编码

如果这些设置不一致，插入的文本可能会显示为“???”而不是预期的字符。

推荐的编码设置

创建数据库时：

CREATE DATABASE japanese_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

创建表时：

CREATE TABLE messages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content TEXT
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

检查客户端设置：

SHOW VARIABLES LIKE 'character_set%';

如有必要：

SET NAMES utf8mb4;

服务器配置（my.cnf / my.ini）

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

更改后请重启 MySQL。

如果出现乱码

  • 使用 SHOW CREATE TABLE table_name; 检查编码
  • 确保客户端使用 utf8mb4
  • 使用明确的编码进行导出和恢复（例如 --default-character-set=utf8mb4）

7. 常见问题 (FAQ)

Q1. 在 MySQL 中，“模式（schema）”和“数据库（database）”是同一个概念吗？

A1. 是的。在 MySQL 中，它们几乎是同义词。CREATE DATABASE 和 CREATE SCHEMA 的行为相同。

Q2. 如果我不指定字符编码会怎样？

A2. 将使用服务器的默认编码。可能是 latin1 或 utf8，这会导致乱码。请始终显式指定 utf8mb4。

Q3. 我可以之后再更改编码吗？

A3. 可以，使用以下方式：

ALTER DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

但现有的表需要单独进行修改。

Q4. 如何备份模式？

mysqldump -u root -p --databases sample_db > sample_db.sql

恢复：

mysql -u root -p < sample_db.sql

Q5. 如何限制特定用户对模式的访问？

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON sample_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

根据用户角色分配权限，以实现适当的安全性。