何时在数据库列中使用逗号分隔值？

发布于 2024-12-05 18:01:01 字数 1604 浏览 0 评论 0原文

好的，我知道技术答案是

但是，有时候，用更少的代码和看似很少的缺点似乎可以让事情变得如此简单，所以请告诉我。

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

minAge
lookingFor
drugs

lookingFor< /code> 和 drugs 可以包含多个值。

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

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

假设用户 1 具有以下限制：

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

现在假设用户 2 想要联系用户 1。好吧，首先我们需要看看他是否符合用户 1 的限制，但这很容易，即使使用逗号分隔的列，如下所示：

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

SELECT * FROM Restrictions WHERE UserID = 1

现在我只是将它们按原样放入 PHP 中：

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

现在我们只需检查 SENDER（用户 2）是否符合这个简单的要求标准：

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，否则他们不能联系。

那有多简单？这看起来非常简单和直接，那么只要我每次用户更新其联系限制时清理数据库的所有输入，这样做的真正问题是什么？能够使用MySQL的IN函数并且已经以它能够理解的格式存储多个值（例如逗号分隔值）似乎使事情变得比必须为每个多个创建连接表容易得多 -选择栏。我给出了一个简化的例子，但是如果有 10 个多项选择列怎么办？然后，由于有如此多的连接表，事情开始变得混乱，而 CSV 方法却很简单。

那么，在这种情况下，如果我使用逗号分隔值真的那么糟糕吗？

**鸭子****

原文

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.

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

Let's say User 1 has the following Restrictions:

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

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:

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

SELECT * FROM Restrictions WHERE UserID = 1

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

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

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)

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

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****

分享到QQ

分享到微博