MySQLでLTSVのデータを扱う 〜ユーザー変数でkey-valueを実現

裏mysql mysql ltsv
  • 値の読み込み(指定値 sample)
SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      "test:27\tsample:8455\thogehoge:45",
      CONCAT('sample',':'),-1),
      "\t",1
 )

ただ、これだと完全なるLTSVではないので、ちょっと考えないとまずいですね。例えば、key-valueが、test:test::test1ssa:とかvalue値に入っているとバグります。

  • 値の読み込み(指定値 test) 失敗例
SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      "test:test:27::abctest:44\tsample:8455\thogehoge:45",
      CONCAT('test',':'),-1),
      "\t",1
 )

『test:27::abctest:44』 になるところが『44』になってしまいます。

これをカンマ区切りの応用で、完全にパースを再現すると

SET @key:='test';
SELECT SUBSTRING_INDEX(
  SUBSTRING_INDEX(SUBSTRING_INDEX(ltsv,"\t",v.n),"\t",-1) 
,CONCAT(@key,':'),-(LENGTH(ltsv)-LENGTH(REPLACE(ltsv,concat(@key,':'),@key)))) value 
 FROM (SELECT "test:test:27::abctest:44\tsample:8455\thogehoge:45" ltsv) c,
      (SELECT 1 n UNION ALL 
       SELECT 2   UNION ALL
       SELECT 3   UNION ALL
       SELECT 4   UNION ALL
       SELECT 5) v 
WHERE  LENGTH(ltsv)-LENGTH(REPLACE(ltsv,"\t",''))+1 >=v.n
  AND SUBSTRING_INDEX(SUBSTRING_INDEX(ltsv,"\t",v.n),"\t",-1) LIKE  concat(@key,':%')

になるわけですが、冗長なので、stored function化したいところです。
尚、UNION ALLの連結個数は、例によってkey-valueの個数のMAX値になるので要注意です。

.....とはいえ、あまりにも大げさな仕組みになってしまうのと、key-valueの個数に制限があるのはナンセンスなので、一番最初の失敗例を改善するやり方を考えて見ましょう。

SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      CONCAT("\t","test:test:27::abctest:44\tsample:8455\thogehoge:45"),
      CONCAT("\t",'test',':'),-1),
      "\t",1
 )

検索対象文字列、検索文字列共に、文字先頭にタブ文字を付け加えてみます。
すると、

『test:27::abctest:44』

と正常にパースされました。

  • 次に指定キーの置換を行ってみます。
SET @str:="sample:8455\ttest:test:27::abctest:44\thogehoge:45";
SET @key:='test';
SET @replace_str:='replace_str';

SELECT
  REPLACE(
    CONCAT("\t",@str,"\t"),
    CONCAT("\t",@key,':',SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        CONCAT("\t",@str),
        CONCAT("\t",@key,':'),-1),
       "\t",1
      ),"\t"),
    CONCAT("\t",@key,':',@replace_str,"\t"))

keyからvalueを取得するSQLを応用し、今度は、行頭と行末を付与してその値を置換しています。
substringを使ってもう少し短縮できそうですね。

SET @str:="sample:8455\ttest:test:27::abctest:44\thogehoge:45";
SET @key:='test';
SET @replace_str:='replace_str';

SELECT
  CONCAT(
    SUBSTRING(
      SUBSTRING_INDEX(
        CONCAT("\t",@str),
        CONCAT("\t",@key,':'),1
      ),2
    ),"\t",@key,':',@replace_str,
    SUBSTRING(
      @str,
      LOCATE(
        "\t",
        @str,
        LOCATE(
          CONCAT("\t",@key,':'),
          CONCAT("\t",@str)
        )
      )
    )
  )

まるで短縮できてない...。これはとりあえず実装は前者にしておきましょうか

  • 次に指定キーの追加

これは簡単、単純にconcatで行末にappendするだけです。

SET @str:="sample:8455\thogehoge:45";
SET @key:='test';
SET @val:='append_str';

SELECT
    CONCAT(@str,"\t",@key,':',@val);
  • 次に文字列内に指定キーが含まれているかどうか判定

これも、行頭にタブ文字を付与した検索文字列で検索して存在するかどうかを判定

-- キーが存在しない場合
SET @str:="sample:8455\thogehoge:45";
SET @key:='test';

SELECT
    LOCATE(CONCAT("\t",@key,':'),CONCAT("\t",@str));

-- キーが存在する場合
SET @str:="sample:8455\ttest:test:27::abctest:44\thogehoge:45";
SET @key:='test';

SELECT
    LOCATE(CONCAT("\t",@key,':'),CONCAT("\t",@str));

上記ができると、ようやく、ユーザー変数を用いてkey-valueの保存が可能になります。