How to handle session value interpretation in MySQL

Question

I have a following schema.

DROP TABLE IF EXISTS REF_CONNECTION1;
CREATE TABLE  REF_CONNECTION1 (
  BPIN INT(11) DEFAULT NULL,
  RIN INT(11) DEFAULT NULL,
  SRC_RIN INT(11) DEFAULT NULL,
  PRIMARY KEY (BPIN,RIN,SRC_RIN)
);

INSERT INTO REF_CONNECTION1 VALUE(14,1,2);
INSERT INTO REF_CONNECTION1 VALUE(14,1,3);
INSERT INTO REF_CONNECTION1 VALUE(14,1,4);
INSERT INTO REF_CONNECTION1 VALUE(14,1,5);
INSERT INTO REF_CONNECTION1 VALUE(14,1,6);
INSERT INTO REF_CONNECTION1 VALUE(14,1,7);

SET @S1 = '2,3,4';

SELECT * FROM REF_CONNECTION1 WHERE SRC_RIN IN (@S1);

Result Set is giving only 1 row, but it should have given 3 rows. I am understanding why it is happening because it is actually converting to following query

SELECT * FROM REF_CONNECTION1 WHERE SRC_RIN IN ('2,3,4');

But I want it to look like following query.

SELECT * FROM REF_CONNECTION1 WHERE SRC_RIN IN (2,3,4);

How can I do this? Any help is highly appreciated.

fancyPants · Accepted Answer · 2013-07-12 12:04:06Z

1

You'd need a prepared statement for this:

SET @S1 = '2,3,4';

set @sql = concat('SELECT * FROM REF_CONNECTION1 WHERE SRC_RIN IN (', @S1, ');');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

answered Jul 12, 2013 at 12:04

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

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

Comments

Sparky · Accepted Answer · 2013-07-13 02:18:38Z

0

You can also use INSTR and CAST, but I wouldn't recommend it on a large data set

    SELECT * FROM REF_CONNECTION1 
   WHERE INSTR(','+@S1+',',','+CAST(src_rin as CHAR(1))+',') > 0

It gives you the flexibility, but performance will not be great

Apparently mySQL treats ','+@S1+',' as a single character (as shown by the following). Use CONCAT as I show in the modified code below, and it works properly

SELECT @s1,","+@s1+",",CONCAT(",",@S1,",")
FROM REF_CONNECTION1
WHERE INSTR(CONCAT(',',@S1,','),','+CAST(src_rin AS CHAR(1))+',') >0

edited Jul 13, 2013 at 2:18

answered Jul 12, 2013 at 12:08

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

7 Comments

Sparky Over a year ago

Just keep performance in mind, SQL cannot optimize queries very well when you have functions like that in the WHERE clause

fancyPants Over a year ago

Apart from the performance, it's plain wrong! Let @S1 be '234, 345, 456' your query will not only return the rows where src_rin is 234, 345 or 456, it will also return 2, 3, 4, 5, 6, 23, 34, 45 and so on...

fancyPants Over a year ago

@VardanGupta Please have a look at my comment above. This solution is wrong.

Sparky Over a year ago

Based on your sample data, the original query was right, but as FancyPants points out, it can fail with larger numbers. I've modified the query to address the issue raised by Fancy

fancyPants Over a year ago

Have a look at this: sqlfiddle.com/#!2/ca0d9/1/1 Your query does not even produce the right result with the limited sample data. My advice, forget about this INSTR() function.

|

Collectives™ on Stack Overflow

How to handle session value interpretation in MySQL

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related