MySQL如何设置可选外键？解决更新为0时查询失败问题

这是个非常常见的外键使用误区，我来帮你梳理清楚怎么处理～

首先得明确：你想用0来表示“无关联”的思路是行不通的，因为外键约束的核心是引用完整性——它要求外键列的取值必须是关联表主键列中存在的有效值，或者是NULL（前提是列允许NULL）。如果关联表里没有0这个主键值，写入0自然会触发约束报错。

正确的解决方案：让外键列允许NULL #

要实现外键列“可选”的需求，正确的做法是让这个外键列支持NULL值，因为NULL在关系型数据库中代表“不存在/未指定”，正好匹配你要的“可选”场景。

1. 修改现有表的结构

如果你的表已经创建好了，执行下面的SQL来修改外键列，让它允许NULL：

ALTER TABLE 你的表名称
MODIFY COLUMN 外键列名称 INT NULL;

注意：如果这个列之前是NOT NULL，且已经有数据存了0这类无效值，你需要先把这些值更新为NULL，再执行上面的修改语句，否则会报错。比如：

UPDATE 你的表名称 SET 外键列名称 = NULL WHERE 外键列名称 = 0;

2. 新建表时直接定义支持NULL的外键

如果是新建表，直接在定义外键列时加上NULL属性即可：

CREATE TABLE 你的表名称 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    外键列名称 INT NULL,
    -- 定义外键约束
    FOREIGN KEY (外键列名称) REFERENCES 关联表名称(关联表主键列名称)
);

3. 使用时用NULL替代0表示“无关联”

之后插入或更新数据时，当不需要关联到另一表的记录，就把外键列设为NULL：

• 插入示例：

INSERT INTO 你的表名称 (外键列名称, 其他列名) VALUES (NULL, '测试内容');

• 更新示例：

UPDATE 你的表名称 SET 外键列名称 = NULL WHERE id = 123;

额外注意事项 #

• 查询没有关联的记录时，要使用外键列名称 IS NULL，而不是外键列名称 = 0，因为NULL不能用等于符号判断：

SELECT * FROM 你的表名称 WHERE 外键列名称 IS NULL;

• 如果业务上确实需要用0来表示某种特殊含义，那你应该在关联表中新增一条主键为0的记录，这样外键列存0就符合约束了，但这种做法通常不推荐，因为0一般不是常规的主键值，会增加数据逻辑的复杂度。

内容的提问来源于stack exchange，提问作者Asa Carter