MySQL int列允许null,但输入null为零 [英] MySQL int column allows null but enters null as zero
问题描述
我有一个PHP函数,我有一个PHP函数以将值数组添加到表中。它首先检查值是否为空,如果是,用 NULL
替换它们,以便在 null
表字段。我放置的每个字段 null
允许 null
但放置一个 0 <
> public static function AddGame($ array,$ tId)
{
global $ db; //将数据库引入范围
//从特定名称获取ID
$ locId = $ array ['fLocation'];
//确保空值为SQL null
$ hTeamId ='{$ array ['fHomeTeam']}';
$ vTeamId ='{$ array ['fVisitTeam']}';
$ hScore ='{$ array ['fHomeScore']}';
$ vScore ='{$ array ['fVisitScore']}';
$ hHoldFor ='{$ array ['fHomeHoldFor']}';
$ vHoldFor ='{$ array ['fVisitHoldFor']}';
//准备插入行
$ row ='','$ tId','$ locId',$ hTeamId,$ vTeamId,'{$ array ['fDate']}', {$ array ['fTime']}',$ hScore,$ vScore,'{$ array ['fGameType']}',$ hHoldFor,$ vHoldFor
$ stmt = $ db-> prepare(INSERT INTO`game` VALUES($ row));
if($ stmt-> execute())return true;
else return false;
}
我已经在各行调试了这个函数,并且它显示这是预期的:
'','1','1','21','21' '10 / 10/12','10:30 AM','NULL','NULL','pool','NULL','NULL'
b $ b
然而,当我检查表文本类型字段字面上有值 NULL
这不是我想要的,也是int字段显示为 0
。如果我将值留空或PHP的null,那么文本字段显示为空(或正确地 null
如我想),但int仍然显示为 0
。
我预计这只是由于间接插入值的方式。
以下是SHOW CREATE TABLE 游戏
CREATE TABLE `game`(
`id` int(11)NOT NULL AUTO_INCREMENT,
`tId` int(11)NOT NULL,
` b`hTeamId` int(11)DEFAULT NULL,
`vTeamId` int(11)DEFAULT NULL,
`date` text NOT NULL,
`time` text NOT NULL,
`hScore` int(11)DEFAULT NULL,
`vScore` int(11)DEFAULT NULL,
`type` text NOT NULL,
`hHoldFor` text,
` vHoldFor` text,
PRIMARY KEY(`id`)
)ENGINE = InnoDB AUTO_INCREMENT = 17 DEFAULT CHARSET = latin1
UPDATE:
INSERT INTO`game` VALUES('','1','1','','' '10 / 09/12','9:30 AM','','','pool','A组冠军','B组冠军')
不能插入'NULL'
。删除 NULL
周围的单引号。
您的字符串
'','1','1','21','21','10/10/12','10:30 AM','NULL' ','pool','NULL','NULL'
b
$ b
'','1','1','21','21','10/10/12' ',NULL,NULL,'pool',NULL,NULL
使 INSERT
(即 INSERT INTO表(col1,col2)VALUES ...
)
编辑1
我建议您查看您的 SHOW CREATE TABLE tbl_name
编辑2
=http://sqlfiddle.com/#!2/8a4a5/1 =nofollow>测试这,我仍然会说,问题是你如何插入数据。
(18,1,1,21,21,'10 / 10/12','10:30 AM',NULL,NULL,'pool' ,NULL,NULL)
有效。
('18','1','1','21','21','10/10/12','10:30 AM','NULL','NULL','pool','NULL' ,'NULL')
无效:整数值不正确:'hScore'第1行:
编辑3
是您的类的改进版本:
public static function AddGame($ array,$ tId)
{
global $ db; //将数据库引入范围
//从特定名称获取ID
$ locId = $ array ['fLocation'];
//确保空值为SQL null
$ hTeamId = empty($ array ['fHomeTeam'])? '空值' : ' 。 $ array ['fHomeTeam']。 ';
$ vTeamId = empty($ array ['fVisitTeam'])? '空值' : ' 。 $ array ['fVisitTeam']。 ';
$ hScore = empty($ array ['fHomeScore'])? '空值' : ' 。 $ array ['fHomeScore']。 ';
$ vScore = empty($ array ['fVisitScore'])? '空值' : ' 。 $ array ['fVisitScore']。 ';
$ hHoldFor = empty($ array ['fHomeHoldFor'])? '空值' : ' 。 $ array ['fHomeHoldFor']。 ';
$ vHoldFor = empty($ array ['fVisitHoldFor'])? '空值' : ' 。 $ array ['fVisitHoldFor']。 ';
//准备插入行
$ row =$ tId,$ locId,$ hTeamId,$ vTeamId,'{$ array ['fDate']}','{$ array ['fTime']}',$ hScore,$ vScore,'{$ array ['fGameType']}',$ hHoldFor,$ vHoldFor
$ stmt = $ db-> prepare(INSERT INTO game(tId,Lid,hTeamId,vTeamId,date,time,hScore,vScore,type,hHoldFor,vHoldFor)VALUES($ row));
if($ stmt-> execute())return true;
else return false;
}
非 - NULL
值将用引号括起来,否则分配 NULL
。我还定义了 INSERT
的列列表,并排除了 id
,因为它是一个 AUTO_INCREMENT
列。
I've looked around and have noticed a few people have had the same problem but their oversight doesn't seem to apply here.
I have a PHP function to add an array of values into a table. It first checks whether the values are empty and if so replaces them with NULL
, in order to place a null
in the table field. Each fields that I'm placing null
into allows for null
yet places a 0
there instead.
Here's some code:
public static function AddGame($array, $tId)
{
global $db; // Bring DB into scope
// Get IDs from particular names
$locId = $array['fLocation'];
// Ensure empty values are SQL null
$hTeamId = "'{$array['fHomeTeam']}'";
$vTeamId = "'{$array['fVisitTeam']}'";
$hScore = "'{$array['fHomeScore']}'";
$vScore = "'{$array['fVisitScore']}'";
$hHoldFor = "'{$array['fHomeHoldFor']}'";
$vHoldFor = "'{$array['fVisitHoldFor']}'";
// Prepare row for insertion
$row = "'','$tId','$locId',$hTeamId,$vTeamId,'{$array['fDate']}','{$array['fTime']}',$hScore,$vScore,'{$array['fGameType']}',$hHoldFor,$vHoldFor";
$stmt = $db->prepare("INSERT INTO `game` VALUES($row)");
if($stmt->execute()) return true;
else return false;
}
I've debugged this function at various lines and have dumped the $row string and it shows this, which is expected:
'','1','1','21','21','10/10/12','10:30AM','NULL','NULL','pool','NULL','NULL'
Yet when I check the table text type fields literally have the value NULL
which is not what I want and also int fields show as 0
. If I leave the values blank or as PHP's null then text fields show as empty (or properly null
as I'd like) yet the ints still show as 0
.
I expect this is only caused due to the way I insert the values indirectly.
Here is the SHOW CREATE TABLE game
CREATE TABLE `game` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tId` int(11) NOT NULL, `Lid` int(11) NOT NULL, `hTeamId` int(11) DEFAULT NULL, `vTeamId` int(11) DEFAULT NULL, `date` text NOT NULL, `time` text NOT NULL, `hScore` int(11) DEFAULT NULL, `vScore` int(11) DEFAULT NULL, `type` text NOT NULL, `hHoldFor` text, `vHoldFor` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1
UPDATE:
INSERT INTO `game` VALUES('','1','1','','','10/09/12','9:30AM','','','pool','winner of pool A','winner of pool B')
You can't insert 'NULL'
. Remove the single quotes around NULL
.
Your string
'','1','1','21','21','10/10/12','10:30AM','NULL','NULL','pool','NULL','NULL'
Should look like
'','1','1','21','21','10/10/12','10:30AM',NULL,NULL,'pool',NULL,NULL
You should also define a column list whenever making an INSERT
(ie. INSERT INTO table (col1, col2) VALUES ...
)
Edit 1
I would recommend looking through your SHOW CREATE TABLE tbl_name
Edit 2
After testing this, I would still say the problem is with how you're inserting the data.
(18,1,1,21,21,'10/10/12','10:30AM',NULL,NULL,'pool',NULL,NULL)
Works.
('18','1','1','21','21','10/10/12','10:30AM','NULL','NULL','pool','NULL','NULL')
Does not work: Incorrect integer value: 'NULL' for column 'hScore' at row 1:
Edit 3
Here is an improved version of your class:
public static function AddGame($array, $tId)
{
global $db; // Bring DB into scope
// Get IDs from particular names
$locId = $array['fLocation'];
// Ensure empty values are SQL null
$hTeamId = empty($array['fHomeTeam']) ? 'NULL' : "'" . $array['fHomeTeam'] . "'";
$vTeamId = empty($array['fVisitTeam']) ? 'NULL' : "'" . $array['fVisitTeam'] . "'";
$hScore = empty($array['fHomeScore']) ? 'NULL' : "'" . $array['fHomeScore'] . "'";
$vScore = empty($array['fVisitScore']) ? 'NULL' : "'" . $array['fVisitScore'] . "'";
$hHoldFor = empty($array['fHomeHoldFor']) ? 'NULL' : "'" . $array['fHomeHoldFor'] . "'";
$vHoldFor = empty($array['fVisitHoldFor']) ? 'NULL' : "'" . $array['fVisitHoldFor'] . "'";
// Prepare row for insertion
$row = "$tId,$locId,$hTeamId,$vTeamId,'{$array['fDate']}','{$array['fTime']}',$hScore,$vScore,'{$array['fGameType']}',$hHoldFor,$vHoldFor";
$stmt = $db->prepare("INSERT INTO game (tId, Lid, hTeamId, vTeamId, date, time, hScore, vScore, type, hHoldFor, vHoldFor) VALUES($row)");
if($stmt->execute()) return true;
else return false;
}
Non-NULL
values will be encased in quotes, otherwise they are assigned NULL
. I've also defined the column list for INSERT
and excluded id
, as it's an AUTO_INCREMENT
column.
这篇关于MySQL int列允许null,但输入null为零的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!