まえがき

マニュアル書いてないけど(Undocumented)、カンマ区切り文字列から行展開するファンクション作成してみた。

環境

[root❣ab9938775985 (日 12月 08 14:38:56) /home/root]$mysql --version
mysql  Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)

スクリプト

jsonの力を借りて。戻り値json指定するのはマニュアル未記載。

drop function if exists split_table;
create function split_table(rsv_args longtext)
returns json deterministic
return(
  with recursive args as(
    select rsv_args as rsv_args
  ),rec (grp,rsv_args,pos,ele)as(
    select 1,rsv_args,locate(',',rsv_args,1),substr(rsv_args,1,locate(',',rsv_args,1)-1) from args
    union all
    select grp+1,rsv_args,case when 0=locate(',',rsv_args,pos+1) then length(rsv_args) else locate(',',rsv_args,pos+1) end
    ,substr(rsv_args,pos+1,case when 0=locate(',',rsv_args,pos+1) then length(rsv_args)+1 else locate(',',rsv_args,pos+1) end-(pos+1))
    from rec
    where grp+1<=length(rsv_args)-length(replace(rsv_args,',',''))+1
  )select
     json_arrayagg(s1.jele)
  from(
     select s1.*,json_objectagg("key",s1.ele)over(order by s1.pos) as jele from rec s1
  )s1
);

使用するときは以下で。

--これはうまくいった。
with sub as(
  select split_table('a,b,c,d') as tgt
)select
  s2.ele
 from sub s1
      ,json_table(
        tgt
        ,"$[*]" columns(ele longtext path "$.key")
      ) as s2
;

--これはうまくいっていない。
with sub as(
  select split_table('apple,banana,cinamon,daikon') as tgt 
)select
  *
 from sub s1
    ,json_table(
      s1.tgt
      ,"$[*]" columns(ele longtext path "$.key")
    )s2
;

動作確認

ユーザー作成

[root💗ab9938775985 (日 12月 08 11:45:51) /home/root]$mysql -uroot -pMysql3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'user01'@'localhost' IDENTIFIED BY 'Mysql3306';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON mysql.* TO 'user01'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)

mysql> create database testdb;
Query OK, 1 row affected (0.02 sec)

mysql> grant all privileges on testdb.* to user01@localhost;
Query OK, 0 rows affected (0.02 sec)

rootユーザーでログインしてファンクション作成。

[root❣ab9938775985 (日 12月 08 13:22:16) /home/root]$mysql -uroot -pMysql3306 -Dtestdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop function if exists split_table;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create function split_table(rsv_args longtext)
    -> returns json deterministic
    -> return(
    ->   with recursive args as(
    ->     select rsv_args as rsv_args
    ->   ),rec (grp,rsv_args,pos,ele)as(
    ->     select 1,rsv_args,locate(',',rsv_args,1),substr(rsv_args,1,locate(',',rsv_args,1)-1) from args
    ->     union all
    ->     select grp+1,rsv_args,case when 0=locate(',',rsv_args,pos+1) then length(rsv_args) else locate(',',rsv_args,pos+1) end
    ->     ,substr(rsv_args,pos+1,case when 0=locate(',',rsv_args,pos+1) then length(rsv_args)+1 else locate(',',rsv_args,pos+1) end-(pos+1))
    ->     from rec
    ->     where grp+1<=length(rsv_args)-length(replace(rsv_args,',',''))+1
    ->   )select
    ->      json_arrayagg(s1.jele)
    ->   from(
    ->      select s1.*,json_objectagg("key",s1.ele)over(order by s1.pos) as jele from rec s1
    ->   )s1
    -> );
Query OK, 0 rows affected (0.01 sec)

root以外で潜入。

[root❣ab9938775985 (日 12月 08 14:03:21) /home/root]$mysql -uuser01 -pMysql3306 -Dtestdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 83
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> select split_table('a,b,c,d');
+----------------------------------------------------------+
| split_table('a,b,c,d')                                   |
+----------------------------------------------------------+
| [{"key": "a"}, {"key": "b"}, {"key": "c"}, {"key": "d"}] |
+----------------------------------------------------------+
1 row in set (0.00 sec)


mysql> with sub as(
    ->   select split_table('a,b,c,d') as tgt
    -> )select
    ->   s2.ele
    ->  from sub s1
    ->       ,json_table(
    ->         tgt
    ->         ,"$[*]" columns(ele longtext path "$.key")
    ->       ) as s2
    -> ;
+------+
| ele  |
+------+
| a    |
| b    |
| c    |
| d    |
+------+
4 rows in set (0.00 sec)

# これがうまくいっていない。切り詰めオプション？？？warningでてる。
mysql> select split_table('apple,banana,cinamon,daikon');
+--------------------------------------------------------------------------+
| split_table('apple,banana,cinamon,daikon')                               |
+--------------------------------------------------------------------------+
| [{"key": "apple"}, {"key": "banan"}, {"key": "cinam"}, {"key": "daiko"}] |
+--------------------------------------------------------------------------+
1 row in set, 3 warnings (0.00 sec)

# 切り詰めオプション発動しているぽい。無効にできないか。
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'ele' at row 1 |
| Warning | 1265 | Data truncated for column 'ele' at row 1 |
| Warning | 1265 | Data truncated for column 'ele' at row 1 |
+---------+------+------------------------------------------+
3 rows in set (0.00 sec)


# これがうまくいっていない。切り詰めオプション？？？warningでてる。
mysql> with sub as(
    ->   select split_table('apple,banana,cinamon,daikon') as tgt 
    -> )select
    ->   *
    ->  from sub s1
    ->     ,json_table(
    ->       s1.tgt
    ->       ,"$[*]" columns(ele longtext path "$.key")
    ->     )s2
    -> ;
    -> ;
+--------------------------------------------------------------------------+-------+
| tgt                                                                      | ele   |
+--------------------------------------------------------------------------+-------+
| [{"key": "apple"}, {"key": "banan"}, {"key": "cinam"}, {"key": "daiko"}] | apple |
| [{"key": "apple"}, {"key": "banan"}, {"key": "cinam"}, {"key": "daiko"}] | banan |
| [{"key": "apple"}, {"key": "banan"}, {"key": "cinam"}, {"key": "daiko"}] | cinam |
| [{"key": "apple"}, {"key": "banan"}, {"key": "cinam"}, {"key": "daiko"}] | daiko |
+--------------------------------------------------------------------------+-------+
4 rows in set, 3 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'ele' at row 1 |
| Warning | 1265 | Data truncated for column 'ele' at row 1 |
| Warning | 1265 | Data truncated for column 'ele' at row 1 |
+---------+------+------------------------------------------+
3 rows in set (0.00 sec)

あとがき

Undocumentedです。

ファンクション作成の練習になった。

大きい型指定していると思うんだけどな、だめだめだ。

以上、ありがとうございました。

20191208追記

単一要素の場合のハンドリングを追加。漏れてた。

drop function if exists split_table;
create function split_table(init_args longtext)
returns json deterministic
return(
  with recursive args as(
    select case when 0=instr(init_args,',') then concat(init_args,',') else init_args end as rsv_args
  ),rec (grp,rsv_args,pos,ele)as(
    select 1,rsv_args,locate(',',rsv_args,1),substr(rsv_args,1,locate(',',rsv_args,1)-1) from args
    union all
    select grp+1,rsv_args,case when 0=locate(',',rsv_args,pos+1) then length(rsv_args) else locate(',',rsv_args,pos+1) end
    ,substr(rsv_args,pos+1,case when 0=locate(',',rsv_args,pos+1) then length(rsv_args)+1 else locate(',',rsv_args,pos+1) end-(pos+1))
    from rec
    where grp+1<=case when 0=instr(init_args,',') then 1 else length(rsv_args)-length(replace(rsv_args,',',''))+1 end
  )select
     json_arrayagg(s1.jele)
  from(
     select s1.*,json_objectagg("key",s1.ele)over(order by s1.pos) as jele from rec s1
  )s1
);

引数がnullの場合

mysql> with sub as(select split_table(null) as tgt)select * from sub s1,json_table(s1.tgt,"$[*]" columns(ele longtext path "$.key"))s2;
+-----------------+------+
| tgt             | ele  |
+-----------------+------+
| [{"key": null}] | null |
+-----------------+------+
1 row in set (0.01 sec)

引数が空文字列の場合

mysql> with sub as(select split_table('') as tgt)select * from sub s1,json_table(s1.tgt,"$[*]" columns(ele longtext path "$.key"))s2;
+---------------+------+
| tgt           | ele  |
+---------------+------+
| [{"key": ""}] |      |
+---------------+------+
1 row in set (0.00 sec)

引数が単一要素文字列の場合

mysql> with sub as(select split_table('a') as tgt)select * from sub s1,json_table(s1.tgt,"$[*]" columns(ele longtext path "$.key"))s2;
+----------------+------+
| tgt            | ele  |
+----------------+------+
| [{"key": "a"}] | a    |
+----------------+------+
1 row in set (0.00 sec)

引数が複数要素文字列の場合

mysql> with sub as(select split_table('a,b') as tgt)select * from sub s1,json_table(s1.tgt,"$[*]" columns(ele longtext path "$.key"))s2;
+------------------------------+------+
| tgt                          | ele  |
+------------------------------+------+
| [{"key": "a"}, {"key": "b"}] | a    |
| [{"key": "a"}, {"key": "b"}] | b    |
+------------------------------+------+
2 rows in set (0.00 sec)

引数が複数要素文字列の場合その2

mysql> with sub as(select split_table('あっぷる,バナナ,ｵﾚﾝｼﾞ,大根') as tgt)select * from sub s1,json_table(s1.tgt,"$[*]" columns(ele longtext path "$.key"))s2;
+------------------------------------------------------------------------------------------------+-----------------+
| tgt                                                                                            | ele             |
+------------------------------------------------------------------------------------------------+-----------------+
| [{"key": "あっぷる"}, {"key": "バナナ"}, {"key": "ｵﾚﾝｼﾞ"}, {"key": "大根"}]                    | あっぷる        |
| [{"key": "あっぷる"}, {"key": "バナナ"}, {"key": "ｵﾚﾝｼﾞ"}, {"key": "大根"}]                    | バナナ          |
| [{"key": "あっぷる"}, {"key": "バナナ"}, {"key": "ｵﾚﾝｼﾞ"}, {"key": "大根"}]                    | ｵﾚﾝｼﾞ           |
| [{"key": "あっぷる"}, {"key": "バナナ"}, {"key": "ｵﾚﾝｼﾞ"}, {"key": "大根"}]                    | 大根            |
+------------------------------------------------------------------------------------------------+-----------------+
4 rows in set (0.00 sec)

以下はこれを実行した時のサーバ設定ファイル**/etc/my.cnf**。備忘。

[root💘ab9938775985 (日 12月 08 17:04:45) /home/root]$cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 以下が追加していたやつ
log_timestamps=SYSTEM
skip-character-set-client-handshake
character-set-server=utf8mb4
explicit_defaults_for_timestamp=ON

さっき試したらうまくいってた。単一の際のハンドリング見直したからかな。良かった良かった。^^

mysql> with sub as(   select split_table('apple,banana,cinamon,daikon') as tgt  )select   *  from sub s1     ,json_table(       s1.tgt       ,"$[*]" columns(ele longtext path "$.key")     )s2;
+------------------------------------------------------------------------------+---------+
| tgt                                                                          | ele     |
+------------------------------------------------------------------------------+---------+
| [{"key": "apple"}, {"key": "banana"}, {"key": "cinamon"}, {"key": "daikon"}] | apple   |
| [{"key": "apple"}, {"key": "banana"}, {"key": "cinamon"}, {"key": "daikon"}] | banana  |
| [{"key": "apple"}, {"key": "banana"}, {"key": "cinamon"}, {"key": "daikon"}] | cinamon |
| [{"key": "apple"}, {"key": "banana"}, {"key": "cinamon"}, {"key": "daikon"}] | daikon  |
+------------------------------------------------------------------------------+---------+
4 rows in set (0.00 sec)

絵文字もいける。

mysql> with sub as(select split_table('apple,\U+1F34E,banana,\U+1F34C,cinamon,ＳＨＩＮＡＭＯＮＮ,daikon') as tgt  )select   *  from sub s1     ,json_table(       s1.tgt       ,"$[*]" columns(ele longtext path "$.key")     )s2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+
| tgt                                                                                                                                                    | ele                         |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | apple                       |
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | 🍎                            |
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | banana                      |
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | 🍌                            |
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | cinamon                     |
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | ＳＨＩＮＡＭＯＮＮ          |
| [{"key": "apple"}, {"key": "🍎"}, {"key": "banana"}, {"key": "🍌"}, {"key": "cinamon"}, {"key": "ＳＨＩＮＡＭＯＮＮ"}, {"key": "daikon"}]                  | daikon                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+
7 rows in set (0.00 sec)