MySQL 5.6 リファレンスマニュアル  /  言語構造  /  ユーザー定義変数

9.4 ユーザー定義変数

あるステートメント内のユーザー定義変数に値を格納し、あとから別のステートメントでこれを参照できます。これにより、あるステートメントから別のステートメントに値を渡すことができます。ユーザー定義変数はセッション固有です。つまり、あるクライアントが定義したユーザー変数を、ほかのクライアントが表示したり、使用したりすることはできません。所定のクライアントセッションのすべての変数は、クライアントが終了すると自動的に解放されます。

ユーザー変数は @var_name と記述されます。ここで変数名 var_name は、英数字文字、._、および$から構成されます。ユーザー変数名を文字列や識別子として引用符で囲めば、ほかの文字も含めることができます (@'my-var'@"my-var"@`my-var` など)。

ユーザー変数名では大文字と小文字を区別しません。

ユーザー定義変数を設定する方法の 1 つに、SET ステートメントを発行する方法が挙げられます。

SET @var_name = expr [, @var_name = expr] ...

SET では、= または := のどちらかを割り当て演算子として使用できます。

SET 以外のステートメントで、ユーザー変数に値を割り当てることもできます。この場合、=SET 以外のステートメントでは比較演算子 = として扱われるので、割り当て演算子にはこちらではなく、:= を使用する必要があります。

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 | 
+------+------+------+--------------------+

ユーザー変数には、限定された一連のデータ型の値 (整数、小数、浮動小数点、バイナリ文字列、非バイナリ文字列、または NULL 値) を割り当てることができます。10 進値と実数値の割り当てでは、値の精度やスケールは維持されません。許可されている型以外の型の値は、許可されている型に変換されます。たとえば、時間を表すデータ型や空間データ型の値は、バイナリ文字列に変換されます。

ユーザー変数に非バイナリ (文字) 文字列値を割り当てた場合、その変数には文字列と同じ文字セットと照合順序が含まれます。ユーザー変数の強制性は暗黙的です。(これはテーブルカラム値と同等の強制性です。)

ユーザー変数に割り当てられたビット値は、バイナリ文字列として扱われます。ビット値を数値としてユーザー変数に割り当てるには、CAST() または +0 を使用します。

mysql> SET @v1 = b'1000001';
mysql> SET @v2 = CAST(b'1000001' AS UNSIGNED), @v3 = b'1000001'+0;
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1  | @v2  | @v3  |
+------+------+------+
| A    |   65 |   65 |
+------+------+------+

結果セットでユーザー変数の値が選択された場合、それは文字列としてクライアントに返されます。

初期化されていない変数を参照する場合、その値は NULL で、型は文字列です。

ユーザー変数は、式が許可されているほとんどのコンテキストで使用できます。これには現在、SELECT ステートメントの LIMIT 句の中や、LOAD DATA ステートメントの IGNORE N LINES 句の中など、リテラル値を明示的に要求するコンテキストは含まれません。

一般的なルールとして、SET ステートメント以外では、同じステートメント内で、ユーザー変数に値を割り当ててその値を読み取ることは決してしないでください。たとえば、変数を増分する場合、次のようにしても問題ありません。

SET @a = @a + 1;

SELECT などのほかのステートメントでは、予想した結果が得られることもありますが、これは保証されません。次のステートメントでは、MySQL が最初に @a を評価し、続いて 2 番目の割り当てを行います。

SELECT @a, @a:=@a+1, ...;

ただし、ユーザー変数を含む式の評価の順序は、定義されていません。

SET 以外の同じステートメント内で変数に値を割り当てて、その値を読み取る場合に生じるもう 1 つの問題は、変数のデフォルトの結果型がステートメントの開始時の型に基づくということです。次の例でこれについて説明します。

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

この SELECT ステートメントの場合、MySQL は、カラム 1 が文字列であり、@a が 2 行目の数値に設定されていても、@a のすべてのアクセスを文字列に変換すると、クライアントにレポートします。SELECT ステートメントの実行後、@a は次のステートメントの数値と見なされます。

この動作による問題を回避するには、単一のステートメント内で同じ変数に値を割り当ててその値を読み取ることを行わないか、使用する前に変数を 00.0、または '' に設定して、その型を定義してください。

SELECT ステートメントでは、それぞれの選択式は、クライアントに送信されるときにのみ評価されます。つまり、HAVINGGROUP BY、または ORDER BY 句では、選択式リストで値を割り当てられた変数を参照しても、予想どおりには機能しないということです。

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

HAVING 句での b の参照は、@aa を使用する選択リスト内の式のエイリアスを参照します。これは予想どおりには機能しません。@aa には、現在の行ではなく、以前に選択した行の id の値が含まれます。

ユーザー変数は、データ値を提供するためのものです。これらは、テーブル名やデータベース名が想定されるコンテキストなどでの識別子または識別子の一部として、または SELECT などの予約語として、SQL ステートメントの中で直接使用することはできません。これは、次の例に示すように、変数が引用符で囲まれている場合でも同じです。

mysql> SELECT c1 FROM t;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> SET @col = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| c1   |
+------+
1 row in set (0.00 sec)

mysql> SELECT `@col` FROM t;
ERROR 1054 (42S22): Unknown column '@col' in 'field list'

mysql> SET @col = "`c1`";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @col FROM t;
+------+
| @col |
+------+
| `c1` |
+------+
1 row in set (0.00 sec)

識別子を提供するためにユーザー変数を使用できないというこの原則の例外が、あとから実行する準備されたステートメントとして使用するために文字列を構築している場合です。この場合、ユーザー変数はステートメントの一部を提供するために使用できます。次の例は、その方法を示しています。

mysql> SET @c = "c1";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM @s;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
+----+
| c1 |
+----+
|  0 |
+----+
|  1 |
+----+
2 rows in set (0.00 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

詳細は、セクション13.5「準備済みステートメントのための SQL 構文」を参照してください。

PHP 5 を使用して次に示すように、同じような手法をアプリケーションプログラムの中で使用することによって、プログラム変数を使用する SQL ステートメントを作成できます。

<?php
  $mysqli = new mysqli("localhost", "user", "pass", "test");

  if( mysqli_connect_errno() )
    die("Connection failed: %s\n", mysqli_connect_error());

  $col = "c1";

  $query = "SELECT $col FROM t";

  $result = $mysqli->query($query);

  while($row = $result->fetch_assoc())
  {
    echo "<p>" . $row["$col"] . "</p>\n";
  }

  $result->close();

  $mysqli->close();
?>

この方法で SQL ステートメントを作成することをダイナミック SQLと呼ぶことがあります。


User Comments
  Posted by Joey Bartlett on April 14, 2006
These are very useful. You can use them for ranks.

Example:

SET @pos=0;
SELECT @pos:=@pos+1,name FROM players ORDER BY score DESC;
  Posted by Mark Malakanov on April 30, 2006
Sometimes it needs to exactly mimic Oracle's ROWNUM where is no possibility to initiate a counter in previous statement by SET @rownum:=0;.
It is still possible in a single SQL.

SELECT @rownum:=@rownum+1 rownum, t.*
FROM (SELECT @rownum:=0) r, mytable t;
  Posted by Martin Klang on May 26, 2006
I found this quite useful for imitating rownum without first setting the variable:
SELECT if(@a, @a:=@a+1, @a:=1)-1 as rownum
It will of course only work as expected when @a hasn't already been set.
  Posted by Marc Grue on June 24, 2006
Example of pagination approach using examples above, and an alternative to the LIMIT clause in stored procedures: http://dev.mysql.com/doc/refman/5.1/en/select.html - scroll to comment of June 24 2006.
  Posted by Nicholas Bernstein on July 7, 2006
Certainly you can use user-variables to do running totals. But doing a running total BY GROUP is a little more difficult. Here is an example

select
t.ID,
t.TIMESTAMP,
@running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL,
@previous:=t.ID from (
select
ID,
TIMESTAMP,
count(*) as NUM
from HISTORY
group by ID, TIMESTAMP
order by ID, TIMESTAMP
)
as t;

Note: You have to do this as a subquery because "order by" only orders the output, it doesn't change the order in which the rows are processed.
  Posted by Miika Heiskanen on January 9, 2009
I'm using this as an alternative to Limit in my stored procs:

SET @rownum = 0;
SET @startRow = 0;
SET @maxRows = 20;

SELECT * FROM (
SELECT @rownum:=@rownum+1 as rownum, t.*
FROM (SELECT @rownum:=0) r, MYTABLE t
) t
WHERE rownum BETWEEN @startRow and @startRow + @maxRows;
  Posted by Petr Neuman on January 4, 2008
to select every 100th row

set @counter=0;
select *, @counter rownum from tbl having (@counter:=@counter+1)%100=0 order by id;
  Posted by Jan Paces on July 2, 2008
How to fill column with unique random values:

set @a:=0;
update my_table set rand_column=@a:=@a+1 order by rand();

  Posted by Bradley Perkins on April 21, 2009
I was having trouble using a user variable in a LIKE clause. This solved the problem.

set @n := "foo";

SELECT name
FROM tbl
WHERE tbl.name LIKE CONCAT("%",@n,"%"); # not LIKE "%@n%"

  Posted by Drew Anderson on July 23, 2009
Nice job with the running totals BY GROUP, Nicholas Bernstein (see post on this page). But if you are keeping the connection open, and you only happen to have one kind of timestamp in your list, you'll keep counting up and up if you repeat the query. So either add the query:

SET @running = @previous = NULL;

after your running total query, or you can keep it within one query and NULL it out in the outermost SELECT:

select
@running:=@previous:=NULL,
f.ID,
f.TIMESTAMP,
f.TOTAL
from (
select
t.ID,
t.TIMESTAMP,
@running:=if(@previous=t.ID,@running,0)+1 as TOTAL,
@previous:=t.ID from (
select
ID,
TIMESTAMP
from HISTORY
order by ID, TIMESTAMP
)as t
)as f;

This is Nicholas Bernstein's original example of keeping a running total (on each record) of how many duplicate timestamps per id. You'll notice I also removed the COUNT and GROUP BY that creates a column of 1's, and just added a number 1 instead. The original example only really works if there are no duplicates in the ID field.
  Posted by Ilan Hazan on June 7, 2010
More elegant solution for the group totals can be:
select t. ID, t. TIMESTAMP, @total:=@total+t.c as TOTAL from (select @total:=0) a, (select ID, TIMESTAMP,count(*) as c from HISTORY group by ID, TIMESTAMP) t ;
NOTES:
1. I have used only one user-defined variable.
2. I have initialized the user-defined variable in the same query
3. There is no need for the ORDER BY clause as GROUP BY returning ordered results

You can learn more MySQL User Defined Variables examples at
http://www.mysqldiary.com/user-defined-variables/

Thank you all.

  Posted by David Park on December 31, 2009
Thanks, everyone, for the great examples! Here's another one.

create table trashme (pk int not null primary key, fk varchar(1) not null, seq int not null)
insert into trashme values (1, 'A', 1)
insert into trashme values (2, 'A', 2)
insert into trashme values (3, 'B', 2)
insert into trashme values (4, 'B', 3)
insert into trashme values (5, 'B', 5)
set @previous := null
set @counter := 0
start transaction
update trashme
set seq = case when @previous = fk then @counter := @counter + 1 else case when @previous := fk then @counter := 0 else @counter := 0 end end
order by fk, seq
select * from trashme order by fk, seq

You'll see that the sequence has been renumbered (starting at zero and closing any previous gaps) for each group.

Note the "trick" to set @previous := fk using a 2nd case statement. Interestingly, the @counter := 0 used is the one in the else clause. Also note that I couldn't nest an if function inside of an if function (v5.0.37), and that's why I used the case statement.
  Posted by Md. Mahmud Ahsan on April 29, 2010
I've created a ranking example using mysql variable. Checkout the link
http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/
  Posted by Anton Schattenfeld on June 25, 2010
Ilan, seems your solution differs from Nicholas's one because
the numbering doesn't restart every time ID changes.
  Posted by Luc Vidal on November 12, 2010
The "trick" mentionned above by David Park can be replace by this :

set seq = case when @previous = fk then @counter := @counter + 1 else concat( left(@previous := fk, 0), @counter := 0) end
order by fk, seq

More generally, each time you want to assign user variables in an update, you can do

update table set col = concat ( left(@var1='val1', 0), left(@var2='val2', 0), .... , 'newcolvalue')

  Posted by Sanford Whiteman on March 25, 2011
Thought I'd share a little uservar-related thingy I just came up with for a peculiar use case.

So I've got TABLE stuff
(name VARCHAR(255), type INT(11))

with VALUES like
('Jimbo Jones',1)
('Blues Baby',2)
('Homburg Harry',1)
('Charlie Chaps',1)
('Ike Igneous',2)

I needed output that had all the rows grouped together by `type` (not just a GROUP BY summary) AND -- the peculiar part, related to a pagination-related thing on the client side -- needed a "countdown" indicator telling me how many more rows remained in the group after the current row.

Desired output:

Name Type Number_Remaining_in_Group
-----------------------------------------------
Jimbo Jones 1 2
Homburg Harry 1 1
Charlie Chaps 1 0
Blues Baby 2 1
Ike Igneous 2 0

So I came up with this:

SELECT *
, CASE
WHEN COALESCE(@grpremain,0) = 0
THEN @grpremain:=sorted.cnt-1
ELSE
@grpremain:=@grpremain-1
END
FROM
(
select
stuff.name
,grouped.type
,grouped.cnt
from stuff
inner join
(
select type,count(*) cnt from stuff
group by type
) grouped
on stuff.type=grouped.type
order by stuff.type
) sorted

Basically, innermost query gets the count by group, next one out does the sorting (otherwise, row order passed to the outermost one is undefined). Then the outermost query ticks down the expected remaining rows, resetting group-by-group.

There may be a more optimized way to do this -- I haven't rolled it out as yet because I have to make sure it uses the right indexes and is happy with a production-size dataset. But it does the trick on the functional level and would be a starting point for anybody with this exact need, which I couldn't find around the net. Cheers!

  Posted by Luc Vidal on October 30, 2012
Another way to set user variable in an update without changing a column, if you want to manipulate number instead of strings :
update table set col = col + (@var1 := 'val1')*0 + (@var2 := 'var2')*0 + ...
  Posted by Avto Vasadze on April 8, 2014
Example, for calculating running totals BY GROUP, Nicholas Bernstein (see post on this page):

select
t.ID,
t.TIMESTAMP,
@running:=if(@previous=t.ID,@running,0)+t.NUM as TOTAL,
@previous:=t.ID from (
select
ID,
TIMESTAMP,
count(*) as NUM
from HISTORY
group by ID, TIMESTAMP
order by ID, TIMESTAMP
)
as t;

In my opinion, contradict this general rule:

"As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement....
... Such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined."

Because, there is no guarantee that the variable @previous first will be checked and then assigned new value t.ID

Am I wrong?

  Posted by George Petrov on February 11, 2015
You're correct Avto Vasadze. I've experienced a lot of random output (sometimes correct) using the approach you quoted.
Sign Up Login You must be logged in to post a comment.