在更新语句中使用强制转换时，mysql错误1292 [英] mysql error 1292 when using cast in update statement

问题描述

以下语句返回错误代码:1292.被截断的不正确的INTEGER值:'95 .00'1.132秒"

The below statement returns "Error Code: 1292. Truncated incorrect INTEGER value: '95.00' 1.132 sec "

update new2006 set new2006.emp=cast(emp as unsigned) where IsNum(emp)=0;

但是，如果我使用以下语句，则可以成功获得结果95.

But if I use the below statement, I can get result 95 successfully.

select cast(emp as unsigned) from new2006 where IsNum(emp)=0;

有人可以帮助我吗?谢谢你.

Can anyone help me? Thanks in advanced.

PS:emp的数据类型为varchar(7).

PS:The data type of emp is varchar(7).

推荐答案

您身处严格的

You are in a strict SQL mode; as documented (emphasis added):

严格模式控制MySQL如何处理数据更改语句(例如 INSERT 或 UPDATE .值可能由于多种原因而无效.例如，它可能具有错误的列数据类型，或者可能超出范围.当要插入的新行不包含其定义中没有显式DEFAULT子句的非NULL列的值时，缺少值. (对于NULL列，如果缺少该值，则会插入NULL.)

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.)

用于不更改数据的语句，例如 SELECT ，无效值会在严格模式下生成警告，而不是错误.

如果希望UPDATE成功而不出错，则需要更改为非严格SQL模式，否则首先将字符串处理为不会引发错误的值，例如SUBSTRING_INDEX(emp, '.', 1).

If you want the UPDATE to succeed without error, you will need to change to a non-strict SQL mode, or else first manipulate the string into a value that won't throw an error, e.g. SUBSTRING_INDEX(emp, '.', 1).

这篇关于在更新语句中使用强制转换时，mysql错误1292的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！