MYSQL 截断了不正确的 DOUBLE 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3456258/
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
MYSQL Truncated incorrect DOUBLE value
提问by Emanuel
When the SQL query below is executed:
当执行下面的 SQL 查询时：
UPDATE shop_category
SET name = 'Secolul XVI - XVIII'
AND name_eng = '16th to 18th centuries'
WHERE category_id = 4768
The following error is raised:
出现以下错误：
1292 - Truncated incorrect DOUBLE value: 'Secolul XVI - XVIII'
How to fix this?
如何解决这个问题？
shop_categorytable structure:
shop_category表结构：
category_id mediumint(8)
name varchar(250)
name_eng varchar(250)
回答by Darin Dimitrov
You don't need the ANDkeyword. Here's the correct syntax of the UPDATE statement:
UPDATE
shop_category
SET
name = 'Secolul XVI - XVIII',
name_eng = '16th to 18th centuries'
WHERE
category_id = 4768
回答by Sidra
I was getting this exception not because of AND instead of comma, in fact I was having this exception just because I was not using apostrophes in where clause.
我得到这个例外不是因为 AND 而不是逗号，事实上我有这个例外只是因为我没有在 where 子句中使用撇号。
Like my query was
就像我的查询是
update table set coulmn1='something' where column2 in (00012121);
when I changed where clause to where column2 in ('00012121');then the query worked fine for me.
当我将 where 子句更改为 where column2 in ('00012121');然后查询对我来说很好。
回答by codaddict
Try replacing the ANDwith ,
请尝试更换AND与,
UPDATE shop_category
SET name = 'Secolul XVI - XVIII', name_eng = '16th to 18th centuries'
WHERE category_id = 4768
The UPDATE Syntaxshows comma should be used as the separator.
该UPDATE语法显示逗号应作为分隔符。
回答by halfpastfour.am
What it basically is
它基本上是什么
It's incorrect syntax that causes MySQL to think you're trying to do something with a column or parameter that has the incorrect type "DOUBLE".
错误的语法会导致 MySQL 认为您正在尝试对具有错误类型“DOUBLE”的列或参数执行某些操作。
Learn from my mistake
从我的错误中吸取教训
In my case I updated the varchar column in a table setting NULLwhere the value 0stood. My update query was like this:
在我的情况下，我更新NULL了值所在的表设置中的varchar 列0。我的更新查询是这样的：
UPDATE myTable SET myValue = NULL WHERE myValue = 0;
Now, since the actual type of myValueis VARCHAR(255)this gives the warning:
现在，由于实际类型myValue是VARCHAR(255)this 给出了警告：
+---------+------+-----------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'value xyz' |
+---------+------+-----------------------------------------------+
And now myTableis practically empty, because myValueis now NULLfor EVERY ROW in the table! How did this happen?
*internal screaming*
现在myTable几乎是空的，因为myValue现在NULL是表中的每一行！这怎么发生的？
*内部尖叫*
Over 30k rows now have missing data.
*internal screaming intensifies*
现在有超过 30k 行缺少数据。
*内部尖叫加剧*
Thank goodness for backups. I was able to recover all the data.
*internal screaming intensity lowers*
谢天谢地有备份。我能够恢复所有数据。
*内部尖叫强度降低*
The corrected query is as follows:
更正后的查询如下：
UPDATE myTable SET myValue = NULL WHERE myValue = '0';
^^^
Quotation here!
I wish this was more than just a warning so it's less dangerous to forget those quotes.
我希望这不仅仅是一个警告，这样忘记这些报价就不会那么危险了。
*End internal screaming*
*结束内心的尖叫*
回答by hrvoj3e
I just wasted my time on this and wanted to add an additional case where this error presents itself.
我只是浪费了我的时间，并想添加一个额外的案例来显示这个错误。
SQL Error (1292): Truncated incorrect DOUBLE value: 'N0003'
Test data
测试数据
CREATE TABLE `table1 ` (
`value1` VARCHAR(50) NOT NULL
);
INSERT INTO table1 (value1) VALUES ('N0003');
CREATE TABLE `table2 ` (
`value2` VARCHAR(50) NOT NULL
);
INSERT INTO table2 (value2)
SELECT value1
FROM table1
WHERE 1
ORDER BY value1+0
The problem is ORDER BY value1+0- type casting.
问题是ORDER BY value1+0- 类型转换。
I know that it does not answer the question but this is the first result on Google for this error and it should have other examples where this error presents itself.
我知道它没有回答这个问题，但这是谷歌上关于这个错误的第一个结果，它应该有其他例子来说明这个错误。
回答by Adrian Goia
Mainly invalid query strings will give this warning.
主要是无效的查询字符串会发出此警告。
Wrong due to a subtle syntax error (misplaced right parenthesis) when using INSTRfunction:
使用INSTR函数时由于细微的语法错误（右括号错位）而错误：
INSERT INTO users (user_name) SELECT name FROM site_users WHERE
INSTR(status, 'active'>0);
Correct:
正确的：
INSERT INTO users (user_name) SELECT name FROM site_users WHERE
INSTR(status, 'active')>0;
回答by Adam Winnipass
It seems mysql handles the type casting gracefully with SELECT statements. The shop_id field is of type varchar but the select statements works
似乎 mysql 使用 SELECT 语句优雅地处理类型转换。shop_id 字段的类型为 varchar，但 select 语句有效
select * from shops where shop_id = 26244317283;
But when you try updating the fields
但是当您尝试更新字段时
update stores set store_url = 'https://test-url.com' where shop_id = 26244317283;
It fails with error Truncated incorrect DOUBLE value: '1t5hxq9'
它失败并显示错误截断不正确的 DOUBLE 值：'1t5hxq9'
You need to put the shop_id 26244317283 in quotes '26244317283' for the query to work since the field is of type varchar not int
您需要将 shop_id 26244317283 放在引号 '26244317283' 中，以便查询工作，因为该字段的类型为 varchar 而不是 int
update stores set store_url = 'https://test-url.com' where shop_id = '26244317283';
回答by Bytech
I experienced this error when using bindParam, and specifying PDO::PARAM_INT where I was actually passing a string. Changing to PDO::PARAM_STR fixed the error.
我在使用 bindParam 并在我实际传递字符串的位置指定 PDO::PARAM_INT 时遇到了这个错误。更改为 PDO::PARAM_STR 修复了错误。
回答by thephper
I did experience this error when I tried doing an WHERE EXIST where the subquery matched 2 columns that accidentially was different types. The two tables was also different storage engines.
当我尝试执行 WHERE EXIST 时，我确实遇到了这个错误，其中子查询匹配了 2 个意外不同类型的列。这两个表也是不同的存储引擎。
One column was a CHAR (90) and the other was a BIGINT (20).
一列是 CHAR (90)，另一列是 BIGINT (20)。
One table was InnoDB and the other was MEMORY.
一张表是 InnoDB，另一张是 MEMORY。
Part of query:
部分查询：
[...] AND EXISTS (select objectid from temp_objectids where temp_objectids.objectid = items_raw.objectid );
Changing the column type on the one column from BIGINT to CHAR solved the issue.
将一列上的列类型从 BIGINT 更改为 CHAR 解决了该问题。
回答by Joe Phillips
If you're getting this problem with an insert that looks like the one below, the problem may simply be the lack of a space between --and the comment text:
如果您在插入如下所示的插入时遇到此问题，则问题可能仅仅是--注释文本之间缺少空格：
insert into myTable (a, b, c)
values (
123 --something
,345 --something else
,567 --something something else
);
The problem with this is that the --somethingshould actually be -- somethingwith a space.
问题在于--something实际上应该-- something有一个空格。
