15.5 Prepared Statements

15.5 Prepared Statements

15.5.1 PREPARE Statement 15.5.2 EXECUTE Statement 15.5.3 DEALLOCATE PREPARE Statement

MySQL 9.5는 서버 측 prepared statement를 지원합니다. 이 지원은 효율적인 클라이언트/서버 바이너리 프로토콜을 활용합니다. 매개변수 값에 대한 placeholder를 사용하는 prepared statement는 다음과 같은 이점이 있습니다:

• 매번 실행될 때 statement를 파싱하는 오버헤드가 적습니다. 일반적으로 데이터베이스 애플리케이션은 거의 동일한 statement를 매우 많이 처리하며, WHERE (쿼리와 delete), SET (update), VALUES (insert)와 같은 절에서 리터럴 또는 변수 값만 변경됩니다.

• SQL 인젝션 공격으로부터 보호합니다. 매개변수 값에는 이스케이프하지 않은 SQL 따옴표 및 구분 기호 문자가 포함될 수 있습니다.

다음 절에서는 prepared statement의 특성에 대한 개요를 제공합니다:

• Prepared Statements in Application Programs

• Prepared Statements in SQL Scripts

• PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements

• SQL Syntax Permitted in Prepared Statements

Prepared Statements in Application Programs

서버 측 prepared statement는 클라이언트 프로그래밍 인터페이스를 통해 사용할 수 있습니다. 여기에는 C 프로그램용 MySQL C API 클라이언트 라이브러리, Java 프로그램용 MySQL Connector/J, .NET 기술을 사용하는 프로그램용 MySQL Connector/NET이 포함됩니다.

예를 들어, C API는 prepared statement API를 구성하는 함수 호출 집합을 제공합니다. 자세한 내용은 C API Prepared Statement Interface를 참조하십시오.

다른 언어 인터페이스는 C 클라이언트 라이브러리를 링크하여 바이너리 프로토콜을 사용하는 prepared statement를 지원할 수 있는데, 한 가지 예가 PHP 5.0 이상에서 사용 가능한 mysqli extension입니다.

Prepared Statements in SQL Scripts

prepared statement에 대한 또 다른 SQL 인터페이스가 제공됩니다. 이 인터페이스는 prepared statement API를 통해 바이너리 프로토콜을 사용하는 것만큼 효율적이지는 않지만, SQL 수준에서 직접 사용할 수 있으므로 프로그래밍이 필요하지 않습니다:

• 사용할 수 있는 프로그래밍 인터페이스가 없을 때 사용할 수 있습니다.

• mysql 클라이언트 프로그램과 같이, 서버에 실행할 SQL statement를 전송할 수 있는 모든 프로그램에서 사용할 수 있습니다.

• 클라이언트가 오래된 버전의 클라이언트 라이브러리를 사용하더라도 사용할 수 있습니다.

prepared statement를 위한 SQL 구문은 다음과 같은 상황에서 사용하도록 의도되었습니다:

• 애플리케이션을 코딩하기 전에 prepared statement가 어떻게 동작하는지 테스트하기 위해.

• 이를 지원하는 프로그래밍 API에 접근할 수 없을 때 prepared statement를 사용하기 위해.

• prepared statement와 관련된 애플리케이션 문제를 대화식으로 문제 해결하기 위해.

• prepared statement와 관련된 문제를 재현하는 테스트 케이스를 작성하여 버그 리포트를 제출할 수 있도록 하기 위해.

PREPARE, EXECUTE, and DEALLOCATE PREPARE Statements

prepared statement를 위한 SQL 구문은 세 가지 SQL statement를 기반으로 합니다:

• PREPARE는 실행을 위해 statement를 준비합니다( Section 15.5.1, “PREPARE Statement” 참조).

• EXECUTE는 prepared statement를 실행합니다( Section 15.5.2, “EXECUTE Statement” 참조).

• DEALLOCATE PREPARE는 prepared statement를 해제합니다( Section 15.5.3, “DEALLOCATE PREPARE Statement” 참조).

다음 예에서는 삼각형의 두 변의 길이가 주어졌을 때 빗변을 계산하는 statement를 준비하는 두 가지 동등한 방법을 보여 줍니다.

첫 번째 예는 statement 텍스트를 제공하기 위해 문자열 리터럴을 사용하여 prepared statement를 생성하는 방법을 보여 줍니다:

1mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
2mysql> SET @a = 3;
3mysql> SET @b = 4;
4mysql> EXECUTE stmt1 USING @a, @b;
5+------------+
6| hypotenuse |
7+------------+
8|          5 |
9+------------+
10mysql> DEALLOCATE PREPARE stmt1;

두 번째 예는 이와 유사하지만, statement 텍스트를 사용자 변수로 제공합니다:

1mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
2mysql> PREPARE stmt2 FROM @s;
3mysql> SET @a = 6;
4mysql> SET @b = 8;
5mysql> EXECUTE stmt2 USING @a, @b;
6+------------+
7| hypotenuse |
8+------------+
9|         10 |
10+------------+
11mysql> DEALLOCATE PREPARE stmt2;

다음은 쿼리를 수행할 테이블 이름을 사용자 변수에 저장하여 런타임에 어떤 테이블에 대해 쿼리를 수행할지 선택하는 방법을 보여 주는 추가 예입니다:

1mysql> USE test;
2mysql> CREATE TABLE t1 (a INT NOT NULL);
3mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);
4
5mysql> SET @table = 't1';
6mysql> SET @s = CONCAT('SELECT * FROM ', @table);
7
8mysql> PREPARE stmt3 FROM @s;
9mysql> EXECUTE stmt3;
10+----+
11| a  |
12+----+
13|  4 |
14|  8 |
15| 11 |
16| 32 |
17| 80 |
18+----+
19
20mysql> DEALLOCATE PREPARE stmt3;

prepared statement는 생성된 세션에만 특정합니다. 이전에 준비된 statement를 해제하지 않고 세션을 종료하면, 서버는 자동으로 이를 해제합니다.

prepared statement는 또한 세션 전체에서 전역적입니다. 저장 루틴 내에서 prepared statement를 생성한 경우, 저장 루틴이 종료되어도 해제되지 않습니다.

동시에 너무 많은 prepared statement가 생성되는 것을 방지하려면 max_prepared_stmt_count 시스템 변수를 설정하십시오. prepared statement 사용을 방지하려면 이 값을 0으로 설정하십시오.

SQL Syntax Permitted in Prepared Statements

다음 SQL statement는 prepared statement로 사용할 수 있습니다:

1ALTER {INSTANCE | TABLE | USER}
2ANALYZE
3CALL
4CHANGE {REPLICATION SOURCE TO | REPLICATION FILTER}
5CHECKSUM
6COMMIT
7{CREATE | DROP} INDEX
8{CREATE | DROP | RENAME} DATABASE
9{CREATE | DROP | RENAME} TABLE
10{CREATE | DROP | RENAME} USER
11DEALLOCATE PREPARE
12DROP VIEW
13DELETE
14DO
15EXECUTE
16FLUSH
17GRANT {ROLE}
18INSERT
19INSTALL PLUGIN
20KILL
21OPTIMIZE
22PREPARE
23REPAIR TABLE
24REPLACE
25REPLICA {START | STOP}
26RESET
27REVOKE {ALL | ROLE}
28SELECT
29SET ROLE
30SHOW {BINLOG EVENTS | BINARY LOGS | BINARY LOG STATUS | CHARACTER SETS | COLLATIONS | DATABASES | ENGINES |
31      ERRORS | EVENTS | FIELDS | FUNCTION CODE | FUNCTION STATUS | GRANTS | KEYS | OPEN TABLES |
32      PLUGINS | PRIVILEGES | PROCEDURE CODE | PROCEDURE STATUS | PROCESSLIST | PROFILE | PROFILES |
33      RELAYLOG EVENTS | REPLICAS | REPLICA STATUS | STATUS | PROCEDURE STATUS | TABLE STATUS | TABLES |
34      TRIGGERS | VARIABLES | WARNINGS}
35SHOW CREATE { DATABASE | EVENT | FUNCTION | PROCEDURE | TABLE | TRIGGER | USER | VIEW}
36TRUNCATE
37UNINSTALL PLUGIN
38UPDATE

참고

CREATE TABLE ... START TRANSACTION은 prepared statement에서 지원되지 않습니다.

다른 statement는 지원되지 않습니다.

진단 statement는 prepare할 수 없다고 명시한 SQL 표준을 준수하기 위해, MySQL은 다음을 prepared statement로 지원하지 않습니다:

• SHOW COUNT(*) WARNINGS

• SHOW COUNT(*) ERRORS

• warning_count 또는 error_count 시스템 변수에 대한 참조를 포함하는 statement.

일반적으로 SQL prepared statement에서 허용되지 않는 statement는 저장 프로그램에서도 허용되지 않습니다. 예외는 Section 27.10, “Restrictions on Stored Programs”에 표시되어 있습니다.

prepared statement가 참조하는 테이블 또는 뷰에 대한 메타데이터 변경 사항은 감지되며, 다음 번에 실행될 때 statement의 자동 재준비를 유발합니다. 자세한 내용은 Section 10.10.3, “Caching of Prepared Statements and Stored Programs”를 참조하십시오.

prepared statement를 사용할 때 LIMIT 절의 인자에 placeholder를 사용할 수 있습니다. Section 15.2.13, “SELECT Statement”를 참조하십시오.

event DDL을 포함하는 prepared statement에서는 placeholder가 지원되지 않습니다. 이러한 statement에서 placeholder를 사용하려고 시도하면, PREPARE에 의해 ERROR 6413 (HY000): Dynamic parameters can only be used in DML statements 오류와 함께 거부됩니다. 대신, 이를 재사용 가능한 방식으로 수행하려면 저장 프로시저 본문에서 event SQL을 포함하는 텍스트를 조립하고, SQL statement의 변수 부분을 IN 파라미터로 저장 프로시저에 전달하면 됩니다.

그런 다음, 저장 프로시저 본문 내에서 PREPARE statement를 사용하여 조립된 텍스트를 prepare하고, 원하는 파라미터 값으로 프로시저를 호출할 수 있습니다. 예시는 Section 15.1.15, “CREATE EVENT Statement”를 참조하십시오.

PREPARE 및 EXECUTE와 함께 사용하는 prepared CALL statement에서 OUT 및 INOUT 파라미터에 대한 placeholder 지원은 MySQL 9.5부터 가능합니다. 예시와 이전 버전에 대한 우회 방법은 Section 15.2.1, “CALL Statement”를 참조하십시오. IN 파라미터에 대해서는 버전과 관계없이 placeholder를 사용할 수 있습니다.

prepared statement를 위한 SQL 구문은 중첩된 방식으로 사용할 수 없습니다. 즉, PREPARE에 전달되는 statement 자체가 PREPARE, EXECUTE 또는 DEALLOCATE PREPARE statement일 수 없습니다.

prepared statement를 위한 SQL 구문은 prepared statement API 호출을 사용하는 것과는 다릅니다. 예를 들어, mysql_stmt_prepare() C API 함수을 사용하여 PREPARE, EXECUTE 또는 DEALLOCATE PREPARE statement를 prepare할 수는 없습니다.

prepared statement를 위한 SQL 구문은 저장 프로시저 내에서는 사용할 수 있지만, 저장 함수나 트리거에서는 사용할 수 없습니다. 그러나 커서는 PREPARE 및 EXECUTE로 prepare 및 execute되는 동적 statement에 사용할 수 없습니다. 커서용 statement는 커서 생성 시점에 검사되므로, 해당 statement는 동적일 수 없습니다.

prepared statement를 위한 SQL 구문은 멀티 statement를 지원하지 않습니다(즉, ; 문자로 구분된 여러 statement를 단일 문자열에 포함하는 것은 지원되지 않습니다).

prepared statement를 포함하는 저장 프로시저를 실행하기 위해 CALL SQL statement를 사용하는 C 프로그램을 작성하려면, CLIENT_MULTI_RESULTS 플래그를 활성화해야 합니다. 이는 각 CALL이 호출 상태를 나타내는 결과를, 프로시저 내에서 실행된 statement에 의해 반환될 수 있는 결과 집합과 별도로 반환하기 때문입니다.

CLIENT_MULTI_RESULTS는 mysql_real_connect()를 호출할 때, CLIENT_MULTI_RESULTS 플래그 자체를 명시적으로 전달하거나, CLIENT_MULTI_RESULTS도 활성화하는 CLIENT_MULTI_STATEMENTS를 전달하여 암묵적으로 활성화할 수 있습니다. 추가 정보는 Section 15.2.1, “CALL Statement”를 참조하십시오.