CHARSET dan COLLATION Posted on : 11/24/2007 12:34:00 PM

Beberapa hari yang lalu, teman sekampungku di Samarinda (1smail) nanya via YM : "Q koq aku ngga bs ngecek field yg case-sensitive ya? semuanya dianggap insensitive". Hmm, dia pake MySQL. Frontend yg dia pake adalah PHP. Dia udah ngasi aku script dia. 'SELECT * FROM TAnggota WHERE Username = $username;' ~> efeknya, apapun isi variabel $username, komparasi stringnya tetep dianggap case-insensitive..

Kucoba di console-ku.

SELECT 'User' = 'uSeR';

hasilnya 0 (alias false)...

wah, udah case-sensitive koq. Trus kulaporin ke dia. "MySQL case-sensitive koq.. kmu nya aja yg keliru paling." *hehehe, tuduhan tak berdasar*... tp 1smail tetep bersikukuh klo dia nyoba di field dia bakal diperlakukan case-insensitive.

Trus aku coba2 buka MySQL manual.. ketemu tulisan kya gini :

Normally, if any expression in a string comparison is case sensitive, the comparison is performed in case-sensitive fashion.

*bingung aku. MySQL bilang, dia case-sensitive. Aku coba, case-sensitive. Koq 1smail coba, case-insensitive yak?

trus aku search2 lagi MySQL manual... ketemu lagi tulisan kya gini :

By default, MySQL searches are not case sensitive (although there are some character sets that are never case insensitive, such as czech). This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. If you want to make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation.

OALAAAAHH, ngono tho... masalah collation ternyata. (Aku lumayan berpengalaman utk masalah CHARSET n COLLATION ini, mengingat beberapa minggu yang lalu aku cukup disibukin ttg gimana caranya masukin query utk field dgn value berupa tulisan arab. heheHe..., tuntutan profesi...)

Trus, kusuru 1smail checking struktur tabelnya TAnggota dia. Cek, column `username` nya apa charset n collationnya. Ternyata, charset = latin1 dan collation = latin1_swedish_ci

Itu dia masalahnya gas..

latin1_swedish_ci ~> ci di sini refer to 'case-insensitive'. Jelas, column username akan diperlakukan insensitive pada string comparison. Ta' suruh ganti collationnya jadi latin1_general_cs. Trus coba lagi.

BISA!

*Tp muncul 1 pertanyaan, kenapa di engine MySQL ku ngga perlu nyetting collation bisa?

Ternyata, pendekatan yg di-ijinkan MySQL utk nentuin collation ada beberapa cara :
1. cara CASTING. on-the-fly. dilakukan ketika melakukan query. contoh :

SELECT * FROM Table_Name WHERE col_name COLLATE latin1_general_cs LIKE 'a%'

2. definisiin pas pembuatan DDL. pas CREATE TABLE. contoh :

CREATE TABLE Table_Name
(
col_name CHARACTER SET latin1 COLLATE latin1_general_cs
) ENGINE = MyISAM;

3. tambahin parameter pas run service MySQL:
--collation-server=latin1_general_cs

4. setting di my.ini (ato my.cnf)
default-character-set=latin1
collation-server=latin1_general_cs

cara no 3 & 4 akan mbikin settingan default MySQL pas create table baru akan nge-apply collation-nya column otomatis akan jadi latin1_general_cs.

dan ternyata pas ta' check-n-recheck, ternyata dulu aku nyetting my.ini ku dgn system variable seperti itu. makanya MySQL Engine ku secara default melakukan string comparison nerapin mode sensitif.. Hehehe, maklum, dah lama ngga ngobok2 settingan server. Aku klo bolak-balik reinstall MySQL (karna bolak-balik reinstall OS?) tinggal copy-in my.ini ku ke directory tertentu n aktivasi server ku pake command-prompt

E:\MySQL\bin>mysqld-nt --install MySQL --defaults-file="E:\MySQL\my-shiepp.ini"

semua database-ku, login account ku, n settingan ku, akan kembali seperti sedia kala...

Begitulah, MySQL. menarik emang.. apalagi liat nasionalisme mereka yg nyetting default collation pake latin1_swedish_ci. Swedia, their homeland... Salute utk MySQL. berhasil mengibarkan panji-panji Viking Kuning di dunia internasional...

Merah putih kapan ya?


Wassalam,

Qvezst