How to use a variable as the result of a group_concat in a IN()

Question

These are the tables i have (simplified):

mytable
--------------------
`pid` int(11) NOT NULL AUTO_INCREMENT
`mydate` date NOT NULL,

pictures
--------------------
`pid` int(11) NOT NULL AUTO_INCREMENT
`image_name` varchar(255)

pictures_2014
--------------------
`pid` int(11) NOT NULL AUTO_INCREMENT
`image_name` varchar(255)

This is the code i have:

SET AUTOCOMMIT=0;
START TRANSACTION;
SELECT @pids := GROUP_CONCAT(CONVERT(pid , CHAR(8)))
FROM  `mytable`
WHERE  `mydate`
BETWEEN  '2014-01-01'
AND  '2014-10-31';
INSERT INTO  pictures_2014 SELECT * FROM `pictures` where pid in(@pids);
COMMIT;

Only one item is being inserted into pictures_2014, the first item in the comma separated list.

The result I expect is all 4111 rows that match my first query (at least those in pictures) will be copied. I do know that at least the first 6 pids in the group_concat shown in phpmyadmin exist in the table pictures I also tried adding LIMIT 0,100 to my first query to see if it was a variable length issue but i still only get one result.

score 0 · Answer 1 · answered Jan 23 '15 at 07:31

0

You're doing more work than you have to - you're going to all of the trouble of combining multiple values into a single string and now you're having problems because IN wants to work with multiple values.

Why not something like:

SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO  pictures_2014 SELECT * FROM `pictures`
where pid in(
  SELECT pid
  FROM  `mytable`
  WHERE  `mydate`
  BETWEEN  '2014-01-01'
  AND  '2014-10-31');
COMMIT;

answered Jan 23 '15 at 07:31

Damien_The_Unbeliever

234,701
27
340
448

with 4111 results in both the pictures table results and the mytable results, your suggested method times out – Traveling_Monk Jan 23 '15 at 07:46
@Traveling_Monk - 4111 results *isn't* a lot of data. If it's taking a long time to process, you might want to look for some general guides on analyzing performance and indexing – Damien_The_Unbeliever Jan 23 '15 at 13:35
The fact is that @pids is not processing correctly and i want to know why. the query that you write actually would have 4111x4111=16900321 results the subquery will run every time the main query is. – Traveling_Monk Jan 23 '15 at 19:53

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

ok so this is what i learned: the variable being set is being interpreted as '321,654,987' rather than '321','654','987' (Thanks @John Woo)

By changing the IN() to FIND_IN_SET() i get the right result!

SET AUTOCOMMIT=0;
START TRANSACTION;
SELECT @pids := GROUP_CONCAT(CONVERT(pid , CHAR(8)))
FROM  `mytable`
WHERE  `mydate`
BETWEEN  '2014-01-01'
AND  '2014-10-31';
INSERT INTO  pictures_2014 SELECT * FROM `pictures` where FIND_IN_SET(pid,@pids);
COMMIT;

edited May 23 '17 at 11:56

Community

1
1

answered Jan 23 '15 at 20:17

Traveling_Monk

778
2
11
28

After further research FIND_IN_SET wont work for my purposes, it appears that 1024 characters is the max usable size in the second parameter in FIND_IN_SET( str,strlist) i figured this out since my pid's are 6 digits and 146 records was the max this transaction would insert so 6 + 1 for the comma and 2 for the quote marks ((6+1)*146)+2 =1024 – Traveling_Monk Jan 25 '15 at 07:30
ok another test discovers that the limitation isn't in the functions but in the length of the variable... SELECT LENGTH( @pids ) ==1024 – Traveling_Monk Jan 25 '15 at 18:49

How to use a variable as the result of a group_concat in a IN()

2 Answers2