这是什么错误? “数据库查询失败:第1行的列'column_name'的数据被截断 [英] What is this error? "Database query failed: Data truncated for column 'column_name' at row 1

问题描述

我正在构建一个PHP/MySQL应用程序，但是我的创建和更新查询遇到了问题.我有5列设置为FLOAT的列，这些列也设置为NULL列.我不打算在工作流程的稍后部分填写它们.

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.

但是，我需要为此数据库创建新记录，并且需要编辑现有记录，而完全不涉及这5个浮点字段.我正在使用使用标准save()方法的OOP PHP，该方法检查对象中是否存在ID.如果不是，则调用create()，如果是，则调用update().通常情况下，效果很好.

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.

update()和create()方法旨在从每个类顶部声明的protected static $db_fields属性数组中提取，该数组包含该表中使用的所有字段. update()和create()遍历该数组，并相应地在SQL中运行INSERT INTO或UPDATE.

The update() and create() methods are designed to pull from a protected static $db_fields attribute 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 INTO or UPDATE in SQL, accordingly.

我的理解是，如果使用''(两个单引号，为空)，SQL将跳过那些INSERT INTO或UPDATE请求，并将其保留为NULL.页面上任何地方甚至没有这5个浮点值的表单字段，因此，当然，当方法运行时，值将为''.

My understanding is that if you use '' (two single quotes, empty), SQL will skip those INSERT INTO or UPDATE requests 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 ''.

这就是为什么我遇到数据被截断"错误的原因吗?似乎有所不同-我以前从未见过截断的错误，这就是为什么我来找你们才华横溢的原因.谢谢.

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.

推荐答案

''和null不同.如果您的mysql服务器处于严格模式，则它将拒绝执行插入操作，因为您已为该列传递了无效数据.如果不使用严格模式，它将返回警告.

'' and null are 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.

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

要么插入显式的null，要么甚至不指定插入中的列.

either insert explicit nulls, or don't even specify the column in the insert.

更新时，您可以发送所有具有的值，因为mysql会自动忽略未更改的值.

when you're updating you can send all of the values you have because mysql will automatically ignore the unchanged ones.

这篇关于这是什么错误? “数据库查询失败:第1行的列'column_name'的数据被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！