Hi Dave,
Dave G wrote:
Hello all,
I'm trying to do some processing on the front end to optimize my query on
the backend. I would like to generate a list of id's for this query like
so:
SELECT REPLACE('3,4,5,6,7,8,9',',',' OR element_id=') INTO @tmp;
Then use it like:
mysql> select @tmp;
+---------------------------------------------------------------------------------------------------+
| @tmp
|
+---------------------------------------------------------------------------------------------------+
| 3 OR element_id=4 OR element_id=5 OR element_id=6 OR element_id=7 OR
element_id=8 OR element_id=9 |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from display__Element where [EMAIL PROTECTED];
MySQL sees that element_id is a number and coerces @tmp into a number. Check the
output of 'show warnings' immediately after this statement. Another way to see what's
happening is to run 'select 0 + @tmp'. MySQL is truncating after the first non-digit
characters.
What you want to do is use a prepared statement; prepare the statement as
concat("select...", @tmp) and then execute the result.
+------------+--------------+------------+
| element_id | display_name | data_units |
+------------+--------------+------------+
| 3 | Sync | |
+------------+--------------+------------+
1 row in set (0.00 sec)
mysql> select * from display__Element where element_id=3 OR element_id=4
OR element_id=5 OR element_id=6 OR element_id=7 OR element_id=8 OR
element_id=9;
A better approach is to use an IN() list, which MySQL might even be able to optimize
better:
WHERE element_id IN(3,4,5,6,7,8,9);
DANGER: do NOT use an IN() subquery, of the form
WHERE element_id IN(select element_id from some_other_table)
MySQL optimizes this kind of query very poorly.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
