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)
This looks like the perfect place for a relation table instead: Table 1:
ID, Name
1, Bill
2, Charles
3, Clare
Table 2 (the relation table)
ID, FriendID
1, 2
1, 3
2, 1
2, 3
3, 1
The second table keeps track of the friend relations by connecting ID's from Table1.
You might want a comma-separated value, but it's not recommended. Why not use another table for showing relationships?
Table2
----------------------
PARENT_ID | FRIEND_ID
----------------------
1 | 2
1 | 3
2 | 1
2 | 3
----------------------
