MySQL Split to use in "SELECT WHERE IN" statement

Question

I've been scouring various MySQL sites in the hope of finding something that will allow me to turn this:

var parameters = "a,b,c,d"; // (Could be any number of comma-delimited values)

Into this (assuming that my parameters are somehow becoming the values you see in the IN block):

SELECT * FROM mytable WHERE parametertype IN('a', 'b', 'c', 'd');

But I'm not having a great deal of success! The best site I've found has been: dev.mysql.com, which discusses the splitting of strings based on a delimiter (',' in my case) although it hasn't turned up any answers...

Does anyone know of a good way of splitting a comma-delimited string into a group of strings that can be used in this context?

Answer 1

you can use dynamic sql

delimiter //; 
create procedure tst() 
begin 
prepare stmp from 'INSERT INTO LOGG SELECT PFILE_Filename FROM PFILE_FILE'; 
execute stmp; 
deallocate prepare stmp; 
end// 
delimiter ;//

you have to do something like this

you have a part of sql query

 SELECT * FROM mytable WHERE parametertype IN( 

then you join it with a string passed as a parameter "'a', 'b', 'c', 'd'" using CONCAT function for example into stmp as above and then execute stmp

Answer 2

It may not have all the flexibility you need, but the MySQL FIND_IN_SET function might be sufficient. There's a hard limit of a maximum of 64 values in the set to compare with though.

For example:

SELECT  *
FROM    mytable
WHERE   FIND_IN_SET( parametertype, 'a,b,c,d' ) != 0

This is an example of the use of an in-line MySQL SET ('a,b,c,d') - its sort-of an enum. It may be a sign that something is awry with the normalisation of the data model if these are being used. But, they can be handy to eliminate a join, or (as in this case) to associate with complex information that resides outside the database.