MySQL 5.6 한글메뉴얼

9.4 사용자 정의 변수

명령문의 사용자 정의 변수에 값을 저장 한 후 나중에 다른 문으로이를 참조 할 수 있습니다. 이렇게하면 한 문에서 다른 문에 값을 전달할 수 있습니다. 사용자 정의 변수는 세션 별입니다. 즉, 클라이언트가 정의한 사용자 변수를 다른 클라이언트가 보거나 사용 할 수 없습니다. 소정의 클라이언트 세션의 모든 변수는 클라이언트가 종료되면 자동으로 해제됩니다.

사용자 변수는 @ var_name 과 설명됩니다. 여기에서 변수 이름 var_name 은 영숫자 문자 " . "," _ "및" $ "로 구성됩니다. 사용자 변수 이름을 문자열 식별자 및 따옴표로 둘러싸면 다른 문자를 포함 할 수 있습니다 ( @'my-var' @ @"my-var" @ @`my-var` 등).

사용자 변수 이름은 대소 문자를 구분하지 않습니다.

사용자 정의 변수를 설정하는 방법의 하나는 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 를 평가 한 다음 두 번째 할당합니다.

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

그러나 사용자 변수를 포함한 식의 계산 순서는 정의되어 있지 않습니다.

SET 이외의 동일한 문에서 변수에 값을 할당하고 그 값을 읽을 때 발생하는 또 다른 문제는 변수의 디폴트의 결과 형이 문 시작시의 형태에 따른 것입니다. 다음 예제에서 이에 대해 설명합니다.

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

이 SELECT 문의 경우, MySQL은 컬럼 1이 문자열이고 @a 가 두 번째 행의 값으로 설정되어 있어도, @ a의 모든 액세스를 문자열로 변환하면 클라이언트에보고합니다 . SELECT 문 실행 후 @a 다음 문 수치로 간주됩니다.

이 동작에 의한 문제를 해결하려면 단일 문에서 같은 변수에 값을 할당하고 그 값을 읽을하지 않거나 사용하기 전에 변수를 0 , 0.0 , 또는 '' 로 설정하고 그 형태를 정의하십시오.

SELECT 문은 각각의 선택적 클라이언트로 전송되는 경우에만 평가됩니다. 즉, HAVING , GROUP 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"이라고 부를 수 있습니다.