MySQL中的数组 [英] array in MySQL

问题描述

我想在记录中存储一个数组.

I want to store an array in a record.

表1:

ID, Name, Friends (friends should be an array)
1, Bill, 2&3
2, Charles, 1&3
3, Clare, 1

我希望能够进行如下搜索:

I want to be able to do a search like this:

SELECT * FROM Table1 WHERE Friends='3'

找到所有将克莱尔(Clare)列为朋友的人

to find everyone who has Clare listed as a friend

推荐答案

除非您有充分的理由这样做，否则应保持数据规范化并将关系存储在其他表中.我想也许您正在寻找的是这个

Unless you have a really good reason for doing this, you should keep your data normalized and store the relationships in a different table. I think perhaps what you are looking for is this:

CREATE TABLE people (
    id int not null auto_increment,
    name varchar(250) not null,
    primary key(id)
);

CREATE TABLE friendships (
    id int not null auto_increment,
    user_id int not null,
    friend_id int not null,
    primary key(id)
);

INSERT INTO people (name) VALUES ('Bill'),('Charles'),('Clare');

INSERT INTO friendships (user_id, friend_id) VALUES (1,3), (2,3);

SELECT *
  FROM people p
    INNER JOIN friendships f
      ON f.user_id = p.id
  WHERE f.friend_id = 3;

+----+---------+----+---------+-----------+
| id | name    | id | user_id | friend_id |
+----+---------+----+---------+-----------+
|  1 | Bill    |  1 |       1 |         3 |
|  2 | Charles |  2 |       2 |         3 |
+----+---------+----+---------+-----------+
2 rows in set (0.00 sec)

这篇关于MySQL中的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！