next up previous contents
Next: OJS 編碼設定 Up: 編碼轉換 Previous: 起源   Contents

MySQL 編碼

  1. 先設定 mysqld 服務的編碼
    [root@csie5 ~]# 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@csie5 ~]# /etc/init.d/mysqld restart
    
  3. 登入 mysql 查詢 charset 變數,資料庫編碼為 character_set_database latin1
    [root@csie5 ~]# mysql -uroot -p
    Enter password: 
    
    mysql> use ijait;
    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)
    
  4. 使用 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
    --------------
    
  5. 設定資料庫預設編碼為 utf8
    mysql> ALTER DATABASE ijait CHARACTER SET utf8 COLLATE utf8_general_ci;
    
  6. 使用 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
    --------------
    
  7. 雖然資料庫編碼已改為 utf8,但其中的資料表還是原先的 latin1 編碼,為一次轉碼,先以預設編碼 latin1 匯出資料庫,資料庫中的中文字串會以系統預設的 utf8 編碼呈現。
    [root@csie5 ~]# cd /var/lib/mysql/
    [root@csie5 mysql]# mysqldump --user=root --password=rootpw --default-character-set=latin1 --skip-set-charset ijait > ijait.sql
    
  8. 將資料庫中的 latin1 字串改成 utf8。
    [root@csie5 mysql]# sed -r 's/latin1/utf8/g' ijait.sql > ijait_utf.sql
    
  9. 再將 utf8 編碼的資料庫重新匯入。
    [root@csie5 mysql]# mysql --user=root --password=rootpw --default-character-set=utf8 ijait < ijait_utf.sql
    
  10. 資料庫雖已完全改成 utf8 編碼,OJS 網頁中中文顯示還是出現 '?????',故必須設定 OJS。



2016-10-25