read/write unicode data in MySql

Question

I am using MySql DB and want to be able to read & write unicode data values. For example, French/Greek/Hebrew values.

My client program is C# (.NET framework 3.5).

How do i configure my DB to allow unicode? and how do I use C# to read/write values as unicode from MySql?

Upddate: 7 Sep. 09

OK, So my Schema, Table & columns are set to 'utf8' + collation 'utf8_general_ci'. I run the 'set names utf8' when the connection is opened. so far so good... but, still values are saved as '??????? '

any ideas?

The Solution!

OK, so for C# client to read & write unicode values, you must include in the connection string: charset=utf8

for example: server=my_sql_server;user id=my_user;password=my_password;database=some_db123;charset=utf8;

of course you should also define the relevant table as utf8 + collation utf8_bin.

Answer 1

You need to set the db charset to UTF-8 (if you are using utf-8), collation for relevant tables/fields to utf, execute SET NAMES 'UTF-8' before doing queries, and of course make sure you set the proper encoding in the html that is showing the output.

Answer 2

You have to set the collation for your MySQL schema, tables or even columns.

Most of the time, the utf8_general_ci collation is used because it is case insensitive and accent insensitive comparisons.

On the other hand, utf8_unicode_ci is case sensitive and uses more advanced sorting technics (like sorting eszet ('ß') near 'ss'). This collation is a tiny bit slower than the other two.

Finally, utf8_bin compares string using their binary value. Thus, it also is case sensitive.

If you're using MySQL's Connector/NET (which I recommend), everything should go smoothly.

Answer 3

The Solution!

OK, so for C# client to read & write unicode values, you must include in the connection string: charset=utf8

for example: server=my_sql_server;user id=my_user;password=my_password;database=some_db123;charset=utf8;

of course you should also define the relevant table as utf8 + collation utf8_bin.