在MySQL中存储用户设置的最佳方法?

问题描述：

我只是想知道为我的Web应用程序存储用户特定设置的最佳方法是什么?用户可能仅有的偏好.我想到了两个选择:

I was just wondering what would be the best way of storing user-specific settings for my web applications? Just preferences users may have. I've thought of two options:

用户表-我将为用户提供一个表.创建一个称为首选项"的列，并将序列化的数据存储在键=>值对中

Users table - I'll have a table for my users. Creating a column called "preferences" and storing serialized data there in key => value pairs

设置表-使用名为user_id列的单独表称为设置.以相同的方式保存设置

Settings table - Have a separate table called settings with a user_id column. Save the settings in the same way

任何输入将不胜感激.谢谢:)！

Any input would be appreciated. Thanks :)!

只需添加一下，如果我不进行序列化/json或放入数据中的任何数据，则每个设置都必须有一个列.

Just to add, if I didn't serialize/json or whatever the data to put in the data, I'd have to have a column for each setting.

答

对于始终为每个用户设置的设置的任何内容，您都应将其保留在表，按通常的归一化.至于可选配置，我倾向于喜欢以下表结构:

For anything that is always set for every user you should tend to keep that in the Users table, per usual normalization. As for optional config I tend to like the following table structure:

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

User_Settings.type其中指定应引用整数还是字符串字段.

Where User_Settings.type specifies whether the integer or string field should be referenced.

即:

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问题:

And for the INSERT/UPDATE issue:

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

而且，正如大多数其他人所说的那样，序列化和存储首选项并不是一个特别好的主意，因为:

Also, as most other people are saying, serializing and storing the preferences is not a particularly good idea because:

您无法通过查询检索单个值，必须检索整个序列化的字符串，将其反序列化，并丢弃不必要的数据.
它很容易损坏，也很难恢复.
为原始查询写一个查询很麻烦，例如:全局修复某个设置.
您要在单个表字段中存储本质上是表格数据.

2016年9月的回顾性

在这段时间里，我和人们讨论了如何最好地存储可选设置以及上面定义的常规表结构的一些争论.

Sept 2016 Retrospective

In the intervening time I've had a few arguments with people about how best to store optional settings, as well as the general table structure defined above.

虽然表结构不是完全错误的坏，但它也不完全是好.它试图充分利用糟糕的情况.只要您可以接受以下设置，就可以对可选设置进行序列化:

While that table structure isn't outright bad, it's not exactly good either. It's trying to make the best of a bad situation. Serialization of optional settings can work so long as you can accommodate for these settings:

所有内容都可以立即加载，无需选择.
无法被索引，搜索或轻松修改批量.

然后，您可以考虑在Users表中添加类似optional_settings的字段，其中包含设置的序列化[eg:JSON]形式.您确实需要权衡上述各项，但这是一种更直接的方法，可以存储更复杂的设置.

Then you might consider adding a field like optional_settings in the Users table containing a serialized [eg: JSON] form of the settings. You do trade off the above, but it's a more straightforward approach and you can store more complex settings.

此外，如果您使用像TEXT这样的LOB类型进行存储，则数据不一定至少在MySQL中存储在行中".

Also, if you use a LOB type like TEXT for storage the data is not necessarily stored "in the row" at least in MySQL.

无论如何，您要确定您的应用程序的要求和约束，并根据这些信息做出最佳选择.

Anyhow, it's up to you to determine what your application's requirements and constraints are, and make the best choice based on that information.

在MySQL中存储用户设置的最佳方法?

2016年9月的回顾性

Sept 2016 Retrospective

相关推荐