查看mysql支持的字符集和校验规则
mysql> show character set;
列出字符集对应的校验规则
mysql> Show collation;
查看字符集所对的校对规则
mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)
查看字符集变量
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |#客户端字符集
| character_set_connection | utf8 |#连接字符集
| character_set_database | latin1 |#数据库的字符集
| character_set_filesystem | binary |#文件系统字符集
| character_set_results | utf8 |#返回结果字符集
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.46 sec)
设置字符集
创建数据库设置默认字符集
mysql> create database bzfys_gbk default character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.22 sec)
mysql> create database bzfys_utf8 default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.04 sec)
服务器字符集和校对规则
在my.cnf中设置:
[mysqld]
charcter-set-server=gbk
或者
在登陆的时候
mysqld --charcter-set-server=gbk
或者
在编译的时候
cmake . --DDEFAULT_CHARSET=GBK
数据库字符集和校对规则
在my.cnf中设置:
[mysqld]
charcter-set-database=gbk
或者
mysql> set character_set_database=gbk;
Query OK, 0 rows affected (0.00 sec)
表字符集和校对规则
mysql>CREATE TABLE `t2` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`str_number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=526325 DEFAULT CHARSET=latin1
设置客户端连接字符集
character_set_client:客户端字符集
character_set_connection:连接字符集
character_set_results:返回字符集
但一般不会单独设置它们一般会通过
set names gbk;来设置
查看默认字符集和校对规则
查看服务器的字符集和校队规则
查看数据库字符集
mysql> show variables like 'character_set_server';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | gbk |
+----------------------+-------+
1 row in set (0.00 sec)
查看数据库字符集校对规则
mysql> show variables like 'collation_server';
+------------------+----------------+
| Variable_name | Value |
+------------------+----------------+
| collation_server | gbk_chinese_ci |
+------------------+----------------+
1 row in set (0.00 sec)
数据库字符集和校对规则
mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.00 sec)
mysql> show variables like 'collation_database';
+--------------------+-------------------+
| Variable_name | Value |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)
表字符集和校对规则
mysql> show create table t3\g;
+-------+------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`str_number` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
如果已经有了数据,那么怎么修改字符集
1、导出表结构,而不导出数据(-d表示只导出表结构,--default-character-set=gbk说明用什么字符集连接)
[root@localhost ~]# mysqldump -uroot -p123456 --default-character-set=gbk -d test2 >/back/createtable.sql
Warning: Using a password on the command line interface can be insecure.
2、确保记录不再更新导出数据
[root@localhost ~]# mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 test2 >data.sql
Warning: Using a password on the command line interface can be insecure.
--quick:转储大的表,强制mysqldump从服务器一次一行地检索表中的行
--no-create-info:不到处每个转储表的create table语句
--extended-insert:使用包括几个values列表的多行insert语法。
--default-character-set=latin1:按照原有的字符集导出所有的数据。