设置MySQL客户端毗邻使用的字符集_数据库误删了
日期:2014-07-13 / 人气: / 来源:网络
SET NAMES 'charset_name';
SET CHARACTER SET charset_name;
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET character_set_connection = x;
将x设置为character_set_connection也就设置了collation_connection是x的默认校对规则。
mysql> SET character_set_client = x;
mysql> SET character_set_results = x;
mysql> SET collation_connection = @@collation_database;
[mysql]
default-character-set=koi8r
例如:假设column1定义为CHAR(5) CHARACTER SET latin2。如果没有设定SET NAMES或SET CHARACTER SET,那么对于SELECT column1 FROM t,当连接后,服务器使用客户端指定的字符集(即character_set_client声明的字符集)返回列column1的所有值。另一方面,如果你设定SET NAMES 'latin1'或SET CHARACTER SET latin1,那么发送结果之前,服务器转换latin2值到latin1。转换可能会丢失那些不属于两种字符集的字符。如果不希望服务器执行任何转换,设置character_set_results为NULL:
mysql> SET character_set_results = NULL;
C:Documents and Settingsshengtong>chcp
活动的代码页: 936
mysql> create table t (a varchar(100),b varchar(20),c int) default character set=gbk;
Query OK, 0 rows affected (0.06 sec)
mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values('中','gbk',1);
Query OK, 1 row affected (0.05 sec)
mysql> select a,b,c,hex(a),hex('中') from t;
------ ------ ------ -------- -----------
| a
------ ------ ------ -------- -----------
| 中
------ ------ ------ -------- -----------
1 row in set (0.00 sec)
这里可以清楚的看到表t正确的存储了汉字“中”,同时也表名了“中”的gbk编码为D6D0。解释一下这个过程,客户端字符集是gbk,然后其通过set names想server声明其字符集为gbk,并且底层表的字符集也是gbk,故一路下来在字符串的穿梭中没有发生任何字符集的转换,其最终在表t中正确的存储了汉字中,并且是正确的编码D6D0。查询时,同样字符串从存储引擎到server到客户端的传递游离中并没有发生字符集的转换,一切都可以正确显示出来。
mysql> set names 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values('中','latin1',2);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> show warnings;
--------- ------ ------------------------------------------------------------
| Level
--------- ------ ------------------------------------------------------------
| Warning | 1366 | Incorrect string value: 'xD6xD0' for column 'a' at row 1 |
--------- ------ ------------------------------------------------------------
1 row in set (0.00 sec)
mysql> select a,b,c,hex(a),hex('中') from t;
------ -------- ------ -------- -----------
| a
------ -------- ------ -------- -----------
| ?
| ??
------ -------- ------ -------- -----------
2 rows in set (0.00 sec)
mysql> create table t1 ( a varchar(10)) default character set=utf8;
Query OK, 0 rows affected (0.06 sec)
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values('中');
Query OK, 1 row affected (0.00 sec)
mysql> select _latin1 x'D6';
---------------
| _latin1 x'D6' |
---------------
| ?
---------------
1 row in set (0.01 sec)
mysql> select _latin1 x'D0';
---------------
| _latin1 x'D0' |
---------------
| ?
---------------
1 row in set (0.00 sec)
mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)
mysql> select hex('?');
----------
| hex('?') |
----------
| 3F
----------
1 row in set (0.03 sec)
mysql> set names 'latin1';
Query OK, 0 rows affected (0.00 sec)
mysql> select hex('?');
----------
| hex('?') |
----------
| 3F
----------
1 row in set (0.00 sec)
mysql> set names 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values('中','utf8',3);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
--------- ------ ------------------------------------------------------------
| Level
--------- ------ ------------------------------------------------------------
| Warning | 1366 | Incorrect string value: 'xD6xD0' for column 'a' at row 1 |
--------- ------ ------------------------------------------------------------
1 row in set (0.14 sec)
mysql> select a,b,c,hex(a),hex('中') from t;
------ -------- ------ -------- -----------
| a
------ -------- ------ -------- -----------
| 涓? | gbk
| ??
| ?
------ -------- ------ -------- -----------
3 rows in set (0.00 sec)
mysql> select _utf8 x'D6D0D3';
ERROR 1300 (HY000): Invalid utf8 character string: 'D6D0D3'
mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)
mysql> select hex('中');
-----------
| hex('中') |
-----------
| D6D0
-----------
1 row in set (0.00 sec)
mysql> set character_set_client=latin1;
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_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select hex('中');
-----------
| hex('??') |
-----------
| C396C390
-----------
1 row in set (0.02 sec)
mysql> set character_set_results=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select hex('中');
-----------
| hex('中') |
-----------
| C396C390
-----------
1 row in set (0.00 sec)
mysql> set character_set_client=latin1;
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_results=latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select '中';
----
| 中 |
----
| 中 |
----
1 row in set (0.00 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> select '中';
------
| �� |
------
| �� |
------
1 row in set (0.00 sec)
mysql> set character_set_results=gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> select '中';
----
| ?? |
----
| ?? |
----
1 row in set (0.00 sec)
mysql> set character_set_results=null;
Query OK, 0 rows affected (0.00 sec)
mysql> select '中';
------
| �� |
------
| �� |
------
1 row in set (0.00 sec)
MySQL数据库主从复制、主主复制和半同步等架构的配置方法
mysql的数据同步功能,不仅在一定程度上提供数据库查询时的负载均衡,而且为实现数据库的冗灾、备份、恢复、负载均衡等都是有极大帮助。而数据的同步功能可以通过主从复制来实现,而主从复制是异步进行的,并且mysql仅支持一主多从,不支持一从多主的复制模型
MySQL数据库
作者:管理员
推荐内容 Recommended
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19
相关内容 Related
- 江苏飞浩信息科技期待您的加入07-20
- 江苏飞浩科技欢迎您07-19