Panduan Ekspresi Reguler MySQL (REGEXP): Sintaks, Fungsi MySQL 8, Contoh, dan Praktik Terbaik

1. Pendahuluan

Menggunakan Ekspresi Reguler di MySQL

MySQL adalah sistem manajemen basis data yang kuat untuk mencari dan memanipulasi data, dan juga mendukung ekspresi reguler (REGEXP) untuk memungkinkan pencarian yang lebih fleksibel. Dengan ekspresi reguler, Anda dapat mengekstrak data yang cocok dengan pola tertentu secara efisien, serta memformat atau memvalidasi data.

Manfaat Menggunakan Ekspresi Reguler di MySQL

Menggunakan ekspresi reguler MySQL memberikan manfaat seperti:

• Pencarian lanjutan : Dengan mudah mengekstrak data yang berisi string atau pola tertentu.
• Memeriksa integritas data : Berguna untuk validasi input (misalnya, memvalidasi format alamat email).
• Lebih kuat daripada operator LIKE : Dibandingkan dengan LIKE, yang menggunakan wildcard (% dan _), REGEXP memungkinkan Anda mendefinisikan kondisi pencarian yang kompleks dengan lebih fleksibel.

Tujuan dan Struktur Artikel Ini

Artikel ini menjelaskan segala hal mulai dari dasar-dasar ekspresi reguler di MySQL hingga penggunaan lanjutan, optimasi kinerja, dan fungsi ekspresi reguler baru yang diperkenalkan di MySQL 8.0 dan versi selanjutnya. Dengan contoh praktis, konten dirancang agar mudah dipahami bahkan bagi pemula.

Struktur artikel:

1. Dasar-dasar dan sintaks ekspresi reguler MySQL
2. Fungsi ekspresi reguler yang ditambahkan di MySQL 8.0 dan versi selanjutnya
3. Contoh praktis (validasi, ekstraksi data, konversi format)
4. Pertimbangan penting saat menggunakan ekspresi reguler (kinerja, keamanan)
5. FAQ (Frequently Asked Questions)
6. Ringkasan

2. Dasar-dasar Ekspresi Reguler dan Sintaks di MySQL

Cara Menggunakan Ekspresi Reguler di MySQL

Di MySQL, Anda menggunakan operator REGEXP (atau RLIKE) untuk bekerja dengan ekspresi reguler. Mirip dengan operator LIKE, operator ini digunakan untuk menentukan apakah nilai kolom cocok dengan pola regex yang ditentukan.

Sintaks dasar

SELECT * FROM table_name WHERE column_name REGEXP 'regex_pattern';

Atau

SELECT * FROM table_name WHERE column_name RLIKE 'regex_pattern';

RLIKE adalah alias untuk REGEXP, dan keduanya berperilaku sama.

Contoh
Misalnya, untuk mencari catatan di mana nama mengandung karakter “山”:

SELECT * FROM users WHERE name REGEXP '山';

Pola Dasar Ekspresi Reguler

Di ekspresi reguler MySQL, Anda dapat menggunakan pola seperti berikut.

Perbedaan dengan Operator LIKE

MySQL juga menyediakan operator LIKE, yang biasanya digunakan untuk pencocokan pola sederhana. Namun, REGEXP lebih kuat daripada LIKE dan mendukung pencarian dengan kondisi yang lebih kompleks.

Contoh 1: Menggunakan operator LIKE

SELECT * FROM users WHERE name LIKE '%山%';

• Dengan LIKE, Anda dapat mencari data yang mengandung 山, tetapi hanya dapat menggunakan wildcard sederhana (% dan _).

Contoh 2: Menggunakan operator REGEXP

SELECT * FROM users WHERE name REGEXP '^山';

• Dengan REGEXP, Anda dapat mengekstrak hanya data di mana nama dimulai dengan 山.

Cara Menentukan Beberapa Pola

Anda dapat menggunakan | untuk menentukan beberapa pola dengan kondisi OR.

Contoh: Dapatkan pengguna yang nama belakangnya “佐藤” atau “田中”

SELECT * FROM users WHERE name REGEXP '佐藤|田中';

Pencocokan Negatif

Jika Anda menggunakan ^ di dalam tanda kurung siku, Anda dapat mengambil data yang “tidak mengandung karakter yang ditentukan”.

Contoh: Data yang dimulai dengan karakter selain “山”

SELECT * FROM users WHERE name REGEXP '^[^山]';

Dalam kasus ini, ia mengembalikan catatan di mana nama tidak dimulai dengan 山.

Ringkasan

• Di MySQL, REGEXP memungkinkan pencarian pola yang lebih kuat dibandingkan LIKE.
• Memahami sintaks dasar dan pola ( . , ^ , $ , [] , * , + , dll.) memungkinkan pencarian yang fleksibel.
• Penting untuk memahami perbedaan dengan LIKE dan menggunakan masing‑masing secara tepat.
• Menggunakan | memungkinkan pencarian dengan beberapa pola.
• Menggunakan kelas karakter negatif [^ ] membantu Anda mencari data yang tidak dimulai dengan karakter tertentu.

3. Regular Expression Functions Added in MySQL 8.0 and Later

Di MySQL 8.0, selain operator REGEXP tradisional, empat fungsi baru ditambahkan untuk memungkinkan pencarian ekspresi reguler yang lebih fleksibel. Fungsi‑fungsi ini memungkinkan Anda mengambil posisi kecocokan, mengekstrak substring, dan melakukan penggantian, sehingga secara signifikan memperluas apa yang dapat Anda lakukan dengan regex di MySQL.

Pada bagian ini, kami menjelaskan setiap fungsi ekspresi reguler baru secara detail dan menampilkan contoh konkret.

3.1 REGEXP_LIKE()

Overview

REGEXP_LIKE(), seperti operator REGEXP, memeriksa apakah nilai kolom cocok dengan pola regex yang ditentukan.

Syntax

REGEXP_LIKE(column_name, 'regex_pattern' [, flags])

• column_name : kolom yang akan dicari
• 'regex_pattern' : pola regex yang digunakan sebagai kondisi
• flags (optional) : sensitivitas huruf (gunakan i untuk pencocokan tidak memperhatikan huruf besar/kecil)

Examples

“Dapatkan pengguna yang emailnya berakhiran gmail.com atau yahoo.co.jp”

SELECT * FROM users WHERE REGEXP_LIKE(email, '(@gmail\.com|@yahoo\.co\.jp)$');

“Cari nama pengguna yang mengandung ‘admin’ (tidak memperhatikan huruf besar/kecil)”

SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');

✅ REGEXP_LIKE() memiliki tujuan yang sama dengan operator REGEXP, tetapi memungkinkan gaya yang lebih standar dan ramah SQL.

3.2 REGEXP_INSTR()

Overview

REGEXP_INSTR() mengembalikan posisi (indeks mulai) di mana kecocokan regex terjadi dalam sebuah string.

Syntax

REGEXP_INSTR(column_name, 'regex_pattern' [, start_position, occurrence, return_option, flags])

• start_position (optional): posisi mulai pencarian (default: 1)
• occurrence (optional): kecocokan ke‑berapa yang akan dikembalikan (default: 1)
• return_option (optional): 0 (kembalikan posisi mulai) atau 1 (kembalikan posisi akhir kecocokan)
• flags (optional): gunakan i untuk pencocokan tidak memperhatikan huruf besar/kecil

Examples

“Jika nomor telepon dimulai dengan 090 atau 080, dapatkan posisi mulai”

SELECT phone, REGEXP_INSTR(phone, '^(090|080)') AS match_pos FROM users;

“Dapatkan posisi mulai bagian domain dalam sebuah alamat email”

SELECT email, REGEXP_INSTR(email, '@') AS domain_start FROM users;

• Ia mengembalikan posisi di mana @ ditemukan (misalnya, user@example.com mengembalikan 5 ).

✅ Dengan REGEXP_INSTR(), Anda dapat mengambil informasi posisi, memperluas pilihan Anda dalam pemrosesan string.

3.3 REGEXP_SUBSTR()

Overview

REGEXP_SUBSTR() mengekstrak substring yang cocok dengan pola regex dari dalam sebuah string.

Syntax

REGEXP_SUBSTR(column_name, 'regex_pattern' [, start_position, occurrence, flags])

• occurrence (optional): dapatkan kecocokan ke‑n (default: 1)
• flags (optional): sensitivitas huruf (i untuk mengabaikan huruf besar/kecil)

Examples

“Ekstrak hanya bagian domain dari sebuah alamat email”

SELECT email, REGEXP_SUBSTR(email, '@[a-zA-Z0-9.-]+') AS domain FROM users;

“Dapatkan urutan angka pertama dalam sebuah pesan”

SELECT message, REGEXP_SUBSTR(message, '[0-9]+') AS first_number FROM logs;

✅ Dengan REGEXP_SUBSTR(), Anda dapat mengekstrak pola spesifik dan menggunakannya untuk mengorganisir serta mentransformasi data.

3.4 REGEXP_REPLACE()

Overview

REGEXP_REPLACE() mengganti substring yang cocok dengan pola regex dengan string lain.

Syntax

REGEXP_REPLACE(column_name, 'regex_pattern', 'replacement' [, occurrence, flags])

• replacement : string yang akan menggantikan bagian yang cocok
• occurrence (opsional): ganti hanya kecocokan ke-n (jika diabaikan, ganti semua kecocokan)
• flags (opsional): gunakan i untuk pencocokan tidak peka huruf besar kecil

Contoh

“Hapus tanda hubung (-) dari nomor telepon”

SELECT phone, REGEXP_REPLACE(phone, '-', '') AS clean_phone FROM users;

“Hapus tag HTML”

SELECT comment, REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_text FROM reviews;

✅ Dengan REGEXP_REPLACE(), konversi format dan pembersihan data menjadi mudah.

3.5 Ringkasan

4. Contoh Praktis Ekspresi Reguler MySQL

Dengan memanfaatkan ekspresi reguler MySQL, Anda dapat menyederhanakan tugas pemrosesan data dunia nyata seperti validasi data, ekstraksi data spesifik, dan konversi format. Pada bagian ini, kami memperkenalkan kasus penggunaan praktis dengan kode SQL dan penjelasan.

4.1 Validasi Alamat Email

Gambaran Umum

Gunakan ekspresi reguler untuk memeriksa apakah alamat email yang disimpan di database mengikuti format yang valid.

SQL yang Digunakan

SELECT email FROM users 
WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

Penjelasan

• ^[a-zA-Z0-9._%+-]+ → satu atau lebih karakter alfanumerik, titik, garis bawah, atau tanda plus sebelum @
• @[a-zA-Z0-9.-]+ → nama domain setelah @
• \.[a-zA-Z]{2,}$ → diakhiri dengan TLD (Top-Level Domain) minimal 2 huruf (misalnya, .com , .jp , .net )

Dengan kueri ini, Anda dapat mengecualikan alamat email yang tidak valid (misalnya, user@@example.com, user@.com).

4.2 Memeriksa Format Nomor Telepon Jepang

Gambaran Umum

Tentukan apakah nomor telepon Jepang umum (misalnya, 090-1234-5678, 03-1234-5678) mengikuti format yang benar.

SQL yang Digunakan

SELECT phone FROM users 
WHERE phone REGEXP '^(0[789]0-[0-9]{4}-[0-9]{4}|0[1-9]-[0-9]{4}-[0-9]{4})$';

Penjelasan

• 0[789]0-[0-9]{4}-[0-9]{4} → nomor seluler ( 090-xxxx-xxxx , 080-xxxx-xxxx , dst.)
• 0[1-9]-[0-9]{4}-[0-9]{4} → nomor telepon rumah ( 03-xxxx-xxxx , 06-xxxx-xxxx , dst.)

Dengan cara ini, ekspresi reguler berguna ketika konsistensi format diperlukan.

4.3 Validasi Format Nomor Kartu Kredit

Gambaran Umum

Validasi format nomor kartu kredit (Visa, Mastercard, American Express, dst.).

SQL yang Digunakan

SELECT card_number FROM payments 
WHERE card_number REGEXP '^(4[0-9]{12}(?:[0-9]{3})?|5[1-5][0-9]{14}|3[47][0-9]{13})$';

Penjelasan

• ^4[0-9]{12}(?:[0-9]{3})?$ → Visa (16 digit atau 13 digit)
• ^5[1-5][0-9]{14}$ → Mastercard (16 digit)
• ^3[47][0-9]{13}$ → American Express (15 digit)

Dengan kueri ini, Anda dapat menyaring nomor kartu yang tidak valid di database (misalnya, panjang yang salah atau digit awal yang tidak valid).

4.4 Menghapus Tag HTML

Gambaran Umum

Jika data yang dikirim pengguna berisi tag HTML, Anda dapat menghapusnya dan mengonversi konten menjadi teks biasa.

SQL yang Digunakan

SELECT REGEXP_REPLACE(comment, '<[^>]+>', '') AS clean_comment FROM reviews;

Penjelasan

• '<[^>]+>' → menghapus semua tag HTML antara < dan >

Contoh

Ini berguna untuk menghapus tag HTML dari komentar pengguna dan posting blog.

4.5 Validasi Format Kode Pos (Jepang)

Gambaran Umum

Validasi apakah kode pos Jepang (misalnya, 123-4567) mengikuti format yang benar.

SQL yang Digunakan

SELECT postal_code FROM addresses 
WHERE postal_code REGEXP '^[0-9]{3}-[0-9]{4}$';

Penjelasan

• ^[0-9]{3}-[0-9]{4}$ → kode pos dalam format “3 digit-4 digit”

Menggunakan regex ini membantu menjaga konsistensi data dan mencegah format yang salah.

4.6 Penyaringan Nama Pengguna (Mendeteksi Kata yang Tidak Diizinkan)

Gambaran Umum

Check whether usernames contain disallowed words reserved for system use (e.g., admin, root, system) during registration.

SQL yang Digunakan

SELECT username FROM users 
WHERE username REGEXP 'admin|root|system';

Penjelasan

• admin|root|system → mendeteksi nama pengguna yang mengandung istilah ini

Kueri ini membantu mencegah pengguna biasa menggunakan nama yang ditujukan untuk administrator sistem.

Ringkasan

• Ekspresi reguler dapat digunakan untuk memvalidasi alamat email, nomor telepon, dan nomor kartu kredit .
• Mereka juga dapat diterapkan untuk menghapus tag HTML dan mengonversi format data .
• Mereka efektif untuk meningkatkan keamanan dan konsistensi basis data melalui tugas seperti menyaring kata yang tidak diizinkan dan memvalidasi kode pos .

5. Pertimbangan Penting Saat Menggunakan Ekspresi Reguler (Kinerja dan Keamanan)

Menggunakan ekspresi reguler di MySQL memungkinkan pencarian dan pembersihan data yang kuat. Namun, penggunaan yang tidak tepat dapat menyebabkan penurunan kinerja dan menimbulkan risiko keamanan. Bagian ini menjelaskan pertimbangan kunci untuk menggunakan ekspresi reguler MySQL secara aman dan efisien.

5.1 Optimasi Kinerja untuk Ekspresi Reguler

Pencarian regex MySQL nyaman, tetapi memiliki kelemahan: indeks umumnya tidak berlaku, yang dapat membuat kueri lebih lambat.

Langkah-langkah untuk Meningkatkan Kinerja

1. Gabungkan indeks LIKE atau FULLTEXT

   SELECT * FROM users WHERE email LIKE '%gmail.com';
   

2. Gunakan ekspresi reguler yang lebih sederhana

   SELECT * FROM users WHERE name REGEXP '^admin|admin$';
   

3. Filter data target terlebih dahulu

   SELECT * FROM users WHERE email LIKE 'a%' AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

5.2 Risiko Keamanan dan Tindakan Pencegahan

1. DoS Ekspresi Reguler (ReDoS)

• Hindari pencocokan tak terbatas seperti .* .
• Gunakan ^ dan $ untuk membatasi ruang pencarian.
• Jangan mengirimkan input pengguna secara langsung ke REGEXP .

2. Injeksi SQL dan Ekspresi Reguler

❌ Kueri tidak aman

SELECT * FROM users WHERE username REGEXP '$input';

✅ Kueri aman (gunakan placeholder)

$stmt = $pdo->prepare("SELECT * FROM users WHERE username REGEXP ?");
$stmt->execute([$sanitized_input]);

5.3 Kompatibilitas Versi

Ringkasan

• Karena REGEXP tidak menggunakan indeks, langkah-langkah kinerja penting.
• Untuk mencegah ReDoS (DoS Ekspresi Reguler), hindari penggunaan wildcard yang berlebihan.
• Untuk mencegah injeksi SQL, jangan menerapkan input pengguna secara langsung ke REGEXP.

6. FAQ (Pertanyaan yang Sering Diajukan)

Berikut adalah pertanyaan umum tentang penggunaan ekspresi reguler MySQL (REGEXP). Menyelesaikan masalah ini akan membantu Anda menggunakan regex lebih efektif.

6.1 Pola regex apa yang dapat saya gunakan di MySQL?

6.2 Apa perbedaan antara LIKE dan REGEXP?

✅ Karena LIKE lebih cepat, pilih LIKE untuk pencarian sederhana dan gunakan REGEXP ketika pencocokan kompleks diperlukan.

6.3 Apa perbedaan fitur regex antara MySQL 5.x dan MySQL 8.x?

6.4 Apa yang harus saya lakukan jika regex tidak berfungsi seperti yang diharapkan?

✅ Daftar Periksa

1. Periksa apakah pelolosan (escaping) sudah benar

   SELECT * FROM users WHERE email REGEXP '\.com$';
   

2. Coba flag i dengan REGEXP_LIKE()

   SELECT * FROM users WHERE REGEXP_LIKE(username, 'admin', 'i');
   

3. Periksa enkoding data

   SHOW VARIABLES LIKE 'character_set_database';
   

6.5 Bagaimana saya dapat meningkatkan kinerja kueri REGEXP?

1. Pra-filter dengan LIKE

   SELECT * FROM users 
   WHERE email LIKE '%gmail.com' 
   AND email REGEXP '^[a-zA-Z0-9._%+-]+@gmail\.com$';
   

2. Gunakan indeks bila sesuai

   ALTER TABLE users ADD FULLTEXT(email);
   

Ringkasan

• REGEXP lebih kuat daripada LIKE, tetapi Anda harus memperhatikan kinerja.
• Pada MySQL 8.0, fungsi regex baru (seperti REGEXP_LIKE()) ditambahkan, memungkinkan pemrosesan yang lebih fleksibel.
• Untuk meningkatkan kinerja: gabungkan dengan LIKE, gunakan indeks, dan rancang pola regex yang sederhana.

7. Ringkasan

Ekspresi reguler MySQL (REGEXP) sangat berguna untuk pencarian data, validasi, dan konversi format. Artikel ini membahas dasar-dasar regex MySQL, fitur baru yang ditambahkan di MySQL 8.0 dan versi selanjutnya, contoh praktis, pertimbangan penting, dan FAQ.

7.1 Poin Penting

Berikut rangkuman singkat dari poin-poin terpenting.

1. Dasar-dasar Ekspresi Reguler MySQL

• Menggunakan operator REGEXP memungkinkan pencocokan pola yang lebih fleksibel dibandingkan LIKE.
• Pola regex umum
• ^ (awal), $ (akhir), . (karakter tunggal apa saja), [] (kelas karakter), + (satu atau lebih pengulangan), dll.

2. Fungsi Ekspresi Reguler Baru di MySQL 8.0+

Pada MySQL 8.0, empat fungsi ini ditambahkan, memungkinkan pemrosesan yang lebih fleksibel:

• REGEXP_LIKE() : alternatif untuk operator REGEXP
• REGEXP_INSTR() : mendapatkan posisi awal substring yang cocok
• REGEXP_SUBSTR() : mengekstrak substring yang cocok
• REGEXP_REPLACE() : mengganti substring menggunakan regex

3. Contoh Praktis

• Validasi alamat email
• Pemeriksaan nomor telepon dan nomor kartu kredit
• Menghapus tag HTML
• Memvalidasi format kode pos
• Mendeteksi kata yang tidak diizinkan
• Pembersihan data (memangkas spasi, menghapus koma, dll.)

4. Pertimbangan Penting Saat Menggunakan Ekspresi Reguler

• Optimasi kinerja
• Karena REGEXP tidak menggunakan indeks, dapat menghasilkan pemindaian seluruh tabel.
• Anda dapat meningkatkan kinerja dengan menggabungkan indeks LIKE atau FULLTEXT.
• Langkah keamanan
• Untuk mencegah ReDoS (Regular Expression DoS), hindari penggunaan berlebihan .*.
• Untuk mencegah SQL injection, gunakan placeholders.

5. FAQ (Pertanyaan yang Sering Diajukan)

• Pola regex yang tersedia di MySQL
• Perbedaan antara LIKE dan REGEXP
• Perbedaan fitur antara MySQL 5.x dan 8.x
• Pemecahan masalah ketika regex tidak berperilaku seperti yang diharapkan
• Cara meningkatkan kinerja

7.2 Praktik Terbaik Menggunakan Ekspresi Reguler

1. Jaga pola regex tetap sederhana

• Contoh: Hindari penggunaan berlebihan .* dan manfaatkan ^ (awal) serta $ (akhir).

2. Gabungkan dengan LIKE atau FULLTEXT bila sesuai

• Prafiltrasi dengan LIKE, kemudian terapkan REGEXP untuk mengurangi biaya query.

3. Gunakan REGEXP_REPLACE() untuk pembersihan data

• Contoh: Hapus tag HTML dan normalisasi spasi yang tidak diperlukan.

4. Manfaatkan MySQL 8.0 dan versi selanjutnya

• Menggunakan fungsi baru seperti REGEXP_LIKE() dapat membuat SQL lebih mudah dibaca.

5. Terapkan langkah keamanan secara menyeluruh

• Jangan menerapkan input pengguna secara langsung ke REGEXP (pencegahan SQL injection).
• Gunakan placeholders untuk query dinamis.

7.3 Sumber Daya untuk Pembelajaran Lebih Lanjut

Berikut referensi untuk membantu Anda mempelajari ekspresi reguler MySQL secara mendalam.

Dokumentasi Resmi

• Referensi Ekspresi Reguler MySQL 8.0
• Dasar-dasar Ekspresi Reguler MySQL 5.x

7.4 Catatan Akhir

Ekspresi reguler MySQL dapat digunakan dalam banyak situasi, termasuk pencarian data, pembersihan, dan validasi. Namun, penting untuk menggunakannya secara tepat sambil memahami pertimbangan kinerja dan keamanan.

Kami harap artikel ini membantu Anda saat bekerja dengan ekspresi reguler MySQL. Cobalah menerapkan teknik ini dalam proyek Anda!