mysql - 如何在 MYSQL 列中添加多个项目

Question

我目前有一个表格，其中包含一个人的用户名、密码、电子邮件和元素(我所说的元素是指您可以在游戏中收集的元素，也称为“库存”)。但是，如何在项目列中添加多个项目？例如，如果我想在此处添加项目 A、B 和 C:

Answer 1

您应该使用 2 个表来存储此数据。

users: username | password | email
-------------------------
items: username | item

然后您可以在第二个表中为给定的用户名插入无限数量的项目(每个项目都是一条新记录)。

然后您可以像这样为一个用户选择所有项目:

SELECT item FROM items WHERE username = ?

您还可以通过多种方式组合这 2 个表格。

/* this one selects all users who have a specific item */
SELECT u.* FROM users u JOIN items i ON u.username = i.username WHERE i.item = ?

您希望如何使用这些数据？

您还应该开始使用 ID 值(数字)，这样您就不必浪费空间重复基于文本的内容，例如用户名。

---

I think this might be a really good way, however can you show me what you mean by each item is a new record? I'm still a bit confused.

您可以像这样插入值:

insert into items values ('jonathan', 'hat');
insert into items values ('jonathan', 'drink');
insert into items values ('jonathan', 'mouse');
insert into items values ('user1981730', 'hat');
insert into items values ('user1981730', 'mouse');

这会导致:

然后您可以查询所有将drink 作为项目的用户:

SELECT u.* FROM users u JOIN items i ON u.username = i.username WHERE i.item = 'drink'

然后获取user1981730获得的所有项目的列表:

SELECT item FROM items WHERE username = 'user1981730'

一旦您对此感到满意，我们就可以讨论添加 ID 列。

---

what do you mean by ID columns?

请注意，在上面的示例中，items 表有很多重复信息。用户名 jonathan 重复了 3 次，项目 hat 重复了两次。对于真实的数据库，这会更糟。

我们可以通过使用整数 ID 来表示每个唯一值来节省空间并通常提高性能。每个用户都会有一个 ID，每个唯一的项目也会有一个 ID。

让我们从用户开始。我们必须将新列添加到用户表中。

我们将其设为 PRIMARY KEY，这将确保所有值都是唯一的，因此不会有 2 个用户拥有相同的 ID。我们还可以使用 AUTO_INCREMENT 让值随着每条新记录自动增加。

然后我们将更改我们的项目表，以便它使用新列而不是用户名。

注意我们如何仍然拥有与之前相同的信息。 ID 号为 1 的用户有饮料、帽子和鼠标，ID 号为 2 的用户有帽子和鼠标。

我们仍然可以执行与以前相同的查询(有一些小的更改)。

/* this selects all the items for a known user_id */
SELECT item FROM items WHERE user_id = ?

/* this selects all the items for a user when we only know the username */
SELECT i.item FROM items i JOIN users u ON u.user_id = i.item_id WHERE u.username = ?

/* this one selects all users who have a specific item */
SELECT u.* FROM users u JOIN items i ON u.user_id = i.user_id WHERE i.item = ?

到目前为止，我们只是修改了表格以避免重复用户名。如果我们还想避免重复项目名称，我们可以添加一个额外的表。这开始变得棘手，所以我不会详细介绍(现在)，但表结构如下:

users: username | password | email
-------------------------------
items: item_id | name
-------------------------------
users_items: user_id | item_id

users 表和以前一样。 items 表有一个新角色，它存储所有唯一的项目。 users_items 表现在链接其他 2 个表。

---

我将在这里结束 SO 答案。如果您有任何进一步的问题，可以使用上面列出的地址(示例表中使用的地址)给我发电子邮件。