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
– Robert H, Commented Jul 27, 2012 at 15:27
One thing I had considered was looking at alternative char sets to UTF8 - for example UTF16, or UTF8-swedish. — Robert H
– Robert H, Commented Jul 27, 2012 at 15:30
'Mojibake' is covered in stackoverflow.com/questions/38363566/… — Rick James
– Rick James, Commented Jun 4, 2019 at 19:19

Wichukorn Dandecha · Accepted Answer · 2013-07-12 18:08:16Z

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, 2013 at 18:08

Wichukorn Dandecha

3132 silver badges9 bronze badges

answered Jul 27, 2012 at 13:54

x06265616e

86411 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

T4mer Over a year ago

this was the connection string generated by the wizard

x06265616e Over a year ago

Ahh well, check out the link i provided, it has all the MySQL Connection Strings

T4mer Over a year ago

i checked it , when using small utf data are returned in the correct encoding , when it's UTF no data returned

Korenron Over a year ago

I search for a solution for 2 days! simple is the best , Thanks!

Frijey Labs Over a year ago

I've been searching for a while with no results... Thanks!!! (My problem was with some accentuated characters)

Anthony · Accepted Answer · 2012-07-27 15:12:48Z

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, 2012 at 15:12

answered Jul 27, 2012 at 15:07

Anthony

4272 silver badges6 bronze badges

1 Comment

Bryan Legend Over a year ago

The GetBytes->GetString didn't do anything for me with this problem.

Bryan Legend · Accepted Answer · 2013-04-19 21:51:26Z

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, 2013 at 21:51

Bryan Legend

6,9461 gold badge62 silver badges60 bronze badges

1 Comment

Rick James Over a year ago

ØºØ²Ø© is Mojibake for غزة, which can be represented in either utf8 or utf8mb4. Still, it is generally better to shoot for utf8mb4.

m.shakeri · Accepted Answer · 2014-07-27 11:00:18Z

1

CHARSET should be uppercase

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

answered Jul 27, 2014 at 11:00

m.shakeri

1,3531 gold badge12 silver badges17 bronze badges

Comments

Ramon Gonzalez · Accepted Answer · 2017-06-26 17:45:10Z

1

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.

answered Jun 26, 2017 at 17:45

Ramon Gonzalez

4213 silver badges3 bronze badges

Comments

Luciano Gonzalez · Accepted Answer · 2022-05-26 21:11:53Z

1

this worked for me:

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

answered May 26, 2022 at 21:11

Luciano Gonzalez

111 bronze badge

1 Comment

Community Over a year ago

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.

Robert H · Accepted Answer · 2012-07-27 15:32:09Z

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, 2012 at 15:32

Robert H

11.7k19 gold badges72 silver badges112 bronze badges

2 Comments

T4mer Over a year ago

that was a bit helpful it's latin1_general_ci how this would be useful ?

Robert H Over a year ago

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

Kevin Tighe · Accepted Answer · 2018-06-08 19:19:35Z

0

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';

answered Jun 8, 2018 at 19:19

Kevin Tighe

21.3k4 gold badges38 silver badges36 bronze badges

Collectives™ on Stack Overflow

C# Mysql UTF8 Encoding

8 Answers 8

5 Comments

1 Comment

1 Comment

Comments

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

5 Comments

1 Comment

1 Comment

Comments

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related