企业官网建设流程全解析

本文整理自今天的一次 SQL 实战问答，涵盖多表关联更新、JOIN 语法、排序规则（collation）原理，以及最终的解决方案。适合有一定 SQL 基础、遇到过类似问题的同学参考。

一、需求背景

有两张表：

• t_part：零件表，有part_code（零件编码）、part_name（零件名称）等字段

• node_name_map：三维节点名称与中文对照表，有NODE_NAME（编码/英文名）、NODE_NAME_CN（中文名）等字段

目标：根据零件名称，把node_name_map里的节点编码回填到t_part.part_code。

最初的 SQL 是这样写的：

sql

update tiss.t_part set part_code = tiss.node_name_map.NODE_NAME where part_name = tiss.node_name_map.NODE_NAME_CN

这段 SQL语法错误，无法执行。下面逐步分析原因和正确写法。

二、为什么这条 UPDATE 不对？

问题有两个：

1. node_name_map没有出现在FROM里，不能在WHERE中直接引用另一张表。

2. 没有建立两表的关联条件，等于让part_code等于一个不确定的值。

MySQL 的UPDATE要引用另一张表，必须通过JOIN把它“拉进来”，并用ON建立关联。

正确写法（MySQL）

sql

UPDATE tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME;

执行逻辑分三步：

1. 把两张表按ON的条件配对

2. 配上的行，执行SET更新

3. 配不上的行，不动

三、JOIN 和 LEFT JOIN 的区别

有同学会问：JOIN ... ON是什么语法？有LEFT JOIN ... ON吗？

有，语法完全一样，区别在语义：

在更新场景下，这个区别非常关键：

sql

-- 用 JOIN：只有匹配上的才更新 UPDATE tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME;

sql

-- 用 LEFT JOIN：所有行都更新，匹配不上的 part_code 会被清成 NULL ⚠️ UPDATE tiss.t_part p LEFT JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME;

所以更新时优先用JOIN，避免误清空数据。

如果确实要用LEFT JOIN，一般加条件兜底：

sql

UPDATE tiss.t_part p LEFT JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME WHERE m.NODE_NAME IS NOT NULL;

加了WHERE后，效果其实等价于JOIN。

四、执行时遇到的报错

把 SQL 改成JOIN后，执行却报了：

text

Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

原因是：两张表关联字段的排序规则（collation）不一致。

• node_name_map.NODE_NAME_CN：utf8mb4_general_ci（建表时显式指定）

• t_part.part_name：继承了表默认的utf8mb4_0900_ai_ci

MySQL 规定：两个字符串比较时，必须使用同一种排序规则。不一致就直接报错。

五、排序规则（collation）是什么？

一句话理解：排序规则就是“字符串怎么比较、怎么排序”的一套规则。

• 字符集（charset）：决定能存哪些字符

• 排序规则（collation）：决定这些字符怎么比大小、是否区分大小写/音调

拆解两个规则

排序规则影响什么

常见坑

1. 数据库升级后报错：老库general_ci，新库默认0900_ai_ci，一关联就报错——正是本次遇到的情况。

2. 唯一索引“误判”：不同规则下，'A'和'a'可能算重复。

3. JOIN 性能：两边规则不一致时加COLLATE转换，会导致索引失效。

核心原则：同一个系统里，尽量让所有表、所有关联字段的排序规则保持一致。

六、两种修复方案

方案一：临时在 SQL 里强制统一（最快）

sql

UPDATE tiss.t_part p JOIN tiss.node_name_map m ON p.part_name COLLATE utf8mb4_general_ci = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME;

把其中一边COLLATE成另一边即可。但注意：

• 只是临时绕过，每次关联都要写

• 在字段上加COLLATE可能导致该字段索引失效

方案二：统一排序规则（推荐，一劳永逸）

整表转换（最省事）

sql

ALTER TABLE tiss.node_name_map CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

这条命令会一次性改掉：

• 表默认排序规则

• 所有字符字段的排序规则

• 相关索引的排序规则

同理可以处理t_part：

sql

ALTER TABLE tiss.t_part CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

只改单个字段

sql

ALTER TABLE tiss.node_name_map MODIFY COLUMN NODE_NAME_CN VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '节点中文名称';

⚠️ 字段定义必须和原定义完全一致（类型、长度、NOT NULL、默认值、注释），否则会改坏表结构。

七、执行前的检查与验证

1. 查看表和字段的排序规则

sql

-- 表默认规则 SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'tiss' AND TABLE_NAME = 'node_name_map'; -- 字段规则 SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'tiss' AND TABLE_NAME = 'node_name_map' AND COLLATION_NAME IS NOT NULL;

2. 查出全库不统一的字段

sql

SELECT TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'tiss' AND COLLATION_NAME IS NOT NULL AND COLLATION_NAME <> 'utf8mb4_0900_ai_ci' ORDER BY TABLE_NAME, COLUMN_NAME;

3. 执行后验证

所有COLLATION_NAME都应为utf8mb4_0900_ai_ci。

4. 最终执行 UPDATE

sql

UPDATE tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME;

八、注意事项汇总

只更新空值的写法

sql

UPDATE tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME WHERE p.part_code IS NULL OR p.part_code = '';

执行前先验证关联结果

sql

SELECT p.part_code, p.part_name, m.NODE_NAME FROM tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN;

九、总结

1. 多表更新必须用JOIN ... ON，不能直接在WHERE里引用另一张表。

2. 更新优先用JOIN，LEFT JOIN会把匹配不上的字段更新成NULL。

3. 排序规则不一致会直接报错，这是数据库迁移/升级后最常见的问题之一。

4. 统一排序规则推荐用ALTER TABLE ... CONVERT TO CHARACTER SET ...，一次改全表。

5. 排序规则的核心原则：同一系统内所有关联字段保持一致。

附：完整操作流程

sql

-- 1. 备份（略） -- 2. 查看现状 SELECT COLUMN_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'tiss' AND TABLE_NAME = 'node_name_map' AND COLLATION_NAME IS NOT NULL; -- 3. 统一排序规则 ALTER TABLE tiss.node_name_map CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 4. 验证关联结果 SELECT p.part_code, p.part_name, m.NODE_NAME FROM tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN; -- 5. 执行更新 UPDATE tiss.t_part p JOIN tiss.node_name_map m ON p.part_name = m.NODE_NAME_CN SET p.part_code = m.NODE_NAME; -- 6. 验证结果 SELECT id, part_code, part_name FROM tiss.t_part LIMIT 10;

本文整理自一次真实的 SQL 排错过程，希望对遇到类似问题的你有所帮助。