utf8 encoding: Can I really have it all?

Databases
1

Hello!

I’m developing my first application that will have some non-English characters and I’ve been investigating the world of encoding. My accented characters look fine in my web application both before and after putting them in the database, but the actual database characters don’t look as they should. On the one hand, I don’t see this as a problem (I can only assume that my database understands that it’s UTF-8, converts it to latin1, then converts it back to UTF-8 when I call for the data); however, it would be nice if I could have the data appear clean in both place.

After doing do some googling, I found that I can change a table’s encoding as:

ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

However, after doing so, I had the opposite problem: manually entering accented characters in the database gave me junk on the screen (php’s mb_detect_encoding() showed that my application is in fact using utf8 encoding() for the accented characters).

Any help in getting my characters to look correct in both places would be great.

Thanks so much,

Eric

2

When you connect to a database the client connection uses a predefined default character set. This can be changed after connecting to the server using:

SET NAMES UTF8;

Try this and see if this helps?

3

Thank you for the quick reply. I logged into phpMyAdmin and wrote SET NAMES UTF8; However, the characters still look the same. Any other thoughts would be appreciated.

4

Unfortunately the characters that have already been added have been added using a different characterset. If you change the character set to latin1 and then issue your select (I am assuming latin1 was the previous default) you will see the entries correctly. I have put together a small demo of what I mean below.


mysql> create table charsettest(name varchar(20) character set utf8);
Query OK, 0 rows affected (0.04 sec)

mysql> desc charsettest;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       | 
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into charsettest(name) values ('Allé');
Query OK, 1 row affected (0.00 sec)

mysql> select * from charsettest;
+-------+
| name  |
+-------+
| Allé | 
+-------+
1 row in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from charsettest;
+---------+
| name    |
+---------+
| Allé | 
+---------+
1 row in set (0.00 sec)

mysql> insert into charsettest(name) values ('Logroño');
Query OK, 1 row affected (0.00 sec)

mysql> select * from charsettest;
+----------+
| name     |
+----------+
| Allé  | 
| Logroño | 
+----------+
2 rows in set (0.00 sec)

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from charsettest;
+---------+
| name    |
+---------+
| Allé   | 
| Logro?o | 
+---------+
2 rows in set (0.00 sec)
5

Muchas gracias, ahora entiendo…

:slight_smile: