MySQL in clause string to array

Question

I saw many similar questions some how i could not find my answer. Here is the deal. I have two tables. I'm trying to find a way to stimulate a text array into in-clause.

table.list    table.user
+--------+    +--------+
|id|list |    |id|name |
+--+-----+    +--+-----+
| 1|1,2,3|    | 1|jack |
+--+-----+    +--+-----+
| 2|6,8  |    | 2|john |
+--+-----+    +--+-----+
              | 3|jane |
              +--+-----+
              | 6|jim  |
              +--+-----+
              | 8|jade |
              +--+-----+

Here is my lovely piece of in-clause code.

SELECT (SELECT GROUP_CONCAT(user.name) 
          FROM user WHERE user.id in (list.list)
       ) 
FROM list;

My question is is there any way to convert list.list to an array. I know the difference. Normally it should be like in (1,2,3) or ('1','2','3') but now i have ('1,2,3'). Any ideas.

Fabien TheSolution · Accepted Answer · 2014-01-18 14:37:00Z

4

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE list
    (`id` int, `list` varchar(5))
;

INSERT INTO list
    (`id`, `list`)
VALUES
    (1, '1,2,3'),
    (2, '6,8')
;

CREATE TABLE user
    (`id` int, `name` varchar(4))
;

INSERT INTO user
    (`id`, `name`)
VALUES
    (1, 'jack'),
    (2, 'john'),
    (3, 'jane'),
    (6, 'jim'),
    (8, 'jade')
;

Query 1:

SELECT (SELECT GROUP_CONCAT(user.name) 
 FROM user WHERE instr(concat(' ,',list.list,','),concat(',',user.id,',')) > 1
 ) 
FROM list

Results:

jack,john,jane  
jim,jade

edited Jan 18, 2014 at 14:37

answered Jan 18, 2014 at 4:24

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Merrin K Over a year ago

It worked, can you explain how it works.

Anthony Stramer · Accepted Answer · 2014-01-18 03:29:44Z

You should add another table to create the link between list.id and user.id. It is not a good idea to use comma separated lists in a database. They are extremely hard to operate on and are much slower than joining well indexed tuples.

If a user can only exist in one list then it would be best to add an additional column to user. Otherwise if user could exist in multiple lists then you would use something like the following.

list    user          user_to_list
+--+    +--------+    +---------------+
|id|    |id|name |    | userId|listId |
+--+    +--+-----+    +-------+-------+
| 1|    | 1|jack |    |      1|1      |
+--+    +--+-----+    |      2|1      |
| 2|    | 2|john |    |      3|1      |      
+--+    +--+-----+    |      6|2      |
        | 3|jane |    |      8|2      |
        +--+-----+    +-------+-------+
        | 6|jim  |
        +--+-----+
        | 8|jade |
        +--+-----+

You can also read more on Database Normalization on Wikipedia (http://en.wikipedia.org/wiki/Database_normalization).

Collectives™ on Stack Overflow

MySQL in clause string to array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related