Saturday, January 5, 2008

MySQL - my (Un)Favourite Database

In my last project I decided to go out with MySQL. Why - do not ask why ;)

Anyway.

In the process of building, debugging, developing I noticed that some of the searches does not return a correct result. A search in a VARCHAR field (for Cyrillic characters). I looked for the table encoding - it was UTF8; I looked for the column encoding - it was UTF8 (both collations utf8_general_ci); I looked for the server default encoding - UTF8; I looked for the encoding passed by the client application - again it was UTF8.

Suddenly, by incident I noticed - the encoding of the database was cp1251 (the Cyrillic one). It killed me.

A simple command solved the problem:

ALTER DATABASE {db_name} DEFATULT ENCODING utf8, DEFAULT COLLATION utf8_general_ci;

But I was inserting correct values, when I was browsing the table I could see the right values, but when I search for specific values in the STORED PROCEDURE with AD-HOC SQL built dynamically in the procedure, I could not get the results ...

No Idea ... but I begin to look more and more to the MS SQL server ... and its EXPRESS edition...

4 comments:

Петър said...

PostgreSQL, Firebird, SQLite и т.н.

Anton Staykov said...

Well, I guess every DB has issues with Encodings and we must carefully use and check every time.

peter said...

А сега на френски ли да ти отговоря?

Anton Staykov said...

Както виждаш. блога е изцяло на английски, но всеки е свободен да коментира както намери за добре ...