特定のシンボルで複数の行と列に分割するMySQLの例

一部の障害コード テーブルでは、履歴またはパフォーマンス上の理由から、次の設計パターンが使用されます。つまり、複数の属性値が同じ行または列に格納されます。次の表の tonnly_error_record に示すように:

この場合、列をセミコロン「;」で分割して複数の行を形成し、次にコンマ「,」で分割して複数の列を形成することを検討できます。次の表に示すように:

これは、MySQL の文字列分割関数を使用して実現できます。関数の説明は次のとおりです。

SUBSTRING_INDEX(文字列、区切り文字、カウント)   
-- str: 分割する文字列; delim: 区切り文字; count: 区切り文字の出現回数

最後に、具体的な実装は次のようになります。

#ステップ 1: セミコロン「;」に基づいて複数の行に分割 #ステップ 2: カンマ「,"」に基づいて複数の列に分割 select distinct S1.tbox_vin,
                (substring_index(substring_index(S1.error_code, ',', 1), ',', -1)) を spn として選択します。
                (substring_index(substring_index(S1.error_code, ',', 2), ',', -1) を選択) fmi、
                S1.変更時間
から （
         t1.tbox_vinを選択し、
                substring_index(substring_index(t1.dm1_string, ';', t2.help_topic_id + 1), ';', -1) を error_code として、
                t1.変更時間
         tonnly_error_record t1 から
                  mysql.help_topic t2に参加する
                       t2.help_topic_id < (length(t1.dm1_string) - length(replace(t1.dm1_string, ';', '')) + 1) の場合
         t1.dm1_string が null ではない
           かつ t1.dm1_string != '') S1
s1.error_code != '' の場合
  s1.error_codeはnullではない
S1.modify_time desc で順序付けします。

関連する知識ポイント

1. 文字列の分割: SUBSTRING_INDEX (str, delim, count)

1. パラメータの説明

2. 例

（１）区切り文字として２番目のカンマ「,」より前の文字をすべて取得します。

SUBSTRING_INDEX('7654,7698,7782,7788',',',2)

（2）最後から2番目のカンマ区切り文字「,」以降のすべての文字を取得します。

SUBSTRING_INDEX('7654,7698,7782,7788',',',-2) 

2. 置換関数: replace(str, from_str, to_str)

1. パラメータの説明

2. 例

（１）区切り文字「,」カンマを「」スペースに置き換えます。

REPLACE('7654,7698,7782,7788',',','')

3. 文字列の長さを取得する: LENGTH( str )

1. パラメータの説明

2. 例 (1) 文字列 '7654,7698,7782,7788' の長さを取得する

長さ('7654,7698,7782,7788') 

SQL解析を実装

選択 
    SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num 
から 
    mysql.ヘルプトピック 
どこ 
    help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

ここでは、MySQL ライブラリの help_topic テーブルの help_topic_id を変数として使用していますが、これは help_topic_id が自動インクリメントされるためです。もちろん、他のテーブルの自動インクリメント フィールドも補助として使用できます。

help_topic テーブル:

実装手順:

ステップ 1:まず、最終的に分割する必要がある文字列の数を取得し、help_topic_id を使用して n 番目の文字列のトラバースをシミュレートします。

関係するコード スニペット:

help_topic_id < LENGTH('7654,7698,7782,7788')-LENGTH(REPLACE('7654,7698,7782,7788',',',''))+1

ステップ 2: SUBSTRING_INDEX (str, delim, count) 関数を使用して、コンマ "," に基づいて文字列を分割し、結果を num フィールドに割り当てます。

関係するコード スニペット:

SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1) AS num

最初のステップ:

区切り文字としてカンマ「,」を使用し、help_topic_id の値に従って n+1 番目の区切り文字の前のすべての文字列をインターセプトします。 (ここで n+1 なのは、help_topic_id が 0 から始まり、ここでは最初の区切り文字から取得する必要があるためです。)

SUBSTRING_INDEX('7654,7698,7782,7788',',',ヘルプトピックID+1)

例えば：
help_topic_id = 0 の場合、取得される文字列 = 7654
help_topic_id = 1 の場合、取得される文字列 = 7654,7698
…（等々）

ステップ2:

区切り文字としてカンマ「,」を使用し、最後の区切り文字の後にあるすべての文字列を抽出します。

SUBSTRING_INDEX(SUBSTRING_INDEX('7654,7698,7782,7788',',',help_topic_id+1),',',-1)

例えば：
最初のステップによると、help_topic_id = 0の場合、取得された文字列 = 7654、この時点で2番目のステップで傍受された文字列 = 7654
最初のステップによると、help_topic_id = 1の場合、取得された文字列 = 7654,7698、この時点で2番目のステップで傍受された文字列 = 7698
…（等々）

最終的に以下の効果を達成することに成功しました〜

注: 区切り文字なしの文字列分割については、MySQL - 文字列分割 (区切り文字なしの文字列インターセプト) を参照してください。

MySQL を特定のシンボルで複数の行と列に分割する例についてはこれで終わりです。MySQL 固有のシンボル分割の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。