How to create a database wiht character set UTF-8


#1

Hello.

Is it possible create one database from phpmyadmin wiht the character set to “UTF8_general_ci”???

If its not possible, how can I do it from the Web admin panel (I see tha its possible create one in “Goodies -> Mysql -> Create a new MySQL database”, but i think than in this way the database will have “Iso-8859-1” encoding).

Please, any sugestion?

Thanks in advance


#2

I have the same problem. I’ve got into massive encoding problems, because the data is stored in MySQL wrongly encoded.

The problem is, the connections to MySQL are in LATIN1 by default. I need them to be UTF-8 by default. I guess that can be achieved by creating the database with proper encoding.

I tried altering the database encoding, but it doesn’t seem to affect the default connection encoding.

Help, please!


#3

Using PhpMyadmin (just navigate in a browser to your database host) you can set you MySQL connection collation via a “pull down” menu in the left most column of the right frame of the default page that is displayed (before you select a database).

I have mine set to utf8_unicode_ci now, and it works fine. Note that this is not set by database, but by host.

–rlparker


#4

I have set that too, but the default encoding is still latin1. See:

$ ssh -C automatthias@myserver.dreamhost.com
[myserver]$ locale
LANG=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
(...)
[myserver]$ mysql -u dbuser -p -h mysql.mydomain.com dbname
(...)
mysql> select post_title from blog_posts where id = 9;
+------------+
| post_title |
+------------+
| Tle? 2006  | ← wrong!
+------------+
1 row in set (0.01 sec)

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | latin1                                    |
| character_set_connection | latin1                                    |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | latin1                                    |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /data/mysql/trabazo/share/mysql/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.28 sec)

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

mysql> select post_title from blog_posts where id = 9;
+------------+
| post_title |
+------------+
| Tleñ 2006 |     ← correct!
+------------+
1 row in set (1.86 sec)

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | latin1                                    |
| character_set_system     | utf8                                      |
| character_sets_dir       | /data/mysql/trabazo/share/mysql/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.41 sec)

What I want is to have utf-8 character set without need to call “set names utf8;”.
Can you please check the same thing with your server?


#5

I’ve tested in my servers and confirmed the same activity as you; I’ve never noticed it before because I do most of my MySQL manipulation via phpMyAdmin, and it handles that pretty well.

That said, from looking at the appropriate MySQL manual page what we are seeing from the command line is “normal” (there are some very good user comments here, too!). I’m not sure there is a way to avoid sending the 'set names utf8" for us ini the DH environment, given that we cant get at the my.cnf file to change stuff in the [mysqld] section. :frowning:

–rlparker