MySQL 関数: 文字列、数値、ユーザー定義、ストアド

何ですか MySQL 機能？

MySQL データを保存したり取得したりするだけではありません。 また、缶 データの操作を実行する 取得または保存する前に。 MySQL 関数が登場します。関数とは、操作を実行して結果を返すコードの断片です。関数の中には引数を受け取るものもあれば、引数を受け取らないものもあります。

簡単に例を見てみましょう。デフォルトでは、 MySQL 日付データ型を「YYYY-MM-DD」形式で保存します。アプリケーションを構築し、ユーザーが日付を「DD-MM-YYYY」形式で返されることを希望するとします。 MySQL これを実現するには組み込み関数 DATE_FORMAT を使用します。DATE_FORMAT は、 MySQLそして、このレッスンの後半で詳しく見ていきます。

どのようなタイプであっても、関数 常に単一の値を返します, ゼロ個以上のパラメータを受け入れることができます 括弧の内側、そして 式が許可されている場所ならどこでも使用できます — SELECT リスト、WHERE 句、または ORDER BY 句内。

なぜ使うの？ MySQL 機能？

関数とは何かを理解したところで、次の疑問は、そもそもなぜこの処理をデータベースに投入する必要があるのか​​、ということです。

上の図に示すように、関数は入力値を受け取り、データベースエンジン内で一度ロジックを適用し、要求するすべてのアプリケーションに単一の結果を返します。

プログラマーは「なぜわざわざ MySQL 関数？同じ効果はスクリプト言語やプログラミング言語でも実現できます。」アプリケーションプログラムにプロシージャを記述することで、それを実現することは可能です。

先ほどの日付の例に戻ると、ユーザーが希望する形式でデータを取得するためには、ビジネスレイヤーが必要な処理を自ら行う必要がある。

これは、アプリケーションを他のシステムと統合する必要がある場合に問題になります。使用するとき MySQL DATE_FORMATなどの関数は、その機能がデータベースに組み込まれており、データを必要とするアプリケーションは必要な形式でデータを取得できます。 ビジネスロジックにおける手戻りを減らし、データの不整合を軽減する.

検討すべきもう XNUMX つの理由 MySQL これらの機能の1つは、クライアント/サーバーアプリケーションのネットワークトラフィックを削減するのに役立つことです。ビジネス層は、ネットワーク経由で生データを取得して操作する必要はなく、保存された関数を呼び出すだけで済みます。平均的に、関数を使用することでシステム全体のパフォーマンスを大幅に向上させることができます。

の種類 MySQL 機能

「何を」「なぜ」行うのかが明確になったので、次に3つの関数群を見ていきましょう。 MySQL 提供される機能：組み込み関数、保存済み関数、およびユーザー定義関数。

組み込み関数

MySQL 多数の組み込み関数が付属しています。 MySQL サーバー。これらは、データに対して様々な種類の操作を実行することを可能にし、一般的に使用される以下のグループに分類されます。

• 文字列関数 – 文字列データ型を操作する
• 数値関数 – 数値データ型を操作する
• 日付関数 – 日付データ型を操作する
• 集計関数 – 上記のすべてのデータ型を操作し、要約された結果セットを生成します。
• その他の機能 – MySQL また、他の種類の組み込み関数もサポートしていますが、このレッスンでは上記で挙げたグループに限定します。

それでは、上記で触れた各グループについて詳しく見ていきましょう。「Myflixdb」サンプルデータベースを用いて、最もよく使われる機能について説明します。

文字列関数

文字列関数はテキスト値に対して動作します。映画テーブルでは、タイトルは小文字と大文字が混在して格納されています。タイトルを大文字で返すクエリを作成したいとします。「UCASE」関数は文字列をパラメータとして受け取り、以下のスクリプトに示すように、すべての文字を大文字に変換します。

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

Pr_media

• UCASE(`title`) は、タイトルをパラメータとして受け取り、それを大文字で返す組み込み関数です。
• AS `upper_case_title` 計算された列にエイリアスを指定することで、結果セットには生の式ではなく、読みやすいヘッダーが付加されます。

上記のスクリプトを実行すると、 MySQL Myflixdbに対してWorkbenchを実行すると、以下の結果が得られます。

UCASEと並んで覚えておくべき2つの仲間がいます。 LCケース 文字列を小文字に変換し、 コンカット 2つ以上の文字列を1つに結合します。完全なリストについては、以下を参照してください。 MySQL 文字列関数のリファレンス.

数値関数

前述のとおり、数値関数は数値データ型に対して動作します。また、SQL文の中で数値データに対して直接数学的な計算を行うこともできます。

算術演算子

MySQL SQL文で計算を実行するために使用できる、以下の算術演算子をサポートしています。

各演算子の例を以下に示します。

整数除算（DIV） — DIV は小数部分を切り捨てて整数部分のみを返します。

SELECT 23 DIV 6;

上記のスクリプトを実行すると、 3.

除算演算子 (/) — DIVとは異なり、除算演算子は結果の小数部分を保持します。

SELECT 23 / 6;

上記のスクリプトを実行すると、 3.8333.

サブtrac演算子 (-)

SELECT 23 - 6;

上記のスクリプトを実行すると、 17.

加算演算子 (+)

SELECT 23 + 6;

上記のスクリプトを実行すると、 29.

乗算演算子 (*)

SELECT 23 * 6 AS `multiplication_result`;

結果：

剰余演算子（% または MOD）

剰余演算子は、NをMで割って余りを求めます。前の例と同じ値を使って、剰余演算子の例を見てみましょう。

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

どちらのスクリプトを実行しても 5.

次に、一般的な数値関数のいくつかを見てみましょう。 MySQL.

FLOOR この関数は、数値から小数点以下の桁を削除し、最も近い整数に切り捨てます。以下のスクリプトは、その使用例を示しています。

SELECT FLOOR(23 / 6) AS `floor_result`;

結果：

ROUND この関数は数値を最も近い整数に丸めます。23 ÷ 6 は 3.8333 と評価されるため、ROUND は 4 を返し、FLOOR は 3 を返します。この 2 つの関数は互換性がありません。

SELECT ROUND(23 / 6) AS `round_result`;

結果：

RAND この関数は乱数を生成します。その値は関数が呼び出されるたびに変化します。以下のスクリプトは、その使用例を示しています。

SELECT RAND() AS `random_result`;

日付関数

日付関数は、日付および日時データ型に対して動作します。DATE_FORMAT 関数は、序論で説明した「YYYY-MM-DD と DD-MM-YYYY の区別」の問題を解決する関数です。

DATE_FORMAT このスクリプトは、フォーマットする日付値と、プレースホルダーから構成されるフォーマット文字列の2つのパラメータを受け取ります。以下のスクリプトは、ユーザーから要望された「日-月-年」の形式で各リリース日を返します。

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

最もよく使用される書式設定プレースホルダーを以下に示します。

日々の業務で頻繁に登場する日付関数は他にも3つあります。

• CURDATE（） 現在の日付をYYYY-MM-DD形式で返します。
• NOW（） 現在の日付を返します (NAIST) と 時間。
• DATEDIFF(d1, d2) 2つの日付間の日数を返します。これは、延滞家賃レポートの基礎となるものです。

全リストについては、以下を参照してください。 MySQL 日付と時刻機能のリファレンス.

格納された関数

組み込み関数は一般的なケースに対応しています。より具体的なビジネスルールが必要な場合は、独自のルールを作成します。それがストアドファンクションの役割です。

ストアド関数は、組み込み関数とほぼ同じように動作しますが、定義はユーザー自身で行う必要があります。一度作成したストアド関数は、他の関数と同様にSQL文で使用できます。基本的な構文は以下のとおりです。

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

Pr_media

• 「CREATE FUNCTION sf_name ([parameter(s)])」 必須であり、 MySQL サーバーは、括弧内に定義されたオプションのパラメータを持つ `sf_name` という名前の関数を作成します。
• 「データ型を返します」 これは必須であり、関数が返すデータ型を指定します。
• 「決定論的」 同じ引数が与えられた場合、関数は常に同じ値を返すことを宣言します。 「決定論的ではない」 正反対のことを主張する。
• 「開始…終了」 関数が実行する手続き型コードをラップします。

レンタルした映画のうち、返却期限を過ぎているものを知りたいとします。返却期限をパラメータとして受け取り、サーバー上の現在の日付と比較するストアドファンクションを作成できます。現在の日付が返却期限よりも後であれば、映画は期限切れなので「Yes」を返し、そうでなければ「No」を返します。

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ 警告 — この関数を DETERMINISTIC とラベル付けしないでください。 本体は CURDATE() を呼び出すため、同じ引数でも今日は「いいえ」、明日は「はい」を返す可能性があります。時間依存関数を DETERMINISTIC で宣言すると、オプティマイザが誤解し、ステートメントベースのレプリケーションでは安全ではありません。 決定的ではない 本体が CURDATE()、NOW()、または RAND() を呼び出すたびに。

上記のスクリプトを実行すると、保存関数`sf_past_movie_return_date`が作成されます。それでは、これをテストしてみましょう。

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

上記のスクリプトを実行すると、 MySQL myflixdb に対して Workbench を実行すると、以下の結果が得られます。

2つのNULL行に注目してください。`return_date`がNULLの場合、どちらの比較もTRUEまたはFALSEではなくNULLと評価されるため、どちらのIF分岐も実行されず、関数はNULLを返します。これは、返却されていない映画には比較対象となる返却日がないため、当然の結果です。

ユーザー定義関数

SQLだけでは十分な速度が得られない場合、 MySQL 3つ目の選択肢も可能になります。ユーザー定義関数（UDF）は、次のようなコンパイル言語で記述されます。 C or C++UDFは共有ライブラリに組み込まれ、サーバーに登録されます。一度追加されると、他の関数と同様に呼び出されます。UDFはサーバープロセス内でネイティブコードとして実行されるため、負荷の高い計算に適していますが、UDFにバグがあるとサーバーがクラッシュする可能性があるため、ストアドファンクションほど頻繁には使用されません。

組み込み関数、保存関数、ユーザー定義関数：どれを使うべきか？

これら3つの関数群はすべて単一の値を返し、任意のSQL文から呼び出すことができますが、作成者、実行場所、およびリスクの程度が異なります。以下の表に、これらの違いをまとめました。

原則として、まずは組み込み関数から始めましょう。適切な関数が見つからない場合は、ルールを1か所にまとめるためにストアド関数を作成します。ストアド関数の処理速度が明らかに遅い場合にのみ、ユーザー定義関数（UDF）の使用を検討してください。

よくあるご質問