求助：关联COUNTRY_NAME更新countryCapCurLang表alpha_2字段失败

问题背景 #

两张数据库表countryCapCurLang与CountryBasicInfo，均包含可为空的Varchar(130)类型字段COUNTRY_NAME，以及alpha_2字段。其中CountryBasicInfo的alpha_2存储对应国家的二位代码（如阿富汗对应AF），需求是将该表的alpha_2值同步更新至countryCapCurLang表的对应字段。

表数据示例 #

• countryCapCurLang数据：

| id | alpha_2 |COUNTRY_NAME|
| -- | ------- | ---------- |
|  1 | Null    |Afghanistan |
|  2 | Null    |France      |

• CountryBasicInfo数据：

| id | alpha_2 |COUNTRY_NAME|
| -- | ------- | ---------- |
|  1 | AF      |Afghanistan |
|  2 | FR      |France      |

尝试过的操作及问题 #

1. 首次执行的UPDATE语句报错，未完成任何更新：

update countryCapCurLang 
left join CountryBasicInfo using(COUNTRY_NAME) 
set countryCapCurLang.alpha_2=(select * from  CountryBasicInfo where  countryCapCurLang.Country_NAME like '%'+CountryBasicInfo.COUNTRY_NAME+'%');

2. 将like替换为=后，可更新大部分数据，但195个国家中有24个未更新。测试发现select * from CountryBasicInfo where country_name='Afghanistan'无结果，而select * from CountryBasicInfo where country_name like '%Afghanistan%'能返回对应行，推测是字段值存在空格、换行符等无效字符导致精确匹配失败。
3. 后续尝试的两个关联更新语句也未解决问题：

UPDATE `countrylangcapcur` 
SET `countrylangcapcur`.`alpha_2`=( select `alpha_2` from `countrybasicinfo` LEFT JOIN countrylangcapcur ON `countrylangcapcur`.`COUNTRY_NAME` like '%'+`countrybasicinfo`.`COUNTRY_NAME`+'%');

update `countrylangcapcur` 
INNER JOIN `countrybasicinfo` on `countrylangcapcur`.`country_name` like '%'+`countrybasicinfo`.`country_name`+'%' 
SET `countrylangcapcur`.`alpha_2`=( select `alpha_2` from `countrybasicinfo` WHERE `countrylangcapcur`.`country_name` like '%'+`countrybasicinfo`.`country_name`+'%');

解决方案 #

1. 清理字段中的无效字符 #

先处理两张表COUNTRY_NAME字段的首尾空格、换行符、回车符等无效字符，确保匹配的基础一致：

清理CountryBasicInfo表：

UPDATE CountryBasicInfo 
SET COUNTRY_NAME = TRIM(REPLACE(REPLACE(COUNTRY_NAME, CHAR(10), ''), CHAR(13), ''));
-- 去除首尾空格，同时移除换行符（CHAR(10)）和回车符（CHAR(13)）

清理countryCapCurLang表：

UPDATE countryCapCurLang 
SET COUNTRY_NAME = TRIM(REPLACE(REPLACE(COUNTRY_NAME, CHAR(10), ''), CHAR(13), ''));

如果字段存在多个连续空格，可进一步处理（MySQL 8.0+支持）：

-- 处理CountryBasicInfo的连续空格
UPDATE CountryBasicInfo 
SET COUNTRY_NAME = REGEXP_REPLACE(COUNTRY_NAME, '\\s+', ' ');

-- 处理countryCapCurLang的连续空格
UPDATE countryCapCurLang 
SET COUNTRY_NAME = REGEXP_REPLACE(COUNTRY_NAME, '\\s+', ' ');

2. 使用关联更新语句 #

清理完成后，采用JOIN方式直接关联更新，避免子查询的逻辑问题：

方法一：精确匹配更新（优先使用）

UPDATE countryCapCurLang c
INNER JOIN CountryBasicInfo b ON c.COUNTRY_NAME = b.COUNTRY_NAME
SET c.alpha_2 = b.alpha_2
WHERE c.alpha_2 IS NULL; -- 仅更新未赋值的行，可选

方法二：模糊匹配更新（针对少量名称差异的情况）

如果仍有部分国家名称存在简称/全称差异，可使用模糊匹配，但建议添加限制条件减少误匹配：

UPDATE countryCapCurLang c
INNER JOIN CountryBasicInfo b ON c.COUNTRY_NAME LIKE CONCAT('%', b.COUNTRY_NAME, '%')
SET c.alpha_2 = b.alpha_2
WHERE c.alpha_2 IS NULL
AND ABS(LENGTH(c.COUNTRY_NAME) - LENGTH(b.COUNTRY_NAME)) < 10; -- 限制名称长度差异，避免误匹配

3. 处理剩余未匹配行 #

若仍有少量行未更新，先查询未匹配的记录：

SELECT c.COUNTRY_NAME 
FROM countryCapCurLang c
LEFT JOIN CountryBasicInfo b ON c.COUNTRY_NAME LIKE CONCAT('%', b.COUNTRY_NAME, '%')
WHERE c.alpha_2 IS NULL AND b.COUNTRY_NAME IS NULL;

根据查询结果，手动修正名称差异后再次执行更新，或直接针对特定国家批量更新：

-- 示例：针对俄罗斯手动更新
UPDATE countryCapCurLang 
SET alpha_2 = 'RU' 
WHERE COUNTRY_NAME LIKE '%Russia%';

内容的提问来源于stack exchange，提问作者DEV KAMAL