mysql 編碼

MySQL 編碼

1.先設定 mysqld 服務的編碼 
[root@sfs3 ~]# vim /etc/my.cnf 
[mysqld]
default-character-set=utf8
default-collation=utf8_unicode_ci
character-set-server=utf8
collation-server=utf8_unicode_ci 

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2.重新啟動 mysqld 服務 
[root@sfs3 ~]# /etc/init.d/mysqld restart
登入 mysql 查詢 charset 變數，資料庫編碼為 character_set_database latin1。 
[root@sfs3 ~]# mysql -uroot -p
Enter password: 

mysql> use sfs3;
mysql> show variables like 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
使用 status; 或 \s 查詢也可以 
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 43
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.69 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 59 min 12 sec

Threads: 1  Questions: 1112  Slow queries: 0  Opens: 645  Flush tables: 1  Open tables: 64  Queries per second avg: 0.313
--------------
3.設定資料庫預設編碼為 utf8 
mysql> ALTER DATABASE sfs3 CHARACTER SET utf8 COLLATE utf8_general_ci;
使用 status; 或 \s 再查詢，資料庫編碼已改 utf8。 
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 43
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.69 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db     characterset: utf8
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 59 min 12 sec

Threads: 1  Questions: 1112  Slow queries: 0  Opens: 645  Flush tables: 1  Open tables: 64  Queries per second avg: 0.313


4.雖然資料庫編碼已改為 utf8，但其中的資料表還是原先的 latin1 編碼，為一次轉碼，先以預設編碼 latin1 匯出資料庫，資料庫中的中文字串會以系統預設的 utf8 編碼呈現。 
[root@sfs3 ~]# cd /var/lib/mysql/
[root@sfs3 mysql]# mysqldump --user=root --password=rootpw --default-character-set=latin1 --skip-set-charset sfs3 > sfs3.sql


5.將資料庫中的 latin1 字串改成 utf8。 
[root@sfs3 mysql]# sed -r 's/latin1/utf8/g' sfs3.sql > sfs3_utf.sql
再將 utf8 編碼的資料庫重新匯入。 
[root@sfs3 mysql]# mysql --user=root --password=rootpw --default-character-set=utf8 sfs3 < sfs3_utf.sql


會亂碼 @@