오류 코드 1292-잘못된 DOUBLE 값 잘림-MySQL

오류 코드 1292-잘못된 DOUBLE 값 잘림-MySQL

---

이 오류가 무엇인지 잘 모르겠습니다!

#1292 - Truncated incorrect DOUBLE value: 

이중 값 필드 또는 데이터가 없습니다!

나는 이것을 알아 내기 위해 한 시간을 낭비했다!

여기 내 질문입니다

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

여기에 결과가 들어가는 테이블에 대한 내 쇼 생성 테이블이 있습니다.

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

---

이 메시지는 WHEREor ON절 에서 숫자와 문자열을 비교하려고 함을 의미합니다 . 귀하의 쿼리에서 발생할 수있는 유일한 잠재적 인 장소는 ON ac.company_code = ta.company_code; 유사한 선언이 있는지 확인하거나 명시 적을 사용 CAST하여 숫자를 문자열로 변환하십시오.

strict모드 를 끄면 오류가 경고로 바뀝니다.

---

쿼리에 구문 오류 또는 원치 않는 문자가 있었기 때문에이 오류를 수정했지만 MySQL은이를 포착 할 수 없었습니다. and업데이트하는 동안 여러 필드 사이 에서 사용 했습니다.

update user 
set token='lamblala', 
    accessverion='dummy' and 
    key='somekey' 
where user = 'myself'

위 쿼리의 문제는 and쉼표 ( ,) 로 대체 하여 해결할 수 있습니다.

---

나는 같은 문제에 직면했다. varchar (100) 열을 숫자 1과 비교하려고합니다. 1292 오류가 발생했습니다. 1 ( '1') 주위에 작은 따옴표를 추가하여 수정되었습니다.

위의 설명에 감사드립니다

---

TL; DR

이것은 또한 OR문자열 열 / 리터럴 에 적용 하여 발생할 수 있습니다 .

풀 버전

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:

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:

string_col1 || '_' || string_col2 || '_' || string_col3

(|| is string concatenation in Oracle). The solution was to use

concat(string_col1, '_', string_col2, '_', string_col3)

instead.

---

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.

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.

---

In my case it was a view (highly nested, view in view) insertion causing the error in 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.

---

Had this issue with ES6 and TypeORM while trying to pass .where("order.id IN (:orders)", { orders }), where orders was a comma separated string of numbers. When I converted to a template literal, the problem was resolved.

.where(`order.id IN (${orders})`);

---

It is possible that this error has resulted from using the not equals operator != in a where clause with a list of multiple or values, such as

where columnName !=('A'||'B')

This can be resolved by using

where columnName not in ('A','B')

ReferenceURL : https://stackoverflow.com/questions/16068993/error-code-1292-truncated-incorrect-double-value-mysql