Linux下MySQL中文乱码的问题

Question

我使用的是树莓派，源于Debian的

mysql的字符集设置如下

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| 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/ |
+--------------------------+----------------------------+

当前数据库字符集

mysql> show create database attic_db;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| attic_db | CREATE DATABASE `attic_db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.01 sec)

表字符集

mysql> show create table video;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| video | CREATE TABLE `video` (
  `vid` int(11) NOT NULL AUTO_INCREMENT,
  `chinesename` text CHARACTER SET latin1,
  `englishname` text CHARACTER SET latin1,
  `tag` text CHARACTER SET latin1,
  `timelength` time DEFAULT NULL,
  `size` int(11) DEFAULT NULL,
  `path` text CHARACTER SET latin1,
  `total` int(11) DEFAULT NULL,
  `filmmaker` text CHARACTER SET latin1,
  `actor` text CHARACTER SET latin1,
  `country` text CHARACTER SET latin1,
  `language` text CHARACTER SET latin1,
  `type` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
  `premiered` timestamp NULL DEFAULT NULL,
  `imdbcode` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `approve` int(11) DEFAULT NULL,
  `object` int(11) DEFAULT NULL,
  `showpicpath` text CHARACTER SET latin1,
  `uploadtime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`vid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

使用navicat for mysql 勾选使用MySQL字符集  65001(UTF-8)

插入中文件，navicat中显示为乱码，mysql查询也是乱码，设置set names utf8，依然是乱码。

使用命令update修改字段内容为中文，仍为乱码

mysql>  update video set chinesename="中文名" where vid=1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select chinesename from video where vid=1;
+-------------+
| chinesename |
+-------------+
| ???         |
+-------------+

以上都是utf8编码，所以我不知道哪里忽略了，还有一个问题，我不清楚系统的字符集会不会影响到mysql

root@Attic:~# locale -a
C
C.UTF-8
en_GB.utf8
POSIX

但是可以创建以中文命名的文件，不会乱码。

谁有好的解决方法吗，多谢分享。

Answer 1

已经设置过了，是按照上面“如何修复”后面的链接操作的

Answer 2

回复
`chinesename` text CHARACTER SET latin1, -- 看下你的表结构

Answer 3

回复
就是上面那个图

Answer 4

回复
让你自己检查下 表结构中的character.....

Answer 5

回复
上面的图很清楚，我也圈出来了，就这字段character的问题，已经改过了。

Answer 6

set character_set_filesystem = utf8

Answer 7

putty的重点编码设置是在终端那里，不是设置这个环境变量的。而且你这个zh_CN.UTF-8在远程的服务器也没有这个字符集

Answer 8

引用来自“Feng_Yu”的评论

这个问题像是你的终端字符集不是utf8编码。如果是第三方终端，确认你的终端编码设置为utf8.

如果是Linux下的终端远程连接，确认远程服务器的终端编码有utf8编码，并且已经启用。

locale看一下编码，locale -a只是看本地所有可用编码而已

Answer 9

这个问题像是你的终端字符集不是utf8编码。如果是第三方终端，确认你的终端编码设置为utf8.

如果是Linux下的终端远程连接，确认远程服务器的终端编码有utf8编码，并且已经启用。

locale看一下编码，locale -a只是看本地所有可用编码而已

Answer 10

引用来自“Feng_Yu”的评论

这个问题像是你的终端字符集不是utf8编码。如果是第三方终端，确认你的终端编码设置为utf8.

如果是Linux下的终端远程连接，确认远程服务器的终端编码有utf8编码，并且已经启用。

locale看一下编码，locale -a只是看本地所有可用编码而已