mysql 사용자 정의 변수(a.k.a.사용자함수) 총정리 [출처:Real Mysql]

mysql의 변수 

- 시스템 변수  - 글로벌 변수

- 사용자 변수  - 세션변수 

 

1. 사용자 정의 변수 

"@"로 시작

커넥션정의된 사용자변수는 다른커넥션과 공유하지 못하고 해당 커넥션에서만 사용 

별도의 타입을 지정하는것이 아니라 저장하는 값에 의해 타입이 정해진다(= 스크립트언어와 동일)

 

set@var := 'my first user variable';

set@var1 = 'my first', @var2 = 'user variable';

select @var as var1, concat(@var1, ' ', @var2) as var2;

set @rownum = 0; -- set으로 rownum을 정의하고 초기값 0 할당

select (@rownum:= @rownum +1) as rownum, emp_no, first_name from employees e  limit 5;
--매 레코드마다 rownum +1을 해준다.

mysql의 사용자변수는 일관성이 떨어지는 부분때문에 사용자정의 변수사용시 발생할 예외케이스를 여러번 체크해야한다.

애플리케이션처럼 고정적인 로직보다 일회성의 대량작업에 더 적합할때가 많다.

일회성 다량 마이그레이션할 경우 실행은 단 한번뿐이지만 처리해야할 레코드건수가 많은경우 사용자변수를 효과적으로 사용할 수 있다. 

 

2. 사용자변수의 기본

- 사용자변수는 하나의 커넥션상에서는 공유된다. (즉, 변수 사용마다 초기화하지않으면 각 코드가 상호 영향을 미칠 수 있다. 따라서 사용자변수 사용시 매번 변수값을 set명령으로 초기화 하는 작업을 잊지 말아야한다)

- 하지만 매번 사용자변수를 초기화해주는 번거러움이 있기때문에 @rownum을 초기화하는 쿼리를 통해 살펴보자

select (@rownum:=@rownum+1) as rownum, emp_no, first_name
from employees e , (select @rownum :=0) der_tab
limit 5;

- select 절의 스칼라서브쿼리보다 단1번 실행되는 from절의 in-line view가 사용자함수를 초기화하기 가장 좋은 위치이다. (단, order by가 사용된 join update나 join delete절에서는 사용 불가하다)

set @old_salary:=900000;

select @old_salary, salary, @old_salary:=salary from salaries s2 limit 1;

set @old_salary:=900000;

select @old_salary, max(@old_salary:=salary) as salary
from salaries limit 1;

select @old_salary;

@old_salary가 90000 -> 158220으로 update된것을 확인할 수 있다.

 

3. 사용자변수의 적용예제 

3.1 n번째 레코드만 가져오기 (주의: 결과값을 1건만 return한다고 디스크가 1건만 읽는다는 뜻은아니니 오해하지말자)

select *
from departments, (select @rn :=0) x
where (@rn:=@rn+1)= 3
order by dept_name ;

3번째 레코드만 읽어오는 쿼리고, having조건 쿼리의 3번째 레코드만 가져온것이랑 동일한 결과

order by도 having, limit절보다 먼저 실행되기때문에 차이가 발생 

select * 
from departments, (select @rn := 0) x
having(@rn:=@rn+1)=3
order by dept_name;

3.2 누적합계 구하기

select emp_no, salary, (@acc_salary:=@acc_salary+salary) as acc_salary
from salaries, (select @acc_salary:=0) x --파생테이블 x 처럼 파생된 테이블은 반드시 alias를 가져야한다.
limit 10;

3.3 그룹별 랭킹 구하기

select 
	emp_no, first_name, last_name,
	case when(@prev_firstnme := first_name,
			  @rank:=@rank+1, @rank:=1+min(@prev_firstname:=first_name)) rank
from   employees e 
     , (select @rank:=0) x1
     , (select @prev_firstname:='DUMMY') x2
where first_name in ('georgi','bezalel')
order by first-name, last_name ;

--흠...8.1ver 안먹힘.

select 
	emp_no, first_name, last_name, @prev_firstname, @rank,
	if(@prev_firstname = first_name, @rank:=@rank+1, @rank:=1+min(@prev_firstname:=first_name)) rank,
	@prev_firstname, @rank
from employees e , (select @rank:=0) x1, (select @prev_firstname:=NULL) x2
where first_name in ('Georgi','Bezalel')
order by first_name, last_name

3.4 랭킹 업데이트 하기

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_memberscore (member_score)
);

일단 테이블을 만든다

select @rank:=0;

rank 사용자함수 초기값 0을 셋팅한다

update tb_ranking r
set r.rank_no = (@rank := @rank+1)
order by r.member_score desc;

update절의 select @rank:=0을 포함하면 join update문장은 order by 절을 사용할 수 없는 mysql의 제한을 보완할 수 있다. (실제로 order by와 update를 동시에 사ㅓ용하면 error메시지를 뱉어낸다)

update tb_ranking r,(select @rank:=0) x
set r.rank_no = (@rank:=@rank+1)
order by r.remeber_score desc;

 

3.5 group by와 order by 가 인덱스를 사용하지 못하는 쿼리

mysql의 사용자변수는 레코드가 디스크로 읽힐때 연산이 수행된다. 

create table tb_uservars (rid varchar(10));

insert into tb_uservars values ('z'),('y'),('b'),('c'),('a'),('z'),('y'),('a'),('b'),('m'),('n');

insert the values (it doesn't work type the korean suddenly;; so, rather than type korean, just typing like this)

select rid, @rank:=@rank+1 as r_rank from tb_uservars, (select @rank:=0) x order by rid;

explain
select rid, @rank:=@rank+1 as r_rank from tb_uservars, (select @rank:=0) x 
order by rid;

select rid, @rank:=@rank+1 as r_rank from tb_uservars, (select @rank:=0) x 
group by rid
order by rid;

explain
select rid, @rank:=@rank+1 as r_rank from tb_uservars, (select @rank:=0) x 
group by rid
order by rid;

group by query 는 using temporary: using file sort를 출력하는것으로 미루어보아 group by와 order by 모두 인덱스를 통해 처리하지 못하고 임시테이블과 별도의 정렬작업을 통해 처리됐음을 알 수 있다. group by 쿼리는 누락번호도 잇을 뿐더러 순차적으로 증가하지도 않는다. -> 정렬 수행전에 지정된 사용자 변수를 연산해버렸기 때문이다. 이럴대는 쿼리 전체를 임시테이블(파생테이블)로 만들어주면 원하는결과를 만들어줄 수 있다.

select rid, @rank:= @rank+1 as r_rank
from (
	select rid from tb_uservars 
	group by rid 
	order by rid 
)x, (select @rank:=0)y;

이런 예쁜 결과값을 출력할 수 있다.

만약, 서브쿼리 결과값이 많아서 임시테이블로 결과를 저장하는게 부담이 갈것 같다면 group by와 order by가 인덱스를 탈 수있도록 하는 방법도 있다. 

alter table tb_uservars add index ix_rid(rid);

select rid, @rank:= @rank+1 as r_rank
from tb_uservars, (select @rank :=0) x
group by rid 
order by rid;

 

4. 주의사항

-사용자변수는 서버 버전간 호환성을 보장하지않으며, 안정적인결과를 보증하지 않는다.

- 고정적으로 사용할 경우 업그레이드 주의! 케이스별 결과값 테스트 주의! 

+ 추가적으로 analytic function 기능을 구현할 수도 있다. (mysql analytics function with user variables 검색해보라)