MySQLでカンマ区切り値の列を列に変換する方法

序文

場合によっては、第 1 正規形設計パターンに従わないビジネス テーブルに遭遇することがあります。つまり、複数の属性値が 1 つの列に格納されます。次の表に示すように

一般的には2つの共通の要件があります（テストデータについては記事の最後を参照してください）

1. 繰り返しのない値をすべて取得する。

SQL は次のとおりです。

選択distinct(substring_index(substring_index(a.col,',',b.help_topic_id+1),',',-1))
から
 (select group_concat(distinct `value`) as col from `row_to_col`) として
参加する
 mysql.help_topicをbとして
b.help_topic_id で < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

2. 各値とそれに対応する主キーを表示します。

SQL は次のとおりです。

a.pk、substring_index(substring_index(a.col、'、'、b.help_topic_id+1)、'、'、-1) を選択します。
から
 (`row_to_col` から `value` を col,pk として選択)
参加する
 mysql.help_topicをbとして
b.help_topic_id で < (char_length(a.col) - char_length(replace(a.col,',',''))+1)

実装のアイデア:

要件 1:

1. group_concat関数を使用して値列の値をコンマ区切りの文字列に連結し、substring_index関数を使用して文字列をインターセプトします。
2. substring_index関数の機能を通じて、文字列にカンマがいくつあるか、各カンマの位置を知る必要がある。
3. カンマの数 = char_length(文字列)-char_length(replace(文字列,',',''))
4. カンマの位置 = mysql.help_topic.id < カンマの数 [+1]
5. 最後に、distinct関数を使用して、インターセプトされた単一の値から重複を削除します。

知らせ：
1. mysql.help_topic テーブルの自動増分 ID は 0 から始まるため、インターセプト時に ID を 1 増やす必要があります。参照: substring_index(a.col,',',b.help_topic_id+1)
2. 値列の最後の文字がカンマでない場合: カンマの数 + 1 は、最後のカンマの後の値が切り捨て中に失われないようにするためのものです。つまり、char_length(a.col) - char_length(replace(a.col,',',''))+1;
値列の最後の文字がカンマの場合: カンマの数は +1 である必要はなく、次のようになります: char_length(a.col) - char_length(replace(a.col,',',''))
3. インターセプト時に ID が +1 である必要があるため、接続は <= ではなく < になります。参照: b.help_topic_id < (char_length(a.col) - char_length(replace(a.col,',',''))[+1])
4. mysql.help_topic (mysql バージョン: 5.7.21-1) テーブルの自動インクリメント ID、最大値は 636 です。 group_concat の後の文字列内のカンマの数がこの値より大きい場合は、自動インクリメント ID の値を個別に処理する必要があります。

要件2: 最終的なクエリが異なる点を除けば、考え方は基本的に要件1と同じです。

関連する機能:

length: 文字列が占めるバイト数を返します。これは計算フィールドの長さです。漢字または漢字記号は 3 文字としてカウントされ、数字、文字、英語記号は 1 文字としてカウントされます。
char_length: 文字列内の文字数を返します。中国語の文字、数字、文字、記号 (中国語か英語かに関係なく) に関係なく、文字とみなされます。
replace(str,old_string,new_string): 文字列 str 内のすべての old_string を new_string に置き換えます。
substring_index(切り捨てられたフィールド、キーワード、キーワードの出現回数): 文字列を切り捨てます。キーワードの出現回数が負の数の場合は、文字列の末尾まで逆方向にカウントされます。
group_concat([DISTINCT] 接続するフィールド (複数指定可能、カンマ区切り) [ソートフィールド ASC/DESC による順序付け] [区切り文字 'separator']):
group by によって生成された同じグループ内の値を連結し、文字列の結果を返します。デフォルトの区切り文字はカンマです。

テストデータ:

`row_to_col` が存在する場合はテーブルを削除します。
テーブル `row_to_col` を作成します (
 `pk` int(11) NOT NULL AUTO_INCREMENT、
 `value` varchar(255) デフォルト NULL,
 主キー (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=8 デフォルト CHARSET=utf8;

`row_to_col` に VALUES ('1', 'ET,AT') を挿入します。
`row_to_col` に VALUES ('2', 'AT,BT') を挿入します。
`row_to_col` VALUES ('3', 'AT,DT') に INSERT します。
`row_to_col` に VALUES ('4', 'DT,CT,AT') を挿入します。

参照:
https://blog.csdn.net/liuzhoulong/article/details/51729168
https://blog.csdn.net/ldl22847/article/details/47609727

これで、MySQL でカンマ区切りの列を行と列に変換する方法についての記事は終了です。MYSQL のカンマ区切りの列を行と列に変換する方法についての詳細は、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き閲覧してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。