原始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