如何将数组设置为 mysql 用户变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11424969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to set an array as a mysql user variable
提问by Chris B
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:
我没想到这会如此困难，但我试图在 MySQL 中设置一个用户变量以包含一个值数组。我不知道如何做到这一点，所以尝试做一些研究，但很惊讶没有找到答案。我试过了：
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!
joostschouten 似乎找到了最优雅的解决方案（我自己还没有测试过）但是下次我写一些需要这个的东西时，我会记得测试它！
回答by hepabolu
Just found the answer here: How to cycle with an array in MySQL?
刚刚在这里找到答案：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);
回答by Kostanos
As Marc B mentioned, there is no array variable in MYSQL.
正如 Marc B 所提到的，MYSQL 中没有数组变量。
The alternative to find_in_setsolution is to use SELECTwith UNIONto simulate the array:
find_in_set解决方案的替代方案是使用SELECT和UNION来模拟数组：
SELECT billable_type FROM (
SELECT 'client1' AS billable_type UNION
SELECT 'client2' AS billable_type UNION
SELECT 'client3' AS billable_type) AS t
So your query will looks like that:
所以你的查询看起来像这样：
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 (
SELECT billable_type FROM (
SELECT 'client1' AS billable_type UNION
SELECT 'client2' AS billable_type UNION
SELECT 'client3' AS billable_type) AS t
)
and tlg.task_id = mttl.id
group by start_date
order by start_date desc;
回答by MrMayor
If the user has the CREATE TABLE privilege, an array can be simulated by creating a temporary, single-column table. A value or values in the table can be retrieved with a SELECT statement. Temporary tables are dropped at the end of the session, but it's a good idea to explicitly drop them once they're no longer needed.
如果用户具有 CREATE TABLE 权限，则可以通过创建临时的单列表来模拟数组。可以使用 SELECT 语句检索表中的一个或多个值。临时表会在会话结束时删除，但最好在不再需要它们时显式删除它们。
CREATE TEMPORARY TABLE billable_types (c VARCHAR(16));
INSERT INTO billable_types VALUES ('client1'), ('client2'), ('client3');
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 (SELECT * FROM billable_types)
and tlg.task_id = mttl.id
group by start_date
order by start_date desc;
DROP TABLE billable_types;
