MySQL UTF-8 coding

Question

I'm very confused with the char coding of my database.

I have the database configure with UTF8_general_ci. But If I try to save something in spanish like "león" the content si spoiled then to fix it I have to use utf8_decode before save and when I read I use utf8_encode.

Then the spanish is correct.

Now I have russian users writing in cyrilic and his content is spoiled (with ???????)

I suppose if I don't use utf8_decode the cyrilic will be correct (or not I don't know) but then the spanish will be not correct.

What can I do to save correctly all the languages? I'm very confused.

What character set does the client/connection use ? – Radu Murzea May 04 '12 at 15:46 — Radu Murzea, May 04 '12 at 15:46

score 1 · Answer 1 · answered May 04 '12 at 15:47

1

Try to run as first statment this sql query:

set names 'utf8';

This sets the encoding of the MySQL connetion to UTF-8 and prevents a double encoding.

answered May 04 '12 at 15:47

rekire

47,260
30
167
264

Thank you!! I've putted after the connection: mysql_query ("SET NAMES 'utf8'"); and it seems it works perfect! – Oscardr May 04 '12 at 16:09

score 1 · Answer 2 · answered May 04 '12 at 15:48

1

Check that your client executes

SET NAMES 'utf8';

when it starts a session. Check that all tables and all char fields in tables use utf-8 encoding (their encoding can differ from database encoding).

answered May 04 '12 at 15:48

Pavel Strakhov

39,123
5
88
127

score 1 · Answer 3 · answered May 04 '12 at 15:48

I use a script that converts all the tables in a database for utf8 charsets. Give it a try:

<?
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
$dbname = 'db_name';
$collation = "utf8_general_ci";
$charset = "utf8";

mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);

mysql_query("ALTER DATABASE $dbname COLLATE $collation");
$result = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_row($result)) {
mysql_query("ALTER TABLE $row[0] COLLATE $collation");
$result1 = mysql_query("SHOW COLUMNS FROM $row[0]");
while ($row1 = mysql_fetch_assoc($result1)) {
if (preg_match('~char|text|enum|set~', $row1["Type"])) {
mysql_query("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET $charset");
mysql_query("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation DEFAULT '$row1[Default]'");
echo $row[0] . "->" . $row1[Field] . "->" . $row1[Type] . " is now UTF8
";
}
}
}
mysql_free_result($result);

?>

Thannk you But it's supposed I have the database in UTF-8, I defined the fields in UTF-8 I don't understand why I have to alter the database to UTF-8. — Oscardr, May 04 '12 at 16:12
I've needed a quick solution and I added after the connection mysql_query ("SET NAMES 'utf8'"); And it seems works well, I'm more relaxed now. Now I'll read carefully your links and I'll try to understand the tpasku script. Thank you, you are awesome!!! Oscar. — Oscardr, May 04 '12 at 16:14

score 1 · Answer 4 · answered May 04 '12 at 15:54

1

 http://stackoverflow.com/questions/367711/what-is-the-best-collation-to-use-for-mysql-with-php

mysql character sets -----------------

 http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html

you can specify a more specific UTF-8 character set like 'utf8_swedish_ci'

answered May 04 '12 at 15:54

Sanath

4,774
10
51
81

I've needed a quick solution and I added after the connection mysql_query ("SET NAMES 'utf8'"); And it seems works well, I'm more relaxed now. Now I'll read carefully your links and I'll try to understand the tpasku script. Thank you, you are awesome!!! Oscar. – Oscardr May 04 '12 at 16:23

MySQL UTF-8 coding

4 Answers4