MySQL道普請便り

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

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

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_ZERONO_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 Procedures26.4.5 sys Schema Stored Functionsをご参照ください。

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

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

著者プロフィール

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

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

Twitter:@keny_lala