Implementing arrays in MySQL

Question

Is there any provision for creating arrays (variable length would be preferable) in MySQL . I need to create 2-3 lists for each user and update/append/delete items from the lists . How do I implement them? . Creating tables for each user will lead to a total mess in my opinion, but anyway I'm new to MySQL so please help me out . Thanks

EDIT:The lists will be one dimensional and contain the ids of other users.

Answer 1

Well it is not possible to create arrays in mysql as of now. but however, you can maintain xml in mysql(required 5.1+)

check below link for same

http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html

Answer 2

It would be better if you told us what you are trying to do, not how you are trying to achieve it. Anyway, you can solve it using three tables List1, List2 and List3 with columns USER_ID, YOUR_VALUE.

You query the list for the specified user using this query:

SELECT YOUR_VALUE FROM List1 WHERE USER_ID=<user id>

Updating values:

UPDATE List1 SET YOUR_VALUE=<value> WHERE USER_ID=<user_id>

Deleting all values for a specified user:

DELETE FROM List1 WHERE USER_ID=<user_id>

Delete some values (depending on a condition) for a given user:

DELETE FROM List1 WHERE USER_ID=<user_id> AND YOUR_VALUE=<value>