如何设置一个数组作为MySQL的用户变量 [英] how to set an array as a mysql user variable
问题描述
我没想到会发现这很困难,但我想设置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.
快进一段时间,我结束了以下快速和肮脏的解决方案,它并没有给我的灵活性,再使用阵列其他地方code,但嘿,这是一个不能充电管理任务,使我不不想花更多的时间就可以了。
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似乎已经找到了最完美的解决方案(没有测试它自己还没有),但下一次我正在写一些东西,呼吁这一点,我会记得测试一下吧!
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!
推荐答案
刚刚找到了答案在这里:的如何使用周期在mysql中的数组?
Just found the answer here: How to cycle with an array in mysql?
set @billable_types = 'client1,client2,client3';
select * from mttl where find_in_set(mttl.type, @billable_types);
这篇关于如何设置一个数组作为MySQL的用户变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!