Learn the Rules, Break The Rules, and Create the New Ones...

Hi... My name is Rizky Prihanto. You can call me RQ, or Rizky either. I am currently living on Bandung, Indonesia. Had a lot of works and research about Enterprise Information Systems (majoring on education and e-governments). I have bunch of interests (some friends call it 'freakz') about MySQL Opensource Database and now I am one of the administrator of MySQL Indonesia User Group - the opensource community initialized by Sun Microsystems Indonesia.

My Company PT Cinox Media Insani, Bandung, Indonesia. I work here since 2008 and I take responsibility as Chief of Software Architect. My job is about planning, imaginating, fantasy-ing, concepting, and build the infrastructure of the new information systems (or app engines) which going to be implemented.

This blog This is my blog that represent my current opinion, research and experiences about anything in Software Engineering. Written since 2007 (actually) and has been vaccum for a lot of while. And now I wanna ressurrect this blog (optimistically) from the long-long-hibernation with something fresh and new ideas -- still about MySQL, software engineering, development, and may be something managerial here.

About the tagline I've learned the statement above from some paper written by Kent Beck about Extreme Programming (XP) methodology -- some sort of practical software development methods which have no boundaries. That's very inspiring me a lot. I have written some article on this blog that tell my interpretation about that statement here.

My Another Blogs I have classifying my blogs into some sort of genre. The blog that you read here right now is my primary blog that tell you (majoring) about IT stuff. But if you wanna look another side of me, you can visit here, here, here,or here. Hope it'll be interesting for some of you.

Credits I would thanks to Blogger for this great blog platform. Skinpress who designed this Wordpress template (which is bloggerized by Free Blogger Templates). My appreciate is also going to you who give your generously time for visiting my blog.

CHARSET dan COLLATION

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

5 comments:

abudiyono mengatakan...

lah kok tahu di minta komentar.. :D

something for A-U mengatakan...

oala... ngono toh kang, baru ngerti kok ada collation ci ama cs

Aries Silfianto mengatakan...

newbie mw nanya gan,, collation itu apa ya?

SunDi3yansyah Library mengatakan...

gk ada mas frontend PHP ada nya backend PHP :D

Catur ilham mengatakan...

thaks gan

Posting Komentar