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