insert into select 踩坑

原始sql语句：

INSERT INTO auth ( open_id, user_id, node_id, helmet_color, is_node_active, admin_id, is_admin_active )
SELECT
	u.open_id,
	u.id AS user_id,
	n.id AS node_id,
	n.helmet_color AS helmet_color,
	n.is_active AS is_node_active,
	a.id AS admin_id,
	a.is_active AS is_admin_active 
FROM
	USER AS u
	LEFT JOIN node AS n ON u.id = n.user_id
	LEFT JOIN admin AS a ON a.user_id = u.id 

有一个字段is_node_active查询出的结果只有0或者1，insert之后莫名其妙出来了2，只运行后半部分select语句，结果也是正确的。

---

修改后的sql语句：

INSERT INTO auth ( open_id, user_id, node_id, helmet_color, is_node_active, admin_id, is_admin_active ) 
SELECT
u.open_id,
u.id AS user_id,
n.id AS node_id,
n.helmet_color AS helmet_color,
IF
	( n.is_active = '1', 1, 0 ) AS is_node_active,
	a.id AS admin_id,
IF
	( a.is_active = '1', 1, 0 ) AS is_admin_active 
FROM
	USER AS u
	LEFT JOIN node AS n ON u.id = n.user_id
	LEFT JOIN admin AS a ON a.user_id = u.id 

在两个字段上添加了IF判断，结果正确，不再出现2。

---

原来 auth表里面 is_node_active 字段是int类型，而node表里面的 is_node_active 是char类型，而mysql会自动进行一些类型转换，所以出错，可以参考：

https://blog.csdn.net/lgh1117/article/details/9169239?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-6&spm=1001.2101.3001.4242