MySQL에서 변수를 선언하고 사용하는 방법

MySQL에는 세 가지 변수 메커니즘이 있습니다. 하나의 연결에서 여러 SQL 문이 값을 공유해야 한다면 @limit 같은 사용자 정의 세션 변수를 사용합니다. 저장 프로시저, 함수, 트리거, 이벤트 안에서는 @가 없는 로컬 변수를 사용합니다. 서버나 하나의 연결을 확인하거나 설정할 때는 @@SESSION.sql_mode 같은 시스템 변수를 사용합니다.

가장 중요한 점은 DECLARE가 임의의 SQL 창에서 사용할 수 있는 일반 문이 아니라는 것입니다. BEGIN ... END 복합문 안에서만 사용할 수 있고, 해당 블록의 실행 문보다 앞에 있어야 합니다. 간단한 스크립트라면 SET @name = value;로 시작합니다. SQL 예제에서는 DECLARE, SET, SELECT ... INTO, BEGIN ... END, SESSION, GLOBAL 키워드를 그대로 유지합니다.

알맞은 MySQL 변수 선택하기

이 형식들은 서로 바꿔 사용할 수 없습니다. 특히 DECLARE @name INT는 로컬 변수 구문이 아니며, 저장 프로그램 밖에서 DECLARE name INT를 단독 실행하면 구문 오류가 발생합니다.

SQL 세션에서 사용자 정의 변수 사용하기

사용자 정의 변수는 @로 시작합니다. 사용하기 전에 선언할 필요가 없습니다. SET으로 값이나 표현식을 대입한 뒤 다음 문에서 참조합니다.

SET @low = 2, @high = 4;
SELECT n FROM demo_numbers WHERE n BETWEEN @low AND @high ORDER BY n;

변수는 현재 연결에만 속합니다. 다른 연결에서는 사용할 수 없고 연결이 종료되면 사라집니다. 초기화하지 않은 사용자 변수의 값은 NULL이므로 재사용할 스크립트의 시작에서 초기화하세요.

SET이 가장 명확한 대입 방법입니다. SET 문에서는 :=도 사용할 수 있습니다.

SET @total = 10;
SET @total := @total + 5;
SELECT @total;

변수의 형식은 선언한 데이터 형식이 아니라 대입한 값에 따라 결정됩니다. 숫자, 소수, 문자열, 이진 문자열, NULL 등을 저장할 수 있으며 형식이 바뀌는 경계에서는 명시적으로 변환하세요.

SELECT ... INTO로 쿼리 결과 읽기

쿼리에서 값을 가져오려면 SELECT ... INTO를 사용합니다. 대상 변수의 개수는 선택한 표현식의 개수와 같아야 합니다.

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

쿼리는 한 행을 반환하도록 설계해야 합니다. COUNT(*)는 자연스럽게 한 행을 반환하고 고유 키 검색도 적합합니다. 여러 행이 가능하면 의도한 ORDER BY와 LIMIT 1로 행을 결정하세요.

사용자 변수로 테이블이나 열 이름을 대신할 수는 없습니다. SET @table_name = 'demo_numbers'; SELECT * FROM @table_name;은 테이블 이름을 치환하지 않습니다. 동적 SQL이 필요하면 PREPARE용 문을 만들고 애플리케이션이 제공한 식별자를 검증하세요.

MySQL 9.7은 호환성을 위해 SET 이외의 대입도 아직 허용하지만 향후 제거될 예정입니다. SELECT @n := n 같은 표현식보다 SET과 SELECT ... INTO를 사용하세요.

저장 프로그램에서 로컬 변수 선언하기

로컬 변수는 @가 없는 DECLARE를 사용하며 MySQL 데이터 형식이 필요합니다. DEFAULT를 지정하지 않으면 초기값은 NULL입니다. BEGIN ... END 블록의 시작에서 실행 문보다 먼저 선언하세요. 해당 블록과 중첩 블록에서만 보입니다.

다음 프로시저는 입력 매개변수, 로컬 변수, 출력 매개변수를 사용합니다. DELIMITER 명령은 mysql 클라이언트용이며 세미콜론이 포함된 본문을 하나의 CREATE PROCEDURE 문으로 보내게 합니다.

DROP PROCEDURE IF EXISTS count_numbers;
DELIMITER //
CREATE PROCEDURE count_numbers(IN p_min INT, OUT p_count INT)
BEGIN
  DECLARE local_count INT DEFAULT 0;
  SELECT COUNT(*) INTO local_count
  FROM demo_numbers
  WHERE n >= p_min;
  SET p_count = local_count;
END//
DELIMITER ;
CALL count_numbers(3, @result);
SELECT @result;

local_count는 프로시저가 실행되는 동안에만 존재합니다. OUT 매개변수는 CALL에 전달한 세션 변수로 반환됩니다. 여러 열을 대입하려면 표현식마다 대상을 두고 일치하는 행이 없는 경우도 처리하세요.

같은 형식의 변수 여러 개를 한 문에서 선언할 수 있지만, 별도 선언이 유지 관리에는 더 쉽습니다.

DECLARE quantity, retries INT DEFAULT 0;
DECLARE label VARCHAR(100) DEFAULT 'pending';

DECLARE는 조건, 커서, 핸들러도 정의합니다. 순서는 변수와 조건, 커서, 핸들러 순이어야 합니다. 따라서 SELECT나 SET 뒤에 선언하면 구문 오류가 발생합니다.

SELECT ... INTO의 행 수 경계 이해하기

스칼라 대상에는 하나의 값만 저장할 수 있습니다. 다음 쿼리는 의도적으로 세 행을 반환하므로 오류 1172가 발생합니다.

SET @selected = 99;
SELECT n INTO @selected FROM demo_numbers ORDER BY n;

ERROR 1172 (42000) at line 6: Result consisted of more than one row

알려진 한 행으로 좁히거나 결과를 집계하거나, 결과를 변수 대신 행 집합으로 유지하세요. LIMIT 1은 의도한 정렬과 함께 사용해야 합니다.

SELECT n INTO @selected
FROM demo_numbers
ORDER BY n DESC
LIMIT 1;

같은 한 행 규칙은 로컬 변수에도 적용됩니다. 행이 없어도 되는 루틴이라면 변수를 명시적으로 초기화하고 필요할 때 NOT FOUND 핸들러나 존재 여부 검사를 사용하세요.

MySQL 시스템 변수 확인 및 설정하기

시스템 변수는 서버나 클라이언트 연결을 설정합니다. SHOW VARIABLES 또는 @@ 수식자로 확인할 수 있습니다.

SHOW SESSION VARIABLES LIKE 'sql_mode';
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

범위를 지정하지 않은 SHOW VARIABLES는 현재 세션을 사용합니다. SESSION 값은 해당 연결에만 영향을 주고 GLOBAL 값은 일반적으로 새 연결의 초기값이 됩니다. GLOBAL 변수를 변경하려면 적절한 권한과 변경 가능한 변수가 필요합니다.

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- A privileged server administrator may use an appropriate GLOBAL setting.
-- SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

주석 처리한 GLOBAL 문은 실행 fixture에 포함하지 않았습니다. 성공 여부가 계정 권한, 변수의 범위와 변경 가능성에 따라 달라지기 때문입니다.

작동하지 않는 변수 문제 해결하기

• DECLARE가 거부되면 BEGIN ... END 안에서 모든 실행 문보다 앞에 있는지 확인하세요. 일반 SQL 스크립트에는 SET @name = value를 사용합니다.
• 예상치 못한 NULL이면 변수를 초기화하고 원본 표현식이나 조회 결과를 확인하세요.
• SELECT ... INTO에서 오류 1172가 발생하면 정확히 한 행을 반환하게 하거나 결과를 행 집합으로 유지하세요.
• 다른 연결에서 @name이 보이지 않는 것은 세션 전용이므로 정상입니다.
• @@GLOBAL.name 변경에 실패하면 변수의 변경 가능성과 관리 권한을 확인하세요.

성공 fixture는 세션 변수를 대입해 필터에 사용하고, 프로시저의 로컬 변수와 출력 매개변수를 확인합니다. 경계 fixture는 여러 행의 SELECT ... INTO 진단을 검증합니다. 둘 다 임시 MySQL 서버를 사용합니다.

자세한 구문은 MySQL 사용자 정의 변수 레퍼런스, 로컬 DECLARE 레퍼런스, SET 대입 레퍼런스를 참조하세요.