如何将空值传递给外键字段?
I have 2 tables:
我有两个表:
university
:
大学:
university_id(p.k) | university_name
and user
:
和用户:
uid | name | university_id(f.k)
How to keep university_id
NULL in user table?
如何在用户表中保存university_id NULL ?
I am writting only 1 query, my query is:
我只写一个查询,我的查询是:
INSERT INTO user (name, university_id) VALUES ($name, $university_id);
Here $university_id
can be null from front end.
这里$university_id可以从前端为空。
university
table will be set bydefault by me.
我将默认设置大学表。
In the front end, student will select the university name, according to that the university_id
will pass to user
table, but if student is not selecting any university name then is should pass null value to the user
table for university_id
field.
在前端,学生将选择大学名称,根据这一点,university_id将传递给用户表,但是如果学生没有选择任何大学名称,则应该将null值传递给university_id字段的用户表。
3 个解决方案
#1
39
Just allow column university_id
of table user
to allow NULL
value so you can save nulls.
只允许表用户的列university_id允许空值,以便保存nulls。
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 NULL
and 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 Injection
if 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 PreparedStatements
you can get rid of using single quotes around values.
作为旁注,如果变量的值来自外部,那么查询就会受到SQL注入的影响。请看下面的文章,学习如何预防。通过使用preparedstatement,可以避免在值周围使用单引号。
- How to prevent SQL injection in PHP?
- 如何防止PHP中的SQL注入?
更多相关文章
- 在MySQL数据库中存储无法访问的用户
- init-connectMysql对用户操作加审计功能
- mysql user表root 用户修改权限后出现无法访问数据库的解决方法
- Linux下修改MySQL初始密码、开启远程登录、授权远程登录用户
- 保存在Java桌面应用程序应用程序和网站上使用的个人用户设置的最
- 是一个mysql临时表,每个用户访问创建它的脚本是唯一的...?
- MVC框架——学生信息管理系统(多表,多事务如何处理,一个用户如何共
- MySQL5.7以上版本root用户空密码修改(windows系统、zip版MySQL)
- MySql查询脚本,每月统计活动用户。