MySQL에서는 두 가지 방법으로 사용자가 정의한 변수를 지원한다. 변수이름은 alphanumeric 문자와 '_', '$', '.'로 구성된다. 변수에 초기값이 지정되지 않으면, NULL이 디폴트이며, integer, real, string 값을 저장할 수 있다. 변수이름은 버전 5.0부터는 대·소문자 구분이 없다.
방법1)
SET 문을 사용하여 변수를 설정
SET @variable={integer expression | real expression | string expression }
[,@variable=...]
【예제】
mysql> set @t3=5;
mysql> select @t3;
+------+
| @t3 |
+------+
| 5 |
+------+
mysql>
방법2)
@variable:=expr 문을 사용하여 설정
【예제】
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1 | @t2 | @t3 |
+----------------------+------+------+------+
| 5 | 5 | 1 | 4 |
+----------------------+------+------+------+
출처 - http://flashcafe.org/index.php?mid=mysql_study&listStyle=webzine&document_srl=3999
MySQL 사용자 정의 변수
09 사용자 정의 변수
변수의 적용 범위가 서버 전체이면 글로벌 변수 커넥션에 종속적이면 세션 변수
서버 실행중 변경가는한 변수에 따라 동적 변수와 정적 변수로 나눔
MySQL 서버의 설정 파일이나 MySQL 명령헹 인자르 통해 설정된 변수는 시스템변수
사용자 정의 변수는 임의로 이름을 부여할수있다
사용자 변수는 항상 세션변수로 취급하며 값을 변경 가능 하므로 동적 변수이다
사용자변수는 절차적인 목적으로 사용할수 있다
9.1 사용자 변수 소개
mysql> SET @str := ‘문자열’;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @str;
+———–+
| @str |
+———–+
| 문자열 |
+———–+
1 row in set (0.00 sec)
mysql> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select (@rownum=@rownum+1) as rownum, emp_no, first_name from employees limit 5;
+——–+——–+————+
| rownum | emp_no | first_name |
+——–+——–+————+
| 0 | 11800 | Aamer |
| 0 | 11935 | Aamer |
| 0 | 12160 | Aamer |
| 0 | 13011 | Aamer |
| 0 | 15332 | Aamer |
+——–+——–+————+
5 rows in set (0.00 sec)
mysql> select (@rownum:=@rownum+1) as rownum, emp_no, first_name from employees limit 5;
+——–+——–+————+
| rownum | emp_no | first_name |
+——–+——–+————+
| 1 | 11800 | Aamer |
| 2 | 11935 | Aamer |
| 3 | 12160 | Aamer |
| 4 | 13011 | Aamer |
| 5 | 15332 | Aamer |
+——–+——–+————+
5 rows in set (0.00 sec)
MySQL 5.0미만의 버전은 변수명의 대소문자를 구분함 그 이상 버전은 구분 하지 않음
사용자 정의 변수를 사용 하는 쿼리는 쿼리 캐시 기능을 사용 못함
초기화되지 않은 변수는 문자열 ‘NULL’값을 가진다
사용자 변수의 연산 순서는 정해져 있지 않다
MySQL 버전 차이에 따라 작동 방식이나 순서에 차이가 있기에 주의해야한다
9.2 기본 활용
mysql> select (@rownum:=@rownum+1) as rownum, emp_no, first_name
-> from employees, (select @rownum:=0) tab1
-> limit 5;
+——–+——–+————+
| rownum | emp_no | first_name |
+——–+——–+————+
| 1 | 11800 | Aamer |
| 2 | 11935 | Aamer |
| 3 | 12160 | Aamer |
| 4 | 13011 | Aamer |
| 5 | 15332 | Aamer |
+——–+——–+————+
5 rows in set (0.00 sec)
set 명령어가 아닌 select 쿼리를 사용하여 초기화
mysql> set @old_sal:=50000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @old_sal, salary, @old_sal:=salary from salaries limit 1;
+———-+——–+——————+
| @old_sal | salary | @old_sal:=salary |
+———-+——–+——————+
| 50000 | 38623 | 38623 |
+———-+——–+——————+
1 row in set (0.00 sec)
mysql> set @old_sal:=50000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @old_sal, GREATEST(salary, LEAST(-1, @old_sal:=salary)) as salary from salaries limit 1;
+———-+——–+
| @old_sal | salary |
+———-+——–+
| 50000 | 38623 |
+———-+——–+
1 row in set (0.00 sec)
GRAETEST() 둘중 높은 값을 반환
LEAST() 둘중 낮은 값을 반환
old_sal 값을 salary칼럼 값으로 초기화함과 동시에 salary칼럼 값만 결과 셋으로 가져오는 쿼리임
9.3 시용자 변수 적용 예
9.3.1 N번째 레코드만 가져오기
mysql> select *
-> from departments, (select @rn:=0) x
-> where (@rn:=@rn+1)=3
-> order by dept_name;
+———+———–+——–+
| dept_no | dept_name | @rn:=0 |
+———+———–+——–+
| d002 | Finance | 0 |
+———+———–+——–+
1 row in set (0.00 sec)
mysql> select *
-> from departments, (select @rn:=0) x
-> having (@rn:=@rn+1)=3
-> order by dept_name;
+———+———–+——–+
| dept_no | dept_name | @rn:=0 |
+———+———–+——–+
| d002 | Finance | 0 |
+———+———–+——–+
1 row in set (0.00 sec)
이 예제는 테으블을 풀스캔하며 건수가 많은 테이블에서는 주의해서 사용해야한다.
9.3.2 누적 합계 구하기
mysql> select emp_no, salary, (@add_sal:=@add_sal+salary) as add_sal
-> from salaries, (select @add_sal:=0) x
-> limit 10;
+——–+——–+———+
| emp_no | salary | add_sal |
+——–+——–+———+
| 253406 | 38623 | 38623 |
| 49239 | 38735 | 77358 |
| 281546 | 38786 | 116144 |
| 15830 | 38812 | 154956 |
| 64198 | 38836 | 193792 |
| 475254 | 38849 | 232641 |
| 50419 | 38850 | 271491 |
| 34707 | 38851 | 310342 |
| 49239 | 38859 | 349201 |
| 274049 | 38864 | 388065 |
+——–+——–+———+
10 rows in set (0.00 sec)
*MySQL에서는 파생된 테이블을 만들어 내는 FROM 절의 서브쿼리에 반드시 별칭(‘x’)을 부여해야한다
9.3.3 그룹별 랭킹 구하기
mysql> select
emp_no, first_name, last_name,
if(@prev_first_name=first_name,
@rank:=@rank+1, @rank:=1+LEAST(0,@prev_first_name:=first_name)) rank
from employees, (select @rank:=0) x1, (select @prev_first_name:=’DUMMY’) x2
where first_name in (‘Georgi’,'Bezalel’)
order by first_name, last_name limit 5;
+——–+————+———–+——+
| emp_no | first_name | last_name | rank |
+——–+————+———–+——+
| 297135 | Bezalel |Acton | 1 |
| 25442 | Bezalel | Adachi | 2 |
| 446963 | Bezalel | Aingworth | 3 |
| 241970 | Bezalel | Anandan | 4 |
| 241885 | Bezalel | Ananiadou | 5 |
+——–+————+———–+——+
5 rows in set (0.01 sec)
9.3.4 랭킹 업데이트
mysql> create table tb_ranking (
member_id int not null,
member_score int not null,
rank_no int not null,
primary key (member_id),
index ix_mem (member_score)
);
Query OK, 0 rows affected (0.12 sec)
mysql> update tb_ranking r set r.rank_no = (@rank:=@rank+1) order by r.member_score DESC;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 0 Changed: 0 Warnings: 0
9.3.5 GROUP BY 와 ORDER BY가 인덱스를 사용하지 못하는 쿼리
mysql> create table tb_uv (rid varchar(10));
Query OK, 0 rows affected (0.07 sec)
mysql> insert into tb_uv values (‘g’), (‘n’), (‘h’), (‘a’), (‘b’);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select rid, @rank:=@rank+1 as rank
-> from tb_uv, (select @rank:=0) x
-> order by rid;
+——+——+
| rid | rank |
+——+——+
| a | 1 |
| b | 2 |
| g | 3 |
| h | 4 |
| n | 5 |
+——+——+
5 rows in set (0.02 sec)
mysql> select rid, @rank:=@rank+1 as rank from tb_uv, (select @rank:=0) x group by rid order by rid;
+——+——+
| rid | rank |
+——+——+
| a | 4 |
| b | 5 |
| g | 1 |
| h | 3 |
| n | 2 |
+——+——+
5 rows in set (0.00 sec)
mysql> explain select rid, @rank:=@rank+1 as rank from tb_uv, (select @rank:=0) x group by rid order by rid;
+—-+————-+————+——–+—————+——+———+——+——+———————————+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+——+———+——+——+———————————+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | PRIMARY | tb_uv | ALL | NULL | NULL | NULL | NULL | 5 | |
| 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+—-+————-+————+——–+—————+——+———+——+——+———————————+
3 rows in set (0.00 sec)
mysql> select rid, @rank:=@rank+1 as rank
-> from (
-> select rid from tb_uv
-> group by rid
-> order by rid
-> )x,
-> (select @rank:=0) y;
+——+——+
| rid | rank |
+——+——+
| a | 1 |
| b | 2 |
| g | 3 |
| h | 4 |
| n | 5 |
+——+——+
5 rows in set (0.03 sec)
mysql> alter table tb_uv add index ix_rid (rid);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select rid, @rank:=@rank+1 as rank
-> from tb_uv, (select @rank:=0) x
-> group by rid
-> order by rid;
+——+——+
| rid | rank |
+——+——+
| a | 1 |
| b | 2 |
| g | 3 |
| h | 4 |
| n | 5 |
+——+——+
5 rows in set (0.01 sec)
9.4 주의사항
버전간에 호환성을 보장 하지 않는데 버전 업그레이드시 주의
'DB > MySQL' 카테고리의 다른 글
| linux 에서 mysql rpm version install (0) | 2013.01.03 |
|---|---|
| mysql - join (1) | 2012.10.19 |
| mysql - boolean 데이터 타입 (0) | 2012.09.26 |
| mysql - insert 시 있으면 update 없으면 insert (0) | 2012.09.22 |
| mysql - mysql에서 null 처리 (0) | 2012.09.22 |
