MySQL: 文字列の結合に||(パイプ)を利用できるようにする方法


MySQLで文字列結合を利用する場合、concat関数しか使えないと思っておりましたが、他者DBMSのように、パイプ(||)で表現することが可能なことを発見してしまいました。

知らなかった・・。

IBM DB2などで文字列結合するようなSQLを発行する場合、基本的には文字列の結合は、|| (パイプ)を利用します。
たとえばこんな感じで書くと思います。

db2 => SELECT 'TABLE NAME' || ' is ' || TABNAME from syscat.tables fetch first 10 rows only

1
------------------------------------------------------------------------------------
TABLE NAME is ATTRIBUTES
TABLE NAME is BUFFERPOOLDBPARTITIONS
TABLE NAME is BUFFERPOOLNODES
TABLE NAME is BUFFERPOOLS
TABLE NAME is CASTFUNCTIONS
TABLE NAME is CHECKS
TABLE NAME is COLAUTH
TABLE NAME is COLCHECKS
TABLE NAME is COLDIST
TABLE NAME is COLGROUPCOLS

10 record(s) selected.

・・でこれを、MySQLでやるとこんなんなっちゃいます・。。。

mysql> select 'TABLE NAME ' || ' is ' || TABLE_NAME from tables limit 10;
+----------------------------------------+
| 'TABLE NAME ' || ' is ' || TABLE_NAME  |
+----------------------------------------+
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
|                                      0 |
+----------------------------------------+
10 rows in set, 30 warnings (0.01 sec)

・・でMySQLの場合は、結局concatを使うことで対処します。

mysql> select concat('TABLE NAME ',' is ',TABLE_NAME) from tables limit 10;
+------------------------------------------------------+
| concat('TABLE NAME ',' is ',TABLE_NAME)              |
+------------------------------------------------------+
| TABLE NAME  is CHARACTER_SETS                        |
| TABLE NAME  is COLLATIONS                            |
| TABLE NAME  is COLLATION_CHARACTER_SET_APPLICABILITY |
| TABLE NAME  is COLUMNS                               |
| TABLE NAME  is COLUMN_PRIVILEGES                     |
| TABLE NAME  is ENGINES                               |
| TABLE NAME  is EVENTS                                |
| TABLE NAME  is FILES                                 |
| TABLE NAME  is GLOBAL_STATUS                         |
| TABLE NAME  is GLOBAL_VARIABLES                      |
+------------------------------------------------------+
10 rows in set (0.01 sec)

はっきりいうと不便・・・DB2に限らず、他の商用DBからMySQLに乗り換える場合など、アプリケーションなどから発行している文字列結合SQLの部分を、concatに直さなくてはいけません。

もうほぼあきらめていたときに、MySQLのオプションでこんなものがあるのを発見しました・・

それがsql_modeです。以下にその手順を記載します。

・sql_modeに以下の定数をセットします。

mysql> set @@session.sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

・これでもう一度、||(パイプ)を利用したSQLを発行してみましょう。

mysql> select 'TABLE NAME ' || ' is ' || TABLE_NAME from tables limit 10;
+------------------------------------------------------+
| 'TABLE NAME ' || ' is ' || TABLE_NAME                |
+------------------------------------------------------+
| TABLE NAME  is CHARACTER_SETS                        |
| TABLE NAME  is COLLATIONS                            |
| TABLE NAME  is COLLATION_CHARACTER_SET_APPLICABILITY |
| TABLE NAME  is COLUMNS                               |
| TABLE NAME  is COLUMN_PRIVILEGES                     |
| TABLE NAME  is ENGINES                               |
| TABLE NAME  is EVENTS                                |
| TABLE NAME  is FILES                                 |
| TABLE NAME  is GLOBAL_STATUS                         |
| TABLE NAME  is GLOBAL_VARIABLES                      |
+------------------------------------------------------+
10 rows in set (0.01 sec)

いかがでしょうか?PIPES_AS_CONCAT定数を、パラメタにセットするだけで、パイプを利用した文字列結合ができたことを確認できたかと思います。

尚、setで値を設定した場合は、MySQLの再起動時に消えてしまうので、my.cnfに設定しておいたほうがよいです。
※sql_modeの複数の値をセットした場合は、カンマで区切って設定します。

cat /etc/my.cnf
# 名前と後続の括弧の間に余白がない場合、パーサは 無効IGNORE_SPACEを用いることで名前を関数呼び出しと解釈します。
# これは関数名が非表現コンテキストで使用されているときも発生します。^M
# mysql> CREATE TABLE count(i INT);^M
# ERROR 1064 (42000): You have an error in your SQL syntax ...^M
# near 'count(i INT)'^M
# エラーを取り除き名前を識別子として扱われるようにするには、名前の後に続く余白を使うか、
# 引用符で囲んだ識別子として書き記してください(あるいは両方)。
# PIPES_AS_CONCATは、|| で文字列を結合するときに利用します。
sql_mode = 'IGNORE_SPACE,PIPES_AS_CONCAT'

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です