MySQL IN clause help

ozzii · #1 (**permalink**) 09-20-09, 11:16

Hi

I have the following stored procedure:

Code:

CREATE PROCEDURE get_customers(IN inCustomerIdList VARCHAR(100))
BEGIN

  SELECT first_name, last_name, email FROM tbl_customers  
  WHERE customer_id IN (inCustomerIdList);

END$$

The above should retrieve all customers whose id appears in the inCustomerIdList variable. inCustomerIdList is a string variable e.g. "8,9,5,6,10".

However the above procedure only retreives the customer which has the id number of the first id in the above string. Now I know i would have to put each id in quotes to retrieve all customers in this list i.e ('8','9','5','6','10').

Is there any way for me to convert the original string variable so that i can pass it in just like this: (8,9,5,6,10) or is there a MySQL function to concat apostraphes to each id?

mike_bike_kite · #2 (**permalink**) 09-20-09, 14:27

I suppose it's not worth me telling you why your general design is poor but to get you out of a fix you could try something like :

Code:

CREATE PROCEDURE get_customers(IN inCustomerIdList VARCHAR(100))
BEGIN

  SELECT first_name, last_name, email FROM tbl_customers  
  WHERE concat( ',',inCustomerIdList,',' ) like concat( '%,',customer_id,',%' );

END$$

ozzii · #3 (**permalink**) 09-20-09, 15:10

Quote:

Originally Posted by mike_bike_kite

I suppose it's not worth me telling you why your general design is poor but to get you out of a fix you could try something like :

Code:

CREATE PROCEDURE get_customers(IN inCustomerIdList VARCHAR(100))
BEGIN

  SELECT first_name, last_name, email FROM tbl_customers  
  WHERE concat( ',',inCustomerIdList,',' ) like concat( '%,',customer_id,',%' );

END$$

Thanks for the tip but i've managed to find an even easier solution by using the FIND_IN_SET clause. By the way why is the design poor?

r937 · #4 (**permalink**) 09-20-09, 17:54

because FIND_IN_SET, and any other solution such as the more complex LIKE one, do not scale

unable to utilize an index, they get slower and slower as the number of rows increases

plus, updates and deletes require egregiously complex application logic

ozzii · #5 (**permalink**) 09-21-09, 05:55

Quote:

Originally Posted by r937

because FIND_IN_SET, and any other solution such as the more complex LIKE one, do not scale

unable to utilize an index, they get slower and slower as the number of rows increases

plus, updates and deletes require egregiously complex application logic

so what would be a better solution? lets say I append each id number in quotes via the application logic and then use the IN clause - the IN clause would still have the same scalability issues?

mike_bike_kite · #6 (**permalink**) 09-21-09, 06:19

You should have your list of customer ids in a table as separate rows. This will be more expandable and much faster as it can use the indexes. I don't know what your database holds so I can't give you a complete answer but look up first normal form for more info. As it stands your system may work fine but if the system expands and you get more users and customers then you'll soon run into problems.

ozzii · #7 (**permalink**) 09-21-09, 08:01

Quote:

Originally Posted by mike_bike_kite

You should have your list of customer ids in a table as separate rows. This will be more expandable and much faster as it can use the indexes. I don't know what your database holds so I can't give you a complete answer but look up first normal form for more info. As it stands your system may work fine but if the system expands and you get more users and customers then you'll soon run into problems.

the customer ids are indeed held in a table and they form the primary key for that table. However if i want to run a query to retrieve some details e.g. firstname, lastname, email etc for a list of customers and all i have is there id numbers eg 101, 102, 109, 111 ... In this instance how would you run a query to retrieve this information? Using the IN or FIND_IN_SET seem like the most obvious to me unless i ran invidual select queries on each id number however that would take forever if i have list of hundred ids.

r937 · #8 (**permalink**) 09-21-09, 12:30

in that case, IN ( list ) is exactly what you want here

Internet Services

Web Development

Digital Marketing

Consumer Tech