报错：1267 - Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)

最新推荐文章于 2024-12-02 22:15:42 发布

原创最新推荐文章于 2024-12-02 22:15:42 发布 · 2.7k 阅读

0 ·

CC 4.0 BY-SA版权

bug记录专栏收录该内容

23 篇文章

订阅专栏

本文探讨了在数据库查询中遇到的排序规则差异问题，如何通过LEFT JOIN解决edc_airline_resource_tempt和edc_airline_resource_spaces表的连接，确保在不同字符集规则下获取有效航班资源信息，重点在于时间范围筛选和空余座位查询。

SELECT 
		NULL as id, 
		t.source_id, 
		t.airline_name,
		t.flight_no,
		t.space_type,
		t.start_station,
		t.start_code,
		t.end_station,
		t.end_code,
		t.transfer_station,
		t.product_type,
		t.free_space,
		t.free_space,
		t.effect_date,
		NOW(),
		NOW() 
	FROM edc_airline_resource_temp t 
	LEFT JOIN edc_airline_resource_space s 
	ON s.effect_date=t.effect_date 
		and s.airline_name = t.airline_name 
		and s.flight_no = t.flight_no 
		and s.space_type = t.space_type 
		and s.start_station = t.start_station 
		and s.end_station = t.end_station 
		and IFNULL(s.transfer_station,'') = IFNULL(t.transfer_station,'') 
		and s.product_type = t.product_type 
	where t.effect_date >= CURDATE() 
		and t.effect_date<=ADDDATE(CURDATE(),2) 
		and s.id is NULL;

原因：连表的两个表的排序规则不一样，一个为utf8mb4_0900_ai_ci，另一个为：utf8mb4_general_ci