1

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

1
  • Also please indicate what version of MySQL you're using.
    – mustaccio
    Jun 15 at 13:23

2 Answers 2

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

0

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.]

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.