何时在DB列中使用逗号分隔的值？ [英] When to use comma-separated values in a DB Column?

问题描述

好的，我知道技术答案是从来没有。

OK, I know the technical answer is NEVER.

但是，有时候，似乎使事情变得更容易，代码少，看起来很少的缺点，所以请在这里我出来。

BUT, there are times when it seems to make things SO much easier with less code and seemingly few downsides, so please here me out.

我需要建立一个名为限制的表格来跟踪人们想要的类型的用户要联系，并将包含以下3列（为了简单起见）：

I need to build a Table called Restrictions to keep track of what type of users people want to be contacted by and that will contain the following 3 columns (for the sake of simplicity):

minAge
lookingFor
drugs

lookingFor code> drug 可以包含多个值。

lookingFor and drugs can contain multiple values.

数据库理论告诉我，我应该使用连接表以跟踪用户可能为这些列中选择的多个值。

Database theory tells me I should use a join table to keep track of the multiple values a user might have selected for either of those columns.

但是，似乎使用逗号分隔的值事情更容易实现和执行。以下是一个例子：

But it seems that using comma-separated values makes things so much easier to implement and execute. Here's an example:

假设用户1 有以下限制：

minAge => 18
lookingFor => 'Hang Out','Friendship'
drugs => 'Marijuana','Acid'

现在我们来说说 User 2 联系用户1 。那么首先我们需要看看他们是否符合用户1 的限制条件，但这很简单，只要使用逗号分隔的列即可。

Now let's say User 2 wants to contact User 1. Well, first we need to see if he fits User 1's Restrictions, but that's easy enough EVEN WITH the comma-separated columns, as such:

首先我会得到目标的（用户1 ）限制：

First I'd get the Target's (User 1) Restrictions:

SELECT * FROM Restrictions WHERE UserID = 1

现在我把它们放入相应的变量 as-is into PHP：

Now I just put those into respective variables as-is into PHP:

$targetMinAge = $row['minAge'];
$targetLookingFor = $row['lookingFor'];
$targetDrugs = $row['drugs'];

现在我们只需检查SENDER（用户2 ）是否适合这种简单条件：

Now we just check if the SENDER (User 2) fits that simple Criteria:

COUNT (*) 
   FROM Users
WHERE 
   Users.UserID = 2 AND
   Users.minAge >= $targetMinAge AND
   Users.lookingFor IN ($targetLookingFor) AND
   Users.drugs IN ($targetDrugs)

最后，如果COUNT == 1，用户2 可以联系用户1 ，否则他们不能

Finally, if COUNT == 1, User 2 can contact User 1, else they cannot.

有多简单？这似乎真的很容易和直接，所以每当用户更新他们的联系限制时，只要我将数据库的所有输入消毒，那么这样做的真正问题是什么？能够使用MySQL的 IN 函数，并且已经以它将理解的格式（例如逗号分隔值）存储多个值似乎使事情比创建更容易连接每个多选列的表。我给了一个简化的例子，但是如果有10个多选列呢？那么事情开始变得混乱，这么多的连接表，而CSV方法保持简单。

How simple was THAT? It just seems really easy and straightforward, so what is the REAL problem with doing it this way as long as I sanitize all inputs to the DB every time a user updates their contact restrictions? Being able to use MySQL's IN function and already storing the multiple values in a format it will understand (e.g. comma-separated values) seems to make things so much easier than having to create join tables for every multiple-choice column. And I gave a simplified example, but what if there are 10 multiple choice columns? Then things start getting messy with so many join tables, whereas the CSV method stays simple.

所以，在这种情况下，真的很糟糕，如果我使用逗号分隔价值？

So, in this case, is it really THAT bad if I use comma-separated values?

* *

*ducks*

推荐答案

您已经知道答案。

首先，您的PHP代码甚至不会工作，因为只有在用户2在LookingFor或Drugs中只有一个价值。如果这些列中的任一列包含多个逗号分隔的值，那么即使这些值的顺序与用户1的值完全相同，IN也不会工作。如果右侧有一个或多个逗号，那么希望IN做什么？

First off, your PHP code isn't even close to working because it only works if user 2 has only a single value in LookingFor or Drugs. If either of these columns contains multiple comma-separated values then IN won't work even if those values are in the exact same order as User 1's values. What do expect IN to do if the right-hand side has one or more commas?

因此，做不容易你想在PHP中实际上这是一个很痛苦的事情，它将涉及将用户2的字段拆分为单个值，使用许多OR编写动态SQL来进行比较，然后执行非常低效的查询来获取结果。

Therefore, it's not "easy" to do what you want in PHP. It's actually quite a pain and would involve splitting user 2's fields into single values, writing dynamic SQL with many ORs to do the comparison, and then doing an extremely inefficient query to get the results.

此外，您甚至需要编写PHP代码以回答关于两个集合的交集的这样一个相对简单的问题的事实意味着您的设计有严重的缺陷。这就是SQL存在的解决问题的那种问题（关系代数）。正确的设计允许您解决数据库中的问题，然后在PHP或其他技术上简单地实现一个表示层。

Furthermore, the fact that you even need to write PHP code to answer such a relatively simple question about the intersection of two sets means that your design is badly flawed. This is exactly the kind of problem (relational algebra) that SQL exists to solve. A correct design allows you to solve the problem in the database and then simply implement a presentation layer on top in PHP or some other technology.

做正确，你会有一个更容易的时间。

Do it correctly and you'll have a much easier time.

这篇关于何时在DB列中使用逗号分隔的值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！