Cfrgtkky

Is there a way to pass an array into a MySQL query and return the results as another array ?(apart from using cursors which would be an overkill for my use case)

For a single id, my query looks like this.

SET @userId = '04b452cd59dcc656'

Select user_account_number from userstore where u_id = @userId ;

Instead of sending each id at a time, I am trying to send a list and return a list

SET @userId = ('04b452cd59dcc656','eqwe52cddasfsd656');

<query returning the list of account numbers>

Also - I think this would be efficient over just sending one id at a time. Thoughts ?

You can use IN:

select user_account_number

from userstore

where u_id in ('04b452cd59dcc656', 'eqwe52cddasfsd656') ;

Using variables is trickier. If you know a maximum number, you can do:

select user_account_number

from userstore

where u_id in (@id1, @id2);

Not satisfying, but it does the job. Similarly unsatisfying is FIND_IN_SET():

set @ids = '04b452cd59dcc656,eqwe52cddasfsd656';



select user_account_number

from userstore

where find_in_set(u_id, @ids) > 0;

Alas, this won't use an index.

Finally there is dynamic SQL:

set @sql = concat('select user_account_number from userstore where u_id in (''',

                  replace(ids, ',', ''','''),

                  ''')'

                 );



prepare s from @sql;

execute s;