在MySQL中存储用户设置的最佳方法是什么？

Question

我在想，对于我的Web应用程序，存储特定于用户的设置的最佳方法是什么？只是用户可能有的偏好设置。我考虑了两个选项：

1. 用户表 - 我会为我的用户创建一个表。创建一个名为"preferences"的列，并以键值对的形式存储序列化数据

2. 设置表 - 使用一个名为"settings"的单独表格和一个"user_id"列。以同样的方式保存设置

感谢任何建议。谢谢 :)！

--

编辑：只是要补充一下，如果我没有将数据序列化/ Json或其他类型的数据放入数据中，我必须为每个设置都有一个列。

- Prash

---

在我看来，一个很好的替代方案是使用 JSON 或序列化，如果你的选项列表不够大（类似于 Blob），那么它应该可以正常工作，这是比 Cookie 更好的选择。 - cristi _b

除非它需要在不同的浏览器之间保持持久性，或者被一个与HTTP响应无直接关系的函数（例如CRON）访问。 - Matthew

@Matthew 没错，cookie 不会持久化。 - cristi _b

Answer 1

对于每个用户始终设置的任何内容，您应该按照通常的规范化方式将其保留在Users表中。至于可选配置，我倾向于使用以下表结构：

TABLE Users:
  id INT AI
  name VARCHAR
  ...

TABLE User_Settings
  user_id INT PK,FK
  name VARCHAR PK
  type BOOL
  value_int INT NULL
  value_str VARCHAR NULL

< p >其中User_Settings.type指定应引用整数或字符串字段。

例如：

INSERT INTO Users (id, name) VALUES (1, 'Sammitch');
INSERT INTO User_Settings (user_id, name, type, value_int) VALUES (1, 'level', 1, 75);
INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'en');

对于INSERT/UPDATE问题：

INSERT INTO User_Settings (user_id, name, type, value_str) VALUES (1, 'lang', 0, 'fr')
  ON DUPLICATE KEY UPDATE value_str='fr';

同时，正如大多数人所说，序列化和存储首选项并不是一个特别好的想法，因为：

1. 您无法通过查询检索单个值，必须检索整个序列化字符串，反序列化它并丢弃不必要的数据。
2. 它很容易损坏，而且很难从中恢复。
3. 编写原始查询非常麻烦，例如：全局固定某个设置。
4. 您正在将本质上是表格数据存储在单个表字段中。

2016年9月回顾编辑：

在这段时间里，我与人们就最佳存储可选设置以及上述定义的一般表结构进行了一些争论。

虽然该表结构并非完全“不好”，但也不完全“好”。 它试图在不良情况下尽力而为。可选设置的序列化可以工作，只要您能够适应这些设置：

1. 全部一次加载，不能挑选或选择。
2. 不可索引，不可搜索或不易批量修改。

然后，您可以考虑在Users表中添加一个类似于optional_settings的字段，其中包含设置的序列化[例如：JSON]形式。 您确实要做出上述权衡，但这是一种更直接的方法，您可以存储更复杂的设置。

此外，如果您使用LOB类型（如TEXT）来存储数据，则在MySQL中数据不一定会存储在行中。

无论如何，最终取决于您确定您的应用程序的要求和约束，并基于该信息做出最佳选择。

- Sammitch

---

@cristi_b 我认为在 user_id,name 上创建复合主键是最好的选择。 - Sammitch

@Prash 这就是键和MySQL的ON DUPLICATE KEY UPDATE语法的作用。 - Sammitch

如果user_id是主键，那么如何拥有多个具有相同主键的行？或者这个问题可以通过复合键来解决吗？如果可以，那太好了，谢谢！如何构建查询语句以根据settings.type的值选择value_int或value_str？ - Prash

我在Google上找到了这个解决方案。回答了我所有的问题，我打算实施它。谢谢。 - Bonxy

只是顺便提一下，如果您的设置值是不同类型的（int、float、string、boolean等），那么最好使用一个（string/enum）列用于类型，另一个（string）用于值。然后您就不需要多个列 - 每个值类型一个... 例如： INSERT [...] VALUE (1, 'level', 'int', '75'); INSERT [...] VALUE (1, 'singer', 'string', 'freddie'); - Lulu

Answer 2

这完全取决于您的数据库设置和模式。无论您做什么，我建议不要像建议1那样对数据进行序列化，如果对数据进行序列化，您将无法针对其编写查询，并且您需要使用与序列化语言相同的语言进行反序列化（或编写相当的语言）。

我建议的选项是将设置放在用户表中，每个列一个设置。缺点是当您向应用程序添加新设置时，您将不得不编写一些DDL脚本以添加新列。这样做的一个（非常好的）优点是每个设置都可以有自己的数据类型。如果此设置是可选的，则还会有存储惩罚。

另一个选项可能是使用设置表，如您所建议，主键为（user_id，setting_name），第三列为设置值。此解决方案假定所有设置都具有相同的数据类型。您无需编写DDL脚本即可添加新设置，只需使用新的键名即可。

- Matthew

---

如何使用一个设置表，每个设置都有一列？ - Prash

我不建议将user_id（1个用户记录，1个设置记录）作为主键，因为这会带来两种解决方案的缺陷。 - Matthew

我明白了，我会考虑一下的。谢谢！ - Prash

每列只能有一个设置。我认为这是一个非常糟糕的想法。我们不希望用所有系统可能支持的设置来堆积用户表。 - B B

Answer 3

这总是同样的问题：你需要多久更改一次这些数据？在我看来，拥有一个单独的表格始终是一个好的解决方案，因为它分离了应用程序的不同方面。

在我的观点中，拥有一个preferences列并不是一个好主意，因为每当用户更改其设置时，您都必须序列化所有他的数据以将它们存储在users表中，无论是只是一个update还是一个单独的表。如果您真的想将所有内容存储在users中，您应该将设置分成不同的列。

- Cynical

Answer 4

第二个选项，但是修改为单独存储设置。不要像这种情况一样在字段中存储序列化数据，这是不好的做法。

想象一下：你把它放在那里，并在那里存储用户的国家或者是否注册了你的通讯。后来...你需要知道有多少人来自俄罗斯。你怎么办？一个一个地取出并解码然后增加计数器吗？（还是使用您的第二种方法，只运行一个简单的SELECT COUNT(id)查询？）

- Sorin Trimbitas

---

你能详细说明第一点吗？我应该如何构建这个表格？ - Prash

为每个设置添加一个表列。 - Sorin Trimbitas

Answer 5

我会选择使用设置表，因为这样以后访问数据会更容易。如果你试图在单个列中创建名称值对之类的东西，那么以后尝试设计查询来访问数据时可能会遇到问题。

- Expedito

Answer 6

这可能不适合您目前的情况，但NoSQL数据库（如Mongo）非常适用于这种情况。例如，一个Mongo数据库中的每个用户对象可能实际上具有不同的属性，您仍然可以对它们进行搜索。

话虽如此，在您所处的情况下，我过去一直遵循其他答案建议的做法，并且拥有一个单独的表格。这可以通过两种方式实现，一种是拥有一个您更容易修改列的单独表格，并与之进行连接。或者，一个更简单的解决方案是拥有一个单独的表格，例如：

用户、参数、值。

然后每个用户都可以拥有不同的额外值。

最佳方法取决于您所需的性能等因素。

- Matt