错误代码 1292 - 截断了不正确的 DOUBLE 值 - Mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16068993/
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
Error Code 1292 - Truncated incorrect DOUBLE value - Mysql
提问by Mike
I am not sure what is this error!
我不确定这是什么错误！
#1292 - Truncated incorrect DOUBLE value:
I don't have double value field or data!
我没有双值字段或数据！
I have wasted a whole hour trying to figure this out!
我浪费了整整一个小时试图弄清楚这一点！
here is my query
这是我的查询
INSERT INTO call_managment_system.contact_numbers
(account_id, contact_number, contact_extension, main_number, created_by)
SELECT
ac.account_id,
REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') AS Phone,
IFNULL(ta.ext, '') AS extention,
'1' AS MainNumber,
'2' AS created_by
FROM
cvsnumbers AS ta
INNER JOIN accounts AS ac ON ac.company_code = ta.company_code
WHERE
LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(ta.phone_number, '-', ''), ' ', ''), ')', ''),'(','') ) = 10
here is my show create table for the table which the results are going into
这是我为结果进入的表创建表
CREATE TABLE `contact_numbers` ( ?
`number_id` int(10) unsigned NOT NULL AUTO_INCREMENT, ?
`account_id` int(10) unsigned NOT NULL DEFAULT '0', ?
`person_id` int(11) NOT NULL DEFAULT '0', ?
`contact_number` char(15) NOT NULL, ?
`contact_extension` char(10) NOT NULL DEFAULT '', ?
`contact_type` enum('Primary','Direct','Cell','Fax','Home','Reception','Office','TollFree') NOT NULL DEFAULT 'Primary', ?
`contact_link` enum('Account','PDM','Other') NOT NULL DEFAULT 'Account', ?
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0 = inactive, 1=active',
?`main_number` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = main phone number', ?
`created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, ?
`created_by` int(11) NOT NULL, ?
`modified_on` datetime DEFAULT NULL, ?
`modified_by` int(11) NOT NULL DEFAULT '0', ?
PRIMARY KEY (`number_id`), ?
KEY `account_id` (`account_id`), ?
KEY `person_id` (`person_id`)
) ENGINE=InnoDB AUTO_INCREMENT=534 DEFAULT CHARSET=utf8
回答by Barmar
This message means you're trying to compare a number and a string in a WHEREor ONclause. In your query, the only potential place where that could be occurring is ON ac.company_code = ta.company_code; either make sure they have similar declarations, or use an explicit CASTto convert the number to a string.
此消息表示您正在尝试比较WHEREorON子句中的数字和字符串。在您的查询中，唯一可能发生的地方是ON ac.company_code = ta.company_code；要么确保它们具有相似的声明，要么使用显式CAST将数字转换为字符串。
If you turn off strictmode, the error should turn into a warning.
如果关闭strict模式，错误应该变成警告。
回答by user1926248
I corrected this error as there was a syntax error or some unwanted characters in the query, but MySQL was not able to catch it. I was using andin between multiple fields during update, e.g.
我更正了这个错误，因为查询中有语法错误或一些不需要的字符，但 MySQL 无法捕获它。我and在更新期间在多个字段之间使用，例如
update user
set token='lamblala',
accessverion='dummy' and
key='somekey'
where user = 'myself'
The problem in above query can be resolved by replacing andwith comma(,)
上述查询中的问题可以通过替换and为逗号( ,)来解决
回答by ForeverLearner
I was facing the same issue. Trying to compare a varchar(100) column with numeric 1. Resulted in the 1292 error. Fixed by adding single quotes around 1 ('1').
我面临着同样的问题。尝试将 varchar(100) 列与数字 1 进行比较。导致了 1292 错误。通过在 1 ('1') 周围添加单引号来修复。
Thanks for the explanation above
谢谢楼上的解释
回答by Frank Schmitt
TL; DR
TL; DR
This might also be caused by applying ORto string columns / literals.
这也可能是由于应用于OR字符串列/文字引起的。
Full version
完整版本
I got the same error message for a simple INSERTstatement involving a view:
对于INSERT涉及视图的简单语句，我收到了相同的错误消息：
insert into t1 select * from v1
although all the source and target columns were of type VARCHAR. After some debugging, I found the root cause; the view contained this fragment:
尽管所有源和目标列的类型都是VARCHAR. 经过一番调试，我找到了根本原因；该视图包含以下片段：
string_col1 OR '_' OR string_col2 OR '_' OR string_col3
which presumably was the result of an automatic conversion of the following snippet from Oracle:
这大概是以下来自 Oracle 的片段的自动转换的结果：
string_col1 || '_' || string_col2 || '_' || string_col3
(||is string concatenation in Oracle). The solution was to use
（||是 Oracle 中的字符串连接）。解决方案是使用
concat(string_col1, '_', string_col2, '_', string_col3)
instead.
反而。
回答by Kylan Hurt
When I received this error I believe it was a bug, however you should keep in mind that if you do a separate query with a SELECT statement and the same WHERE clause, then you can grab the primary ID's from that SELECT: SELECT CONCAT(primary_id, ',')) statement and insert them into the failed UPDATE query with conditions -> "WHERE [primary_id] IN ([list of comma-separated primary ID's from the SELECT statement)" which allows you to alleviate any issues being caused by the original (failed) query's WHERE clause.
当我收到此错误时，我认为这是一个错误，但是您应该记住，如果您使用 SELECT 语句和相同的 WHERE 子句进行单独的查询，那么您可以从该 SELECT:)SELECT CONCAT(primary_id, ',')语句中获取主 ID并插入将它们添加到带有条件的失败 UPDATE 查询中 ->“WHERE [primary_id] IN（[SELECT 语句中逗号分隔的主 ID 列表）”，这允许您减轻由原始（失败）查询的 WHERE 子句引起的任何问题。
For me, personally, when I was using quotes for the values in the "WHERE ____ IN ([values here])", only 10 of the 300 expected entries were being affected which, in my opinion, seems like a bug.
就我个人而言，当我对“WHERE ____ IN ([此处的值])”中的值使用引号时，300 个预期条目中只有 10 个受到影响，在我看来，这似乎是一个错误。
回答by enharmonic
It is possible that this error has resulted from using the not equals operator !=in a whereclause with a list of multiple orvalues, such as
此错误可能是由于!=在具有where多个or值列表的子句中使用不等于运算符导致的，例如
where columnName !=('A'||'B')
This can be resolved by using
这可以通过使用解决
where columnName not in ('A','B')
回答by Varadhan Work
If you have used CHECK CONSTRAINTon table for string field length
如果您已在表上使用CHECK CONSTRAINT来获取字符串字段长度
e.g: to check username length >= 8
例如：检查用户名长度 >= 8
use:
用：
CHECK (CHAR_LENGTH(username)>=8)
instead of
代替
CHECK (username>=8)
fix the check constraint if any have wrong datatype comparison
如果有错误的数据类型比较，请修复检查约束
回答by Nae
In my case it was a view (highly nested, view in view) insertion causing the error in mysql-5.6:
在我的情况下，它是一个视图（高度嵌套，视图中的视图）插入导致mysql-5.6 中的错误：
CREATE TABLE tablename AS
SELECT * FROM highly_nested_viewname
;
The workaround we ended up doing was simulating a materialized view (which is really a table) and periodically insert/update it using stored procedures.
我们最终做的解决方法是模拟一个物化视图（它实际上是一个表）并使用存储过程定期插入/更新它。
回答by mdawsondev
Had this issue with ES6 and TypeORM while trying to pass .where("order.id IN (:orders)", { orders }), where orderswas a comma separated string of numbers. When I converted to a template literal, the problem was resolved.
ES6 和 TypeORM 在尝试传递时遇到了这个问题.where("order.id IN (:orders)", { orders })，其中orders是逗号分隔的数字字符串。当我转换为模板文字时，问题就解决了。
.where(`order.id IN (${orders})`);
