はじめに
2024年7月1日にイノベーションリリースとなる MySQL9.0 がリリースされました。
初版となる 9.0.0 は 8,001個以上のテーブルが存在する場合に、再起動ができなくなるというBugが存在した為、削除されています。 MySQL9.0 のご使用を検討されている方は、執筆時点では 2024年7月23日にリリースされた 9.0.1 が最新となりますので、そちらをご使用下さい。
MySQL9.0 の Enterprise Edition では、ストアドプロシージャ及びストアドファンクションを含むストアドプログラムに JavaScript を用いて実装をすることが可能となりました。
※ Community版では使用できません。
これについては、Oracle社のブログでも分かり易く紹介されているので、是非、一読下さい。
また、リファレンスは以下となりますので、使用を検討される方は、こちらもご確認下さい。
ということで、今回は、JavaScriptを用いたストアドプログラムについて確認した内容を執筆したいと思います。
Multilingual Engine Component (MLE)
はじめに、JavaScriptストアドプログラムを実装する為には、Multilingual Engine Component コンポーネントをインストールする必要があります。
コンポーネント自体は、SQL以外の言語をサポートする為のコンポーネントですが、現時点でサポートされているのは、JavaScript のみとなります。
コンポーネントのインストールには INSTALL COMPONENT を使用します。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> INSTALL COMPONENT 'file://component_mle'; Query OK, 0 rows affected (0.76 sec) mysql> SELECT * FROM mysql.component; +--------------+--------------------+------------------------------------+ | component_id | component_group_id | component_urn | +--------------+--------------------+------------------------------------+ (省略) | 3 | 3 | file://component_mle | +--------------+--------------------+------------------------------------+ |
実装
ストアドファンクション
以下のように簡単な URLエンコード をするファンクションを作成してみました。
|
1 2 3 4 5 6 7 8 |
mysql> CREATE FUNCTION js_url_encode(url VARCHAR(512)) -> RETURNS VARCHAR(512) LANGUAGE JAVASCRIPT -> AS -> $$ $> let x = encodeURI(url); $> return x; $> $$ -> ; |
1行目は、通常のファンクションと変わりないですが、2行目にある RETURNS句 の型の定義の後に LANGUAGE JAVASCRIPT と定義する必要があります。
また、通常のファンクションを作成する際は、一般的には、以下のリファレンスのように delimiter で区切り文字を変更し、 BEGIN と END 内で処理を実装します。
ですが、JavaScriptで実装する際は、AS $$ から $$ 内で処理を実装します。
作成したファンクションを実行すると問題なく実行できています。
|
1 2 3 4 5 6 |
mysql> SELECT js_url_encode('https://www.google.com/search?q=まいえすきゅーえる'); +-------------------------------------------------------------------------------------------------------------------+ | js_url_encode('https://www.google.com/search?q=まいえすきゅーえる') | +-------------------------------------------------------------------------------------------------------------------+ | https://www.google.com/search?q=%E3%81%BE%E3%81%84%E3%81%88%E3%81%99%E3%81%8D%E3%82%85%E3%83%BC%E3%81%88%E3%82%8B | +-------------------------------------------------------------------------------------------------------------------+ |
ストアドプロシージャ
プロシージャを使用するケースでは、SQLを実行するようなケースが多々ある為、以下のような実装をしてみました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> CREATE PROCEDURE js_proc() -> LANGUAGE JAVASCRIPT -> AS -> $$ $> let sql_execute = session.sql("SELECT * FROM world.city;") $> let prepared_statement = session.prepare("INSERT INTO blog.city VALUES (?, ?, ?, ?, ?);") $> $> let sql_result_world = sql_execute.execute() $> let row = sql_result_world.fetchOne() $> $> while(row) { $> prepared_statement.bind(row[0], row[1], row[2], row[3], row[4]) $> let sql_result_blog = prepared_statement.execute() $> row = sql_result_world.fetchOne() $> } $> $> prepared_statement.deallocate() $> $$ -> ; |
処理の内容としては、MySQLの Example Databases の world データベースの city テーブルのレコードを同一のテーブル構造を持った blog.city テーブルへコピーするという処理内容となっています。
変数名が長くていけてないのですが、返されるオブジェクトが何なのかを分かりやすくするために命名してみました。
SQLの実行に伴って使用したオブジェクトは以下の通りです。
各メソッドの詳細については、オブジェクト名のリンク先のリファレンスをご参照下さい。
- SQLを実行するには、最初に Session オブジェクトを使用します。
- session.sql() の結果は、SqlExecute オブジェクトが返されます。
- session.prepare() の結果は、PreparedStatement オブジェクトが返されます。
- SqlExecute.execute() と PreparedStatement.execute() の結果は、SqlResult オブジェクトが返されます。
- SqlResult.fetchOne() の結果は、Row オブジェクトが返されます。
作成したプロシージャを実行した結果、問題なくレコードがコピーされています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
mysql> CALL js_proc(); Query OK, 1 row affected (7.91 sec) mysql> SELECT COUNT(*) FROM blog.city; +----------+ | COUNT(*) | +----------+ | 4079 | +----------+ 1 row in set (0.00 sec) mysql> SELECT * FROM blog.city LIMIT 5; +------+------------------------------------+-------------+------------------------+------------+ | ID | Name | CountryCode | District | Population | +------+------------------------------------+-------------+------------------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | +------+------------------------------------+-------------+------------------------+------------+ |
ちなみに、PreparedStatement.bind() は以下のように world.city テーブルから取得した値を設定していますが、
|
1 |
prepared_statement.bind(row[0], row[1], row[2], row[3], row[4]) |
連想配列を用いたテーブルのカラム名で値を参照することも可能です。
|
1 |
prepared_statement.bind(row["ID"], row["Name"], row["CountryCode"], row["District"], row["Population"]) |
JavaScriptで実装したファンクション&プロシージャの判別方法
ファンクションとプロシージャの一覧を確認したい場合は、 INFORMATION_SCHEMA.ROUTINES で確認することができます。
JavaScriptで実装されたかどうかを判別するには、同テーブルの EXTERNAL_LANGUAGE が JAVASCRIPT であるかどうかで確認できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> SELECT -> ROUTINE_SCHEMA -> ,ROUTINE_NAME -> ,ROUTINE_TYPE -> FROM -> INFORMATION_SCHEMA.ROUTINES -> WHERE -> EXTERNAL_LANGUAGE="JAVASCRIPT"; +----------------+---------------+--------------+ | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | +----------------+---------------+--------------+ | blog | js_url_encode | FUNCTION | | blog | js_proc | PROCEDURE | +----------------+---------------+--------------+ |
MLEコンポーネントのセッション情報とオプション
上記リファレンスに記載されていますが、JavaScriptを用いたストアドプログラムを構成する為の MLE コンポーネントには、以下の関数が用意されています。
- mle_session_reset()
- mle_session_state()
- mle_set_session_state()
中でも非常に便利なのが mle_session_state() で、以下のように JavaScriptの console.log() や console.error() に設定した情報を後から確認することができます。
以下のようなプロシージャを作成してみました。
cityテーブルから5件を抽出するクエリですが、取得したデータの Name カラムの値を console.log() で出力し、 Population カラムの値を console.error() で出力してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> CREATE PROCEDURE js_console() -> LANGUAGE JAVASCRIPT -> AS -> $$ $> let sql_execute = session.sql("SELECT * FROM world.city LIMIT 5;") $> $> let sql_result_world = sql_execute.execute() $> let row = sql_result_world.fetchOne() $> $> while(row) { $> console.log(row["Name"]) $> console.error(row["Population"]) $> $> row = sql_result_world.fetchOne() $> } $> $> $$ -> ; |
ひとまず、実行した後に、
|
1 2 |
mysql> CALL js_console(); Query OK, 0 rows affected (0.00 sec) |
mle_session_state('stdout') を参照すると、 console.log() に設定した値が参照できます。
|
1 2 3 4 5 6 7 |
mysql> SELECT mle_session_state('stdout')\G *************************** 1. row *************************** mle_session_state('stdout'): Kabul Qandahar Herat Mazar-e-Sharif Amsterdam |
mle_session_state('stderr') も同様に、 console.error() に設定した値が参照できます。
|
1 2 3 4 5 6 7 |
mysql> SELECT mle_session_state('stderr')\G *************************** 1. row *************************** mle_session_state('stderr'): 1780000 237500 186800 127800 731200 |
実装過程で、取得した値を確認したい時等、実装中のデバッグ用途として有用に使える手段ではないでしょうか。
よく、Oracleの DBMS_OUTPUT.PUT_LINE のようなものが、MySQLにはないですか？という質問を頂くことがありますが、通常のプロシージャ内では、SELECTクエリで文字列を出力する程度のことしかできません。
しかし、この機能を活用すれば十分に代替になり得そうです。
また、以下のようにJavaScript関数を定義して、出力の際に、システム日時を設定して出力するようにして実装してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
mysql> CREATE PROCEDURE js_debug() -> LANGUAGE JAVASCRIPT -> AS -> $$ $> $> function console_log(str) { $> let date = new Date(); $> date.setTime(date.getTime() - (date.getTimezoneOffset()*60*1000)); $> let str_date = date.toISOString().replace('T', ' ').substr(0, 19); $> console.log(str_date + " " + str); $> } $> $> console_log("START SQL!") $> $> let res = session.sql("SELECT SLEEP(5);").execute() $> $> console_log("END SQL!") $> $> $$ -> ; |
これを実行すれば、とある処理にどの位の時間がかかったかが分かるようになります。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> CALL js_debug(); Query OK, 0 rows affected (5.01 sec) mysql> SELECT mle_session_state('stdout'); +--------------------------------------------------------------+ | mle_session_state('stdout') | +--------------------------------------------------------------+ | 2024-07-31 18:13:21 START SQL! 2024-07-31 18:13:26 END SQL! | +--------------------------------------------------------------+ |
現時点で、MySQLではプロシージャの処理に実行時間がかかったとしても スロークエリログ には、プロシージャを呼び出したステートメントが出力されるだけなので、プロシージャ内のどの処理で実行に時間がかかっているかというのは、別途、何らかの手段で確認する必要があります。
その為、このような使い方も非常に有用なデバッグ手段だと思います。
ちなみに、console.log() 、console.error() で設定した値は蓄積されていくので、1度確認した後は、 SELECT mle_session_reset(); でクリアすることをお勧めします。
制限・制約
ストアドプログラムを JavaScript で実装できるようになったとはいえ、全ての機能が使用できるという訳でもなく、サードパーティのモジュールの使用もサポートされておりません。
詳細については、以下のリファレンスをご確認下さい。
また、JavaScriptストアドプログラムでサポートされているMySQLのデータ型についても、以下のリファレンスに記載がありますので、ご使用の際は、以下のリファレンスもご確認下さい。
ちなみに、MySQL9.0 から導入された VECTOR型 は、まだサポートされていません。
MLEコンポーネントに関するパラメータ
MLEコンポーネントに関するパラメータについては、以下のリファレンスに記載があります。
現時点では、MLEコンポーネントが使用するメモリ量の制限をする mle.memory_max のみとなっており、設定値について上記リファレンスでは、以下のように記載されております。
By default, the amount of memory allocated to the MLE component is 64MB (67108864 bytes). You can adjust this by setting the mle.memory_max system variable up to a maximum of 8GB (8589934592 bytes); the minimum possible value is 32MB.
記載内容からは、次のように読み取れます。
| 設定値 | |
|---|---|
| デフォルト値 | 64MB |
| 最小値 | 32MB |
| 最大値 | 8GB |
ですが、こちらのリファレンス では、次のように記載されています。
| 設定値 | |
|---|---|
| デフォルト値 | 1300MB |
| 最小値 | 320MB |
| 最大値 | 64GB |
デフォルト値は、以下のようになり、
|
1 2 3 4 5 6 |
mysql> SHOW GLOBAL VARIABLES LIKE 'mle%'; +----------------+------------+ | Variable_name | Value | +----------------+------------+ | mle.memory_max | 1363148800 | +----------------+------------+ |
320M 以下の値を設定した場合は、起動時に以下のようなログが出力されて、設定値は 335544320 となります。
|
1 |
2024-08-05T01:18:47.022036Z 0 [Warning] [MY-000081] [Server] option 'mle.memory-max': unsigned value 67108864 adjusted to 335544320. |
また、64G 以下の値を設定した場合は、起動時に以下のようなログが出力されて、設定値は 68719476736 となりますので、設定値については、後者のリファレンス情報の方が正しいようです。
|
1 |
2024-08-05T01:29:09.358891Z 0 [Warning] [MY-000081] [Server] option 'mle.memory-max': unsigned value 69793218560 adjusted to 68719476736. |
メモリの使用状況としては、Mle_memory_used で割り当てたメモリの何%が実際に使用されているかを確認でき、メモリ不足でエラーになった際は Mle_oom_errors がカウントされるので、これらのステータス変数を確認しながら、設定値を調整すると良さそうです。
まとめ
Community版では使用できませんが、JavaScript に慣れ親んだ開発者にとっては非常に取っ付きやすく、MySQLのストアドプログラムを実装する敷居が下がるのではないかと思います。
特にデバッグする為の手段が強力になっており、より効率よく実装できることが期待できます。
今回は検証できておりませんが、通常のストアドプログラムとの性能差についても機会があれば確認してみたいと思います。
