[FreeBSD] PHP, MySQL 與 UTF-8 中文亂碼

1. Modify /etc/my.cnf, add "default-character-set = utf8" to the following section.

[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8

2. Ensure MySQL environment.

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 | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+

SHOW VARIABLES LIKE 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+

3. Create database and table with UTF-8

CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `table_name` (
`url` VARCHAR(512) NOT NULL ,
PRIMARY KEY ( `url` )
) TYPE = MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

4. If your database or table are already created, modify the database and table to UTF-8.

ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
ALTER TABLE `table_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

5. PHP Programming : mysql_query("SET NAMES 'utf8'");

$dblink = mysql_connect($dbhost, $dbuser, $dbpass);
mysql_query("SET NAMES 'utf8'");
$dbselect = mysql_select_db($dbname);