MySQL 如何将空值传递给外键字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15082874/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to pass a null value to a foreign key field?
提问by Harjeet Jadeja
I have 2 tables:
我有2张桌子：
university:
university：
university_id(p.k) | university_name
and user:
和user：
uid | name | university_id(f.k)
How to keep university_idNULLin user table?
如何在用户表中保持university_idNULL？
I am writting only 1 query, my query is:
我只写了 1 个查询，我的查询是：
INSERT INTO user (name, university_id) VALUES ($name, $university_id);
Here $university_idcan be null from front end.
这里$university_id可以从前端为空。
universitytable will be set bydefault by me.
university表将由我默认设置。
In the front end, student will select the university name, according to that the university_idwill pass to usertable, but if student is not selecting any university name then is should pass null value to the usertable for university_idfield.
在前端，学生将选择大学名称，根据该名称university_id将传递给user表，但如果学生未选择任何大学名称，则应将空值传递给user表的university_id字段。
回答by John Woo
Just allow column university_idof table userto allow NULLvalue so you can save nulls.
只允许university_id表列user允许NULL值，这样您就可以保存空值。
CREATE TABLE user
(
uid INT NOT NULL,
Name VARCHAR(30) NOT NULL,
university_ID INT NULL, -- <<== this will allow field to accept NULL
CONSTRAINT user_fk FOREIGN KEY (university_ID)
REFERENCES university(university_ID)
)
UPDATE 1
更新 1
based on your comment, you should be inserting NULLand not ''.
根据您的评论，您应该插入NULL而不是''.
insert into user (name,university_id) values ('harjeet', NULL)
UPDATE 2
更新 2
$university_id = !empty($university_id) ? "'$university_id'" : "NULL";
insert into user (name,university_id) values ('harjeet', $university_id);
As a sidenote, the query is vulnerable with SQL Injectionif the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatementsyou can get rid of using single quotes around values.
作为一个旁注，查询是脆弱的SQL Injection，如果值（小号变量）从外面走了进来。请查看下面的文章，了解如何预防。通过使用，PreparedStatements您可以摆脱在值周围使用单引号。
回答by Devang Rathod
Here suppose i have foreign key user_idand i want to insert null value for that.
这里假设我有外键user_id，我想为此插入空值。
Checkbox must be checked for insert null value for foreign key.
必须选中复选框为外键插入空值。
回答by G_comp
I was using MySQL InnoDB and even allowing NULL in the FK column and using NULL as default I was getting an error. So I used the SET syntax:
我正在使用 MySQL InnoDB，甚至在 FK 列中允许 NULL 并使用 NULL 作为默认值我收到错误。所以我使用了 SET 语法：
INSERT INTO (table) SET value1=X...
And I just don't set the FK column.
我只是不设置 FK 列。
