mysql int 可以null吗,MySQL int列允许null，但输入null为零

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.