MySQL数据库中不同数据类型字段关联后结果居然有这么大差异？

点击上方蓝字关注我

在数据库的世界里，数据的连接操作是至关重要的。但在处理关联表的字段的数据类型不同时，得到的结果经常会出乎预料。

1.  案例

1.1 数据库中先创建表及数据

-- 创建tb1
CREATE TABLE tb1 (
  id BIGINT NOT NULL PRIMARY KEY, NAME VARCHAR (20)
);




INSERT INTO tb1 (id, NAME)
VALUES
  (1459066134882947196, 'na1'), (1459066134882947172, 'cccb'), (1459066134882947163, 'tttttttn'), (1459066134882947198, 'acqada');
 
--  创建tb2
CREATE TABLE tb2 (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, pid VARCHAR (20), c1 VARCHAR (10)
);


INSERT INTO tb2 (pid, c1)
VALUES
  ('1459066134882947196', 'cs'), (1459066134882947197, 'tt');

tb1 的id表为bigint,tb2表pid字段类型为varchar

1.2  进行左连接查询

SELECT  a.id,b.pid 
FROM  tb1 a  LEFT JOIN tb2 b 
ON a.id=b.`pid`
WHERE a.id =1459066134882947196

查询结果如下：

结果为非预期，因为2个表的关联字段的内容并不相同

1.3  使用内连接

SELECT  a.id,b.pid 
FROM  tb1 a   JOIN tb2 b 
ON a.id=b.`pid`
WHERE a.id =1459066134882947196

使用内连接后，结果也不正确

1.4  不加where条件的左连接

SELECT  a.id,b.pid 
FROM  tb1 a   LEFT JOIN tb2 b 
ON a.id=b.`pid`

查询结果如下：

关联后确实是非预期的结果

1.5 不加where条件的内连接

SELECT  a.id,b.pid 
FROM  tb1 a    JOIN tb2 b 
ON a.id=b.`pid`

查询结果为：

此时不加where条件的内连接的结果却是正确的

2. 解决方案

解决此问题的方法主要是解决两个关联字段的类型不同的问题，可以有2种方式

2.1  显式类型转换

在关联的时候显式地进行字段类型转换，例如：

SELECT  a.id,b.pid FROM  tb1 a LEFT JOIN tb2 b 
ON CAST(a.`id`  AS  CHAR)=b.`pid`
WHERE a.id=1459066134882947196

结果如下：

此时结果正确。
内连接结果也正确

SELECT  a.id,b.pid 
FROM  tb1 a    JOIN tb2 b 
ON CAST(a.`id`  AS  CHAR)=b.`pid`
WHERE a.id =1459066134882947196

2.2 改变字段类型（推荐）
如果两张表的数据量较大，使用显式的字段类型转换（包括当前隐式字段类型转换）都将导致关联时不能使用索引，影响性能。因此建议在表设计时就将存在关联关系的字段类型设置为类型相同（字符类型时字符集及排序规则也一致）
例如:

ALTER TABLE  tb2 MODIFY pid BIGINT;

修改后再查询看一下结果：

SELECT  a.id,b.pid 
FROM  tb1 a   LEFT JOIN tb2 b 
ON a.`id`=b.`pid`
WHERE a.id =1459066134882947196

结果正确：

3. 小结

此情况的出现是因为两表的关联字段类型不同时进行字段类型转换导致。bigint与varchar转换过程中字段精度出现问题，实际超过int最大值的数据（2147483647,即2^31 - 1）的数据被截断为2^31 - 1处理，因为两表进行左关联时，存在异常。

从上面的过程中，也发现左连接过程与内连接的过程中的中间数据结果（1.4及1.5中）也不同。

往期精彩回顾

1.  MySQL高可用之MHA集群部署

2.  mysql8.0新增用户及加密规则修改的那些事

3.  比hive快10倍的大数据查询利器-- presto

4.  监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5.  PostgreSQL主从复制--物理复制

6.  MySQL传统点位复制在线转为GTID模式复制

7.  MySQL敏感数据加密及解密

8.  MySQL数据备份及还原（一）

9.  MySQL数据备份及还原（二）

扫码关注