mysql - 如何对长字符串进行数据库规范化？

Question

我需要一些关于如何规范化当前数据库表的建议。

目前的模式看起来像:

+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| date     | date          | YES  | MUL | NULL    |       | 
| hostname | varchar(255)  | YES  |     | NULL    |       | 
| username | varchar(255)  | YES  | MUL | NULL    |       | 
| path     | varchar(1024) | YES  | MUL | NULL    |       | 
| count    | int(11)       | YES  |     | NULL    |       | 
+----------+---------------+------+-----+---------+-------+

path 是一个很长的字符串，它经常以相同的值重复多次。我尝试将它移到另一个表并将其视为当前表的外键。这将节省大量磁盘空间，因为目前我每周有 3000 万条记录。

问题是，当我将数据插入到另一个名为 Path 的表中时，我不知何故需要知道该路径是否存在。如果是，则获取现有条目并将其作为 FK 分配给当前表的新条目，否则在 Path 表中创建一个新路径条目。我考虑过使 Path 表中的 path 字段唯一，但 mysql 不允许这样做并给出 Specified key was too long;最大 key 长度为 1000 字节错误。

我的问题是:最好的方法是什么:

1. 检查路径值是否存在于另一个表中
2. 如果是，获取它的 id 作为 FK 并在当前表中创建一个条目
3. 如果不是，则创建一个新的路径条目，将PK作为FK并在当前表中创建一个条目

后续问题:

现在我正在做一个长字符串连接来组成一个 INSERT INTO sql 语句来进行数据插入。如果我拆分表，听起来我需要在插入发生之前进行查找。我认为这会大大减慢该过程。有没有办法避免这种情况？

Answer 1

首先，当您用代理 ID 号替换字符串时，您进行的是数据压缩，而不是规范化。没有一个正常的形式需要用数字替换字符串。

I thought about making the path field in Path table unique, but mysql doesn't allow that and gives Specified key was too long; max key length is 1000 bytes error.

嗯，看起来 Path.path 需要是唯一的。您有一些选择。

• 将路径设为 1000 字节而不是 1024。
• 切换到对较长值实现唯一约束的数据库管理系统。 PostgreSQL 可以。
• 在应用程序级别检查唯一性，而不是在数据库级别强制执行唯一性。当心竞争条件。编写代码以支持重复路径的报告。 (如“打印重复路径报告”。)最终，您将找到重复路径。

最常见的方法是调用存储过程来插入行。 SP 不首先检查——它只是插入行并捕获由重复键引发的错误。无论如何，您都必须捕获错误——除了重复键之外，很多事情都可能出错。

如果插入成功，SP返回新的ID号。如果它因重复键错误而失败，它会选择现有路径的 ID 号并返回该 ID 号。如果因其他错误而失败，该怎么办取决于应用程序。

如果先检查再插入，则每次插入都需要往返数据库两次。当您先插入并捕获错误时，您只需要一个新行的圆头。这样可以减轻一点负担。或者很多，具体取决于您在做什么。