KAL
Menu

GROUP_CONCAT

Menggabungkan nilai string per kelompok beserta batas panjang dan pengurutannya.

Daftar isi

Topik ini membahas fungsi agregat GROUP_CONCAT yang menggabungkan banyak nilai string dari satu kelompok menjadi satu nilai teks. Pembahasan mencakup penggabungan dasar per kelompok, pengaturan pemisah dan urutan, penanganan duplikat dan NULL, serta batas panjang group_concat_max_len yang memotong hasil secara diam-diam. Basis data latihan berupa rekap ujian sekolah dibangun ulang dari berkas skrip, sehingga seluruh keluaran pada contoh dapat diulang dari data yang sama.

Menyiapkan basis data latihan#

Basis data latihan sekolah dibangun ulang dari berkas skrip agar seluruh contoh pada topik ini bermula dari data yang sama. Berkas data-sekolah.sql memuat pembuatan ulang basis data, tabel ujian, dan dua belas baris data. Setiap baris mewakili satu siswa pada satu kelas, yaitu kelas 10A, 10B, dan 11A dengan empat siswa per kelas, lengkap dengan kolom nilai ujiannya. Susunan ini membuat keluaran penggabungan nama terlihat jelas dan mudah diperiksa per kelas.

sql data-sekolah.sql 
-- tidak-dijalankan
-- Berkas latihan topik GROUP_CONCAT: membangun ulang basis data sekolah dari nol.
-- Jalankan dengan: mysql -u root < data-sekolah.sql

DROP DATABASE IF EXISTS sekolah;
CREATE DATABASE sekolah;
USE sekolah;

CREATE TABLE ujian (
  id INT PRIMARY KEY,
  kelas VARCHAR(4) NOT NULL,
  siswa VARCHAR(30) NOT NULL,
  nilai TINYINT UNSIGNED NOT NULL
);

INSERT INTO ujian (id, kelas, siswa, nilai) VALUES
  (1,  '10A', 'Ali Rahman',    88),
  (2,  '10A', 'Budi Santoso',  74),
  (3,  '10A', 'Citra Lestari', 91),
  (4,  '10A', 'Dewi Anggraini', 79),
  (5,  '10B', 'Eka Putri',     65),
  (6,  '10B', 'Fajar Nugroho', 82),
  (7,  '10B', 'Gita Wulandari', 77),
  (8,  '10B', 'Hadi Pratama',  70),
  (9,  '11A', 'Intan Maharani', 85),
  (10, '11A', 'Joko Prasetyo', 68),
  (11, '11A', 'Kartika Sari',  93),
  (12, '11A', 'Lutfi Hakim',   80);
bash 
# membangun ulang basis data latihan dari berkas skrip
mysql -u root < data-sekolah.sql

Perintah di atas tidak mencetak apa pun karena skripnya hanya berisi pernyataan pembuatan struktur dan pengisian data. Tabel ujian kini menjadi bahan penggabungan pada seluruh contoh berikutnya. Struktur tabelnya sengaja dibuat sama dengan tabel latihan topik agregat, sehingga perbedaan keluaran kedua topik murni berasal dari fungsi yang dipakai. Nama siswa pada data juga unik, sehingga penggabungan biasa dan penggabungan dengan DISTINCT menghasilkan daftar yang sama, sedangkan pengaruh DISTINCT baru terlihat pada kolom kelas yang berisi duplikat. Apabila latihan diulang, jalankan perintah pemuatan yang sama dan keadaan datanya kembali persis seperti awal.

Menggabungkan nilai per kelompok#

Fungsi GROUP_CONCAT mengambil satu nilai dari setiap baris dalam kelompok dan menyambungnya menjadi satu string. Pemisah bawaannya adalah koma tanpa spasi, dan urutan nilai pada hasil penggabungan dinyatakan dengan ORDER BY di dalam kurung fungsi itu sendiri. Query berikut menyatukan nama siswa per kelas, diurutkan menurut nama, dengan pemisah koma dan spasi.

bash 
# menyatukan nama siswa per kelas
mysql -t -u root sekolah -e "SELECT kelas, GROUP_CONCAT(siswa ORDER BY siswa SEPARATOR ', ') AS siswa FROM ujian GROUP BY kelas ORDER BY kelas;"
text Keluaran 
+-------+----------------------------------------------------------+
| kelas | siswa                                                    |
+-------+----------------------------------------------------------+
| 10A   | Ali Rahman, Budi Santoso, Citra Lestari, Dewi Anggraini  |
| 10B   | Eka Putri, Fajar Nugroho, Gita Wulandari, Hadi Pratama   |
| 11A   | Intan Maharani, Joko Prasetyo, Kartika Sari, Lutfi Hakim |
+-------+----------------------------------------------------------+

Setiap baris keluaran memuat satu kelas beserta seluruh nama siswanya dalam satu kolom teks. Ada dua ORDER BY yang bekerja pada query ini, yaitu yang berada di dalam GROUP_CONCAT untuk mengurutkan isi gabungan dan yang berada di luar untuk mengurutkan baris kelompok. Keduanya perlu ditulis eksplisit karena sejak MySQL 8.0 urutan baris GROUP BY tidak dijamin, begitu pula urutan nilai di dalam fungsi agregat apabila ORDER BY internalnya tidak ditulis. Fungsi ini menampung seluruh nilai kelompok terlebih dahulu, menyaring NULL, mengurutkannya sesuai ORDER BY internal, baru menyambungnya dengan pemisah yang ditetapkan. Karena tahapan itu, hasilnya selalu berupa satu nilai teks untuk setiap kelompok, sepanjang tidak ada nilai yang terpotong oleh batas panjang yang dibahas pada bagian akhir.

DISTINCT, pemisah khusus, dan NULL#

Kata kunci DISTINCT menghapus duplikat sebelum penggabungan, sedangkan kata kunci SEPARATOR mengganti pemisah bawaannya. Query berikut menghitung banyaknya baris tabel dan menyatukan daftar kelas yang unik dengan pemisah garis vertikal yang diberi spasi di kedua sisinya.

bash 
# menyatukan daftar kelas unik dengan pemisah khusus
mysql -t -u root sekolah -e "SELECT COUNT(*) AS baris, GROUP_CONCAT(DISTINCT kelas ORDER BY kelas SEPARATOR ' | ') AS daftar_kelas FROM ujian;"
text Keluaran 
+-------+-----------------+
| baris | daftar_kelas    |
+-------+-----------------+
|    12 | 10A | 10B | 11A |
+-------+-----------------+

Banyaknya baris adalah 12, tetapi daftar kelasnya hanya memuat tiga nilai karena DISTINCT menyaring duplikatnya terlebih dahulu. Pemisah dengan karakter khusus, misalnya garis vertikal, perlu dipilih dengan memperhatikan isi data, karena nilai yang memuat karakter yang sama akan menyulitkan pembacaan laporan. Pada tabel latihan ini nama kelasnya aman, sedangkan pada data yang tidak terkendali, pilih pemisah yang tidak mungkin muncul di dalam nilai. Fungsi ini juga mengabaikan NULL sebagaimana fungsi agregat lain, sehingga nilai NULL tidak meninggalkan bekas pemisah apa pun pada hasilnya. Query berikut menggabungkan tiga nilai sementara, satu di antaranya NULL, dengan pemisah tanda hubung.

bash 
# mengamati penanganan NULL pada penggabungan
mysql -t -u root sekolah -e "SELECT GROUP_CONCAT(n ORDER BY n SEPARATOR '-') AS gabungan FROM (SELECT 1 AS n UNION ALL SELECT NULL UNION ALL SELECT 3) AS coba;"
text Keluaran 
+----------+
| gabungan |
+----------+
| 1-3      |
+----------+

Hasilnya berupa 1-3 tanpa pemisah ganda, karena NULL dikeluarkan dari daftar nilai sebelum penyambungan. Apabila seluruh nilai pada kelompok berisi NULL, hasil GROUP_CONCAT pun berupa NULL, bukan string kosong.

Batas panjang group_concat_max_len#

Panjang hasil penggabungan dibatasi oleh variabel sistem group_concat_max_len, dan batas bawaannya dapat diperiksa dari server.

bash 
# memeriksa batas panjang penggabungan
mysql -t -u root sekolah -e "SELECT @@group_concat_max_len;"
text Keluaran 
+------------------------+
| @@group_concat_max_len |
+------------------------+
|                   1024 |
+------------------------+

Batas 1024 karakter berlaku per nilai hasil penggabungan, bukan per baris data sumber. Hasil yang melewatinya dipotong tanpa error, sehingga query tetap dianggap berhasil dan hasilnya tampak utuh meskipun ada nilai yang hilang di ujungnya. Untuk mengamatinya, query berikut membentuk delapan baris teks melalui ekspresi tabel umum (common table expression, CTE) rekursif, lalu mengukur panjang gabungannya dengan CHAR_LENGTH. Setiap baris berisi 160 karakter, sehingga delapan baris menyumbang 1280 karakter, dan tujuh pemisah koma melengkapi totalnya menjadi 1287 karakter.

bash 
# menguji pemotongan pada batas bawaan
mysql -t -u root sekolah <<'SQL'
WITH RECURSIVE deret AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM deret WHERE n < 8
)
SELECT CHAR_LENGTH(GROUP_CONCAT(REPEAT('ab', 80) ORDER BY n SEPARATOR ',')) AS panjang
FROM deret;
SHOW WARNINGS;
SQL
text Keluaran 
+---------+
| panjang |
+---------+
|    1024 |
+---------+
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1260 | Row 7 was cut by GROUP_CONCAT() |
+---------+------+---------------------------------+

Panjang hasil tercatat tepat 1024, dan SHOW WARNINGS mengungkap peringatan 1260 yang menyatakan baris ketujuh terpotong. Peringatan itu tidak tercetak pada hasil query itu sendiri, sehingga pemotongan baru diketahui apabila diperiksa sengaja melalui SHOW WARNINGS. Batasnya dapat dinaikkan untuk sesi saat ini melalui SET SESSION, lalu penggabungan yang sama diulang.

bash 
# menaikkan batas lalu mengulang penggabungannya
mysql -t -u root sekolah <<'SQL'
SET SESSION group_concat_max_len = 4096;
WITH RECURSIVE deret AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM deret WHERE n < 8
)
SELECT CHAR_LENGTH(GROUP_CONCAT(REPEAT('ab', 80) ORDER BY n SEPARATOR ',')) AS panjang
FROM deret;
SQL
text Keluaran 
+---------+
| panjang |
+---------+
|    1287 |
+---------+

Dengan batas 4096, gabungan penuh 1287 karakter lolos tanpa pemotongan. Penyetelan SET SESSION hanya berlaku pada koneksi yang menjalankannya, sehingga sesi lain tetap memakai batasnya masing-masing. Apabila gabungan yang panjang memang menjadi kebutuhan rutin, naikkan batasnya secukupnya karena hasil penggabungan ditampung penuh di memori sebelum dikirim.

Kesalahan umum#

Kesalahan pertama adalah menghilangkan ORDER BY di dalam GROUP_CONCAT. Urutan nilai pada hasil penggabungan menjadi tidak terjamin tanpa klausa itu, dan daftar yang sama dapat tersusun berbeda pada eksekusi lain. Pada query yang hasilnya diurutkan ulang di sisi aplikasi, kehilangan urutan internal ini sering tidak terdeteksi sampai keluarannya dibandingkan antar lingkungan. Selalu nyatakan urutan isi gabungan, terutama ketika hasilnya dipakai sebagai laporan.

Kesalahan kedua adalah mengabaikan batas group_concat_max_len. Gabungan yang melewati 1024 karakter terpotong tanpa error, dan peringatannya hanya muncul melalui SHOW WARNINGS. Biasakan mengukur panjang hasil atau menaikkan batas sesi secara sadar sebelum memakai fungsi ini untuk data yang besar.

Kesalahan ketiga adalah memakai GROUP_CONCAT untuk kelompok yang berisi puluhan ribu nilai. Hasilnya ditampung utuh di memori, sehingga query dapat memakan waktu dan memori yang besar hanya untuk menyusun satu kolom teks. Ukur dahulu banyaknya nilai per kelompok dengan COUNT, karena angkanya sekaligus memperkirakan panjang hasil penggabungan. Untuk kebutuhan semacam itu, pertimbangkan penggabungan di sisi aplikasi atau perkecil cakupan kelompoknya.

Ringkasan#

  • GROUP_CONCAT menyambung nilai string satu kelompok menjadi satu teks, dengan ORDER BY ... SEPARATOR untuk menegaskan urutan isi dan pemisahnya.
  • DISTINCT menghapus duplikat sebelum penggabungan, dan NULL diabaikan tanpa meninggalkan pemisah.
  • Hasil penggabungan dibatasi group_concat_max_len sebesar 1024 karakter secara bawaan, dan pemotongannya terjadi tanpa error.
  • Batas tersebut dapat dinaikkan per sesi melalui SET SESSION, sedangkan keutuhan hasil diperiksa melalui SHOW WARNINGS atau CHAR_LENGTH.

Referensi#