3.1 第一张表和第一条数据

设计数据表的时候，永远首先想清楚第一条数据是怎么来的。

用这句话作为开篇语，是因为这是我的编程生涯里最常用到的准则。 具体哪本书书里面提出的已经记不得了，但是简单一句话背后的逻辑其实有几个点：

1. 数据结构的明确：从第一条数据开始思考，可以帮助我们明确数据的结构和类型。了解数据的具体字段和类型是设计数据库的基础。

2. 数据完整性:考虑第一条数据也意味着要思考如何保证数据的完整性。这包括了解如何处理缺失或错误的数据，以及如何保证数据的准确性和一致性。

3. 数据关系的定义：业务上的一条数据很多时候不是数据库中的一条数据，这一点我们在讲数据关系的时候再展开叙述。

4. 性能优化：在列出数据的时候，我们就应该意识到哪些在业务上有搜索的需求，哪些可能用来排序，这在建立数据库索引的时候，也会影响到性能。

我们先来看我们的核心业务：提示词的管理。 要分享，首先得有第一条数据，数据可能长这样：

字段名	说明
title	提示词的标题或名称，方便用户理解和搜索
description	提示词的描述，可以是一个简短的摘要或详细的描述
body	提示词的主体内容，这是提示词的核心部分

这3个字段是少不了的核心。 除此之外，还有一些跟业务无关的东西要放进去。

字段名	说明
prompt_id	提示词的唯一标识符，用于在数据库中唯一标识一个提示词
created_at	提示词的创建时间，用于追踪提示词的生命周期
updated_at	提示词的最后更新时间，用于追踪提示词的修改历史

然后考虑管理和呈现上的需求：

字段名	说明
status	提示词的状态，例如，是否被审核、是否被发布、是否被删除等
category	提示词的类别，例如，它可能属于某个特定的主题或类别

再来考虑运营时让用户互动时产生的需求：

字段名	说明
usage_count	提示词被使用的次数，可以是一个计数器，用于追踪提示词的受欢迎程度
likes	提示词获得的喜欢或赞的数量，用于追踪提示词的受欢迎程度
tags	与提示词相关的标签，可以用于分类和搜索

你还能想到什么吗？ 我们先来整理一下：

字段名	说明
prompt_id	提示词的唯一标识符，用于在数据库中唯一标识一个提示词
title	提示词的标题或名称，方便用户理解和搜索
description	提示词的描述，可以是一个简短的摘要或详细的描述
body	提示词的主体内容，这是提示词的核心部分
creator_id	创建提示词的用户的唯一标识符，用于追踪谁创建了这个提示词
created_at	提示词的创建时间，用于追踪提示词的生命周期
updated_at	提示词的最后更新时间，用于追踪提示词的修改历史
usage_count	提示词被使用的次数，可以是一个计数器，用于追踪提示词的受欢迎程度
likes	提示词获得的喜欢或赞的数量，用于追踪提示词的受欢迎程度
tags	与提示词相关的标签，可以用于分类和搜索
status	提示词的状态，例如，是否被审核、是否被发布、是否被删除等
category	提示词的类别，例如，它可能属于某个特定的主题或类别

然后我们再将它们的字段类型和长度定义好。

3.1.1 时间格式到底怎么存？

字段名	数据类型	长度	主键	是否可空	默认值	描述
prompt_id	INT		是	否		提示词ID
title	VARCHAR	255		否		提示词标题
description	TEXT			是		提示词描述
body	TEXT			否		提示词主体内容
creator_id	INT			否		创建者ID
created_at	？			否		创建时间
updated_at	？			否		更新时间
usage_count	INT			否	0	使用次数
likes	INT			否	0	喜欢的数量
tags	VARCHAR	255		是		标签
status	TINYINT			否		状态
category	VARCHAR	255		是		类别

这里的crated_at和update_at到底用什么格式呢？

TIMESTAMP，我相信很多人第一反应都是这个。 但是我选择的是INT(10)，存储的是unix时间戳。

UNIX时间戳（UNIX Timestamp）是自1970年1月1日（UTC，世界协调时间）以来经过的秒数，不包含闰秒。它是一种常用的表示时间的方式，特别是在计算机系统和编程中。因为它是一个纯数字形式，所以处理起来非常简单和直接。

使用它的好处是：

1. 平台独立性:

   • INT数据类型是通用的，它不受任何特定数据库或平台的限制。无论是MySQL、PostgreSQL还是SQLite，INT类型的处理方式都是一样的。而TIMESTAMP的实现可能会因不同的数据库系统而有所不同，碰到不同的语言，也会有不同的处理方式。

2. 存储格式:

   • 使用INT存储时间戳，是一种简单、明确且一致的方式。它以整数格式存储，容易理解和处理。

   • 而TIMESTAMP类型通常会以特定的日期时间格式存储，可能需要进行格式转换以满足应用程序的需求。

3. 时区处理:

   • MySQL会自动将TIMESTAMP字段从服务器的时区转换为UTC进行存储，并在检索时将其转回服务器的时区。

   • INT类型的时间戳通常是时区无关的，它只是一个表示特定时刻的数字。这对于跨时区的应用来说是一个好处，因为它使得时间的处理变得简单和一致。

   • 而TIMESTAMP类型可能会受到时区设置的影响，这可能会导致时间处理变得更为复杂。

4. 可迁移性:

   • 使用通用的INT类型存储时间戳可以简化数据的迁移过程，因为你不必担心不同数据库系统之间的TIMESTAMP实现差异。

至于DATETIME，因为要占用8个字节，而INT只要4个字节。 当然，还有一点非常重要，很多新手在看到INT(10)以为10是占用的空间，其实10不是指字节大小或数据长度，而是指显示宽度。在MySQL中，无论是INT(9)、INT(10)还是INT(11)，它们的实际存储空间都是4字节，而且它们的索引速度也是相同的，因为它们实际上都是INT类型。显示宽度对存储和索引没有任何影响。

3.1.2 数据库三范式与多对多关系

字段名	数据类型	长度	主键	是否可空	默认值	描述
tags	VARCHAR	255		是		标签

这个字段是用来存储标签的，我们可以用逗号隔开，例如"teacher,roleplay,fun"，这样可以实现一个提示词对应多个标签。 在搜索的时候，我们只需要搜索对应的标签即可。 但是，如果我们要搜索同时包含teacher和fun的，那么SQL写出来就是：

SELECT * FROM prompts WHERE tags LIKE '%teacher%' AND tags LIKE '%fun%';

这样的查询会返回所有标签字段中同时包含teacher和fun的记录。

然而，这种设计方式（使用逗号分隔的字符串来存储多个值）在处理多值字段时会有一些问题和限制：

1. 搜索效率低：上述的SQL查询中使用了两次LIKE操作，这种操作很难被优化，特别是当数据量变大时，它的效率会非常低。

2. 数据冗余：同一个标签可能会在多个记录中重复出现，这增加了数据冗余。

3. 数据更新困难：如果需要修改或删除某个标签，你需要搜索整个表，并在每个记录中进行字符串替换或删除操作。

4. 不易扩展：如果未来需要对标签进行更复杂的操作（如统计每个标签的使用次数），这种设计将很难满足需求。

要解决这个问题，我们就不得不提到数据库的五范式，然而第四和第五范式在实践中去极端地消除冗余并不那么好用（投入产出比的不匹配），后来只有前三个范式被多数项目所遵从。

1. 第一范式(1NF):

   • 数据库表中的字段都是单一属性，不允许出现重复的列，每个字段不能再分。

2. 第二范式(2NF):

   • 在1NF的基础上，消除了非主属性对码的部分函数依赖（一个表只描述一种关系，非主属性完全依赖于主键，而不是依赖于主键的一部分）。

3. 第三范式(3NF):

   • 在2NF的基础上，消除了非主属性对码的传递函数依赖（非主属性不依赖于其他非主属性）。

看不明白的话，没关系，网上很多相关的教程可以查阅。 在这里我不展开讲，只做简单描述。

在出现以逗号分隔的tags字段时，这个设计违反了第一范式（1NF）。第一范式要求表中的每个字段都是原子的，不能再分割。在这个设计中，tags字段是一个逗号分隔的列表，可以再分割为多个独立的标签。因此，该设计不满足第一范式。

由于不满足第一范式，也就不可能满足更高的范式（2NF, 3NF, BCNF, 4NF, 5NF）。在实际应用中，有时为了查询效率和开发效率，可能会选择违反第一范式，但这通常会带来更复杂的代码和可能的数据一致性问题。例如，在更新、删除或查询特定标签时，处理逗号分隔的字符串会比处理规范化的设计更复杂。

为了解决这些问题，通常会采用将标签单独存储在一个表中，并通过中间表来维护提示词和标签之间的关系，这样的设计更符合数据库的规范化原则，同时也更易于维护和扩展。例如，你可以创建一个tags表和一个prompt_tags中间表，如下所示：

tags表：

id	name
1	teacher
2	roleplay
3	fun

prompt_tags表：

prompt_id	tag_id
...	...

manyToMany关系是一种多对多的关系。 在这种关系中，一个表中的记录可以与另一个表中的多个记录关联，反之亦然。

• 优点：

  • 能够处理复杂的业务关系，适用的场景较为广泛。

• 缺点：

  • 数据模型和查询较为复杂，通常需要通过中间表来维护多对多的关系。

  • 数据冗余可能会比较严重，需要花费额外的精力来保持数据的一致性。

• 实现：

  • 通常通过一个中间表来实现多对多关系。中间表至少包含两个外键，分别指向参与多对多关系的两个表，就跟上面本项目中所表示的一样。

我们添加完中tags表和中间表以后，当需要搜索包含特定标签的提示词时，可以通过简单的JOIN操作来实现，这会比使用LIKE操作具有更高的效率和准确性。

3.1.3 为什么MySQL的字段名不要包含大写字母

在MySQL中，我会要求团队使用小写字母作为字段名，而且我所在的很多企业都有这个准则，追究其原因主要有：

1. 平台兼容性：

   • 不同的操作系统和数据库管理系统可能对大小写敏感或不敏感。例如，在Windows和Mac OS X中，MySQL表名和列名的大小写不敏感，而在Linux和Unix中，它们是大小写敏感的。使用小写可以避免跨平台时产生的问题。

2. 一致性：

   • 当所有的字段名都是小写时，代码看起来更加整洁和一致。这样，开发人员不需要记住每个字段的大小写规则，也不容易因为大小写问题而产生bug。

3. 易读性：

   • 使用小写字母和下划线（例如：user_name）的命名规则，通常更容易阅读和理解，而不是使用驼峰命名法（例如：UserName）。

4. 避免混淆和错误：

   • 如果不统一规定大小写，开发者可能会随意使用大小写，容易造成混淆，例如在查询时可能因为大小写错误而找不到对应的字段。

5. 开源项目和库的兼容性：

   • 很多开源项目和库可能默认字段名是小写的，遵循这个规范可以提高与这些项目和库的兼容性。

6. 国际通用标准：

   • 在很多编程社区和开源项目中，小写字母命名是一种普遍接受的或推荐的做法。遵循这种通用标准，可以让其他开发者更容易理解和接受你的代码。

7. 简化数据库迁移：

   • 如果未来需要迁移到其他数据库系统，统一的小写命名规则可能会简化迁移过程，减少因大小写不匹配导致的问题。

8. 减少开发过程中的错误和额外的工作：

   • 在编写SQL查询时，不需要额外地为大小写问题而考虑或者使用双引号和反引号来区分大小写。

遵循小写命名规范是一个好的实践，它可以帮助减少在开发过程中遇到的很多不必要的问题。