[MySQL]数据库与表创建

欢迎来到啾啾的博客🐱。
这是一个致力于构建完善 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逐渐要被淘汰了，但还是有面试题在问两者差别。其实在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数据类型。

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

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

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

• 功能正确性：年龄通常是 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。

合适的字段数量

要小心控制表的宽度，即列数。过多的列会带来大的存储开销、降低缓存命中率、降低查询效率。且维护复杂，索引难以覆盖等等……
一般追求性能时不建议一张表的宽度超过20列。

合适的索引

可以看MySQL索引这篇。