MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 4 分

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_schemaperformance_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上のステートメント情報のレポートを作成します。こちらについては次回詳しく紹介いたします。

著者プロフィール

北川健太郎(きたがわけんたろう)

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで,好きなOracleDatabaseの機能はログオントリガー。

Twitter:@keny_lala

コメント

コメントの記入