9장 사용자 정의 변수

9. 사용자 정의 변수

---

• MySQL의 변수는 누가 생성하는가에 따라 크게 시스템 변수와 사용자 변수로 구분하며, 변수의 적용 범위가 MySQL 서버 전체인지 아니면 커넥션에 종속적인지에 따라 글로벌 변수와 세션 변수로 나뉜다.
• 또한 동적(MySQL 서버가 재시작되지 않고)으로 변수를 변경할 수 있느냐에 따라 동적 변수와 정적 변수로 구분한다.
• MySQL 서버의 설정 파일이나 MySQL 서버의 명령행 인자를 통해 설정되는 변수는 시스템 변수라고 한다.
• 시스템 변수는 MySQL에서 정의한 고정된 이름을 갖고 있지만, 사용자 정의 변수는 시스템 변수와 충돌되지만 않는다면 임의로 이름을 부여할 수 있다.

사용자 변수는 해당 커넥션에서만 유효하기 때문에 항상 세션 변수로 취급된다. 또한 사용자가 언제든지 값을 변경할 수 있기 때문에 동적 변수로 볼 수 있다.

9.1 사용자 정의 변수 소개

• 사용자 정의 변수는 스토어드 프로시저나 함수뿐 아니라 SQL 문장에서도 사용할 수 있다. 하나의 커넥션에서 정의된 사용자 변수는 다른 커넥션과 공유하지 못하고 해당 커넥션에서만 사용할 수 있다.

• MySQL의 사용자 변수의 이름은 “@”로 시작한다.

• 사용자 변수는 일반적인 스크립트 언어와 마찬가지로 별도로 타입을 정의하지 않고 저장하는 값에 의해서 그 타입이 정해진다.
• 사용자 정의변수에 저장할 수 있는 값은 Integer, Decimal, Float, Binary와 문자열 타입만 가능하다. 또한 타입이 정해지지 않은 NULL도 저장할 수 있으며, 초기 값을 설정하지 않은 사용자 정의 변수는 기본값으로 NULL을 가진다.
• 변수는 SET 문장으로 값이 할당됨과 동시에 생성된다. 값을 할당하는 SET 문장은 “=” 또는 “:=” 연산자를 이용한다.

*** 주의 사항

• 절대 동일 SQL 문장에서 변수에 값을 할당하고 동시에 값을 참조하지 말라
• ex) @rownum:=@rownum+1
• MySQL 5.0 미만의 버전에서는 변수명의 대소문자를 구분했지만 그 이상의 버전에서는 대소문자 구분을 하지 않는다.
• 사용자 정의 변수를 사용하는 쿼리는 MySQL의 쿼리 캐시 기능을 사용하지 못한다.
• 초기화되지 않은 변수는 문자열(VARCHAR) 타입의 NULL을 가진다.
• 사용자 정의 변수의 연산 순서는 정해져 있지 않다
• MySQL의 버전에 따른 작동 방식이나 순서에 차이가 있기 때문에 여러 버전에 걸쳐서 사용할 때는 주의해야 한다.

사용자 정의 변수를 사용하는 이유

• 잠재된 효용 가치가 크다.

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

• ex) 일회성으로 데이터를 마이그레이션하는 작업은 여러 차례 테스트를 진행하고 준비해서 단 한번 실행하지만 한번에 처리해야 하는 레코드 건수는 많다.

9.2 사용자 변수의 기본 활용

• 사용자 변수는 커넥션 간에는 공유되지 않지만 하나의 커넥션에서는 공유된다.

• 사용자 변수를 사용하는 경우에는 매번 사용자 변수 값을 SET 명령으로 초기화하는 작업을 잊어서는 안된다.

• 하지만 매번 사용자 변수를 사용할 때마다 초기화한다는 것은 상당히 번거로운 일일 것이다. 이러한 번거로움을 없애고자 SQL 문장에서 사용자 변수를 초기화하는 방법을 자주 사용한다.

  SELECT (@rownum:=@rownum+1) AS rownum, emp_no, first_name
  FROM employees, (SELECT @rownum:=0) def_tab
  LIMIT 5;
  

  • SQL 문장에서 FROM 절은 쿼리가 실행되는 동안 단 1번만 참조되기 때문에 사용자 변수를 초기화하기에 가장 좋은 위치다.

9.3 사용자 변수의 적용 예제

9.3.1 N번째 레코드만 가져오기

• 일반적인 SQL 문장으로는 결과 셋에서 특정 몇 번째의 레코드만 가져오는 작업은 불가능하다.

• 다음 쿼리는 dept_name 순서대로 정렬해서 결과를 가져오면서 읽는 레코드 순서대로 번호를 붙이고 그 번호가 3인 레코드만 사용자에게 반환한다, 이 예제에서 주의해야 할점은 사용자에게 반환되는 레코드는 3번째 레코드 한 건이지만 이것이 실제 내부적으로 1건의 레코드만 디스크에서 읽는다는 것을 의미하지는 않는다는 점이다.

• SELECT *
  FROM departments, (SELECT @rn:=0) x
  WHERE (@rn:=@rn+1)=3
  ORDER BY dept_name;
  

• 위와 같은 기능을 HAVING 절로도 구현할 수 있다. 직접 실행해 보면 같은 결과가 반환되지만 사실은 같지 않다. 위 WHERE 조건을 이용한 쿼리는 정렬을 하지 않고, 3번째 레코드만 읽어와서 결과를 반환한 것이다.

• SELECT *
  FROM departments, (SELECT @rn:=0) x
  HAVING (@rn:=@rn+1)=3
  ORDER BY dept_name;
  

• 위의 HAVING 조건을 사용한 쿼리는 전체 레코드를 dept_name로 정렬한 다음 3번째 레코드만 가져온 것이다. 이것은 사용자 변수 특성 때문이 아니라 쿼리에서 각 절의 실행 순서와 연관이 있는 것이다.

• HAVING 절은 쿼리 실행의 거의 마지막 단계에 적용된다. 물론 LIMIT는 HAVING의 뒤에 실행되지만 나머지 거의 모든 절은 HAVING보다 먼저 실행되는데, ORDER BY도 HAVING절보다 빨리 실행되기 때문에 이런 차이가 생기는 것이다.

9.3.2 누적합계 구하기

• 사용자 정의 변수를 사용하면 읽어 오는 레코드 순서대로 누적 합계도 쉽게 처리할 수 있다.

• 다음 예제는 사원의 급여 테이블에서 10개의 레코드를 읽고, acc_salary라는 칼럼에는 레코드별로 누적된 salary 합계 값을 계산해서 가져오는 쿼리다.

• SELECT emp_no, salary, (@acc_salary:=@acc_salary+salary) AS acc_salary FROM salaries, (SELECT @acc_salary:=0) x LIMIT 10;
  

  • FROM 절의 (select @acc_salary:=0)는 누적 합계 값을 임시로 저장해 두는 @acc_salary 변수를 0으로 초기화하기 위한 서브 쿼리로 사용됐다.
  • SELECT 절에서는 “(@acc_salary:-@acc_salary+salary)” 표현식을 이용해 @acc_salary 사용자 변수에 각 레코드의 salary 칼럼 값을 더해서 조회하고 있다.

9.3.3 그룹별 랭킹 구하기

• 실제로 사용자 변수는 이러한 요건을 처리할 때 가장 빈번히 사용된다.

• SELECT
  	emp_no, first_name, last_name,
  	IF(@prev_firstname=first_name,
  		@rank:=@rank+1, @rank:=1+LEAST(0, @prev_firstname:=first_name)) rank
  FROM employees, (SELECT @rank:=0) x1, (SELECT @prev_firstname:='DUMMY') x2
  WHERE first_name IN ('Georgi', 'Bezalel')
  ORDER BY first_name, last_name;
  

  employees 테이블에서 first_name이 ‘Georgi’ 또는 ‘Bezalel’인 사원만 검색한 다음 first_name과 last_name로 정렬해서 사원 정보를 가져오는 쿼리다.

  first_name이 똑같은 사원끼리 그룹핑하고, 그 그룹 내에서 last_name 칼럼의 정렬 순서대로 순위를 부여하는 데 사용자 변수가 사용됐다.

• @rank와 @prev_firstname 사용자 변수를 초기화하기 위해 FROM 절에 두 개의 서브 쿼리가 사용됐다.
• LEAST() 함수에 사용된 2개 인자 중에서 @prev_firstname 변수는 (first_name 칼럼이 NOT NULL 이기 때문에) 항상 NOT NULL인 문자열 값을 갖게 되므로 LEAST( ) 함수의 결과는 항상 0을 반환한다. 그래서 “@rank:=1+LEAST(0, @prev_firstname:=first_name)” 표현식은 항상 “@rank:=1+0”가 되는 것이다. 하지만 LEAST() 함수에 의해 “@prev_firstname:=first_name” 표현식이 매번 실행되고 @prev_firstname 변수에는 마지막 읽었던 사원 정보의 first_name이 저장된다.
• IF() 함수는 3개의 인자를 받는데, 항상 제일 첫 번째 인자를 평가하고, 그 결과가 참일 때는 2번째 인자만 평가하고, 거짓일 때는 3번째 인자만 평가한다.

9.3.4 랭킹 업데이트하기

• 사용자 정의 변수는 SELECT에만 사용할 수 있는 것은 아니다. UPDATE 문장에서도 처리하는 레코드 단위로 순서를 부여해서 특정한 처리를 수행할 수 있다. 다음 예제를 통해 랭킹을 업데이트하는 방법을 살펴보자.

• -- 회원의 member_score로 랭킹을 저장하는 테이블
  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)
  );
  

  • 각 회원의 member_score는 매번 게임에 참여할 때마다 업데이트하지만 rank_no는 실시간으로 업데이트하지 않고 몇 시간 단위로 한번씩 집계한다고 가정한다.

• SELECT @rank:=0;
    
  UPDATE tb_ranking r
  SET r.rank_no = (@rank:=@rank+1)
  ORDER BY r.member_score DESC;
  

  • tb_ranking 테이블을 member_score 칼럼의 값으로 내림차순으로 정렬해서 UPDATE를 수행한다. 이때 레코드를 읽을 때마다 @rank 갑을 1씩 증가시키면서 rank_no 칼럼에 @rank 값을 저장하는 것이다.

• 위의 UPDATE 문장에서 사용자 정의 변수(@rank)를 초기화하는 부분을 UPDATE 문장 안으로 넣지 않았다. “SELECT @rank:=0” 부분을 다음과 같이 UPDATE 문에 포함하면 JOIN UPDATE 문장은 ORDER BY 절을 사용할 수 없는 MySQL의 제한사항 때문이다. 실제로 다음 쿼리를 실행하면 MySQL 서버는 ORDER BY와 UPDATE를 동시에 사용할 수 없다는 메시지를 출력할 것이다. 이는 JOIN UPDATE에서 ORDER BY를 사용하지 못한다는 것을 의미한다.

• UPDATE tb_ranking r, (SELECT @rank:=0) x
  SET r.rank_no = (@rank:=@rank+1)
  ORDER BY r.member_score DESC;
    
  = ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
  

9.3.5 GROUP BY와 ORDER BY가 인덱스를 사용하지 못하는 쿼리

• 사용자 변수는 결과가 클라이언트로 전송될 때 연산된다고 메뉴얼에서 소개하고 있지만, 일반적으로 MySQL의 사용자 변수는 레코드가 디스크로부터 읽힐 때 연산이 수행된다.
• 대표적으로 사용자 변수가 기대하지 않은 결과를 보이는 경우로는 GROUP BY와 ORDER BY가 함께 사용된 쿼리에서 그룹핑과 정렬이 인덱스를 사용하지 못하고 “Using temporary; Using filesort” 실행 계획이 사용되는 쿼리다.

9.4 주의사항

• 사용자 변수의 강력한 기능 뒤에는 위험이 있다는 사실을 항상 기억해야 한다. 즉, 지금까지 언급한 예제는 모두 쿼리 하나에서 변수에 대한 할당과 참조를 동시에 하고 있다. 하지만 MySQL에서는 이러한 사용자 변수의 사용에 대해 안정적인 결과를 보증하지 않는다. 또한 이는 MySQL 서버의 버전 간 호환성을 보장하지 않는다는 이야기도 되기 때문에 애플리케이션에서 고정적으로 사용할 때는 업그레이드에 주의해야 한다. 또한 다양한 케이스에 대한 테스트는 필수적이다.