join结果不对

【 TiDB 使用环境】
测试环境版本是:5.7.25-TiDB-v4.0.6,生产环境版本是:5.7.25-TiDB-v4.0.7
补充一点:测试环境 安装的时候 默认没有启用 新的排序规则(Collation),生产环境安装的时候故意开启了新的排序规则。

【概述】 场景 + 问题概述
同一个sql,测试环境和生产环境数据一样,测试环境能查出结果,生产环境查不出结果,初步排查是因为在生产环境join关联不到。关联字段加个trim(也就是:ON trim(a.digit_identity) = trim(b.channelname))可以关联查询出来,但是加trim性能太差。尝试trim之后更新字段的方式,还是join不到。sql如下:
SELECT
b.time,
b.channelvalue,
a.id AS “id”,
a.equipment_code AS “equipmentCode”,
a.digit_name AS “digitName”,
a.digit_identity AS “digitIdentity”,
a.data_type AS “dataType”,
a.value_upperlimit AS “valueUpperlimit”,
a.value_lowerlimit AS “valueLowerlimit”,
a.digit_type_id AS “digitTypeId”,
a.digit_type_name AS “digitTypeName”,
a.energy_type_id AS “energyTypeId”,
a.energy_type_name AS “energyTypeName”,
a.description AS “description”,
a.create_by AS “createBy.id”,
a.create_date AS “createDate”,
a.update_by AS “updateBy.id”,
a.update_date AS “updateDate”,
a.del_flag AS “delFlag”,
a.unit_id AS “unitId”,
a.unit AS “unit”,
a.fault_def_id AS “faultDefId”,
a.fault_def_name AS “faultDefName”,
a.is_monitored AS “isMonitored”,
a.icon_url AS “iconUrl”,
a.is_trend_monitored AS “isTrendMonitored”,
a.is_data_distribute AS “isDataDistribute”,
a.plc_datatype AS “plcDatatype”,
a.plc_address AS “plcAddress”,
a.plc_bit AS “plcBit”,
a.data_source AS “dataSource”,
a.formula_str AS “formulaStr”,
a.parent_identity AS “parentIdentity”,
a.parent_identity_full AS “parentIdentityFull”,
a.gateway_identity_id AS “gatewayIdentityId”
FROM
t_equipment_digit a
JOIN eventlog b ON a.digit_identity = b.channelname
WHERE
a.del_flag = 0
AND a.equipment_code = ‘GANTEN(JX)-DPLZ2’
AND a.digit_type_id = ‘8d2b235e-ba55-11e8-816f-000c291a2998’

ORDER BY
b.time DESC
LIMIT 1;

2赞

是否有可复现的sql测试数据?

1赞

有,但是要数据量比较大,我要弄一部分出来才可以。测试环境 安装的时候 默认没有启用 新的排序规则(Collation),生产环境安装的时候故意开启了新的排序规则。不知道是否跟这个排序规则有关?

1赞

只要是能报错的记录就可以了,不需要所有数据,感觉应该和数据量没关系

1赞

你说的是这种情况么?

1赞

估计原因跟这个类似。现在应该怎么处理?

1赞

我看了下两张表的collation确实不一样,然后我通过alter table eventlog COLLATE=‘utf8_general_ci’ 修改了其中一张表的collation,sql执行结果还是join不出来

1赞

在这个帖子里,贴主有说把数据的collation改为一致,这个怎么改的? 我只改了表定义的collation

1赞

DDL 是非阻塞的,需要让子弹飞一会,才会有结果

等俩表的collation 一样了,在试试

1赞

搞定了,只改表定义的collation还不行,要改字段的collation才可以。

4赞

感谢分享:handshake:

1赞