mysql字符集相关汇总

1.修改服务器级 a. 临时更改： mysql>SET GLOBAL character_set_server=utf8; b. 永久更改：shell>vi /etc/my.cnf[mysqld]default-character-set=utf8


2.修改数据库级 a. 临时更改： mysql>SET GLOBAL character_set_database=utf8; b. 永久更改：改了服务器级就可以了


3.修改表级 mysql>ALTER TABLE table_name DEFAULT CHARSET utf8; 更改了后永久生效


4.修改列级修改示例： mysql>ALTER TABLE `products` MODIFY `products_model` `products_model` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL; 更改了后永久生效 


5.更改连接字符集 a. 临时更改：mysql> SET NAMES utf8;b. 永久更改： shell>vi /etc/my.cnf在[client]中增加：default-character-set=utf8




查看服务器和客户端字符集 
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 | D:\MySQL\MySQL 5.0\share\charsets\ | 
+--------------------------+------------------------------------+ 
8 rows in set (0.09 sec) 


mysql> show variables like "%colla%"; 
+----------------------+-----------------+ 
| Variable_name | Value | 
+----------------------+-----------------+ 
| collation_connection | utf8_general_ci | 
| collation_database | utf8_general_ci | 
| collation_server | utf8_general_ci | 
+----------------------+-----------------+ 
3 rows in set (0.00 sec) 


查看数据库的字符集 
mysql> show create database test; 
+----------+---------------------------------------------------------------+ 
| Database | Create Database | 
+----------+---------------------------------------------------------------+ 
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | 
+----------+---------------------------------------------------------------+ 
1 row in set (0.00 sec) 


查看表的字符集,包括各个字段的字符集,如果各字段没有标明,表示与表的字符集一致 
mysql> show create table books \G 
*************************** 1. row *************************** 
Table: books 
Create Table: CREATE TABLE `books` ( 
`id` varchar(8) NOT NULL default '', 
`name` varchar(24) NOT NULL default '', 
`title` varchar(96) NOT NULL default '', 
`price` float NOT NULL default '0', 
`yr` int(4) unsigned NOT NULL default '0', 
`description` varchar(30) NOT NULL default '', 
`saleAmount` int(10) unsigned NOT NULL default '0', 
PRIMARY KEY (`id`) 
) ENGINE=MyISAM DEFAULT CHARSET=gbk 
1 row in set (0.01 sec)  






通过MySQL命令行修改:


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> set character_set_database=utf8;
Query OK, 0 rows affected (0.00 sec)


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


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


mysql> set character_set_system=utf8;
Query OK, 0 rows affected (0.01 sec)


mysql> set collation_connection=utf8;
Query OK, 0 rows affected (0.01 sec)


mysql> set collation_database=utf8;
Query OK, 0 rows affected (0.01 sec)


mysql> set collation_server=utf8;
Query OK, 0 rows affected (0.01 sec)




查看:
mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)


mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+






        1) 查看当前mysql数据库支持的字符集
mysql> show character set;


        2) 查看对应字符集可用的校对规则
mysql> show collation like 'gb%';
mysql> show collation like 'utf8%';


        3) 查看当前mysql服务器使用的默认字符集状态
mysql> status
……
Server characterset:  utf8
Db     characterset:  utf8
Client characterset:  utf8
Conn.  characterset:  utf8


        4) 在创建数据库时指定使用的默认字符集：
mysql> create database testdb default character set utf8;
mysql> show create database testdb;     //验证数据库建立信息


        5) 在创建数据表时指定使用的默认字符集：
mysql> create table testdb.tb1 ( id int(10)  NOT NULL AUTO_INCREMENT,name varchar(15) NOT NULL default '',PRIMARY KEY  (id) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8;
mysql> show create table testdb.tb1;     //验证数据表建立信息


        6) 在mysql>客户端环境中设置使用的默认字符集
mysql> set NAMES utf8;


帮助查看
 mysql --verbose --help|grep 'default-character-set'|grep -v name
导入导出设置字符集
mysqldump -uroot --compact --default-character-set=latinl set emp > tst.txt