mysql 数组 或,MySQL中的数组

I want to store an array in a record.

Table1:

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'

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)