C# Mysql UTF8 Encoding

Question

I have a mysql database with utf8_general_ci encoding ,

i'm connecting to the same database with php using utf-8 page and file encode and no problem but when connection mysql with C# i have letters like this ØºØ²Ø©

i editit the connection string to be like this

server=localhost;password=root;User Id=root;Persist Security Info=True;database=mydatabase;Character Set=utf8

but the same problem .

If you find a solution I will be impressed - I tried UTF8 encoding with no luck as well, I ended up having to resort to stripping those characters prior to adding to the database by doing a `string.Replace("\xFFFD", "");`, which obviously causes issues when someone has a minuscule as part of their name. — Robert H, Jul 27 '12 at 15:27
One thing I had considered was looking at alternative char sets to UTF8 - for example UTF16, or UTF8-swedish. — Robert H, Jul 27 '12 at 15:30
'Mojibake' is covered in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored — Rick James, Jun 04 '19 at 19:19

score 39 · Answer 1 · edited Jul 12 '13 at 18:08

39

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; CharSet=utf8;

Note! Use lower case value utf8 and not upper case UTF8 as this will fail.

See http://www.connectionstrings.com/mysql

edited Jul 12 '13 at 18:08

Wichukorn Dandecha

313
2
8

answered Jul 27 '12 at 13:54

x06265616e

854
11
14

this was the connection string generated by the wizard – T4mer Jul 27 '12 at 13:59
Ahh well, check out the link i provided, it has all the MySQL Connection Strings – x06265616e Jul 27 '12 at 14:01
i checked it , when using small utf data are returned in the correct encoding , when it's UTF no data returned – T4mer Jul 27 '12 at 14:06
I search for a solution for 2 days! simple is the best , Thanks! – Korenron Mar 07 '19 at 09:29
I've been searching for a while with no results... Thanks!!! (My problem was with some accentuated characters) – Frijey Labs Jun 27 '22 at 14:45

Anthony · Answer 2 · 2012-07-27T15:12:48.887

5

could you try:

Server=localhost;Port=3306;Database=xxx;Uid=x xx;Pwd=xxxx;charset=utf8;"

Edit: I got a new idea:

//To encode a string to UTF8 encoding
string source = "hello world";
byte [] UTF8encodes = UTF8Encoding.UTF8.GetBytes(source);

//get the string from UTF8 encoding
string plainText = UTF8Encoding.UTF8.GetString(UTF8encodes);

good luck

more info about this technique http://social.msdn.microsoft.com/forums/en-us/csharpgeneral/thread/BF68DDD8-3D95-4478-B84A-6570A2E20AE5

edited Jul 27 '12 at 15:12

answered Jul 27 '12 at 15:07

Anthony

427
2
6

1

The GetBytes->GetString didn't do anything for me with this problem. – Bryan Legend Apr 19 '13 at 18:10

score 4 · Answer 3 · answered Apr 19 '13 at 21:51

4

You might need to use the "utf8mb4" character set for the column in order to support 4 byte characters like this: "λ "

The utf8 charset only supports 1-3 bytes per character and thus can't support all unicode characters.

See http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html for more details.

answered Apr 19 '13 at 21:51

Bryan Legend

6,790
1
59
60

`ØºØ²Ø©` is Mojibake for `غزة`, which can be represented in either utf8 or utf8mb4. Still, it is generally better to shoot for utf8mb4. – Rick James Jun 04 '19 at 19:22

score 1 · Answer 4 · answered Jul 27 '14 at 11:00

1

CHARSET should be uppercase

Server=localhost;Port=3306;Database=xxx;Uid=x xx;Pwd=xxxx;CHARSET=utf8;

answered Jul 27 '14 at 11:00

m.shakeri

1,203
1
12
16

score 1 · Answer 5 · answered Jun 26 '17 at 17:45

Just in case some come here later.

I needed to create a Seed method using Mysql with EF6, to load a SQL file. After running it I got weird characters on database like ? replacing é, ó, á

SOLUTION: Make sure I read the file using the right charset: UTF8 on my case.

     var path = System.AppDomain.CurrentDomain.BaseDirectory;
     var sql = System.IO.File.ReadAllText(path + "../../Migrations/SeedData/scripts/sectores.sql", Encoding.UTF8);

And then M.Shakeri reminder:

CHARSET=utf8 on cxn string in web.config. Using CHARSET as uppercase and utf8 lowercase.

Hope it helps.

R.

score 0 · Answer 6 · answered Jul 27 '12 at 15:32

0

One thing I found, but haven't had the opportunity to really browse is the collation charts available here: http://www.collation-charts.org/mysql60/

This will show you which characters are part of a given MySQL collation so you can pick the best option for your dataset.

answered Jul 27 '12 at 15:32

Robert H

11,520
18
68
110

that was a bit helpful it's latin1_general_ci how this would be useful ? – T4mer Jul 27 '12 at 16:44
If you set your collation to latin1_general_ci in mySQL you should be able to pull the correct data from your database in C# and PHP - worth a shot anyways – Robert H Jul 27 '12 at 16:59

score 0 · Answer 7 · answered Jun 08 '18 at 19:19

Setting the charset in the connection string refers to the charset of the queries sent to the server. It does not affect the results returned from the server.

https://dev.mysql.com/doc/connectors/en/connector-net-connection-options.html

One way I have found to specify the charset from the client is to run this after opening the connection.

set character_set_results='utf8';

score 0 · Answer 8 · answered May 26 '22 at 21:11

0

this worked for me:

"datasource=xxx;port=3306;username=xxx;password=xxx;database=xxx;charset=utf8mb4"

answered May 26 '22 at 21:11

Luciano Gonzalez

1

2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 26 '22 at 21:32

C# Mysql UTF8 Encoding

8 Answers8

Linked

Related