パフォーマンスとデータ整合性を両立させる MySQL 関数の DETERMINISTIC、NO SQL、READS SQL DATA 属性

2024-04-02

MySQL 関数の DETERMINISTIC、NO SQL、READS SQL DATA 属性について

MySQL でストアドファンクションを作成する際、DETERMINISTIC、NO SQL、READS SQL DATA のいずれかの属性を指定する必要があります。これらの属性は、関数がどのように動作し、バイナリログに記録されるかを決定します。

各属性の詳細

DETERMINISTIC

関数が常に同じ結果を返すことを意味します。つまり、同じ入力パラメータを与えれば、常に同じ出力を生成します。
外部ソース (ファイルシステム、ネットワークなど) からデータを読み取ったり、ランダム値を生成したりする関数は、DETERMINISTIC 属性を持つことができません。
DETERMINISTIC 属性を持つ関数は、バイナリログに確実に記録されます。

NO SQL

関数が SQL データを読み取ったり書き換えたりしないことを意味します。
許可されるのは、計算や文字列操作などの操作のみです。
NO SQL 属性を持つ関数は、バイナリログに記録されません。

READS SQL DATA

データの書き換えは許可されません。
READS SQL DATA 属性を持つ関数は、バイナリログに記録されるかどうかは、log_bin_trust_function_creators サーバ変数の設定によって異なります。

設定方法

関数作成時に、DETERMINISTIC、NO SQL、READS SQL DATA 属性を指定できます。
例:

CREATE FUNCTION my_function() RETURNS INT
DETERMINISTIC
BEGIN
  RETURN 1 + 1;
END;

注意事項

DETERMINISTIC 属性を持つ関数は、常に同じ結果を返す必要があるため、外部ソースからのデータ読み取りやランダム値生成は避けてください。
NO SQL 属性を持つ関数は、SQL データへのアクセスを行わないため、データの整合性に関する問題が発生する可能性があります。
READS SQL DATA 属性を持つ関数は、データを読み取るため、パフォーマンスに影響を与える可能性があります。

補足

上記の属性は、MySQL 5.6 以降で利用可能です。
バイナリログは、データベースの復元やレプリケーションに使用されます。

CREATE FUNCTION my_function(x INT) RETURNS INT
DETERMINISTIC
BEGIN
  RETURN x + 1;
END;

SELECT my_function(1); -- 2 を返す
SELECT my_function(2); -- 3 を返す

CREATE FUNCTION my_function() RETURNS VARCHAR(255)
NO SQL
BEGIN
  RETURN CONCAT('Hello, ', CURRENT_USER());
END;

SELECT my_function(); -- 'Hello, ユーザ名' を返す

CREATE FUNCTION my_function(x INT) RETURNS INT
READS SQL DATA
BEGIN
  RETURN (SELECT COUNT(*) FROM table WHERE id = x);
END;

SELECT my_function(1); -- テーブル 'table' の id が 1 のレコードの数を返す