來源表:

##示例
CREATE TABLE `order_wide` (`order_id` varchar(33) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '訂單ID',`member_id` int(11) DEFAULT NULL COMMENT '用戶ID',`content` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '訂單標簽',PRIMARY KEY (`order_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='訂單大寬表'
;

## 示例
CREATE TABLE `order_tag` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',`order_id` varchar(33) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`content` varchar(255) NOT NULL DEFAULT '' COMMENT '標簽內容',`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1生效2失效',`admin_id` int(11) NOT NULL DEFAULT '0' COMMENT '添加管理員id，0表示系統',`is_show` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1是2否 展示',`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間',`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '時間',PRIMARY KEY (`id`),KEY `idx_is_show` (`is_show`),KEY `idx_order_id` (`order_id`),
) ENGINE=InnoDB AUTO_INCREMENT=103419 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='訂單標簽表'
;

sql

select * from order o 
left join order_tag ot on o.order_id = ot.order_id 
where xxx  xxx

索引失效的原因

關聯字段的charset和collate不同會導致索引失效