MySQL SET 변수 · PREPARE — 동적 쿼리 완전 정리

 

 

Database · MySQL

MySQL 사용자 변수(@변수)와 PREPARE —
동적 쿼리 완전 정리

SET으로 값을 저장하고, PREPARE·EXECUTE로 쿼리를 즉석에서 조립하는 법

시작 오늘 분석할 코드

일반 SELECT문은 조건이 코드에 고정되어 있다. 하지만 "LIMIT 몇 개를 보여줄지"를 변수로 받아서 그때그때 쿼리를 조립하고 싶을 때가 있다. 아래 코드는 MySQL 사용자 변수 @myVar1에 값을 담아두고, PREPARE로 쿼리 틀을 준비한 뒤, EXECUTE ... USING으로 그 변수를 끼워 넣어 실행하는 패턴이다.

SET @myVar1 = 3 ; -- 변수명 지정 PREPARE myQuery -- 동적 쿼리 준비 FROM 'SELECT Name, height FROM usertbl ORDER BY height LIMIT ?'; -- 쿼리 안에서만 ? 사용할 수 있음 EXECUTE myQuery USING @myVar1 ; -- 사용 후에는 DEALLOCATE PREPARE myQuery; 를 꼭 해줘야함

실제 결과 미리보기

@myVar1 = 3이므로 LIMIT ? 자리에 3이 들어가 키가 작은 순으로 3명만 조회된다.

Name	height
박지연	158
임정연	162
이승엽	164

용어 용어 정리

@변수MySQL 세션 안에서 값을 담아두는 사용자 정의 변수

SET변수에 값을 대입하는 명령어

:=SELECT문 안에서도 사용 가능한 대입 연산자

PREPARE문자열 쿼리를 미리 준비(파싱)해두는 명령어

EXECUTE준비된 쿼리를 실제로 실행하는 명령어

?준비된 쿼리 안에서 값이 들어갈 자리(플레이스홀더)

USINGEXECUTE 시 ? 자리에 넣을 변수를 지정하는 절

DEALLOCATE PREPARE준비된 쿼리를 메모리에서 해제하는 명령어

---

1 SET @변수 = 값 — 사용자 변수란?

왜 변수가 필요한가?

프로그래밍 언어에 변수가 있듯 MySQL에도 값을 잠깐 담아둘 그릇이 필요할 때가 있다. @myVar1처럼 앞에 @이 붙은 이름은 MySQL이 "이건 사용자 변수다"라고 인식하는 표시다. SET @myVar1 = 3;은 "이후 이 세션에서 @myVar1이라는 이름으로 3이라는 값을 계속 쓰겠다"는 선언이다.

※ 일반 컬럼명이나 테이블명과 헷갈리지 않도록 MySQL은 앞에 @을 강제한다. @ 없이 myVar1이라고만 쓰면 컬럼명으로 오해될 수 있다.

@변수 (사용자 변수)

@ 하나
내가 직접 선언한 값
SET으로 자유롭게 대입

@@변수 (시스템 변수)

@ 두 개
MySQL이 미리 정의한 설정값
예: @@version, @@max_connections

---

2 = vs := — 대입 연산자가 두 개인 이유

SET에서는 =, SELECT에서는 := 를 쓴다

SET문 안에서는 =가 이미 "대입"이라는 뜻이라 문제없다. 하지만 SELECT문 안에서 =는 원래 "비교 연산자"(같다/다르다)로 쓰이기 때문에, 같은 문장 안에서 변수에 값을 넣고 싶으면 :=를 써서 "이건 비교가 아니라 대입"이라고 명확히 구분해준다.

SET @myVar1 = 3; → SET 전용이라 = 그대로 대입 SELECT @total := COUNT(*) FROM usertbl; → SELECT 안에서는 := 로 대입 (= 는 비교로 해석될 위험)

---

3 PREPARE — 동적 쿼리를 준비한다는 것

왜 쿼리를 문자열로 감싸서 미리 준비하는가?

PREPARE myQuery FROM '...'는 작은따옴표 안 문자열을 "SQL 문장"으로 등록해두는 과정이다. 이때 MySQL은 이 문자열을 실제 쿼리로 해석·검증(파싱)만 해두고 아직 실행하지는 않는다. myQuery는 이렇게 준비된 쿼리에 붙인 이름표(핸들)로, 이후 EXECUTE에서 이 이름으로 불러와 실행한다.

※ 이 방식의 진짜 쓸모는 "쿼리 틀은 고정하고 값만 바꿔가며 여러 번 실행"할 때 나온다. 매번 문자열을 새로 조립하는 대신, 한 번 준비해둔 틀에 값만 갈아 끼우는 것이다.

---

 

반응형

 

4 ? 플레이스홀더 — 왜 값 대신 물음표를 쓰는가?

문자열 붙이기(concat) 대신 ?를 쓰는 이유

PREPARE로 준비하는 쿼리 문자열 안에서는 값을 직접 적지 않고 ?라는 자리 표시자만 남겨둔다. 만약 LIMIT 3처럼 값을 문자열에 직접 이어붙이면, 사용자 입력을 그대로 쿼리에 섞는 셈이라 SQL 인젝션 위험이 커진다. ?는 "여기에 값이 들어올 것"이라는 자리만 잡아두고, 실제 값은 EXECUTE ... USING 단계에서 안전하게 바인딩된다.

문자열 이어붙이기 (위험)

'... LIMIT ' + 사용자입력
→ 입력값이 SQL 일부가 됨
→ 인젝션 가능

? 플레이스홀더 (안전)

'... LIMIT ?'
→ 값은 별도로 바인딩
→ SQL 구조와 값이 분리됨

※ 원본 코드 주석에 있듯 ?는 PREPARE로 준비된 쿼리 문자열 안에서만 쓸 수 있다. 일반 SELECT문에 ?를 그냥 쓰면 문법 오류가 난다.

---

5 EXECUTE ... USING — 변수를 ?자리에 바인딩

USING 뒤에 오는 값이 순서대로 ?에 대응

EXECUTE myQuery USING @myVar1;은 "이름표 myQuery로 준비해둔 쿼리를 실행하는데, 그 안의 ? 자리에는 @myVar1의 값을 넣어라"는 뜻이다. ?가 여러 개면 USING @a, @b, @c처럼 콤마로 나열한 순서대로 하나씩 매칭된다.

PREPARE

→

'... LIMIT ?'

→

EXECUTE ... USING @myVar1

→

'... LIMIT 3' 실행

---

6 DEALLOCATE PREPARE — 뒷정리가 필요한 이유

PREPARE로 만든 쿼리는 메모리에 남는다

PREPARE로 준비한 쿼리는 세션이 끝나거나 명시적으로 해제할 때까지 서버 메모리에 계속 남아있다. 같은 이름(myQuery)으로 다시 PREPARE를 하려 하면 이미 존재한다는 오류가 나거나 리소스가 계속 쌓일 수 있다. 그래서 원본 코드 주석처럼 사용이 끝나면 DEALLOCATE PREPARE myQuery;로 반드시 해제해줘야 한다.

PREPARE myQuery FROM '...'; EXECUTE myQuery USING @myVar1; DEALLOCATE PREPARE myQuery; -- 다 쓰면 꼭 해제

---

7 변수의 범위 — 왜 세션마다 따로 노는가?

@변수는 "내 접속" 안에서만 산다

사용자 변수(@변수)는 현재 접속(세션) 안에서만 살아있다. 다른 사용자나 다른 접속 창에서는 같은 이름 @myVar1을 써도 전혀 다른 값이거나 아예 비어있다(NULL). 접속을 끊으면 그 세션의 모든 사용자 변수도 함께 사라진다.

※ 세션 범위 정리

• 같은 접속 안에서는 여러 쿼리에 걸쳐 값이 유지된다
• 접속을 새로 열면 이전 @변수 값은 이어지지 않는다
• 다른 사람의 접속과 절대 값을 공유하지 않는다 (동시성 걱정 없음)

---

8 CONCAT — 테이블·컬럼명까지 동적으로 바꾸기

? 는 "값"만 대신할 수 있다

? 플레이스홀더는 WHERE 조건값이나 LIMIT 숫자처럼 값 자리에만 쓸 수 있다. 테이블 이름이나 컬럼 이름처럼 SQL 문법 구조 자체를 바꾸는 부분에는 ?를 쓸 수 없다. 이럴 때는 CONCAT()으로 문자열을 직접 이어 붙여 쿼리 전체를 완성한 다음, 그 완성된 문자열을 PREPARE에 넘긴다.

SET @tbl = 'usertbl'; SET @sql = CONCAT('SELECT Name, height FROM ', @tbl, ' ORDER BY height LIMIT ?'); -- CONCAT으로 테이블명까지 문자열에 끼워 완성 PREPARE myQuery FROM @sql; -- FROM 뒤에 변수(@sql)를 바로 써도 됨 (문자열 리터럴이 아니어도 OK) EXECUTE myQuery USING @myVar1; DEALLOCATE PREPARE myQuery;

※ 테이블·컬럼명을 CONCAT으로 조립할 때 그 값이 사용자 입력에서 그대로 왔다면 반드시 화이트리스트 검증을 거쳐야 한다. ?와 달리 CONCAT으로 합친 문자열은 자동으로 이스케이프되지 않는다 — SQL 인젝션에 그대로 노출될 수 있다.

※ ? 와 CONCAT의 역할 분담

• ? + USING — WHERE 조건값, LIMIT 개수 등 "값"이 바뀔 때
• CONCAT + PREPARE FROM @sql — 테이블명·컬럼명·정렬 방향 등 "구조"가 바뀔 때
• 실무에서는 둘을 섞어서 "구조는 CONCAT, 값은 ?"로 나누는 경우가 많다

---

9 서버가 이 코드를 처리하는 순서

지금까지 나온 SET → PREPARE → EXECUTE → DEALLOCATE 네 단계가 MySQL 서버 안에서 실제로 어떤 순서로 처리되는지 정리하면 아래와 같다.

1

SET @myVar1 = 3;세션 메모리에 이름표(@myVar1)와 값(3)을 저장

2

PREPARE myQuery FROM '...LIMIT ?';문자열을 SQL 문법으로 파싱하고 실행 계획을 미리 만들어 myQuery라는 이름으로 보관

3

EXECUTE myQuery USING @myVar1;보관된 실행 계획을 꺼내와 ? 자리에 @myVar1(=3)을 채우고 실제로 실행

4

결과 반환Name, height 컬럼이 height 오름차순으로 3행만 클라이언트에 전달됨

5

DEALLOCATE PREPARE myQuery;myQuery라는 이름표와 그에 연결된 실행 계획을 서버 메모리에서 제거

※ 같은 myQuery를 EXECUTE만 여러 번 반복하고 PREPARE는 한 번만 하는 것이 이 패턴의 핵심 이점이다. 매번 문자열을 새로 파싱하지 않아도 되기 때문에, 같은 쿼리 구조를 값만 바꿔 여러 번 실행할 때 더 효율적이다.

---

10 자주 하는 실수

실수	증상	해결
변수 이름에 @ 빠뜨림	컬럼명으로 오인되어 오류(Unknown column)	사용자 변수는 항상 @변수명 형태로
SELECT 안에서 =로 대입 시도	비교식으로 해석되어 원하는 값이 안 들어감	SELECT 안 대입은 := 사용
준비된 쿼리 문자열 안에 ? 대신 값 직접 삽입	매번 문자열을 재조립해야 하고 인젝션 위험	? 플레이스홀더 + USING으로 분리
DEALLOCATE PREPARE 생략	같은 이름 재사용 시 충돌·메모리 누적	사용 끝나면 항상 DEALLOCATE PREPARE
다른 세션에서 같은 @변수 값 기대	NULL이거나 예상과 다른 값	사용자 변수는 세션 범위임을 인지
테이블명까지 ?로 넘기려 시도	문법 오류(구조는 바인딩 대상이 아님)	구조가 바뀌는 부분은 CONCAT으로 문자열 조립
CONCAT에 사용자 입력을 검증 없이 그대로 삽입	SQL 인젝션 취약점 발생	화이트리스트 검증 후 CONCAT, 값은 반드시 ?로

---

핵심 한 줄 요약

SET @변수 = 값사용자 변수 선언 및 값 대입

:=SELECT문 안에서 쓰는 대입 연산자 (= 는 비교용)

PREPARE ... FROM쿼리 문자열을 이름표 붙여 미리 준비

?준비된 쿼리 안에서 값이 들어올 자리(안전한 바인딩)

EXECUTE ... USING준비된 쿼리를 변수값으로 채워 실행

DEALLOCATE PREPARE다 쓴 준비 쿼리는 반드시 해제

변수 범위@변수는 현재 세션(접속) 안에서만 유효

CONCAT테이블·컬럼명 등 구조를 동적으로 조립할 때 사용

@변수 vs @@변수@는 사용자 정의값, @@는 MySQL 시스템 설정값

Tags

#MySQL #사용자변수 #SET #PREPARE #EXECUTE #동적쿼리 #@변수 #DEALLOCATE #플레이스홀더 #SQL #데이터베이스 #DB #MySQL변수 #쿼리최적화 #웹개발 #티스토리

▼ 티스토리 태그 입력란 복사용

MySQL, 사용자변수, SET, PREPARE, EXECUTE, 동적쿼리, @변수, DEALLOCATE, 플레이스홀더, SQL, 데이터베이스, DB, MySQL변수, 쿼리최적화, 웹개발, 티스토리