本文地址：http://www.cnblogs.com/yhLinux/p/4036506.html
在 my.cnf 配置文件中設置相關選項，改變為相應的character set。
設置數據庫編碼(sudo vi /etc/mysql/my.cnf)：
[client] default-character-set = utf8 [mysqld] character-set-server = utf8 collation-server = utf8_general_ci
參考資料：
14.1.4 Character Set
5.1.4 Server System Variables
10.5, “Character Set Configuration”
10.1 Character Set Support
You can specify character sets at the server, database, table, and column level.
10.1.3 Specifying Character Sets and Collations
10.1.3.1 Server Character Set and Collation
MySQL Server has a server character set and a server collation. These can be set at server startup on the command line or in an option file and changed at runtime.
10.1.3.2 Database Character Set and Collation
Every database has a database character set and a database collation.
The character set and collation for the default database can be determined from the values of the character_set_database and collation_database system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server and collation_server.
Specify character settings at server startup. To select a character set and collation at server startup, use the --character-set-server and --collation-server options. For example, to specify the options in an option file, include these lines:
[mysqld] character-set-server=utf8 collation-server=utf8_general_ci
These settings apply server-wide and apply as the defaults for databases created by any application, and for tables created in those databases.
You can force client programs to use specific character set as follows:
[client]
default-character-set=charset_name
This is normally unnecessary. However, when character_set_system differs from character_set_server or character_set_client, and you input characters manually (as database object identifiers, column values, or both), these may be displayed incorrectly in output from the client or the output itself may be formatted incorrectly. In such cases, starting the mysql client with --default-character-set=—that is, setting the client character set to match the system character set—should fix the problem.system_character_set
查看設置結果
更改my.cnf之前：
mysql> SHOW VARIABLES LIKE '%char%'; +--------------------------+---------------------------------------------------------------+ | 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 | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/local/mysql-5.6.21-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------+
設置之後，重啟mysql服務($ sudo service mysql restart)：
mysql> SHOW VARIABLES LIKE '%char%'; +--------------------------+---------------------------------------------------------------+ | 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/mysql-5.6.21-linux-glibc2.5-x86_64/share/charsets/ | +--------------------------+---------------------------------------------------------------+
通過mysql命令修改：
修改數據庫的字符集
mysql>use mydb
mysql>alter database mydb character set utf-8;
創建數據庫指定數據庫的字符集
mysql>create database mydb character set utf-8;
通過配置文件修改:
修改/var/lib/mysql/mydb/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
為
default-character-set=utf8
default-collation=utf8_general_ci
重起MySQL:
修改默認字符集
1、 最簡單的修改方法，就是修改mysql的my.ini文件中的字符集鍵值，
如 default-character-set = utf8
character_set_server = utf8
修改完後，重啟mysql的服務，service mysql restart
使用 mysql> SHOW VARIABLES LIKE 'character%';查看，發現數據庫編碼均已改成utf8
+--------------------------+---------------------------------+
| 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 | D:"mysql-5.0.37"share"charsets" |
+--------------------------+---------------------------------+
2、還有一種修改字符集的方法，就是使用mysql的命令
mysql> SET character_set_client = utf8 ;
mysql> SET character_set_connection = utf8 ;
mysql> SET character_set_database = utf8 ;
mysql> SET character_set_results = utf8 ;
mysql> SET character_set_server = utf8 ;
mysql> SET collation_connection = utf8 ;
mysql> SET collation_database = utf8 ;
mysql> SET collation_server = utf8 ;
3、一般就算設置了表的默認字符集為utf8並且通過UTF-8編碼發送查詢，你會發現存入數據庫的仍然是亂碼。問題就出在這個connection連接層上。解決方法是在發送查詢前執行一下下面這句：
SET NAMES 'utf8';
它相當於下面的三句指令：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
**補充：***
先把數據導出，把數據庫轉換完畢後再把數據導回數據庫
***********
--
以上，希望對你有所幫助。...余下全文>>
