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

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

序文

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

ペック価値
1 ET、AT
2 AT、BT
3 AT、DT
4 DT、CT、AT

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

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

価値
BT
CT
DT
ET

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. 各値とそれに対応する主キーを表示します。

ペック価値
1 ET
1
2
2 BT
3
3 DT
4 DT
4 CT
4

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL の行から列の詳細
  • MySQLで行を列に変換する方法
  • MySQL で行を列に変換したり、列を行に変換したりする詳細な例
  • MySQL ストアド プロシージャで動的な行から列への変換を使用する
  • MySQL の行から列へ、列から行へ

<<:  Element における複数データ読み込み最適化の実装

>>:  Javascript配列の重複排除のいくつかの方法の詳細な説明

推薦する

WeChatアプレットはウォーターフォールフローのページングスクロール読み込みを実装します

この記事では、WeChatアプレットのウォーターフォールフローページングスクロールロードを実装するた...

ドメイン名を nginx サービスにバインドする方法

nginx.conf で複数のサーバーを設定します。 http リクエストを処理する際、nginx ...

MySql ページングで limit+order by を使用する場合のデータ重複の解決策

目次まとめ問題の説明問題を分析する問題を解決するまとめ複雑な知識をシンプルに説明できることは重要です...

Reactホームページの読み込みが遅い問題のパフォーマンス最適化事例の詳細な説明

しばらくReactを勉強した後、実践してみたいと思います。そこで、個人のブログのウェブサイトを再構築...

Dockerにlogstashをインストールする詳細な手順

docker-compose.yml を編集し、次のコンテンツを追加します。 バージョン: '...

Linux および Unix サーバーのセキュリティを強化する方法

ネットワーク セキュリティは非常に重要なトピックであり、サーバーはネットワーク セキュリティにおける...

Docker Machineの詳細な説明

Docker と Docker Machine の違いDocker はクライアント サーバー アーキ...

2列のデータをSQLの新しい列として操作する

以下のように表示されます。 bb_sbからa1、a2、a1+a2 a、a1*a2 b、a1*1.0/...

Apache Tika を使用してファイルが破損しているかどうかを検出する方法

Apache Tika は、さまざまな形式のファイルからファイル タイプを検出し、コンテンツを抽出す...

Vueカスケードドロップダウンボックスの設計と実装

目次1. データベース設計2. フロントエンドページ3. 完全なデモフロントエンド開発では、カスケー...

Vue プロジェクトの最初の画面のパフォーマンス最適化コンポーネントの実践ガイド

目次Vue ファースト スクリーン パフォーマンス最適化コンポーネント説明するインターセクションオブ...

CocosCreatorでゲームコントローラーを使用する方法

目次1. シーンレイアウト2. ハンドルリスナーを追加する1. イベントの変更を監視する2. 座標設...

MySQLのSeconds_Behind_Masterの詳細な説明

目次マスターの後ろの秒数オリジナルの実装最終マスタータイムスタンプマスターとのクロック差他の実行時間...

Nginx における accept lock の仕組みと実装の詳細な説明

序文nginx はマルチプロセス モデルを使用します。リクエストが届くと、システムはプロセスをロック...

MySQLチュートリアルDMLデータ操作言語の例の詳細な説明

目次1. データ操作言語 (DML) 2. データを追加する(挿入) 3. 既存のテーブルをコピーし...