第82回　sysスキーマの便利なストアドプロシージャやストアドファンクション［その1］

MySQL5.7とそれ以降からsysスキーマがデフォルトでインストールされます。show databases構文を実行すると、sysデータベースが確認できます。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

このsysスキーマはPerformance Schemaの情報を一目で理解できるように変更したビューを提供したり、診断レポートの作成やPerformance Schemaの設定を変更するストアドプロシージャやストアドファンクションなどを提供します。

今回は、sysスキーマが提供する運用に役に立つ便利なストアドプロシージャやストアドファンクションについて、いくつか紹介したいと思います。今回使用しているMySQLのバージョンは5.7.23です。

table_exists() プロシージャ

指定したテーブルが通常のテーブル、テンポラリーテーブルまたはビューとして存在するかチェックします。引数にデータベース名とテーブル名と戻り値用の変数をセットします。実行後、戻り値用の変数を確認することでテーブルが存在しているか確認することができます。

testデータベースにt0テーブルが存在するか実行してみます。

mysql > CALL sys.table_exists('test','t0',@output);
Query OK, 0 rows affected (0.04 sec)

mysql > SELECT @output;
+------------+
| @output    |
+------------+
| BASE TABLE |
+------------+
1 row in set (0.00 sec)

ビューの場合は以下のようになります。

mysql > CALL sys.table_exists('test','t0_view',@output);
Query OK, 0 rows affected (0.00 sec)

mysql  > SELECT @output;
+---------+
| @output |
+---------+
| VIEW    |
+---------+

戻り値として、以下が定義されています。

'' : テーブルやビューが存在していない場合は空の値が返ります
'BASE TABLE' : 通常のテーブルが存在していることを表します
'VIEW' : ビューが存在していることを表します
'TEMPORARY' : テンポラリーテーブルが存在していることを表します

create_synonym_db() プロシージャ

スキーマ名を指定すると、基のスキーマ内のすべての表およびビューを参照するビューを持つシノニムスキーマが作成されます。指定したスキーマがすでに存在してる場合はエラーとなります。たとえば、information_schemaやperformance_schemaといった長い名称のスキーマを毎回、mysqlコマンドから打ち込むのは大変だと思います。

このプロシージャーを使用して短い名称のシノニムスキーマを作成することで、その名称でuse構文やシノニムスキーマ名.テーブル名でアクセスできるようになります。

引数に基のスキーマとシノニムスキーマを指定します。

information_schemaのシノニムスキーマI_Sを作成する例

mysql > CALL sys.create_synonym_db('INFORMATION_SCHEMA', 'I_S');
+----------------------------------------+
| summary                                |
+----------------------------------------+
| Created 61 views in the `I_S` database |
+----------------------------------------+

mysql > show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| I_S                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

mysql > use I_S;
Database changed

mysql > show tables like 'TABLES';
+------------------------+
| Tables_in_I_S (TABLES) |
+------------------------+
| TABLES                 |
+------------------------+

execute_prepared_stmt() プロシージャ

引数にSQL文を指定することで、プリペアドステートメントが作成され、実行されます。実行後はそのプリペアドステートメントは開放されます。よって、1回限りの動的SQLを実行するのに役に立ちます。

mysql > CALL sys.execute_prepared_stmt('SELECT * FROM sys.version');
+-------------+---------------+
| sys_version | mysql_version |
+-------------+---------------+
| 1.5.1       | 5.7.23        |
+-------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

statement_performance_analyzer() プロシージャ

実行中のMySQL上のステートメント情報のレポートを作成します。こちらについては次回詳しく紹介いたします。

quote_identifier() ファンクション

テーブル名やスキーマ名等で使用したい文字列を引数で指定すると、SQL文を記述するのに適した引用符付きの文字列を生成してくれます。予約語であったり、バッククォートを含む文字列の際に便利です。このファンクションは、MySQL 5.7.14とそれ以降から使用可能です。

mysql > SELECT sys.quote_identifier('test`test');
+-----------------------------------+
| sys.quote_identifier('test`test') |
+-----------------------------------+
| `test``test`                      |
+-----------------------------------+

mysql > SELECT sys.quote_identifier('select');
+--------------------------------+
| sys.quote_identifier('select') |
+--------------------------------+
| `select`                       |
+--------------------------------+

list_add()/list_drop() ファンクション

カンマで区切られた値のリストに対して追加や削除をすることができます。たとえば、MySQLのsql_modeオプションは、1つの変数に対してカンマで区切られた値を保持します。sql_modeからあるモードを追加や削除する際には、このファンクションを使用すると便利です。

第1引数にリストの値、第2引数には追加または削除する値を指定します。

以下は、すでにsql_modeに登録されたONLY_FULL_GROUP_BYのみを削除する例です。list_drop()を使用します。

mysql > SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode                                |
+-------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------+
mysql > SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
Query OK, 0 rows affected (0.00 sec)

mysql > SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+

追加する場合は、list_add()を使用します。また、addとdropともに複数の値を扱うことも可能です。例として、先ほどのsql_modeに対して、ERROR_FOR_DIVISION_BY_ZEROとNO_ZERO_IN_DATEを追加してみます。

mysql > SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql > SET @@sql_mode = sys.list_add(@@sql_mode, 'ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_IN_DATE');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql > SELECT @@sql_mode;
+-------------------------------------------------------------------+
| @@sql_mode                                                        |
+-------------------------------------------------------------------+
| NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

また、MySQLのSET型のカラムについてもカンマ区切りで値が管理されています。SET型の値を変更する際にもこのファンクションで追加や削除することが可能です。

例として、SET型のset_valカラムのレコードからbを削除してみます。

mysql >  SELECT * FROM test_set;
+---------+
| set_val |
+---------+
| b       |
| b,c     |
| a,b,c   |
+---------+
3 rows in set (0.00 sec)

mysql >  UPDATE test_set SET  set_val=sys.list_drop(set_val,'b');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql > SELECT * FROM test_set;
+---------+
| set_val |
+---------+
|         |
| c       |
| a,c     |
+---------+
3 rows in set (0.00 sec)

まとめ

sysスキーマには運用や分析を便利にしてくれる機能があります。今回説明したもの以外にも、さまざまなプロシージャやファンクションがあります。詳しく確認したい方は、マニュアルの「26.4.4 sys Schema Stored Procedures」や「26.4.5 sys Schema Stored Functions」をご参照ください。

また、使い方については各プロシージャやファンクションのコメントにも記載されています。プロシージャであればSHOW PROCEDURE STATUS LIKE 'procedure_name'\G、ファンクションであればSHOW FUNCTION STATUS LIKE 'function_name'\Gで確認することも可能です。

次回は、statement_performance_analyzerプロシージャを中心に紹介したいと思います。