其中
- COLLATION_CONNECTION 当前连接的校对 - COLLATION_DATABASE 当前日期的默认校对。每次使用USE语句来“跳转”到另一个数据库时，这个变量就会改变。 - COLLATION_SERVER 服务器默认校对 Database 1、查看某数据库的字符集方法：show create database DatabaseName；
mysql> show create database zjtravel; +----------+-------------------------------------------------------------------+ | Database | Create Database | +----------+-------------------------------------------------------------------+ | zjtravel | CREATE DATABASE `zjtravel` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-------------------------------------------------------------------+ 1 row in set (0.00 sec) Table 1、数据库表的字符集设置方法：show create table TableName；
显示一： mysql> show create table destination; +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | destination | CREATE TABLE `destination` ( `id` int(10) NOT NULL AUTO_INCREMENT, `pid` int(10) NOT NULL DEFAULT '0', `name` varchar(20) NOT NULL, ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 | +-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 显示二： mysql> show create table destination \G *************************** 1. row *************************** Table: destination Create Table: CREATE TABLE `destination` ( `id` int(10) NOT NULL AUTO_INCREMENT, `pid` int(10) NOT NULL DEFAULT '0', `name` varchar(20) NOT NULL, `subarea` varchar(1000) DEFAULT NULL, ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) column 1、数据库表中字段的字符集设置 显示一： mysql> show full columns from destination; +------------+---------------+-----------------+------+-----+---------------------+-----------------------------+---------------------------------+--------------------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+---------------+-----------------+------+-----+---------------------+-----------------------------+---------------------------------+--------------------+ | id | int(10) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | pid | int(10) | NULL | NO | MUL | 0 | | select,insert,update,references | | | name | varchar(20) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | | subarea | varchar(1000) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +------------+---------------+-----------------+------+-----+---------------------+-----------------------------+---------------------------------+--------------------+ 4 rows in set (0.00 sec) 显示二： mysql> show full columns from destination \G *************************** 1. row *************************** Field: id Type: int(10) Collation: NULL Null: NO Key: PRI Default: NULL Extra: auto_increment Privileges: select,insert,update,references Comment: *************************** 2. row *************************** Field: pid Type: int(10) Collation: NULL Null: NO Key: MUL Default: 0 Extra: Privileges: select,insert,update,references Comment: *************************** 3. row *************************** Field: name Type: varchar(20) Collation: utf8_general_ci Null: NO Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: *************************** 4. row *************************** Field: subarea Type: varchar(1000) Collation: utf8_general_ci Null: YES Key: Default: NULL Extra: Privileges: select,insert,update,references Comment: 4 rows in set (0.00 sec) 修改默认字符集 1、修改mysql的my.cnf文件中的字符集键值 编辑配置文件 #vi /etc/my.cnf [mysqld] default-character-set = utf8 character_set_server = utf8 collation-server=utf8_bin 更新编辑完成之后，重启mysql服务器使其生效。 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 ;使其快速设置成相应的字符集，但重启之后会变成之前系统默认字符集。
问题追踪