Use IN() clause with stored procedure

I have a stored procedure that accepts one parameter same below:

getData (
  par开发者_JS百科am varchar(500)
)
BEGIN
  SELECT *
  FROM category
  WHERE ID in ( param );
END

Given param is "1,2,3" how can i make it as 1,2,3 with out the "" of the varchar to perform the query same as ID IN(1,2,2) not as ID in ("1,2,3")?

very ugly cludge i'm afraid as mysql sprocs currently dont support array type params:

Example call:

mysql> call foo('2,4,6');
+---------+----------+
| user_id | username |
+---------+----------+
|       2 | b        |
|       4 | d        |
|       6 | f        |
+---------+----------+
3 rows in set (0.00 sec)

Full script:

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null
)
engine=innodb;

insert into users (username) values ('a'),('b'),('c'),('d'),('e'),('f'),('g');

drop procedure if exists foo;

delimiter #

create procedure foo
(
in p_id_csv varchar(1024)
)
proc_main:begin

declare v_id varchar(10);
declare v_done tinyint unsigned default 0;
declare v_idx int unsigned default 1;

    if p_id_csv is null or length(p_id_csv) <= 0 then
        leave proc_main;
    end if;

    -- split the string into tokens and put into an in-memory table...

    create temporary table ids(id int unsigned not null)engine = memory;    

    while not v_done do
    set v_id = trim(substring(p_id_csv, v_idx, 
      if(locate(',', p_id_csv, v_idx) > 0, 
                locate(',', p_id_csv, v_idx) - v_idx, length(p_id_csv))));

      if length(v_id) > 0 then
        set v_idx = v_idx + length(v_id) + 1;
                insert into ids values(v_id);
      else
        set v_done = 1;
      end if;
  end while;

    select u.* from users u
    inner join ids on ids.id = u.user_id
    order by u.username;

    drop temporary table if exists ids;

end proc_main #

delimiter ;

call foo('2,4,6');

Given param would be something like '1,2,3' you could create prepared statement and then execute it

SET query = CONCAT('SELECT * FROM category WHERE ID in (',param,')');
PREPARE stmt FROM query;
EXECUTE stmt;