mysql-prepared-statements.md

MySQLでSQLにパラメータを利用する

概要

• Prepared Statement を使う
• ユーザー定義変数を使ってSQL文を作成し、Prepared Statement を実行する

ユーザーを作成するサンプル

SET @db_username = 'user_name';
SET @db_password = 'password';

--  DROP USER IF EXISTS 'user_name';
SET @drop_user = CONCAT("DROP USER IF EXISTS '", @db_username, "';");
PREPARE stmt FROM @drop_user;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

--  CREATE USER user_name@`%` IDENTIFIED BY 'password';
SET @create_user = CONCAT("CREATE USER ", @db_username, "@`%` IDENTIFIED BY '", @db_password, "';");
PREPARE stmt FROM @create_user;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

--  GRANT ALL PRIVILEGES ON *.* TO `user_name`@`%`;
SET @grant = CONCAT("GRANT ALL PRIVILEGES ON *.* TO ", @db_username, "@`%`;");
PREPARE stmt FROM @grant;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

参考資料

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.5 準備済みステートメントのための SQL 構文