MySQLでToxi法でのタグ登録をまるっとできるストアド

はじめに

ハッシュタグの機能を追加したいと思ったときに、テーブル設計はToxi法でいこう→プログラム大変じゃない？となったので、まるっとStoredProcedureで登録できる処理を作りました。
といってもネット上で落ちているソースを組み合わせただけですが・・・
トランザクション管理などは各々で追加していただければと思います。

各テーブル(参考)

comments テーブル
id	id(主キー)
comment	コメント
registd	登録日

tag_maps テーブル
id	id(主キー)
comment_id	commentsテーブルの主キー
tag_id	tagsテーブルの主キー

tags テーブル
id	id(主キー)
name	ハッシュタグの名前

コメントに対してハッシュタグを複数個登録ができることを想定しています。

処理の流れ

登録する文字列をカンマ区切りで受け取り登録します。

コンマで文字列を区切り、一時テーブルに文字をインサートします。
一時テーブル内にある文字がtagsテーブルにない場合はインサートします。

一時テーブルとtagsをjoinして、各tags.idをtag_mapsテーブルにinsertします。

▼特定の文字列をカウントするFunction
カンマを数えるために使用します。

CREATE FUNCTION substrCount(s VARCHAR(255), ss VARCHAR(255)) RETURNS tinyint(3) unsigned
    READS SQL DATA
BEGIN
DECLARE count TINYINT(3) UNSIGNED;
DECLARE offset TINYINT(3) UNSIGNED;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s = NULL;
SET count = 0;
SET offset = 1;
REPEAT
IF NOT ISNULL(s) AND offset > 0 THEN
SET offset = LOCATE(ss, s, offset);
IF offset > 0 THEN
SET count = count + 1;
SET offset = offset + 1;
END IF;
END IF;
UNTIL ISNULL(s) OR offset = 0 END REPEAT;
RETURN count;
END;

▼特定の文字で区切られた文字列を配列のように取得する

CREATE FUNCTION strSplit(x varchar(21845), delim varchar(255), pos int) 
RETURNS varchar(255)
BEGIN
return replace(
    replace(substring_index(x, delim, pos),substring_index(x, delim, pos - 1),''),
    delim,
    '');
END;

▼一時テーブルにタグを登録する

CREATE PROCEDURE splitter(x varchar(255), delim varchar(12))
BEGIN
    SET @Valcount = substrCount(x,delim)+1;
    SET @v1=0;
    DROP TABLE if exists splitResults;
    create temporary
    table splitResults (split_value varchar(255));
    WHILE (@v1 < @Valcount) DO
        SET @val = strSplit(x,delim,@v1+1);
        INSERT INTO splitResults (split_value) VALUES (@val);
        SET @v1 = @v1 + 1;
    END WHILE;
END;

▼タグのデータ登録処理

CREATE PROCEDURE Regist_tag(vString VARCHAR(8000),pid int,updateflag int)
BEGIN

    IF updateflag = 1 THEN
      DELETE FROM tag_maps WHERE post_id = pid;
    END IF;

    CALL splitter(vString, ',');

    INSERT INTO tags(name)
        SELECT trim(x.name)
        FROM (SELECT split_value AS name FROM splitResults ) AS x
        LEFT JOIN tags ON x.name = tags.name
        WHERE tags.id is NULL;

    INSERT INTO tag_maps(post_id,tag_id)
        SELECT pid ,tags.id
        FROM tags
        INNER JOIN splitResults ON splitResults.split_value = tags.name
        GROUP BY tags.id;
END;