まえがき
マニュアル書いてないけど(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)