字符集是我们必须面对的,稍不留神就会出现乱码;有两个概念,一个是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