同一个sql 在mysql和tidb 返回结果不一样

Hacker_cBh8J2wj · 2020 年9 月 25 日 08:37

【TiDB 版本】：4.0.2
【问题描述】：

同一个sql ，mysql和tidb返回结果不一样。mysql是正确的。表结构和数据一样。
sql如下：
SELECT b.*
from t_equipment_digit b
join t_equipment c ON c.del_flag = ‘0’ AND c.equipment_code = b.equipment_code
where b.del_flag = ‘0’

表结构如下：

CREATE TABLE t_equipment (
id varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT ‘id’,
equipment_code varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘设备编码’,
equipment_type_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘设备类型id’,
equipment_type_name varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘设备类型名称’,
equipment_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘设备名称’,
equipment_model varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘设备型号’,
production_unit varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘生产产品单位’,
is_monitored char(1) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘是否支持监控’,
factory_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘工厂id’,
factory_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘工厂名称’,
workshop_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘车间id’,
workshop_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘车间名称’,
line_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘生产线id’,
line_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘生产线名称’,
workposition_code varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘工位编号’,
online_time datetime DEFAULT NULL COMMENT ‘上线日期’,
responsible_dept_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘责任部门id’,
responsible_dept_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘责任部门名称’,
responsible_user_ids varchar(500) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘责任人id（可以多选）’,
responsible_user_names varchar(500) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘责任人姓名’,
supplier_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘供应商id’,
supplier_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘供应商名称’,
production_time datetime DEFAULT NULL COMMENT ‘生产日期’,
theoretical_life int(11) DEFAULT NULL COMMENT ‘理论寿命(年)’,
img_url varchar(500) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘设备图片url’,
equipment_status tinyint(4) DEFAULT NULL COMMENT ‘设备状态’,
maintenance_status tinyint(4) DEFAULT NULL COMMENT ‘保养状态’,
create_by varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘创建者’,
create_date datetime DEFAULT NULL COMMENT ‘创建时间’,
update_by varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘更新者’,
update_date datetime DEFAULT NULL COMMENT ‘更新时间’,
del_flag char(1) COLLATE utf8_general_ci DEFAULT ‘0’ COMMENT ‘删除标记’,
unit_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘计量单位id’,
standard_capacity decimal(12,4) DEFAULT NULL COMMENT ‘标准产能’,
PRIMARY KEY (id),
KEY AK_AK_equipment_code (equipment_code),
KEY idx_mutil1 (line_id,equipment_code),
KEY idx_mutil2 (workshop_id,line_id,equipment_code),
KEY idx_multi3 (line_id,equipment_type_id,equipment_code),
KEY idx_multi2 (workshop_id,line_id,equipment_type_id,equipment_code),
KEY idx_multi1 (factory_id,workshop_id,line_id,equipment_type_id,equipment_code),
KEY idx_multi4 (equipment_type_id,equipment_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘设备台账’;

CREATE TABLE t_equipment_digit (
id varchar(64) COLLATE utf8_general_ci NOT NULL COMMENT ‘id’,
equipment_code varchar(30) COLLATE utf8_general_ci NOT NULL COMMENT ‘设备编码’,
digit_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘名称’,
digit_identity varchar(60) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘标识’,
data_type tinyint(4) DEFAULT NULL COMMENT ‘数据类型’,
value_upperlimit decimal(12,4) DEFAULT NULL COMMENT ‘数据上限’,
value_lowerlimit decimal(12,4) DEFAULT NULL COMMENT ‘数据下限’,
digit_type_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘数字类型id’,
digit_type_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘数字类型名称’,
energy_type_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘子类型id’,
description varchar(256) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘描述’,
create_by varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘创建者’,
create_date datetime DEFAULT NULL COMMENT ‘创建时间’,
update_by varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘更新者’,
update_date timestamp NULL DEFAULT NULL COMMENT ‘更新时间’,
del_flag char(1) COLLATE utf8_general_ci DEFAULT ‘0’ COMMENT ‘删除标记’,
unit_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘计量单位id’,
unit varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘计量单位’,
energy_type_name varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘子类型名称’,
fault_def_id varchar(64) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘故障类型id’,
fault_def_name varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘故障类型名称’,
is_monitored tinyint(4) DEFAULT NULL COMMENT ‘是否监视’,
icon_url varchar(255) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘图标url’,
is_trend_monitored tinyint(4) DEFAULT NULL COMMENT ‘是否趋势监控’,
is_data_distribute tinyint(4) DEFAULT NULL COMMENT ‘是否数据下发’,
plc_datatype varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘PLC数据类型’,
plc_address varchar(50) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘PLC点位地址’,
plc_bit int(11) DEFAULT NULL COMMENT ‘plc点位序号’,
data_source tinyint(4) DEFAULT NULL COMMENT ‘数据来源’,
formula_str varchar(500) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘公式’,
parent_identity varchar(30) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘父节点标志’,
parent_identity_full varchar(1000) COLLATE utf8_general_ci DEFAULT NULL COMMENT ‘父节点标志全路径’,
PRIMARY KEY (id),
KEY idx_digit_type_id (digit_type_id),
KEY idx_multi2 (digit_identity,digit_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT=‘设备数字点’;

yilong · 2020 年9 月 25 日 09:45

麻烦展示下 mysql 和 tidb 的结果，具体哪里不一样，多谢。

Hacker_cBh8J2wj · 2020 年9 月 28 日 02:25

返回条数不一样，mysql返回结果是1122条，tidb返回875条。

Hacker_cBh8J2wj · 2020 年9 月 28 日 02:25

返回条数不一样，mysql返回结果是1122条，tidb返回875条。

Hacker_cBh8J2wj · 2020 年9 月 28 日 02:28

mysql结果条数：

tidb结果条数：

Hacker_cBh8J2wj · 2020 年9 月 28 日 02:37

补充一点，如下两个sql，mysql和tidb返回结果一样: 分别是1167、356
SELECT b.* from t_equipment_digit b where b.del_flag = ‘0’
SELECT DISTINCT c.equipment_code from t_equipment c where c.del_flag = ‘0’。
但是join之后，结果就不一样了。

SELECT c.equipment_code from t_equipment c where c.del_flag = ‘0’，mysql和tidb的结果也一样，都是356

Hacker_cBh8J2wj · 2020 年9 月 28 日 02:46

执行计划:执行计划.xlsx (9.4 KB)

yilong · 2020 年9 月 28 日 03:27

可以试一下查询以下的结果吗？
SELECT distinct b.*
from t_equipment_digit b
join t_equipment c ON c.del_flag = ‘0’ AND c.equipment_code = b.equipment_code
where b.del_flag = ‘0’
麻烦也反馈下 mysql 的执行计划，多谢。
是否方便将数据导出，我们本地复现一下，多谢。

Hacker_cBh8J2wj · 2020 年9 月 28 日 05:20

我好像已经发现了问题原因所在，是因为 mysql里边设置了不区分字符大小写，这样join的时候大写跟小写字符可以关联上。但在tidb里边默认是区分大小写的，导致没关联上，我搜过过 lower_case_table_names相关的帖子，貌似tidb里边该参数值只能为2？也就是说必须区分大小写吗不能改吗？请老师帮忙解答一下，辛苦了！

yilong · 2020 年9 月 28 日 06:45

TiDB 区分大小写，因为 collation 默认是 utf8mb4_bin，可以用 ci collation。测试了一下，新的 ci 下可以满足要求。但是只有新建集群时才能打开这个参数.