如何通过FROM子句关联其他表更新数据 附MySQL IP段关联更新示例

原语句执行问题 #

你编写的语句无法在MySQL中正常执行：UPDATE ... FROM ... 是PostgreSQL、SQL Server等数据库支持的多表更新语法，MySQL的多表更新逻辑不支持该写法，直接运行会抛出语法错误。

正确实现方案 #

MySQL支持通过关联JOIN的方式实现跨表更新，正确语句如下：

UPDATE play_log_new pln
INNER JOIN ip_range ipr 
ON INET_ATON(pln.ip) BETWEEN ipr.ip_start_digit AND ipr.ip_end_digit
SET pln.country = ipr.country_name
WHERE pln.ip IS NOT NULL 
AND pln.country IS NULL;

注意事项 #

• 执行更新前建议先执行对应SELECT语句验证匹配结果是否符合预期，避免误更新：

SELECT pln.ip, pln.country AS old_country, ipr.country_name AS new_country
FROM play_log_new pln
INNER JOIN ip_range ipr 
ON INET_ATON(pln.ip) BETWEEN ipr.ip_start_digit AND ipr.ip_end_digit
WHERE pln.ip IS NOT NULL 
AND pln.country IS NULL;

• 如果play_log_new是大表，建议分批次更新，避免长时间锁表影响业务
• 如果存在单个IP匹配到多个IP段的情况，需要先对ip_range表做去重处理，避免更新结果不符合预期
• 如果需要支持IPv6地址，将INET_ATON替换为INET6_ATON，同时确保ip_range表的IP起止字段适配IPv6的存储格式

内容的提问来源于stack exchange，提问作者Tadeh Boghosian