[MySQL] Prepared 명령

■ Prepared 명령(Prepared Statement)

• PREPARE Statement

• EXECUTE Statement

• DEALLOCATE PREPARE Statement

 

MySQL 5.7은 server-side prepared statements을 지원합니다. 이 지원은 효율적인 클라이언트 / 서버 바이너리 프로토콜을 활용합니다. 매개 변수 값에 자리 표시 자와 함께 prepared statements을 사용하면 다음과 같은 이점이 있습니다.

+ 실행될 때마다 명령문을 구문 분석하기위한 오버 헤드가 줄어 듭니다. 일반적으로 데이터베이스 응용 프로그램은 쿼리 및 삭제 WHERE, 업데이트 SET 및 삽입 VALUES와 같은 절의 리터럴 또는 변수값만 변경하여 대량의 거의 동일한 명령문을 처리합니다.

+ SQL injection 공격에 대해 보호합니다. 매개 변수 값에는 이스케이프되지 않은 SQL 따옴표 및 분리 문자가 포함될 수 있습니다.

 

▶︎ 응용 프로그램의 Prepared Statements

C 프로그램 용 MySQL C API 클라이언트 라이브러리, Java 프로그램 용 MySQL Connector / J 및 .NET 기술을 사용하는 프로그램 용 MySQL Connector / NET을 포함한 클라이언트 프로그래밍 인터페이스를 통해 서버 측 준비 명령문을 사용할 수 있습니다. 예를 들어 C API는 준비된 명령문 API를 구성하는 함수 호출 세트를 제공합니다. 다른 언어 인터페이스는 C 클라이언트 라이브러리에서 링크하여 이진 프로토콜을 사용하는 준비된 명령문을 지원할 수 있습니다 (예 : mysqli 확장). PHP 5.0 이상에서 사용 가능합니다.

 

▶︎ SQL 스크립트에서 준비된 명령문(Prepared Statements)

준비된 명령문에 대한 대체 SQL 인터페이스가 사용 가능합니다. 이 인터페이스는 준비된 명령문 API를 통해 바이너리 프로토콜을 사용하는것 만큼 효율적이지는 않지만 SQL레벨에서 직접 사용할 수 있으므로 프로그래밍이 필요하지 않습니다.

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

+ mysql 클라이언트 프로그램과 같이 SQL문을 서버로 전송하여 실행할 수 있는 모든 프로그램에서 사용할 수 있습니다.

+ MySQL 4.1 이상을 실행하는 서버에 연결되어 있으면 클라이언트가 이전 버전의 클라이언트 라이브러리를 사용하는 경우에도 사용할 수 있습니다.

 

준비된 명령문(prepared statements)의 SQL 구문은 다음과 같은 상황에 사용됩니다.

+ 코딩하기 전에 애플리케이션에서 준비된 명령문이 작동하는 방식을 테스트합니다.

+ 지원하는 프로그래밍 API에 액세스할 수 없는 경우 준비된 명령문을 사용합니다.

+ 준비된 명령문으로 응용 프로그램 문제를 대화식으로 해결합니다.

+ 준비된 문장으로 문제를 재현하는 테스트 케이스를 작성하여 버그 보고서를 제출할 수 있습니다.

 

 

▶︎ PREPARE, EXECUTE 및 DEALLOCATE PREPARE 문

준비된 명령문(prepared statements)의 SQL 구문은 세 가지 SQL 문을 기반으로합니다.

+ PREPARE는 실행할 명령문을 준비합니다.

+ EXECUTE는 준비된 명령문을 실행합니다.

+ DEALLOCATE PREPARE는 준비된 명령문(prepared statements)을 발행합니다.

 

다음 예는 두 변의 길이에 따라 삼각형의 빗변을 계산하는 명령문을 준비하는 두 가지 동등한 방법을 보여줍니다.

 

첫 번째 예는 문자열 리터럴을 사용하여 명령문의 텍스트를 제공하여 준비된 명령문을 작성하는 방법을 보여줍니다.

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

 

두 번째 예는 비슷하지만 명령문의 텍스트를 사용자 변수로 제공합니다.

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

 

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

mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a  |
+----+
|  4 |
|  8 |
| 11 |
| 32 |
| 80 |
+----+
mysql> DEALLOCATE PREPARE stmt3;

 

준비된 명령문(prepared statement)은 작성된 세션에 고유합니다. 이전에 준비된 명령문을 할당 해제하지 않고 세션을 종료하면 서버가 자동으로 할당 해제합니다.

준비된 명령문 또한 세션 전체에 적용됩니다. 저장 루틴 내에서 준비된 명령문을 작성하면 저장 루틴(stored routine)이 종료 될 때 할당 해제되지 않습니다.

너무 많은 준비된 명령문이 동시에 작성되지 않도록하려면 max_prepared_stmt_count 시스템 변수를 설정합니다. 준비된 명령문을 사용하지 않으려면 값을 0으로 설정합니다.

 

▶︎ 준비된 명령문(prepared statement)에서 허용되는 SQL 구문

다음 SQL 문을 준비된 명령문으로 사용할 수 있습니다.

ALTER TABLE
ALTER USER
ANALYZE TABLE
CACHE INDEX
CALL
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
COMMIT
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
DELETE
DO
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
INSERT
INSTALL PLUGIN
KILL
LOAD INDEX INTO CACHE
OPTIMIZE TABLE
RENAME TABLE
REPAIR TABLE
REPLACE
RESET {MASTER | SLAVE | QUERY CACHE}
REVOKE
SELECT
SET
SHOW {WARNINGS | ERRORS}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
TRUNCATE TABLE
UNINSTALL PLUGIN
UPDATE

 

MySQL 5.7.2에서 ‘진단 명령문(아래 명령문)을 준비할 수 없다’ 라는 SQL 표준을 준수하기 위해 MySQL은 준비된 명령문(prepared statements) 문구를 지원하지 않습니다.

+ SHOW WARNINGS, SHOW COUNT(*) WARNINGS

+ SHOW ERRORS, SHOW COUNT(*) ERRORS

+ warning_count 또는 error_count 시스템 변수에 대한 참조가 포함된 명령문.

 

MySQL 5.7에서는 다른 명령문이 지원되지 않습니다.

 

일반적으로 SQL의 준비된 명령문(prepared statements)에서 허용되지 않는 명령문도 스토어드 프로그램(stored program)에서 허용되지 않습니다.

 

준비된 명령문(prepared statements)이 참조하는 테이블 또는 뷰의 메타 데이터 변경 사항이 감지되고 다음에 실행될 때 명령문이 자동으로 다시 준비됩니다.

 

Placeholders는 준비된 명령문(prepared statements)을 사용할 때 LIMIT 절의 인수에 사용할 수 있습니다.

Placeholders : 교체 대상자를 위해 임의로 자리를 가지는 기법. 위에서는 limit의 값을 치환하기 위해 다른 값이 임의로 가지고 있음.

 

PREPARE 및 EXECUTE와 함께 사용되는 준비된(prepared)CALL 문에서 MySQL 5.7부터 OUT 및 INOUT 매개 변수에 대한 플레이스 홀더 지원을 사용할 수 있습니다. 플레이스 홀더(place holder)는 버전에 관계없이 IN 매개 변수에 사용할 수 있습니다.

 

준비된 명령문(prepared statement)의 SQL구문은 중첩 방식으로 사용될 수 없습니다. 즉, PREPARE로 전달된 명령문 자체는 PREPARE, EXECUTE 또는 DEALLOCATE PREPARE 문이 될 수 없습니다.

 

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

 

준비된 명령문(prepared statement)의 SQL구문은 저장 프로시저 내에서 사용할 수 있지만 저장함수나 트리거에서는 사용할 수 없습니다. 그러나 PREPARE 및 EXECUTE로 준비되고 실행되는 동적 명령문에는 커서를 사용할 수 없습니다. 커서 작성시 커서에 대한 명령문이 점검되므로 명령문이 동적 일 수 없습니다.

 

준비된 명령문(prepared statement)의 SQL 구문은 다중 명령문 (즉, 단일 문자 내에서 문자로 구분 된 다중 명령문)을 지원하지 않습니다.

 

준비된 명령문(prepared statement)은 쿼리 캐시를 사용합니다.

 

CALL SQL 명령문을 사용하여 준비된 명령문이 포함된 스토어드 프로시저를 실행하는 C 프로그램을 작성하려면 CLIENT_MULTI_RESULTS 플래그가 사용 가능해야합니다. 이는 각 CALL이 프로 시저 내에서 실행된 명령문에 의해 리턴될 수 있는 결과 세트 외에 호출 상태를 나타내는 결과를 리턴하기 때문입니다.

 

CLIENT_MULTI_RESULTS 플래그 자체를 명시 적으로 전달하거나 암시적으로 CLIENT_MULTI_STATEMENTS를 전달하여 CLIENT_MULTI_RESULTS를 활성화하여 mysql_real_connect ()를 호출 할 때 CLIENT_MULTI_RESULTS를 활성화할 수 있습니다.

 

 

■ PREPARE 명령

PREPARE stmt_name FROM preparable_stmt

PREPARE 명령문은 SQL 문을 준비하고 나중에 명령문을 참조할 이름 stmt_name을 지정합니다. 준비된 명령문은 EXECUTE로 실행되고 DEALLOCATE PREPARE와 함께 릴리스됩니다.

 

명령문 이름은 대소문자를 구분하지 않습니다. preparable_stmt는 문자열 리터럴 또는 SQL 문의 텍스트를 포함하는 사용자 변수입니다. 텍스트는 여러 명령문이 아닌 단일 명령문을 나타내야 합니다. 명령문 내에서 ?(물음표) 문자를 매개 변수 표시 문자로 사용하여 데이터 값을 나중에 실행할 때 쿼리에 바인딩할 위치를 나타낼 수 있습니다. ? 문자열 값에 바인드하려는 경우에도 따옴표로 묶지 않아야합니다. 매개 변수 표시 문자는 SQL 키워드, ID 등이 아닌 데이터 값이 표시되는 위치에만 사용할 수 있습니다.

 

주어진 이름의 준비된 명령문(prepared statement)이 이미 존재하면 새 명령문이 준비되기 전에 암시적으로 할당이 해제됩니다. 이는 새 명령문에 오류가 있고 준비할 수 없는 경우 오류가 리턴되고 지정된 이름의 명령문이 존재하지 않음을 의미합니다.

 

준비된 명령문(prepared statement)의 범위는 명령문이 작성되는 세션이며 몇 가지 영향을 미칩니다.

+ 한 세션에서 작성된 준비된 문장은 다른 세션에서 사용할 수 없습니다.

+ 세션이 끝나면 정상적으로 또는 비정상적으로 준비된 명령문이 더 이상 존재하지 않습니다. 자동 재 연결이 활성화 된 경우 클라이언트에게 연결이 끊어 졌다는 알림이 표시되지 않습니다. 이러한 이유로 클라이언트는 자동 재 연결을 비활성화 할 수 있습니다.

+ 저장된 프로그램 내에 작성된 준비된 명령문은 프로그램 실행이 완료된 후에도 계속 존재하며 나중에 프로그램 외부에서 실행될 수 있습니다.

+ 저장된 프로그램 컨텍스트에서 준비된 명령문은 프로그램이 종료될 때 범위를 벗어나 프로그램 외부에서 나중에 실행할 명령문이었기 때문에 스토어드 프로시저나 함수 매개 변수 또는 로컬 변수를 참조할 수 없습니다. 해결 방법으로 세션 범위가 있는 사용자 정의 변수를 이용합니다.

 

■ EXECUTE 명령문

EXECUTE stmt_name
    [USING @var_name [, @var_name] ...]

PREPARE로 명령문을 준비한 후 준비된 명령문(prepared statement) 이름을 참조하는 EXECUTE 문으로 실행합니다. 준비된 명령문에 매개 변수 표시 문자가 포함된 경우 매개 변수에 바인드 할 값이 포함된 사용자 변수를 나열하는 USING절을 제공해야합니다. 매개 변수 값은 사용자 변수로만 제공할 수 있으며 USING 절은 명령문의 매개변수 표시 문자 수만큼 정확하게 변수의 이름을 지정해야합니다.

 

주어진 준비된 명령문을 여러번 실행하여 다른 변수를 전달하거나 각 실행 전에 변수를 다른 값으로 설정할 수 있습니다.

 

■ DEALLOCATE PREPARE 명령문

{DEALLOCATE | DROP} PREPARE stmt_name

PREPARE로 생성된 준비된 명령문(prepared statement)을 할당 해제하려면 준비된 명령문 이름을 참조하는 DEALLOCATE PREPARE 문을 사용합니다. 할당 해제 후 준비된 명령문을 실행하려고 하면 오류가 발생합니다. 너무 많은 준비된 명령문이 작성되어 DEALLOCATE PREPARE문 또는 세션 종료에 의해 할당 해제되지 않으면 max_prepared_stmt_count 시스템 변수에 의해 적용되는 상한이 발생할 수 있습니다.