Mysql 字符集不一致导致连表异常的解决

MySQL字符集不一致可能会导致连表异常、数据显示乱码等问题。为了解决这种问题，我们可以按照以下攻略进行处理：

1.确认 MySQL 当前字符集

我们可以使用以下 SQL 语句来查看 MySQL 当前字符集：

show variables like 'character_set%';

执行结果中，我们需要关注以下变量：

• character_set_client：客户端连接时使用的字符集
• character_set_connection：客户端和服务端之间通信所采用的字符集
• character_set_database：默认的数据库字符集
• character_set_server：MySQL 服务端使用的字符集

通常我们需要确保客户端、服务端、以及各个数据库的字符集均一致。

2.确认表结构和数据字符集

我们可以使用以下 SQL 语句来查看表结构和数据信息以及所用的字符集：

SHOW CREATE TABLE table_name;
SELECT column_name, CHARACTER_SET_NAME(column_type) FROM information_schema.COLUMNS WHERE table_name = 'table_name';
SELECT column_name, CHARSET(CONVERT(column_name USING gbk)) FROM table_name;

在执行以上语句时，如果有不同字符集的表，需要确认字段所用字符集是否与表的默认字符集一致。

例如，在一个包含中文的表中，当我们使用以下 SQL 语句时：

SELECT * FROM table1 JOIN table2 ON table1.field1 = table2.field2 WHERE table1.field1 = '中文';

当 table1 和 table2 使用不同字符集时，会导致查询异常或者数据乱码。

3.修改字符集

如果发现 MySQL 的字符集不一致，我们可以使用以下方式进行调整：

修改 MySQL 默认字符集：

SET character_set_client = utf8mb4;
SET character_set_connection = utf8mb4;
SET character_set_database = utf8mb4;
SET character_set_server = utf8mb4;

当然，如果只需要对特定的表或者字段进行字符集修改，我们也可以使用以下方式：

-- 修改表字符集
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 修改字段字符集
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

示例

以下是两个示例，用于展示 MySQL 字符集不一致导致连表异常的情况：

示例 1

假设我们有以下两张表：

CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

我们可以发现，test1 表使用的字符集是 gbk，而 test2 表使用的字符集是 utf8mb4。

当我们执行以下 SQL 语句时：

SELECT * FROM test1 JOIN test2 ON test1.name = test2.name;

会发现查询结果为空，整个查询过程出现异常。这是由于两个表采用了不同的字符集，在比较字段名时会出现问题。

要解决这个问题，我们需要将 test1 表的字符集修改为 utf8mb4：

ALTER TABLE `test1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

这样，两个表的字符集就一致了，再执行上述 SQL 语句即可正常查询。

示例 2

假设我们有以下表结构：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `product` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4;

我们可以看到，user 表和 order 表的字符集均为 utf8mb4。

当我们执行以下 SQL 语句时：

SELECT `order`.id, `user`.name, `order`.product
FROM `order`
JOIN `user` ON `order`.user_id = `user`.id
WHERE `user`.name = 'Tom';

会发现查询结果为空，这是因为 user 表中的数据存储时，字符集为 utf8mb4_general_ci，而我们在查询时使用了不同的字符集（例如 utf8_general_ci），导致比较失败，数据无法正确查询。

为了解决这个问题，我们可以在 SQL 语句中将字符集进行指定：

SELECT `order`.id, `user`.name, `order`.product
FROM `order`
JOIN `user` ON `order`.user_id = `user`.id
WHERE `user`.name = CONVERT('Tom' USING utf8mb4);

这样，查询就可以正确执行了。