Mysql字符集

字符集是我们必须面对的，稍不留神就会出现乱码；有两个概念，一个是Character Sets，一个是Collations，前者是字符内容及编码，后者是对前者进行比较操作的一些规则

可以在数据库实例，数据库，表，列修改字符集

在源码安装时候要注意指定字符集，默认是latin1，而二进制默认是utf8

default-character-set是默认建库的字符集，注意，后来更新为character-set-server

客户端连接相关的字符集邮三个character_set_client，character_set_results,character_set_connection

查看数据库字符集

mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.04 sec)

mysql> show variables like 'character%';
+--------------------------+----------------------------------+
| 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/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)

表字符集

mysql> show create table t1\G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

列字符集

mysql> show full columns from t1;  
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type        | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id    | int(11)     | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| name  | varchar(10) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------+-------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.00 sec)

配置文件修改字符集

[client]
character-set-server=utf8

[mysql]
character-set-server=utf8

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8' #客户端连接时候会触发
character-set-server=utf8

命令修改字符集

mysql> set character_set_client=utf8
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> set character_set_connection=utf8;
Query OK, 0 rows affected (0.00 sec)

修改数据库字符集

mysql> alter database t default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> alter database t default character set=utf8;
Query OK, 1 row affected (0.00 sec)

mysql> create database d2 default character set utf8;

表字符集

mysql> alter table t1 default character set utf8;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 convert to character set utf8;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

批量生成修改脚本-表：

SELECT a.TABLE_TYPE,CONCAT('alter TABLE ',A.TABLE_NAME,' default character set = utf8;')  
FROM INFORMATION_SCHEMA.TABLES A
WHERE A.TABLE_SCHEMA='DB1' AND a.TABLE_TYPE='BASE TABLE';

批量生成脚本-列：

SELECT CONCAT(CONCAT(CONCAT('alter TABLE ',c.TABLE_NAME,' modify'),CONCAT(' ',C.COLUMN_NAME,' '),C.COLUMN_TYPE),' ',' character set gb2312 COLLATE gb2312_chinese_ci;') AS CLOU
 FROM  INFORMATION_SCHEMA.COLUMNS C,INFORMATION_SCHEMA.TABLES A
 WHERE c.TABLE_SCHEMA='DB1'
   AND A.TABLE_NAME=c.TABLE_NAME
   AND A.TABLE_TYPE='BASE TABLE'
   AND c.DATA_TYPE='varchar'

其他注意事项：

另外需要注意的是操作系统字符集和客户端工具的字符集：

比如linux操作系统字符集查看修改：

[root@oracledb ~]#  echo $LANG
zh_CN.GB18030

root@oracledb ~]# export LANG=en_US.UTF-8