もう迷わない！MySQLストアドプロシージャの動的SQLを使いこなすための徹底ガイド

2024-04-07

MySQLストアドプロシージャで動的SQLを使用する方法

MySQLストアドプロシージャは、データベース操作をカプセル化し、コードの再利用性を高めるために使用されます。動的SQLを使用すると、ストアドプロシージャ内で実行するSQL文をプログラム実行時に生成することができます。これは、さまざまな条件に基づいて異なるクエリを実行したり、ユーザー入力を受け取ってクエリを動的に生成したりする必要がある場合に便利です。

方法

MySQLストアドプロシージャで動的SQLを使用するには、以下の2つの方法があります。

CONCAT関数を使用して、文字列を連結することで動的SQL文を生成することができます。

DELIMITER //

CREATE PROCEDURE get_products_by_category(
  IN category_id INT
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = CONCAT('SELECT * FROM products WHERE category_id = ', category_id);

  PREPARE stmt FROM sql_text;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

この例では、get_products_by_categoryというストアドプロシージャを作成しています。このストアドプロシージャは、category_idという入力パラメータを受け取り、そのカテゴリに属するすべての製品をproductsテーブルから取得します。

PREPAREとEXECUTEステートメントを使用して、動的SQL文を準備し実行することができます。

DELIMITER //

CREATE PROCEDURE get_products_by_category(
  IN category_id INT
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = 'SELECT * FROM products WHERE category_id = ?';

  PREPARE stmt FROM sql_text;
  EXECUTE stmt USING category_id;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

この例は、上記の例と同様ですが、CONCAT関数を使用せずにPREPAREとEXECUTEステートメントを使用して動的SQL文を生成しています。

注意事項

動的SQLを使用する際には、SQLインジェクション攻撃に注意する必要があります。ユーザー入力を受け取ってクエリを生成する場合は、入力値を適切にサニタイズする必要があります。
動的SQLは静的SQLよりも実行速度が遅くなる場合があります。パフォーマンスが重要な場合は、静的SQLを使用することを検討してください。

補足

上記以外にも、MySQLストアドプロシージャで動的SQLを使用する方法はいくつかあります。詳細は、MySQLの公式ドキュメントを参照してください。

CONCAT関数を使用する

DELIMITER //

CREATE PROCEDURE get_products_by_category(
  IN category_id INT
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = CONCAT('SELECT * FROM products WHERE category_id = ', category_id);

  PREPARE stmt FROM sql_text;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

使用方法

CALL get_products_by_category(1);

この例では、category_idが1の製品を取得します。

PREPAREとEXECUTEを使用する

DELIMITER //

CREATE PROCEDURE get_products_by_category(
  IN category_id INT
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = 'SELECT * FROM products WHERE category_id = ?';

  PREPARE stmt FROM sql_text;
  EXECUTE stmt USING category_id;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

CALL get_products_by_category(1);

ユーザー入力を受け取ってクエリを生成する

DELIMITER //

CREATE PROCEDURE get_products_by_name(
  IN product_name VARCHAR(255)
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = CONCAT('SELECT * FROM products WHERE product_name LIKE ', '%', product_name, '%');

  PREPARE stmt FROM sql_text;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

CALL get_products_by_name('iPhone');

この例では、名前

MySQLストアドプロシージャで動的SQLを使用するその他の方法

DELIMITER //

CREATE PROCEDURE get_products_by_category(
  IN category_id INT
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = CONCAT('SELECT * FROM products WHERE category_id = ', category_id);

  EXECUTE IMMEDIATE sql_text;
END //

DELIMITER ;

CASE文を使用して、条件に基づいて異なるSQL文を実行することができます。

DELIMITER //

CREATE PROCEDURE get_products(
  IN category_id INT,
  IN product_name VARCHAR(255)
)
BEGIN
  DECLARE sql_text VARCHAR(255);

  SET sql_text = CASE
    WHEN category_id IS NOT NULL THEN CONCAT('SELECT * FROM products WHERE category_id = ', category_id)
    WHEN product_name IS NOT NULL THEN CONCAT('SELECT * FROM products WHERE product_name LIKE ', '%', product_name, '%')
    ELSE 'SELECT * FROM products'
  END CASE;

  PREPARE stmt FROM sql_text;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

この例では、get_productsというストアドプロシージャを作成しています。このストアドプロシージャは、category_idとproduct_nameという2つの入力パラメータを受け取り、条件に基づいて異なるSQL文を実行します。

保存された変数を使用して、動的に生成されたSQL文の一部を保存することができます。

DELIMITER //

CREATE PROCEDURE get_products_by_category(
  IN category_id INT
)
BEGIN
  DECLARE sql_text VARCHAR(255);
  DECLARE category_name VARCHAR(255);

  SET category_name = (SELECT category_name FROM categories WHERE category_id = category_id);

  SET sql_text = CONCAT('SELECT * FROM products WHERE category_name = ', category_name);

  PREPARE stmt FROM sql_text;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //

DELIMITER ;