来源表:
##示例
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不同会导致索引失效
)
)
