MySQL _ カンマ区切りデータ

MySQL : カンマ区切りデータを使いやすくする

• カンマ区切りデータの分解、正規化

  元テーブル`org`(id int primary key not null, words text comment="カンマ区切りデータ" ) にたいして、 正規化テーブルとして、(id int not null, word varchar(100) comment="１要素ずつ" )のテーブルを作成する方法。 varchar の必要バイト数は、文字コードによっても異なるので、実際の格納データを鑑みて設定必要です。

  • 下準備：連番テーブルを作成しておく

    wordsに含まれるカンマの個数に応じて行数を増やす必要があるので、何番目のデータ取り出しを行うか指定するための連番テーブルを作成。
    0-9 の10個のみのテーブルを１個作ると、あとは、自己結合で百、千、万と増やせます。 今回は、カンマ区切りのカンマ個数は最大100個未満の場合で

    CREATE TABLE `digits` ( `num` int );
    insert into `digits` values (0),(1),(2), (3), (4), (5), (6), (7), (8), (9);
    --  0～99 の連番テーブル、以下をサブクエリとして使う。
    select n1.num + n2.num *10 from digits n1 , digits n2 ;
    

    
    

  • 下準備２：カンマ個数の調査方法

    select max( length(words)-length( replace(words, ',' , '')) ) as maxcnt from `org` ;
    --  ついでに　カンマ数分布調査
    select length(words)-length( replace(words, ',' , '')) as wcnt , count(*) as cnt
     from `org` group by wcnt order by wcnt ;
    

    
    

  • 正規化テーブル用データ

    元テーブルデータ側で、wordsカラムの各行のカンマ個数に１を足して要素数を割り出してサブクエリとし、 このサブクエリと先ほどの連番サブクエリを連結して、切り出しデータテーブルにする

    select t.id,  SUBSTRING_INDEX( SUBSTRING_INDEX(words, ',' ,n.num), ',' , -1) as word , n.num
     from (select id,words, length(words)-length( replace(words, ',' , '')) +1 as wcnt from `org` ) t
      left join ( select n1.num + n2.num *10 as num from digits n1 , digits n2 ) n
      on t.wcnt >= n.num and n.num > 0  ;
    

    
    

  • 正規化テーブル生成

    create 文でカラム情報を付けなければ、selectで生成されるデータに準じて適宜カラムサイズが設定されているので、あとから、indexなどを付けたり、今後のデータ増加でwordの文字数が増える可能性がある場合は、その最大長に準じてカラム型変更を行う。

    create table `t_normal` 
      select t.id,  SUBSTRING_INDEX( SUBSTRING_INDEX(words, ',' ,n.num), ',' , -1) as word , n.num
       from (select id,words, length(words)-length( replace(words, ',' , ''))+1 as wcnt from `org` ) t
        left join ( select n1.num + n2.num *10 as num from digits n1 , digits n2 ) n
        on t.wcnt >= n.num and n.num > 0  ;
    
    --  word の文字数を増やす場合。 varcharはバイト数指定なので、utf8での10文字は最大40バイト必要。
    alter table `t_normal` MODIFY  COLUMN  `word` varchar(40) CHARACTER SET = utf8 COLLATE = utf8_general_ci ;
    
    -- idごとのword に重複を許さないならば primary key を (id,word) で付ける。
    -- 重複許可なら、(id,num) をprimary key とし、 (id,word)は非unique とする。
    alter table `t_normal` add  primary key (id,num) ;
    create  index  `non_uni` on `t_normal` (id, word(10));
      --  index における長さ指定は、char や varchar では文字数での指定になる
    

    
    

  • 正規化テーブルの利用

    --  カンマ区切り文字列を再生成、元の並び順をnumカラムから指定する場合
    select  id , group_concat(word order by num) as words from t_normal  group by id;
     --  重複省いて　カンマ区切りを再生成
    select  id , group_concat(distinct word order by word) as words from t_normal  group by id;
    
    --  id ごとのword数
    select id, count(*) as word_count from t_normal ;
     --  id ごとの重複なしのword数
    select id, count(distinct word) as word_count from t_normal ;
    
    --  word 集計
    select word, count(word) as cnt from t_normal  group by word order by word ;
     --  同一 id の重複省いて word 集計
    select word, count(distinct id ) as cnt from t_normal  group by word order by word ;
    --  word に "xx" が出現する id のリスト　カンマ区切り表示
    select  group_concat( distinct id ) as id_list from t_normal where word = "xx" ;
     --  word に "xx" と "yy" の両方が出現する id の一覧
    select  id from t_normal 
      where word = "xx" or word = "yy"
      group by id
      having count(distinct word) = 2  ;
     --  word に "xx" は出現するが "zz" は含まれない id の一覧
    select distinct id from t_normal as t0
      where exists ( select  * from t_normal as t1  where t1.word = "xx" and t1.id=t0.id )
        and not exists( select  * from t_normal as t2  where t2.word = "zz" and t2.id=t0.id ) ;
    /* 上記の検索は、元のカンマ区切りデータではlike検索になり高速化は全く望めないが、
    　正規化することによりindexを使った検索が行われるので高速になる。 
     */
    

    
    

• set 型カラム

  すでに作成されているカンマ区切りデータに出現する各要素以外の要素が今後新たに出現しないことが確定の場合は、元テーブルのデータ型を set型にしてしまうことで、検索スピードを速めることができるだろう。 また、要素リストは、最大 64 個である必要があるので、ここも注意。
  先に、出現要素のdistinctリストが必要だけど、一回リスト取ればあとは、SET('a','b','c','d') でカラム型指定できる。
  set型カラム値として、setに指定した要素のうちのいくつかをカンマ区切りで代入する。同一要素を同一行で重複して持つことはできない。 set型の実体は、ビットを立てたか立てないかなので、同一ビットを複数とはならないためである。 よって、insert やupdate で同一要素を複数指定しても、一つのみ保持となる。

  • 下準備：出現データの重複なしリスト

    set型は、カンマ区切りに出現するデータのリストを指定しないとならないので、一旦前節で解説した正規化テーブルを作るか、１個ずつ切り出したものをunion連結してリストをとるなどの必要があります。

    -- 正規化テーブルから、wordの重複無し全リストをとる
    select distinct word  from t_normal ;
      --  set() の中身に入れやすいように "" くくりの文字列にしてカンマ区切りで出力
    select concat('"', group_concat( distinct word order by word separator '","' ) , '"')  as distinct_list  from t_normal ;
    
    --  正規化テーブル作成用のselect文をテーブルサブクエリとして、重複無し全リストをとる場合
    select concat('"', group_concat( distinct word order by word separator '","' ) , '"')  as distinct_list 
      from ( select t.id,  SUBSTRING_INDEX( SUBSTRING_INDEX(words, ',' ,n.num), ',' , -1) as word , n.num
           from ( select id,words, length(words)-length( replace(words, ',' , ''))+1 as wcnt from `org` ) t
           left join ( select n1.num + n2.num *10 as num from digits n1 , digits n2 ) n
           on t.wcnt >= n.num and n.num > 0 
      ) as sub ;
    
    
    --  元テーブルからunionで直接リストを取得する場合、　先に、最大カンマ個数調査を行っておく
    select max( length(words)-length(replace(words, ',' , '')) ) as maxlen from `org` ;
      -- 最大カンマ個数3個の場合、カンマ個数回のunionを行う  , 出力は ""くくり文字列のカンマ区切りリスト
    select concat('"', group_concat( word order by word separator '","' ) , '"') as distinct_list
      from ( select  SUBSTRING_INDEX(words, ',' ,1) as word from `org`
       union select SUBSTRING_INDEX( SUBSTRING_INDEX(words, ',' ,2), ',' , -1)  from `org`
       union select SUBSTRING_INDEX( SUBSTRING_INDEX(words, ',' ,3), ',' , -1)  from `org`
       union select SUBSTRING_INDEX( words, ',' , -1)  from `org`
      )  as temp ;
    

    
    

  • データ型変更

    前記の""くくりでカンマ区切りにしたデータを set() の中に文字列置換して実行

    alter table `org`  MODIFY COLUMN  `words`  set("aa","bb" /* 文字列用のくくり付きでカンマ区切り */ )  ;
    

    
    元テーブルはそのまま残して、別テーブルにset型カラム定義する場合

    create  table `t_set` ( id int primary key not null,
     `words`  set("aa","bb" /* 文字列用のくくり付きでカンマ区切り */ ) 
    ) select id, words from `org`  ;
    

    
    

  • set 型でのデータ検索

    別テーブル `t_set` を作った場合で表記

    --  要素値"xx"を検索
    select * from t_set where  FIND_IN_SET("xx", words)>0 ;
     --  要素値 "xx" と "yy" の両方が出現する id の一覧
    select id from t_set where  FIND_IN_SET("xx", words)>0 and FIND_IN_SET("yy", words)>0  ;
     --  word に "xx" は出現するが "zz" は含まれない id の一覧
    select id from t_set where  FIND_IN_SET("xx", words)>0 and FIND_IN_SET("zz", words)=0;
    
    --  set型で設定したメンバーのビット位置から検索指定, 先頭の場合0シフト
    select * from t_set where  words & (1<<0) ;
     --  set型で設定したメンバーのビット位置から検索指定, 複数対象(0,3,5番目の要素いずれか含む)
    select * from t_set where  words & ( 1 | (1<<3) | (1<<5) ) ;
     --  set型で設定したメンバーのビット位置から検索指定, 複数対象(1,3,5番目の要素いずれも含む)
    select * from t_set where (words & (1<<1)) and (words & (1<<3)) and (words & (1<<5)) ;
    /* ビット位置指定のシフト演算は、1<<0つまり1が先頭０番ビット、最終ビットでは 1<<(全ビット数-1) での指定となります。
     */
    
    --  各行に含まれる 要素数をカウント、※bit_count(b):ビットに１が入ってる個数が返る
    select id, bit_count(words) as cnum  from t_set ;
    
    --  実際に使われてる要素の出現数集計；　ビット位置指定のn.nn は０開始, 全要素個数-1まで
    select  n.nn , sum( (words & (1<<n.nn))>>n.nn ) as wcnt ,
     SUBSTRING_INDEX( SUBSTRING_INDEX(:sets, ',' ,n.nn), ',' , -1) as value
      from t_set as t
        , ( select n1.num + n2.num*10 as nn from digits n1,digits n2  having :sn > nn ) as n
      group by n.nn  order by n.nn ;
     /*　ビット位置番号での集計となるので、実名表示はアプリ側で:setsにset定義の文字列リストを置き換えしないと無理. 
     ついでにset要素の全個数も :sn にいれる形式にする。
    */
    

    めも：

    setメンバーの各文字列とビット位置との対応は、カラム型定義の時にアプリケーション側でも保持するか、 または、次節紹介のように別テーブルに保持しておかなければ、テーブル定義から構文解析する必要があります。

     SHOW COLUMNS FROM `t_set` LIKE `words` ;

    で取り出されるデータの`Type`カラムから、set定義の構文を取り出すことになります。

    
    

  • set 型での要素名リスト

    ビット番号から直接要素名取り出しができないので、１要素ずつのリストを別テーブルに作ることで、１要素における文字列表記を要素名として使う。
    以下は、別テーブル`t_set` にset型カラムを作った場合で表記。
    あと、check構文で bit_count(words)=1 の条件をつけたいところだが、mysqlではcheck構文は無視されるだけなので、自前で、およびアプリケーション側で気を付けないと行けない。

    -- まずは、テーブル定義を写す. set型を別テーブルに作った場合は、そちらから作る
    create table  `orgset`  like t_set  ;
    
    --  複数回insert してしまわないよう primary key も付ける
    alter table `orgset`  primary key (words)  ;
    
    --  データinsert には ビット番号で指定するselect文を作る。:sn はset定義の全要素個数を入れる。
    /*　全要素個数を指定するのは、setに設定されてる要素数より多いビットを指定するとデータがtrancateされて null の行ができるため。
    */
    insert into `orgset` (id,words) 
      select n.nn+1 as id,  (1<< n.nn) as words 
      from  ( select n1.num + n2.num*10 as nn from digits n1,digits n2  having :sn > nn ) as n  ;
    
    --  前節、「要素の出現数集計」にorgsetテーブルから要素名を取得,
    /*   orgsetの行数から連番テーブルの絞り込み条件をつけることで、外から与えるデータは不要となる。
    */
    select  n.nn , sum( (t.words & (1 << n.nn))>>n.nn ) as wcnt ,
     (select o.words from orgset as o where o.words & (1 << n.nn) ) as value
      from t_set as t
        , ( select n1.num + n2.num*10 as nn from digits n1,digits n2 
            having (select count(words) from orgset) > nn 
          ) as n
      group by n.nn  order by n.nn  ;
    

    
    

  • set型　データの編集について

    要素値での追加や削除したいときは、mysql8.0マニュアル set型のuser note に以下の方法が載っている。
    また、ビット位置での指定時は、 &（ビットand）、 |（ビットor）、 ~（ビット反転）を使う。

    --  add bits:　テーブル名tbl set型カラム名words、追加要素値 'toadd', 編集対象行カラムid値が :id
    UPDATE tbl SET words=CONCAT_WS(',', words, 'toadd') where id=:id  ;
     --  add bits:　　テーブル名tbl カラム名words、追加要素ビット位置７番（先頭０番）
    UPDATE tbl SET words= words | (1<<7) where id=:id  ;
    
    --  delete bits:  カンマが連続してもtrim されるので問題なし。削除要素値 'toremove'
    UPDATE tbl SET words=REPLACE(words, 'toremove', '')  where id=:id  ;
     --  delete bits:　テーブル名tbl カラム名words、削除要素ビット位置３番（先頭０番から数えて３番目）
    UPDATE tbl SET words= words & ~(1<<3)  where id=:id  ;