php 这是什么错误？“数据库查询失败：第 1 行的列 'column_name' 的数据被截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1704304/
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
What is this error? "Database query failed: Data truncated for column 'column_name' at row 1
提问by rhodesjason
I'm building a PHP/MySQL application and I'm running into a problem with my create and update query. I have 5 columns that are set to type FLOAT that are also set as NULL columns. I don't plan to fill them in until much later in the workflow.
我正在构建一个 PHP/MySQL 应用程序，但我的创建和更新查询遇到了问题。我有 5 列设置为 FLOAT 类型，这些列也设置为 NULL 列。我不打算在工作流程的后期填写它们。
However, I need to create new records for this database, and I need to edit existing records, without touching these 5 float fields at all. I'm using OOP PHP that uses a standard save()method that checks to see if an ID exists in the object. If not, it calls create(), and if so, it calls update(). It works very well, usually.
但是，我需要为此数据库创建新记录，并且需要编辑现有记录，而根本不涉及这 5 个浮动字段。我正在使用 OOP PHP，它使用一种标准save()方法来检查对象中是否存在 ID。如果不是，则调用create()，如果是，则调用update()。通常，它工作得很好。
The update()and create()methods are designed to pull from a protected static $db_fieldsattribute array declared at the top of each Class, that contains all of the fields used in that table. update()and create()run through that array and either INSERT INTOor UPDATEin SQL, accordingly.
的update()和create()方法是从设计到拉protected static $db_fields在每个类的顶部声明属性阵列，它包含了所有在该表中使用的字段。update()并相应地create()运行该数组和SQL 中的一个INSERT INTO或一个UPDATE。
My understanding is that if you use ''(two single quotes, empty), SQL will skip those INSERT INTOor UPDATErequests and leave them as NULL. There aren't even form fields for those 5 float values anywhere on the page, so of course when the methods run, the values are going to be ''.
我的理解是，如果您使用''（两个单引号，空），SQL 将跳过这些INSERT INTO或UPDATE请求并将它们保留为 NULL。页面上的任何地方甚至都没有这 5 个浮点值的表单字段，因此当然，当方法运行时，这些值将是''.
Is that why I'm getting the "Data truncated" error? It seems different -- I haven't seen the truncated error before and that's why I'm coming to you geniuses. Thanks.
这就是我收到“数据被截断”错误的原因吗？似乎不同——我以前从未见过截断的错误，这就是我来找你们天才的原因。谢谢。
回答by longneck
''and nullare not the same. if your mysql server is in strict mode, then it will refuse to do the insert since you have passed invalid data for the column. without strict mode, it returns a warning.
''并且null不一样。如果您的 mysql 服务器处于严格模式，那么它将拒绝执行插入，因为您为该列传递了无效数据。如果没有严格模式，它会返回警告。
mysql> create table a (a float not null);
Query OK, 0 rows affected (0.11 sec)
mysql> insert a values ('');
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> set sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.02 sec)
mysql> insert a values ('');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
either insert explicit nulls, or don't even specify the column in the insert.
要么插入显式nulls，要么甚至不指定插入中的列。
when you're updating you can send all of the values you have because mysql will automatically ignore the unchanged ones.
当您更新时，您可以发送您拥有的所有值，因为 mysql 会自动忽略未更改的值。
回答by GZipp
This isn't meant as a solution, but only to perhaps shed some more light on the problem.
这并不意味着作为解决方案，而只是为了更清楚地说明问题。
If you add the keyword IGNORE to the statement, and try to insert '' (or any string not beginning with a number) into a float column, MySQL will insert the value 0, instead of the default value.
如果在语句中添加关键字 IGNORE，并尝试将 ''（或任何不以数字开头的字符串）插入浮点列，MySQL 将插入值 0，而不是默认值。
INSERT IGNORE INTO a_table (float_column) VALUE ('')
INSERT IGNORE INTO a_table (float_column) VALUE ('abc')
Both will insert 0, even if the default is null. Without IGNORE, of course, both statements will raise the error you're seeing.
两者都会插入 0，即使默认值为 null。当然，如果没有 IGNORE，这两个语句都会引发您所看到的错误。
In short, you get the message because the data you're supplying is neither the expected type nor the default value.
简而言之，您收到消息是因为您提供的数据既不是预期类型也不是默认值。
回答by Oz.
If you aren't going to INSERT the data it should not be in the SQL statement.
如果您不打算 INSERT 数据，则不应在 SQL 语句中。
As an example, say you have a table "Data" with id, float1, float2, float3, and float4, Float5, of which you will only be adding the ID. The INSERT should look like this:
例如，假设您有一个表“Data”，其中包含 id、float1、float2、float3 和 float4、Float5，您将只添加其中的 ID。INSERT 应如下所示：
INSERT INTO `Data` ( `id` )
VALUES ( 'Some-ID-Value' )
If the floats are defined to accept NULLthey will be defaulted to NULL. The same goes for UPDATE statements, you should only list fields you will be altering.
如果浮点数被定义为接受，NULL它们将被默认为NULL. UPDATE 语句也是如此，您应该只列出您将要更改的字段。
