MySQL中关联查询更新UUID列时出现值异常问题

我有两张表（Account和Customer），均使用UUID作为主键，但未建立正确关联。account表的customer_id（非UUID类型）与customer表的provider_customer_id值一致。尝试通过以下脚本将account表的customer_id更新为customer表的UUID主键以建立外键关联：

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE accounts
    MODIFY COLUMN customer_id BINARY(16) NOT NULL;
UPDATE accounts a
    INNER JOIN  customers c on a.customer_id = c.provider_customer_id
SET a.customer_id = (c.id)
WHERE c.provider_customer_id is not null ;

ALTER TABLE accounts
    ADD CONSTRAINT FK_ACCOUNTS_ON_CUSTOMER FOREIGN KEY (customer_id) REFERENCES customers (id);
SET FOREIGN_KEY_CHECKS=1;

但MySQL执行更新后，account表的customer_id中的UUID值与customer表的UUID完全不符，更新后的值示例如下：

43313231-3443-4134-0000-000000000000
43313231-3634-3137-0000-000000000000
43313231-3436-4847-0000-000000000000
43313231-3443-4134-0000-000000000000

问题原因 #

核心问题是字段类型修改时机错误：你先把customer_id改成了BINARY(16)，此时原customer_id是字符串类型的provider_customer_id值，直接转二进制会把字符串本身编码成二进制，而非对应UUID的二进制形式。后续更新时，MySQL会把c.id（二进制UUID）隐式转换为字符串再存回BINARY(16)字段，最终导致值错乱。

正确解决脚本 #

方案一：用临时字段过渡（更稳妥）

SET FOREIGN_KEY_CHECKS=0;

-- 添加临时字段存储正确的二进制UUID
ALTER TABLE accounts ADD COLUMN temp_customer_id BINARY(16);

-- 关联查询，将customer表的UUID主键转换为二进制存入临时字段
UPDATE accounts a
JOIN customers c ON a.customer_id = c.provider_customer_id
SET a.temp_customer_id = UNHEX(REPLACE(c.id, '-', ''))
WHERE c.provider_customer_id IS NOT NULL;

-- 替换原字段
ALTER TABLE accounts DROP COLUMN customer_id;
ALTER TABLE accounts CHANGE COLUMN temp_customer_id customer_id BINARY(16) NOT NULL;

-- 添加外键约束
ALTER TABLE accounts
ADD CONSTRAINT FK_ACCOUNTS_ON_CUSTOMER FOREIGN KEY (customer_id) REFERENCES customers (id);

SET FOREIGN_KEY_CHECKS=1;

方案二：先更新字符串UUID再转类型

SET FOREIGN_KEY_CHECKS=0;

-- 先将customer_id更新为字符串格式的UUID
UPDATE accounts a
JOIN customers c ON a.customer_id = c.provider_customer_id
SET a.customer_id = c.id
WHERE c.provider_customer_id IS NOT NULL;

-- 将字段类型修改为BINARY(16)，MySQL会自动完成字符串到二进制的转换
ALTER TABLE accounts MODIFY COLUMN customer_id BINARY(16) NOT NULL;

-- 添加外键约束
ALTER TABLE accounts
ADD CONSTRAINT FK_ACCOUNTS_ON_CUSTOMER FOREIGN KEY (customer_id) REFERENCES customers (id);

SET FOREIGN_KEY_CHECKS=1;

说明 #

• 方案一通过临时字段隔离更新和类型转换操作，避免隐式转换干扰，适合数据量较大或对数据安全要求高的场景。
• 方案二更简洁，前提是customers.id是标准的带横杠的UUID字符串，MySQL能自动识别并转换为二进制格式。

内容的提问来源于stack exchange，提问作者Babajide Apata