You can employ MySQL user variables to remember results without
having to store them in temporary variables in the client. (See
Section 9.4, “User-Defined Variables”.)
For example, to find the articles with the highest and lowest
price you can do this:
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
Remember that using user variables the query will not be cached if you use the query caching option on the server, and therefore the answer could be slower than what we might expect.
Posted by Richard Bronosky on March 21 2007 6:59pm
I don't have time to get into it here, but if you need to use a variable as either a column name or a table name, you need to use a prepared statement.
eg: SELECT @col FROM @tbl;
It gets more complicated in stored routines: > CREATE PROCEDURE `get_longest`(tbl varchar(64), col varchar(64)) BEGIN
SET @sql=CONCAT_ws(' ', 'select char_length(',col,') longest', 'from', tbl, 'group by CHAR_LENGTH(',col,')', 'order by longest desc limit 1');
User Comments
Remember that using user variables the query will not be cached if you use the query caching option on the server, and therefore the answer could be slower than what we might expect.
There really should be a brief explanation of prepared statements and an link to http://dev.mysql.com/doc/refman/5.0/en/sqlps.html in this page.
1 row in set (0.00 sec)I don't have time to get into it here, but if you need to use a variable as either a column name or a table name, you need to use a prepared statement.
eg: SELECT @col FROM @tbl;
It gets more complicated in stored routines:
> CREATE PROCEDURE `get_longest`(tbl varchar(64), col varchar(64))
BEGIN
SET @sql=CONCAT_ws(' ',
'select char_length(',col,') longest',
'from', tbl,
'group by CHAR_LENGTH(',col,')',
'order by longest desc limit 1');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
END;
> call get_longest('frl_2007_1_sch','SCHOOL_NAME');
Add your own comment.