11.4 User-Defined Variables

11.4 User-Defined Variables

하나의 statement에서 user-defined variable에 값을 저장해 두고, 나중에 다른 statement에서 이를 참조할 수 있습니다. 이렇게 하면 하나의 statement에서 다른 statement로 값을 전달할 수 있습니다.

User variable은 @var_name처럼 작성하며, variable 이름 var_name 은 영숫자 문자와 ., _, $로 구성됩니다. User variable 이름은 문자열 또는 identifier로 quoting하면 (예: @'my-var', @"my-var", 또는 ``@`my-var```), 다른 문자도 포함할 수 있습니다.

User-defined variable은 세션별입니다. 한 클라이언트가 정의한 user variable은 다른 클라이언트가 볼 수 없고 사용할 수도 없습니다. (예외: Performance Schema user_variables_by_thread table에 접근 권한이 있는 사용자는 모든 세션의 모든 user variable을 볼 수 있습니다.) 특정 클라이언트 세션의 모든 variable은 해당 클라이언트가 종료될 때 자동으로 해제됩니다.

User variable 이름은 대소문자를 구분하지 않습니다. 이름의 최대 길이는 64자입니다.

User-defined variable을 설정하는 한 가지 방법은 SET statement를 사용하는 것입니다:

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

SET의 경우, 할당 연산자로 = 또는 :=을 사용할 수 있습니다.

User variable에는 제한된 데이터 타입 집합에서 값을 할당할 수 있습니다: integer, decimal, floating-point, binary 또는 nonbinary string, 또는 NULL 값입니다. Decimal 및 real 값의 할당 시에는 해당 값의 precision이나 scale이 보존되지 않습니다. 허용되는 타입이 아닌 타입의 값은 허용되는 타입으로 변환됩니다. 예를 들어, temporal 또는 spatial 데이터 타입을 가진 값은 binary string으로 변환됩니다. JSON 데이터 타입을 가진 값은 문자 집합이 utf8mb4, collation이 utf8mb4_bin인 string으로 변환됩니다.

User variable에 nonbinary(문자형) string 값을 할당하면, 그 variable은 해당 string과 동일한 문자 집합과 collation을 갖습니다. User variable의 강제 변환 우선순위(coercibility)는 암시적입니다. (이는 table column 값의 coercibility와 동일합니다.)

User variable에 할당된 hexadecimal 또는 bit 값은 binary string으로 취급됩니다. Hexadecimal 또는 bit 값을 숫자로서 user variable에 할당하려면, 이를 숫자 컨텍스트에서 사용해야 합니다. 예를 들어, 0을 더하거나 CAST(... AS UNSIGNED)를 사용합니다:

1mysql> SET @v1 = X'41';
2mysql> SET @v2 = X'41'+0;
3mysql> SET @v3 = CAST(X'41' AS UNSIGNED);
4mysql> SELECT @v1, @v2, @v3;
5+------+------+------+
6| @v1  | @v2  | @v3  |
7+------+------+------+
8| A    |   65 |   65 |
9+------+------+------+
10mysql> SET @v1 = b'1000001';
11mysql> SET @v2 = b'1000001'+0;
12mysql> SET @v3 = CAST(b'1000001' AS UNSIGNED);
13mysql> SELECT @v1, @v2, @v3;
14+------+------+------+
15| @v1  | @v2  | @v3  |
16+------+------+------+
17| A    |   65 |   65 |
18+------+------+------+

Result set에서 user variable의 값을 선택하면, 클라이언트에게 string으로 반환됩니다.

초기화되지 않은 variable을 참조하면, 그 값은 NULL이고 타입은 string입니다.

Prepared statement에서 user variable 참조의 타입은 해당 statement가 처음 준비될 때 결정되며, 그 후에 statement가 실행될 때마다 이 타입을 유지합니다. 마찬가지로, 저장 프로시저 내의 statement에서 사용되는 user variable의 타입은 저장 프로시저가 처음 호출될 때 결정되며, 이후 각 호출마다 이 타입을 유지합니다.

User variable은 expression이 허용되는 대부분의 컨텍스트에서 사용할 수 있습니다. 여기에는 현재 literal 값을 명시적으로 필요로 하는 컨텍스트는 포함되지 않습니다. 예를 들면, SELECT statement의 LIMIT 절이나 LOAD DATA statement의 IGNORE N LINES 절입니다.

이전 버전의 MySQL에서는 SET 이외의 statement에서도 user variable에 값을 할당하는 것이 가능했습니다. 이 기능은 MySQL 9.5에서 하위 호환성을 위해 지원되지만, 향후 MySQL의 릴리스에서 제거될 수 있습니다.

이와 같은 방식으로 할당을 수행할 때에는, 반드시 :=을 할당 연산자로 사용해야 합니다. SET 이외의 statement에서는 =가 비교 연산자로 취급됩니다 (SET와는 다르게).

User variable이 관련된 expression의 평가 순서는 정의되어 있지 않습니다. 예를 들어, SELECT @a, @a:=@a+1 이 먼저 @a를 평가한 다음 할당을 수행한다는 보장은 없습니다.

또한, variable의 기본 result 타입은 statement 시작 시점의 타입에 기반합니다. 하나의 statement 내에서 어떤 variable에 서로 다른 타입의 새 값을 할당하는 경우, statement 시작 시점에 해당 variable이 한 타입의 값을 가지고 있으면 의도하지 않은 결과가 발생할 수 있습니다.

이러한 동작으로 인한 문제를 피하려면, 하나의 statement 안에서 동일한 variable에 값을 할당하고 그 값을 읽지 않도록 하거나, 사용하기 전에 해당 variable의 타입을 정의하기 위해 0, 0.0, ''로 variable을 설정하십시오.

Select expression list에서 값이 할당되는 variable을 HAVING, GROUP BY, ORDER BY가 참조하는 경우, 이들은 기대한 대로 동작하지 않습니다. 이는 expression이 클라이언트 측에서 평가되며, 이때 이전 row의 column 값이라는 stale 값을 사용할 수 있기 때문입니다.

User variable은 데이터 값을 제공하기 위한 것입니다. Table 또는 database 이름이 기대되는 컨텍스트나, SELECT와 같은 예약어가 기대되는 컨텍스트 등, identifier나 identifier의 일부로 SQL statement에서 직접 사용할 수 없습니다. 이는 다음 예에서 보이는 것처럼 variable을 quoting한 경우에도 마찬가지입니다:

1mysql> SELECT c1 FROM t;
2+----+
3| c1 |
4+----+
5|  0 |
6+----+
7|  1 |
8+----+
92 rows in set (0.00 sec)
10
11mysql> SET @col = "c1";
12Query OK, 0 rows affected (0.00 sec)
13
14mysql> SELECT @col FROM t;
15+------+
16| @col |
17+------+
18| c1   |
19+------+
201 row in set (0.00 sec)
21
22mysql> SELECT `@col` FROM t;
23ERROR 1054 (42S22): Unknown column '@col' in 'field list'
24
25mysql> SET @col = "`c1`";
26Query OK, 0 rows affected (0.00 sec)
27
28mysql> SELECT @col FROM t;
29+------+
30| @col |
31+------+
32| `c1` |
33+------+
341 row in set (0.00 sec)

User variable을 identifier 제공 용도로 사용할 수 없다는 이 원칙에 대한 예외는, 나중에 실행할 prepared statement로 사용할 string을 구성할 때입니다. 이 경우, user variable을 statement의 어떤 부분에도 사용할 수 있습니다. 다음 예는 이를 수행하는 방법을 보여 줍니다:

1mysql> SET @c = "c1";
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");
5Query OK, 0 rows affected (0.00 sec)
6
7mysql> PREPARE stmt FROM @s;
8Query OK, 0 rows affected (0.04 sec)
9Statement prepared
10
11mysql> EXECUTE stmt;
12+----+
13| c1 |
14+----+
15|  0 |
16+----+
17|  1 |
18+----+
192 rows in set (0.00 sec)
20
21mysql> DEALLOCATE PREPARE stmt;
22Query OK, 0 rows affected (0.00 sec)

자세한 내용은 Section 15.5, “Prepared Statements”를 참조하십시오.

비슷한 기법을 애플리케이션 프로그램에서도 사용할 수 있으며, 다음 예처럼 프로그램 variable을 사용해 SQL statement를 구성할 수 있습니다(PHP 5 사용 예시):

1<?php
2  $mysqli = new mysqli("localhost", "user", "pass", "test");
3
4  if( mysqli_connect_errno() )
5    die("Connection failed: %s\n", mysqli_connect_error());
6
7  $col = "c1";
8
9  $query = "SELECT $col FROM t";
10
11  $result = $mysqli->query($query);
12
13  while($row = $result->fetch_assoc())
14  {
15    echo "<p>" . $row["$col"] . "</p>\n";
16  }
17
18  $result->close();
19
20  $mysqli->close();
21?>

이와 같은 방식으로 SQL statement를 조합하는 것을 “Dynamic SQL”이라고 부르기도 합니다.