在MySQL中,我們可以將一個值或一個查詢結果儲存的一個使用者自訂的變數中,然後在後面的語句在應用。
SET定義變數;SET @var_name := expr [, @var_name = expr ] ....
SET @var_name = expr [, @var_name = expr ] ....
注意:①這裡用 ":=" or "="都行,但是"="在其他statement語句中有相等的意思,容易混淆,有時也會出錯。強烈建議用 ":="。②在語句裡,可以直接用@var_name = expr定義用,不提倡這樣,相當於不聲明直接用。
下面給出一些例子:簡單的定義,顯示
mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+| @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |+------+------+------+--------------------+| 1 | 2 | 4 | 7 | +------+------+------+--------------------+
Someone use them for rank
SET @pos := 0; #這裡用等號,感覺像是邏輯判斷,結果也不對了
SELECT @pos:=@pos+1 as rank,name FROM players ORDER BY score DESC;
print only the 100th users
SET @counter:=0;SELECT users.*FROM usersHAVING (@counter:=@counter+1)%100=0ORDER BY user_id; |
儲存查詢結果值value:
SELECT @total := COUNT(*) FROM table_name; # simicolon 分割連個語句SELECT table_name.id COUNT(*) AS 'count', COUNT(*) / (SELECT @total) AS percent FROM table_name,WHERE 1=1GROUP BY YEAR(birthday)ORDER BY YEAR(birthday)
注意上面這個SQL語句看起來邏輯清晰,但與下面的語句執行效果和時間都一樣(可能MySQL內部最佳化了)
SELECT table_name.id COUNT(*) AS 'count', COUNT(*) / (SELECT
COUNT(*)
FROM
table_name) AS percent
FROM table_name,WHERE 1=1GROUP BY YEAR(birthday)ORDER BY YEAR(birthday)
其他一些例子:
http://www.mysqldiary.com/user-defined-variables/
疑問:
這裡的變數只能儲存一個結果值,如何才能臨時儲存一個select出的結果集呢。
當然簡單的方法是建立表/視圖; 或者暫存資料表 ,還有好的方法呢? 待研究。
DECLARE聲明變數,然後在賦值
DECLARE @var_name var_type
這裡舉個例子:
例1:
DECLARE @total INT DECLARE @total_distinct INTSELECT @total:=COUNT(lice_no) #using ":=" notationFROM table_name; SELECT @total_distinct:=COUNT( DISTINCT lice_no) #using ":=" noationsFROM table_name; SELECT @total - @total_distinct
例2:
DECLARE @register_count INT;DECLARE @total_count INT;SELECT @register_count := COUNT(1) FROM t1 WHERE id > 10 ;
SELECT @total_count := COUNT(1) FROM t1 ; SELECT (@register_count * @total_count) AS ratio2
DECLARE 與 SET 區別:
- DECLARE 必須指定類型,而SET是不用的
- SET定義的是使用者自訂變數,是Session Sensitive 的; DECLARE 聲明的變數一般為局部變數,其有效區間是聲明的函數或預存程序中。
- 定義全域變數應該為 SET GLOBAL @var_name 後者SET @@GLOBAL.var_name
注意及附錄:這裡關於變數的使用範圍要清楚。官方介紹網址: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html