mysql 用户变量数组,如何设置一个数组作为MySQL的用户变量

I didn't expect to find this so difficult, but I'm trying to set a user variable in MySQL to contain an array of values. I have no clue how to do this so tried doing some research and was quite suprised to find no answer. I have tried:

SET @billable_types = ['client1','client2','client3'];

The reason being I would like to use the variable in the following statement later on:

SELECT sum((time_to_sec(timediff(tlg.time_stop, tlg.time_start))/3600)) as billable_hours

from mod_tmlog_time_log tlg, mod_tmlog_task_list mttl

where date(tlg.time_start) >= @time_start

and date(tlg.time_stop) <= @time_stop

and mttl.type IN (@billable_types)

and tlg.task_id = mttl.id

group by start_date

order by start_date desc;

Would be very grateful for help.

Fast forward a while, I ended up with the following quick and dirty solution which doesn't give me the flexibility of re-using the array elsewhere in the code but hey it's an unchargeable admin task so I don't want to spend any more time on it.

SELECT WEEKDAY(tlg.time_start) AS day_of_week, date(tlg.time_start) as start_date,

sum((time_to_sec(timediff(tlg.time_stop, tlg.time_start))/3600)) as billable_hours

from mod_tmlog_time_log tlg, mod_tmlog_task_list mttl

where date(tlg.time_start) >= @time_start

and date(tlg.time_stop) <= @time_stop

and mttl.type IN ('c1','c2','c3')

and tlg.task_id = mttl.id

group by start_date

order by start_date desc;

joostschouten seems to have found the most elegant solution (not tested it myself yet) but next time I'm writing something which calls for this I will remember to test it!

解决方案set @billable_types = 'client1,client2,client3';

select * from mttl where find_in_set(mttl.type, @billable_types);