Convert Long list of IN clause into a temporary table

Question

Instead of using a list of items for IN Clause I'm trying to use a temporary table.

The code I used previously.

  SELECT * FROM table1 AS t1
        WHERE itemid in ('3363134,5189076, ...)

The code I'm using for PostgreSQL

    SELECT * FROM table1 as t1 join(
        select itemid from (
            select split('3363134,5189076, ....) as bar )
        cross join unnest(bar) as t(itemid)) as table2 as t2
    on t1.itemid = t2.itemid

I am trying to save list of ids as a temporary table and select from that temporary table using MYSQL. This code works in PostgreSQL but not in MYSQL.

Can you guys give me a hint or some sample code for achieving the same using MYSQL.

MYSQL Version is 5.7

Answer 1

In MySQL it is quite hard to split a string, because there is no built in function.

One not so optimal way is create a stored procedure which split the string like below:

DELIMITER //

CREATE PROCEDURE splitString(
  IN inputString text,
  IN delimiterChar CHAR(1)
)
BEGIN
  DROP TEMPORARY TABLE IF EXISTS temp_string;
  CREATE TEMPORARY TABLE temp_string(vals text); 
  WHILE LOCATE(delimiterChar,inputString) > 1 DO
    INSERT INTO temp_string SELECT SUBSTRING_INDEX(inputString,delimiterChar,1);
    SET inputString = REPLACE(inputString, (SELECT LEFT(inputString, LOCATE(delimiterChar, inputString))),'');
  END WHILE;
  INSERT INTO temp_string(vals) VALUES(inputString);
  SELECT TRIM(vals) as my_string_split FROM temp_string;
END; //

DELIMITER ;

Then you can call it and use it on the join condition :

call splitString('3363134,5189076',',');

SELECT t1.*
FROM  table1 t1
INNER JOIN temp_string ts ON t1.itemid=ts.vals;

https://www.db-fiddle.com/f/uLPPE1DoKjKYBoSXfKahJN/15

Another approach would be creating a numbers table with the max length (how many comma separated values) of your string

For example:

create table numbers (
nr int );

insert into numbers values (1),(2), (3),(4), (5),(6);

Then use following query:

select * from table1 where itemid in (

select 
       SUBSTRING_INDEX(SUBSTRING_INDEX(table2.bar, ',', numbers.nr), ',', -1) as itemid
from numbers 
inner join table2 on CHAR_LENGTH(table2.bar)-CHAR_LENGTH(REPLACE(table2.bar, ',', ''))>=numbers.nr-1
 );

https://www.db-fiddle.com/f/uLPPE1DoKjKYBoSXfKahJN/16

Answer 2

A faster solution (no looping, no splitting):

Setup:

DROP PROCEDURE IF EXISTS Spray;
DELIMITER //
CREATE PROCEDURE Spray(IN _table VARCHAR(64),
                       IN _col VARCHAR(64),
                       IN _string MEDIUMTEXT)
    LANGUAGE SQL
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
    SET @sql = CONCAT('INSERT INTO ', _table,
                      '(', _col, ')',
                      ' VALUES (', REPLACE(_string, ',', '),(' ), ')'
                     );
    PREPARE _sql FROM @sql;
    EXECUTE _sql;
    DEALLOCATE PREPARE _sql;
END //
DELIMITER ;

Test:

CREATE TEMPORARY TABLE test_spray (
    val INT NOT NULL
    );
CALL Spray('test_spray', 'val', "11,22,33,44,55,99999");
SELECT * FROM test_spray;

The table built and the intermediate SQL that populated it:

mysql> SELECT * FROM test_spray;
+-------+
| val   |
+-------+
|    11 |
|    22 |
|    33 |
|    44 |
|    55 |
| 99999 |
+-------+

mysql> select @sql;
+---------------------------------------------------------------------+
| @sql                                                                |
+---------------------------------------------------------------------+
| INSERT INTO test_spray(val) VALUES (11),(22),(33),(44),(55),(99999) |
+---------------------------------------------------------------------+

The main trick is in the REPLACE. To break a sentence into words (assuming one space between words), change some datatypes, and change the REPLACE to

REPLACE(_string, ' ', '"),("' )

If _string might be more than a megabyte long, you will probably need to split it into chunks. Multiple calls to Spray() will add more rows.

The table could be created with an AUTO_INCREMENT column, but I don't see any particular need for such.

(I think this technique will work for any versions of MySQL since 4.1, plus MariaDB.)

[Thanks for forcing me to dream up this solution; I have had need for it.]