外部ツールを使用せずに MySQL でセミコロン区切りの値でテーブルを結合できますか?

Pure MySQL でこれを解決できますか? (結合; 列内の区切られた値)

問題:

データが 2 つのテーブルに格納されており、最初のテーブルの 1 つの列に複数のデータがリストされています。セミコロンで区切られた値。これらの分離された値に基づいて内部結合を実行する必要がありますが、リンク テーブルがないため、外部プログラミング言語を使用できません。

ディスカッション:

課題は、セミコロンで区切られたリストを個別の行に変換して、2 番目のテーブルと結合できるようにすることにあります。これは、データの「導出」または「正規化」と呼ばれる手法を使用して実現できます。

解決策:

1. Integerseries テーブルを作成します:

データを正規化するには、まず数値範囲を含むテーブルが必要です。この場合、1 からセミコロン区切りのリストで予想される要素の最大数までの ID を持つ integerseries テーブルを作成できます。

2。 JOIN とサブクエリを使用します:

integerseries テーブルを取得したら、次のクエリを実行します:

<code class="sql">SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') /* normalize */

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data</code>

説明:

• このクエリは、user_resource テーブルと integerseries テーブルを結合して、各ユーザーおよび対応するリソース列の各要素の行のセットを生成します。
• COUNT_IN_SET 関数は、リソース列の要素の数をカウントし、 VALUE_IN_SET 関数は、isequence.id に基づいて特定の要素を抽出します。
• リソース テーブルとの最後の結合では、抽出された要素と ID を照合して、対応するデータを取得します。

追加関数 (オプション):

クエリは 2 つのカスタム関数 COUNT_IN_SET と VALUE_IN_SET を使用します。これらは次のように定義できます:

<code class="sql">-- Function to count the number of delimited items in a string
DELIMITER $$
DROP FUNCTION IF EXISTS `COUNT_IN_SET`$$
CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1)
                               ) RETURNS INTEGER
BEGIN
      RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1;
END$$
DELIMITER ;

-- Function to get the value at a specific index in a delimited string
DELIMITER $$
DROP FUNCTION IF EXISTS `VALUE_IN_SET`$$
CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1), 
                               which INTEGER
                               ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
      RETURN  SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which),
                     delim,
                     -1);
END$$
DELIMITER ;</code>

これらの関数は一般的な方法を提供します。 SQL クエリ内の区切り文字列を操作します。

テーブルとデータの例:

<code class="sql">-- Integerseries table
CREATE TABLE `integerseries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
-- Resource table
CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Data for resource table
INSERT INTO `resource` (`id`, `data`) VALUES
(1, 'abcde'),
(2, 'qwerty'),
(3, 'azerty');

-- User_resource table
CREATE TABLE `user_resource` (
  `user` varchar(50) NOT NULL,
  `resources` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`user`)
);

-- Data for user_resource table
INSERT INTO `user_resource` (`user`, `resources`) VALUES
('sampleuser', '1;2;3'),
('stacky', '2'),
('testuser', '1;3');</code>

出力:

サンプル データに対するクエリは次の出力を生成します:

+----------+-------+
| user     | data   |
+----------+-------+
| sampleuser | abcde  |
| sampleuser | qwerty |
| sampleuser | azerty |
| stacky    | qwerty |
| testuser  | abcde  |
| testuser  | azerty |
+----------+-------+

以上が外部ツールを使用せずに MySQL でセミコロン区切りの値でテーブルを結合できますか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。