MySQL Variable for IN clause

Question

I have a need to use a variable in my WHERE IN (@variable) clause because I have multiple and dynamic values being passed into my stored procedure variable. I've seen this done with MS SQL before by creating a function that will split the parameters but I'm not quite sure if that can be implemented on MySQL.

Is there anyway I can get this to work because I really don't have any other options?

Thanks

Mosty Mostacho · Accepted Answer · 2012-02-09 03:03:01Z

4

Maybe a FIND_IN_SET can help you.

answered Feb 9, 2012 at 3:03

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Encryption Over a year ago

I thought that might work at first, but I would be implementing like so: FIND_IN_SET(id, table). Where ID is the string of variables and table would refer to the table/field I need to check. However, on the mySQL reference manual it says FIND_IN_SET does not work if the first arguement contains a comma (,)

Encryption Over a year ago

Thanks for the help. This actually works in the following way: FIND_IN_SET (table_column, variablestring). I had it backwards, originally.

cronoklee Over a year ago

Seems to be MUCH slower than IN. Not practical for my use case.

theglauber · Accepted Answer · 2012-02-08 23:14:28Z

1

How about putting the values you're interested in, in a temp table, then either joining the temp table with your main table or using it in a sub-select statement?

Or use an actual table, with a column for your run-id and another column for the values, and use the run-id in your join so you can have parallel invocations of your sql or stored procedure.

answered Feb 8, 2012 at 23:14

theglauber

29.9k7 gold badges31 silver badges47 bronze badges

4 Comments

Encryption Over a year ago

I'm not sure I follow your second paragraph. However, the temp table is doable, but I need to call the stored procedure that creates it from the first stored proc I'm running. I found a MS SQL Function that can support this, but MySQL doesn't have functions? Is is possible to translate this code into MySQL?

Encryption Over a year ago

CREATE FUNCTION [dbo].[Fn_SplitParms] ( psCSString VARCHAR(MAX) ) RETURNS otTemp TABLE(sID VARCHAR(20)) AS BEGIN DECLARE sTemp VARCHAR(10) WHILE LEN(psCSString) > 0 BEGIN SET sTemp = LEFT(psCSString, ISNULL(NULLIF(CHARINDEX(',', psCSString) - 1, -1), LEN(psCSString))) SET psCSString = SUBSTRING(psCSString,ISNULL(NULLIF(CHARINDEX(',', psCSString), 0), LEN(psCSString)) + 1, LEN(@psCSString)) INSERT INTO otTemp VALUES (sTemp) END RETURN END

theglauber Over a year ago

Sorry, i haven't used MySQL. The second paragraph was an after-thought in case you couldn't use temp tables. If you have to use an actual table, then you need to do something to separate the data from different invocations (simultaneous or not). You can clean up the table at the end of the day or clean up partially after each run. Hopefully someone more versed in MySQL than me can help you further.

Mike Christensen Over a year ago

This is one of the many reasons I use PostgreSQL (Ack troll get out!)

BritishWerewolf · Accepted Answer · 2021-06-10 13:02:01Z

0

I might be too late to the party, but keeping this since this also can be useful.

SET @data ='12323,12323,84949,2323223';
SET @query = CONCAT("select * FROM table where column in (",@data,")");
PREPARE stmt1 FROM @query;
EXECUTE stmt1;

edited Jun 10, 2021 at 13:02

BritishWerewolf

3,9785 gold badges34 silver badges54 bronze badges

answered Jun 9, 2021 at 5:03

Pasindu

812 silver badges8 bronze badges

Collectives™ on Stack Overflow

MySQL Variable for IN clause

3 Answers 3

3 Comments

4 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

Comments

Related