系统Centos
编码
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
原表数据正常utf8mysql> select * from cep.temp1 limit 5;
+--------+-----------+
| code | name |
+--------+-----------+
| 110000 | 北京市 |
| 110100 | 市辖区 |
| 110101 | 东城区 |
| 110102 | 西城区 |
| 110105 | 朝阳区 |
+--------+-----------+
5 rows in set (0.00 sec)数据复制后就乱码了。
mysql> insert into cep.area (code ,name) select code,name from cep.temp1;
Query OK, 3507 rows affected, 3507 warnings (0.15 sec)
Records: 3507 Duplicates: 0 Warnings: 0mysql> select * from cep.area limit 5;
+--------+---------+-------+-------------+
| code | name | level | parent_code |
+--------+---------+-------+-------------+
| 0086 | Chinese | 1 | 0 |
| 640402 | ??? | NULL | NULL |
| 640401 | ??? | NULL | NULL |
| 640400 | ??? | NULL | NULL |
| 640381 | ???? | NULL | NULL |
+--------+---------+-------+-------------+
5 rows in set (0.00 sec)两张表的编码也没问题。 show create table cep.temp1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| temp1 | CREATE TABLE `temp1` (
`code` varchar(8) NOT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
CREATE TABLE `area` (
`code` varchar(8) CHARACTER SET latin1 NOT NULL,
`name` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
`level` char(1) CHARACTER SET latin1 DEFAULT NULL,
`parent_code` varchar(8) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |我都没有直接写入中字,它内部复制也会出现这种情况,真不搞不懂。
求大神指点指点啊……MySQL中文乱码
编码
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
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 | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
原表数据正常utf8mysql> select * from cep.temp1 limit 5;
+--------+-----------+
| code | name |
+--------+-----------+
| 110000 | 北京市 |
| 110100 | 市辖区 |
| 110101 | 东城区 |
| 110102 | 西城区 |
| 110105 | 朝阳区 |
+--------+-----------+
5 rows in set (0.00 sec)数据复制后就乱码了。
mysql> insert into cep.area (code ,name) select code,name from cep.temp1;
Query OK, 3507 rows affected, 3507 warnings (0.15 sec)
Records: 3507 Duplicates: 0 Warnings: 0mysql> select * from cep.area limit 5;
+--------+---------+-------+-------------+
| code | name | level | parent_code |
+--------+---------+-------+-------------+
| 0086 | Chinese | 1 | 0 |
| 640402 | ??? | NULL | NULL |
| 640401 | ??? | NULL | NULL |
| 640400 | ??? | NULL | NULL |
| 640381 | ???? | NULL | NULL |
+--------+---------+-------+-------------+
5 rows in set (0.00 sec)两张表的编码也没问题。 show create table cep.temp1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| temp1 | CREATE TABLE `temp1` (
`code` varchar(8) NOT NULL,
`name` varchar(32) DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
CREATE TABLE `area` (
`code` varchar(8) CHARACTER SET latin1 NOT NULL,
`name` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
`level` char(1) CHARACTER SET latin1 DEFAULT NULL,
`parent_code` varchar(8) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |我都没有直接写入中字,它内部复制也会出现这种情况,真不搞不懂。
求大神指点指点啊……MySQL中文乱码
set names utf8;或者set names gbk;试试
mysql> select * from cep.temp1 limit 5;
select hex(col1) from tb如果是3F则确实是? 字符集没有转化过来
大哥,这还真是转化出了问题呀mysql> select hex(name) from cep.temp1 limit 5;
+--------------------+
| hex(name) |
+--------------------+
| E58C97E4BAACE5B882 |
| E5B882E8BE96E58CBA |
| E4B89CE59F8EE58CBA |
| E8A5BFE59F8EE58CBA |
| E69C9DE998B3E58CBA |
+--------------------+
5 rows in set (0.00 sec)mysql> select hex(name) from cep.area limit 5;
+----------------+
| hex(name) |
+----------------+
| 4368696E657365 |
| 3F3F3F |
| 3F3F3F3F |
| 3F3F3F3F |
| 3F3F3F3F |
+----------------+
5 rows in set (0.00 sec)大哥这种情况怎么解决呀?
我全部在Centos 上操作也是一样的结果啊
mysql> insert into cep.area (code ,name) select code,name from cep.temp1;
Query OK, 3507 rows affected, 3507 warnings (0.15 sec)
Records: 3507 Duplicates: 0 Warnings: 0
mysql> select * from cep.temp1 limit 5;
+--------+-----------+
| code | name |
+--------+-----------+
| 110000 | 北京市 |
| 110100 | 市辖区 |
| 110101 | 东城区 |
| 110102 | 西城区 |
| 110105 | 朝阳区 |
+--------+-----------+
5 rows in set (0.00 sec)mysql> select hex(name) from cep.temp1 limit 5;
+--------------------+
| hex(name) |
+--------------------+
| E58C97E4BAACE5B882 |
| E5B882E8BE96E58CBA |
| E4B89CE59F8EE58CBA |
| E8A5BFE59F8EE58CBA |
| E69C9DE998B3E58CBA |
+--------------------+
5 rows in set (0.00 sec)
`code` varchar(8) CHARACTER SET latin1 NOT NULL,
`name` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
`level` char(1) CHARACTER SET latin1 DEFAULT NULL,
`parent_code` varchar(8) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
drop 表后重建就可以了。原因分析:
这张area表,在建表时是默认的 latin1 编码。然后我在里面插入了中文数据,程序中发现取出是乱码后,直接对数据库及表 执行了alter 操作。 空表执行后没问题,这张表有中文的表不知是不是出于对数据的保护,还是保留了原来的编码储存,但其表参数中显示为修改后的utf8编码,导致我们误认为修改成功。