在MySQL中存储用户设置的最佳方式？

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:

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

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

   用户表 - 我将为我的用户提供一个表。创建一个名为“preferences”的列，并将序列化数据存储在key => value对中

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

   设置表 - 有一个名为settings的单独表，其中包含user_id列。以相同的方式保存设置

Any input would be appreciated. Thanks :)!

任何输入将不胜感激。谢谢 ：）！

--

-

EDIT: 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.

编辑：只是添加，如果我没有序列化/ json或任何数据放入数据，我必须有一个列为每个设置。

6 个解决方案

#1

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:

对于始终为每个用户设置的任何内容，您应该按照通常的规范化将其保留在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

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

User_Settings.type指定是否应引用整数或字符串字段。

ie:

即：

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');

And for the INSERT/UPDATE issue:

对于INSERT / UPDATE问题：

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:

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

1. You can't retrieve a single value with a query, you must retrieve the entire serialized string, de-serialize it, and discard the unnecessary data.
2. 您无法使用查询检索单个值，您必须检索整个序列化字符串，对其进行反序列化并丢弃不必要的数据。
3. It's easily corruptable, and difficult to recover from.
4. 它容易腐败，难以恢复。
5. It's a pain in the booty to write a raw query for, ie: to globally fix a certain setting.
6. 编写原始查询的赃物很痛苦，即：全局修复某个设置。
7. You're storing what is essentially tabular data within a single table field.
8. 您将在单个表字段中存储基本上是表格的数据。

Sept 2016 Retrospective Edit:

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:

虽然那个表结构并不完全糟糕，但它也不是很好。它试图充分利用糟糕的情况。只要您可以适应这些设置，可选设置的序列化就可以工作：

1. All being loaded at once, no picking or choosing.
2. 所有都是一次装载，没有挑选或选择。
3. Not being indexable, searchable, or easily modified en masse.
4. 不可转换，可搜索或易于修改。

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.

然后，您可以考虑在Users表中添加一个类似optional_settings的字段，其中包含设置的序列化[eg：JSON]形式。你做了以上的交易，但这是一种更简单的方法，你可以存储更复杂的设置。

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

此外，如果您使用像TEXT这样的LOB类型进行存储，则数据不一定存储在“行中”，至少在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.

无论如何，由您决定应用程序的要求和约束是什么，并根据该信息做出最佳选择。

#2

Second option but modified with storing settings separately. Don't store serialized data in a field as in this case is bad practice.

第二个选项，但分别修改存储设置。不要将序列化数据存储在字段中，因为在这种情况下是不好的做法。

Think of this : you have it like that and you store there the country of the user or if it is registered to your newsletter. Later on .. you need to know how many people are from Russia. What do you do? Take them one by one and decode them and increase a counter? (or using your second method and just run a simple SELECT COUNT(id) query?)

想一想：你就是这样的，你可以在那里存储用户的国家或者在你的时事通讯中注册。后来..你需要知道有多少人来自俄罗斯。你是做什么？逐个拿它们解码它们并增加一个计数器？ （或使用您的第二种方法，只运行一个简单的SELECT COUNT（id）查询？）

#3

It's always the same problem: how often do you need to change those data? In my opinion having a separate table is always a good solution, since it separated different aspects of your application.

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

Having a preferencescolumns it's not a good idea, IMHO, because whenever your user changes its settings, you would have to serialize all his data to store them in the users table, whether it's just an update with a separate one. If you really want to store everything in users, you should divide your settings in different columns.

有一个偏好列，这不是一个好主意，恕我直言，因为无论何时你的用户更改其设置，你必须序列化他的所有数据，以将它们存储在用户表中，无论它只是一个单独的更新。如果您确实要将所有内容存储在用户中，则应将设置划分为不同的列。

#4

It all depends on the settings and schema of your database. Whatever you do I would recommend against serializing data as per your suggestion 1, if you serialize the data you can no longer write queries against it, and you would need to deserialize it using the same language you serialized it with (or write something comparable in that language).

这一切都取决于数据库的设置和架构。无论你做什么我都会建议不要按照你的建议1来序列化数据，如果序列化数据就不能再针对它编写查询了，你需要使用你序列化的相同语言对它进行反序列化（或者写一些类似于那种语言）。

The option that I would recommend is putting the settings within the users table, 1 setting per column. The downside to this is when you add a new setting to the application, you will have to write some DDL script to add the new column. One (very good) advantage to this is each setting can have its own data-type. This will also have a storage penalty if this setting is optional.

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

Another option could be using a settings table, as you suggested, with primary key of (user_id, setting_name) with a third column of setting value. This solution assumes all settings are of the same data-type. You do not need to write a DDL script to add a new setting, simply use a new key name.

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

#5

I would go with the settings table, because it will make it easier to access later. When you try to do things like making name value pairs in a single column, you might run into problems later trying do design queries to access the data.

我会使用设置表，因为它将使以后更容易访问。当您尝试在单个列中执行名称值对等操作时，稍后尝试执行设计查询以访问数据时可能会遇到问题。

#6

This may not be appropriate for your current situation, but NoSQL databases (like Mongo) are great for this. This a mongo database for instance, each user object could actually have different properties, and you could still search on them.

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

That said, for the situation you're in, in the past I've followed what other answers here are suggesting, and had a separate table. This can be done in two ways, have a separate table you're more comfortable modifying columns in, and join against it. OR, a simpler solution, having a separate table such as,

也就是说，对于你所处的情况，过去我已经遵循了这里提出的其他答案，并且有一个单独的表格。这可以通过两种方式完成，有一个单独的表，您可以更轻松地修改列中的列，并加入它。或者，一个更简单的解决方案，有一个单独的表，如，

User, Parameter, Value.

用户，参数，价值。

Then each user could have different extra values.

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

The best approach depends on your required performance, etc..

最好的方法取决于您所需的性能等。