MYSQL字符集

MYSQL经常会出现一些乱码现象，大多数情况是字符集设置造成的。下面简要说明下

1. 查看数据使用的字符集 (DATABASE)
   

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       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

character_set_client 客户端字符集

character_set_database 数据库字符集

       2. 查看特定表的字符集(TABLE)

         mysql> SHOW TABLE STATUS FROM db_xx LIKE 'table_xx';
         +---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| table_xx | MyISAM |      10 | Dynamic    |    1 |             20 |          20 | 281474976710655 |         2048 |         0 |              2 | 2013-08-23 11:01:18 | 2013-08-23 11:01:18 | NULL       | latin1_swedish_ci |     NULL |                |         |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
     3 . 查看数据列字符集(COLUMN)

     mysql> SHOW FULL COLUMNS FROM table_xx;
+------------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field            | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+------------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id         | int(19)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name           | varchar(200) | latin1_swedish_ci | NO   |     | NULL    |                | select,insert,update,references |         |
| presence         | int(1)       | NULL              | NO   |     | 1       |                | select,insert,update,references |         |
| picklist_valueid | int(19)      | NULL              | NO   |     | 0       |                | select,insert,update,references |         |
+------------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+

    4. 修改字符集

   1) 全局变量

       # vi /etc/my.cnf

     [client] 下增加
     default-character-set=utf8

     [mysqld]下增加

     default-character-set=utf8

    #sudo  /etc/init.d/mysqld restart

  2) DATABASE

   mysql> ALTER DATABASE db_xx CHARACTER SET UTF8;

  3) TABLE

  mysql> ALTER TABLE table_xx CONVERT TO CHARACTER SET UTF8;

  4) COLUMN
   mysql> ALTER TABLE table_xx   MODIFY COLUMN column_xx  VARCHAR(255) CHARACTER SET UTF8 NOT NULL;