【パフォーマンス重視】GROUP BYとSUBSTRING_INDEX()を使った効率的な文字列連結

2024-04-05

MySQLでGROUP BYを使用して文字列を連結する方法

MySQLでGROUP BYを使用して文字列を連結するには、GROUP_CONCAT関数を使用します。この関数は、グループ内のすべての文字列を1つの文字列に結合します。

構文

SELECT
  column_name,
  GROUP_CONCAT(column_name SEPARATOR ', ') AS concatenated_string
FROM table_name
GROUP BY column_name;

パラメータ

column_name: 結合する文字列を含む列名
SEPARATOR: 連結された文字列間の区切り文字
GROUP BY: グループ化する列名

例

以下の例では、usersテーブルのcity列の値をグループ化し、各グループ内のすべての都市名をカンマ区切りで連結しています。

SELECT
  country,
  GROUP_CONCAT(city SEPARATOR ', ') AS cities
FROM users
GROUP BY country;

出力例

| country | cities                   |
|---------|--------------------------|
| Japan   | Tokyo, Osaka, Kyoto       |
| USA     | New York, Los Angeles, Chicago |
| UK      | London, Manchester, Birmingham |

DISTINCT: 重複する値を無視して連結
ORDER BY: 結合する文字列の順序を指定

注意

GROUP_CONCAT()関数は、デフォルトで1024文字までしか連結できません。連結する文字列が長い場合は、max_lengthシステム変数を設定する必要があります。

この回答は、2024年4月4日時点の情報に基づいています。

-- テーブル users のスキーマ
CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  city VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO users (name, city) VALUES ('John Doe', 'Tokyo');
INSERT INTO users (name, city) VALUES ('Jane Doe', 'Osaka');
INSERT INTO users (name, city) VALUES ('Alice Smith', 'New York');
INSERT INTO users (name, city) VALUES ('Bob Smith', 'Los Angeles');
INSERT INTO users (name, city) VALUES ('Charlie Brown', 'London');
INSERT INTO users (name, city) VALUES ('Lucy Heart', 'Manchester');

-- GROUP BY を使用して都市名を連結
SELECT
  country,
  GROUP_CONCAT(city SEPARATOR ', ') AS cities
FROM users
GROUP BY country;

| country | cities                   |
|---------|--------------------------|
| Japan   | Tokyo, Osaka              |
| USA     | New York, Los Angeles     |
| UK      | London, Manchester        |

重複する値を無視して連結

SELECT
  country,
  GROUP_CONCAT(DISTINCT city SEPARATOR ', ') AS cities
FROM users
GROUP BY country;

結合する文字列の順序を指定

SELECT
  country,
  GROUP_CONCAT(city ORDER BY city ASC SEPARATOR ', ') AS cities
FROM users
GROUP BY country;

MySQLでGROUP BYを使用して文字列を連結する他の方法

FOR XML PATH('')

SELECT
  country,
  (
    SELECT
      city
    FROM users
    WHERE country = u.country
    FOR XML PATH('')
  ) AS cities
FROM users AS u
GROUP BY country;

SUBSTRING_INDEX()

SELECT
  country,
  SUBSTRING_INDEX(
    GROUP_CONCAT(city ORDER BY city ASC),
    ',',
    -1
  ) AS cities
FROM users
GROUP BY country;

自作関数

DELIMITER //

CREATE FUNCTION concat_cities(country VARCHAR(255)) RETURNS VARCHAR(255)
BEGIN
  DECLARE cities VARCHAR(255);
  DECLARE cursor CURSOR FOR
    SELECT city
    FROM users
    WHERE country = country;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET cities = NULL;

  OPEN cursor;
  SET cities = '';

  REPEAT
    FETCH cursor INTO @city;
    SET cities = CONCAT(cities, @city, ', ');
  UNTIL NOT FOUND END REPEAT;

  CLOSE cursor;

  RETURN cities;
END //

DELIMITER ;

SELECT
  country,
  concat_cities(country) AS cities
FROM users
GROUP BY country;