错误代码 1292 - 截断了不正确的 DOUBLE 值 - Mysql

2021-11-20 00:00:00 mysql mysql-error-1292 truncated

我不确定这是什么错误！

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!

这是我的查询

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

推荐答案

此消息表示您正在尝试比较 WHERE 或 ON 中的数字和字符串条款.在您的查询中，唯一可能发生的地方是 ON ac.company_code = ta.company_code;要么确保它们具有相似的声明，要么使用显式 CAST 将数字转换为字符串.

This message means you're trying to compare a number and a string in a WHERE or ON clause. 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 CAST to convert the number to a string.

如果关闭 strict 模式，错误应该会变成警告.

If you turn off strict mode, the error should turn into a warning.

相关文章