10

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!

  • 2
    there's no such thing as arrays in SQL - there's only sets. Unfortunately, what you're trying to doesn't work - mysql will replace @billtable_types with the contents of your "array" as a single monolithic string, and not consider it as a bunch of separate comma-separated values. Try using the `FIND_IN_SET()` function instead of the `IN` operator. – Marc B Jul 11 '12 at 03:10

3 Answers3

15

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);
Community
  • 1
  • 1
hepabolu
  • 1,214
  • 4
  • 18
  • 29
  • 2
    the "in" keyword does not exist `Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'in @billable_types)` – Mindwin Remember Monica Feb 05 '14 at 14:17
0

As Marc B mentioned, there is no array variable in MYSQL.

The alternative to find_in_set solution is to use SELECT with UNION to simulate the array:

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;
Kostanos
  • 9,615
  • 4
  • 51
  • 65
  • As this thread has surfaced again I will edit my original question and add what I ended up doing. –  May 09 '14 at 05:28
0

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 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;
MrMayor
  • 1
  • 1