当前位置：首页 > news >正文

[MySQL]数据库与表创建

news 来源：原创 2025/9/13 14:36:29

欢迎来到啾啾的博客🐱。
这是一个致力于构建完善 Java 程序员知识体系的博客📚。
它记录学习点滴，分享工作思考和实用技巧，偶尔也分享一些杂谈💬。
欢迎评论交流，感谢您的阅读😄。

本篇简单记录总结一下数据库、表创建事项。
持续更新。

创建数据库

字符集（Charset）与排序规则（Collation）

使用可视化工具创建MySQL数据库时，可以选择数据编码（CHARSET）与排序规则（COLLATE）。
不选择时模式字符集为utf8mb4，排序规则为utfmb4_0900_ai_ci。

下面做这两者的具体解释。

字符集 Charset

字符集是数据库中字符的编码方式，决定了可以存储哪些字符（如字母、符号、表情等）。

utf8mb4：支持完整的Unicode字符（如Emoji），推荐使用。
latin1：仅支持西欧语言字符。
gbk：支持简体中文。
若需多语言支持（如中文、日文、Emoji），使用 utf8mb4（MySQL的utf8仅支持3字节，已过时）。
仅需英文或西欧字符时，可用更节省空间的latin1。

排序规则

排序规则是字符集中字符的比较和排序规则，影响ORDER BY、WHERE查询及索引行为。

排序规则组成部分为：字符集_版本_附加规则_比较规则

以默认的排序规则为utfmb4_0900_ai_ci为例

0900（基于 Unicode 9.0 标准）
常见版本：

unicode:：版本<MySQL8.0使用
0900：新版unicode，MySQL8.0+版本支持
general：较简单的排序规则，性能略优但不精确

ai-发音无关
ci-不区分大小写
常见后缀（附加规则、比较规则）：

ci（Case Insensitive）：不区分大小写（如'a' = 'A'）。
cs（Case Sensitive）：区分大小写（如'a' ≠ 'A'）。
bin（Binary）：按二进制值比较（区分大小写和重音）。
ai（Accent Insensitive）：不区分重音符号（如 a = à = á）。
as（Accent Sensitive）：区分重音符号（如 a ≠ à ≠ á）。

MySQL 8按默认使用utfmb4_0900_ai_ci，之前版本使用utf8mb4_unicode_ci即可。

引擎

MySQL常见的引擎有MyISAM和InnoDB。

特性	MyISAM	InnoDB
事务支持	❌ 不支持事务	✅ 支持 ACID 事务（提交、回滚、隔离）
锁机制	表级锁（写操作会锁全表）	行级锁（写操作仅锁定特定行）
外键约束	❌ 不支持	✅ 支持外键约束
崩溃恢复	❌ 数据易损坏，需手动修复	✅ 通过 redo log 自动恢复，可靠性高
索引结构	非聚簇索引（索引与数据分离）	聚簇索引（主键索引直接存储数据行）
全文索引	✅ 支持（早期版本仅支持 MyISAM）	✅ MySQL 5.6+ 支持
存储文件	3 个文件：`.frm`（表结构）、`.MYD`（数据）、`.MYI`（索引）	1 个文件：`.ibd`（表空间，包含数据和索引）
MVCC（多版本并发控制）	❌ 不支持	✅ 支持，适合高并发读操作
缓存机制	仅缓存索引，数据依赖 OS 缓存	缓存索引和数据（Buffer Pool）

MyISAM逐渐要被淘汰了，但还是有面试题在问两者差别。其实在MySQL5.6开始支持全文索引后，创建的时候基本不考虑MyISAM引擎了。

不过MyISAM的高性能读设计值得了解。仅了解部分和InnoDB设计差异的地方，不一定比InnoDB好。
MyISAM索引与数据完全分离，索引加载到内存的数据更快，数据连续存储扫描更快。
MyISAM 通过 key_buffer_size 配置项缓存索引块，减少磁盘 I/O。InnoDB则是按需加载，LRU淘汰。
不支持事务也就没有更多的锁竞争。
总的来说适合写入后基本不修改的读多写少场景。

MySQL现在默认创建都是InnoDB。

创建表

命名

命名规范

参考这篇"良好的命名规范能减轻工作负担"。创建表也是，需要尽可能精简。
实际生产中，因为表数量特别多，多采取分段式命名，例如：
xx系统_xx模块_xx业务，或者 xx模块_xx实体_xx关系。

另外，MySQL 表名长度限制为 64 个字符（包括字符集编码后的字节数）。

注意事项

还有值得注意的一点，不同操作系统的文件系统对文件名大小写敏感性的处理不同。
Linux通常是大小写敏感的，table1 和 Table1 被视为不同文件。
Windows是大小写不敏感的，table1 和 Table1 被视为相同文件。

MySQL 的 lower_case_table_names 参数定义了表名的大小写处理规则，常用值如下：

0：表名大小写敏感，存储和查询时严格区分大小写。
1：表名存储为小写，查询时大小写不敏感（将表名转换为小写后比较）。
2：表名大小写敏感，但存储时保持原始大小写，查询时大小写不敏感。

Linux/Unix：默认为 0（大小写敏感）。
Windows：默认为 1（表名存储为小写，查询大小写不敏感）。
macOS：默认为 2（大小写敏感存储，查询不敏感，但 macOS 文件系统默认不敏感）。

所以，考虑跨平台影响，推荐设计时全小写表名。

三范式

满足基本三范式:原子性，主键依赖，非传递依赖

第一范式(关系型数据库的基本需求)
列（属性）的原子性
第二范式
满足第一范式，表必须有主键，且非主键属性必须完全依赖于主键。
（非主键列必须直接依赖主键）
第三范式
满足第二范式，且非主键列不传递依赖主键

理解数据类型

可以看之前的这篇MySQL数据类型。

选择数据类型时需要权衡可维护性、可拓展性、存储效率、性能。遵循的原则优先级为：

功能正确性：确保数据类型满足业务需求（范围、精度、操作）。
存储效率：在功能正确的前提下，选择占用空间较小的类型。
性能优化：考虑查询、排序、索引等性能影响。
可维护性和扩展性：选择便于维护和未来扩展的类型。
实现复杂性：尽量降低开发和维护的复杂度。

假设你要设计一个字段存储用户的年龄：

功能正确性：年龄通常是 0-150 之间的整数，TINYINT UNSIGNED（0-255）足够。
存储效率：TINYINT（1 字节）优于 INT（4 字节）。
性能优化：整数类型比字符串类型更快，TINYINT 索引效率高。
可扩展性：TINYINT 足以应对未来需求（150+ 的年龄极少见）。
实现复杂性：TINYINT 直观易用，无需复杂转换。

如果选 VARCHAR(3) 存储年龄，虽然功能上可行，但存储效率低、性能差、维护复杂，完全不可取。

必须字段

有序的主键
在没有主键时，InnoDB会检查是否存在一个唯一非空列索引并将其作为实际上主键，没有则会生成隐式ROWID，6字节长度。
定义主键可以确保数据唯一，避免重复或歧义，也避免隐式主键带来的额外管理开销，隐式主键性能也低于显示主键。

通用冗余字段

为了优化查询性能、简化开发、记录元数据或支持未来扩展，设计表时常在表中添加一些并非严格必需的字段。

created_at、updated_at
创建时间created_at与更新时间updated_at，便于审计和排序。
命名为created_time、updated_time也可以。没这个需求可以不创建，很多时候只创建一个updated_at也能满足需求。
status
支持业务状态管理。
is_deleted
用于实现软删除。
created_by、updated_by
创建人和更新人，适用于多用户系统。

注意，VARCHAR类型可变长度，但在MySQL中，单行最大大小被限制为65535字节，即64KB。

合适的索引

可以看MySQL索引这篇。

目录