php MySQL - 十进制不能为 NULL

MySQL - Decimal cannot be NULL

提问by user547794

Everytime I try to make an insert statement into my dastabase I get a "Incorrect decimal value: 'NULL' for column 'bounty3' at row 1" error. How do I insert a null value into a decimal datatype? Should I just make the default value 0.00?

每次我尝试在数据库中插入插入语句时，我都会收到“不正确的十进制值：第 1 行的‘bounty3’列的‘NULL’”错误。如何在十进制数据类型中插入空值？我应该将默认值设为 0.00 吗？

Incorrect decimal value: '' for column 'bounty3' at row 1 Whole query: INSERT INTO songs (userid, wavURL, mp3URL, genre, songTitle, BPM, insWanted, bounty, insWanted2, bounty2, insWanted3, bounty3, insWanted4, bounty4, insWanted5, bounty5, insWanted6, bounty6, insWanted7, bounty7, insWanted8, bounty8, insWanted9, bounty9, insWanted10, bounty10) VALUES ('12534545', '/audio/wav/jqmrgpfcMichael/135259578210secreason.wav', '/audio/mp3/jqmrgpfcMichael/135259578210secreason.mp3', 'Rock/Funk', 'titlee', '120', 'bass', '20.00', 'guitar', '20.00', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

I tried this statement with a NULL value too. Here is the error:

我也用 NULL 值尝试了这个语句。这是错误：

Incorrect decimal value: 'NULL' for column 'bounty3' at row 1 Whole query: INSERT INTO songs (userid, wavURL, mp3URL, genre, songTitle, BPM, insWanted, bounty, insWanted2, bounty2, insWanted3, bounty3, insWanted4, bounty4, insWanted5, bounty5, insWanted6, bounty6, insWanted7, bounty7, insWanted8, bounty8, insWanted9, bounty9, insWanted10, bounty10) VALUES ('12534545', '/audio/wav/jqmrgpfcMichael/143922765110secreason.wav', '/audio/mp3/jqmrgpfcMichael/143922765110secreason.mp3', 'Rock/Funk', 'title', '110', 'bass', '110.00', 'guitar', '20.00', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL', 'NULL')

采纳答案by user547794

use blank, not null, if you want the default value.

如果需要默认值，请使用空白而不是空。

回答by

Oh.. you are trying to insert an empty string '' into bounty3. Replace it with NULL. I have also noticed empty strings for other possible numeric values eg bonty4. You should replace all empty strings with NULL for numeric values.

哦.. 你正试图在 bounty3 中插入​​一个空字符串 ''。将其替换为 NULL。我还注意到其他可能的数值的空字符串，例如 bonty4。对于数值，您应该用 NULL 替换所有空字符串。

eg: mysql_query("INSERT INTO empty_number,number VALUES(NULL,1)");

例如：mysql_query("INSERT INTO empty_number,number VALUES(NULL,1)");

EDIT: HEY HEY get the point, you cannot insert NULL to a numeric value as 'NULL' because this is a string, you should insert as NULL without any quotation marks

编辑：嘿嘿，明白了，你不能将 NULL 作为 'NULL' 插入到数值中，因为这是一个字符串，你应该作为 NULL 插入而不带任何引号

回答by user3249681

I just dealt with this issue in MySql 5.1.61. If the server is in strict mode, you must enter 0 instead of '' for decimal fields. Disabling strict mode allows you to just do '' to populate a null value.

我刚刚在 MySql 5.1.61 中处理了这个问题。如果服务器处于严格模式，则必须为十进制字段输入 0 而不是 ''。禁用严格模式允许您只执行 '' 来填充空值。

回答by Daniel García

You can run this query to allow NULL values in 'bounty3' field.

您可以运行此查询以允许 'bounty3' 字段中的 NULL 值。

ALTER TABLE songsCHANGE bounty3bounty3DECIMAL(10,0) NULL;

ALTER TABLE songsCHANGE bounty3bounty3DECIMAL(10,0) NULL;

回答by Cameron Skinner

You need to make sure that your database schema allows NULL values for that column. Run describe <tablename>;in your MySQL client to see what the schema for that table is.

您需要确保您的数据库架构允许该列的 NULL 值。describe <tablename>;在您的 MySQL 客户端中运行以查看该表的架构是什么。

If the schema does not allow null then you can use alter tableto change the schema. Make sure you don't specify NOT NULLfor that column and you should be fine.

如果架构不允许为空，那么您可以使用alter table来更改架构。确保您没有NOT NULL为该列指定，您应该没问题。

回答by Robert Lattery

Make sure the field type allows for NULLvalues and that the default is NULL.

确保字段类型允许NULL值并且默认值为NULL.

Then use:

然后使用：

UPDATE table_name SET date_field=IF('$date_value'='',NULL,'$date_value')

This works for inserts or updates.

这适用于插入或更新。

I have tried using $date_value = NULLand I have even tried unset($date_value)before inserting or updating, and it never worked on my decimal fields. MySQL always converted them to 0.00. The method above was the only solution that worked for me.

我试过使用$date_value = NULL，甚至unset($date_value)在插入或更新之前也试过，但它从未在我的十进制字段上工作。MySQL 总是将它们转换为 0.00。上面的方法是唯一对我有用的解决方案。

回答by Filipe

Thanks, it worked using NULL when it comes empty:

谢谢，它在空时使用 NULL 工作：

$conn->bindParam(':'.$valor, ( $key[0] ? $key[0] : NULL ), PDO::PARAM_STR);

回答by Brooky

Another option is to make sure that in your source data, the empty cells or fields contain 'NULL' before you import them in MySQL. This way, MySQL will recognize these fields as being reallyNULL, and won't transform them to 0's.

另一种选择是确保在您的源数据中，空单元格或字段在您将它们导入 MySQL 之前包含“NULL”。这样，MySQL 会将这些字段识别为真正的NULL，并且不会将它们转换为 0。

回答by pilat

1. Make sure that the field is "nullable" (does not have NOT NULLin its definition)

2. Make field's default value NULL.

   Ex.: price decimal(12,2) DEFAULT NULL

3. Now, to test it, store some number, then '' (an empty string) into this field — it should become NULL in the end.

1. 确保该字段是“可为空的”（NOT NULL在其定义中没有）

2. 将字段的默认值设为 NULL。

   前任。： price decimal(12,2) DEFAULT NULL

3. 现在，为了测试它，存储一些数字，然后 ''（一个空字符串）到这个字段中——它最终应该变成 NULL。

回答by Marius

The field is most likely numeric. If the field is numeric then only numbers are accepted. Empty string is not accepted

该字段很可能是数字。如果该字段是数字，则只接受数字。不接受空字符串

insert into songs (...,bounty3) values(...,'')

neither the NULL string

既不是 NULL 字符串

insert into songs (...,bounty3) values(...,'NULL')

('NULL' which is just a string, nothing to do with the actual NULL value, so 'NULL' si similar to 'HONEY BEE' if you want).

（'NULL' 只是一个字符串，与实际的 NULL 值无关，所以如果你愿意，'NULL' si 类似于 'HONEY BEE'）。

So if the column is numeric then it will only take numbers. However if the column is NULLABLE then it will accept NULL, too. That means your insert must have

因此，如果该列是数字，那么它只会采用数字。但是，如果该列是 NULLABLE，那么它也将接受 NULL。这意味着您的插入必须具有

insert into songs (...,bounty3) values (..., NULL)

If you push NULL on such a column and the column has a default value then DEFAULT value will be used instead of the NULL you are pushing.

如果您在这样的列上推送 NULL 并且该列具有默认值，则将使用 DEFAULT 值而不是您正在推送的 NULL。