Err 1292-截断了错误的DOUBLE值 [英] Err 1292 - Truncated incorrect DOUBLE value

问题描述

我有这两个表:

OLD:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `crdate` int(11) DEFAULT NULL,
  `fb_id` text,
  `email` varchar(64) DEFAULT NULL,
  `fb_access_token` varchar(256) DEFAULT NULL,
  `display_name` varchar(128) DEFAULT NULL,
  `first_name` varchar(128) DEFAULT NULL,
  `middle_name` varchar(128) DEFAULT NULL,
  `last_name` varchar(128) DEFAULT NULL,
  `gender` varchar(128) DEFAULT NULL,
  `timezone` tinyint(4) DEFAULT NULL,
  `locale` varchar(16) DEFAULT NULL,
  `fb_profile_url` text,
  `balanced_id` text,
  `token` varchar(100) DEFAULT NULL,
  `address_id` int(11) DEFAULT NULL,
  `admin` tinyint(1) DEFAULT '0',
  `zip` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

新:

CREATE TABLE IF NOT EXISTS `friendzy`.`users_new` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(64) NULL DEFAULT NULL,
  `display_name` VARCHAR(128) NULL DEFAULT NULL,
  `first_name` VARCHAR(128) NULL DEFAULT NULL,
  `middle_name` VARCHAR(128) NULL DEFAULT NULL,
  `last_name` VARCHAR(128) NULL DEFAULT NULL,
  `gender` VARCHAR(128) NULL DEFAULT NULL,
  `redis_token` VARCHAR(100) NULL DEFAULT NULL,
  `admin` TINYINT(1) NULL DEFAULT '0',
  `api_token` VARCHAR(255) NULL DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `salt` VARCHAR(255) NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT '0',
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `locked` TINYINT(1) NULL DEFAULT NULL,
  `expired` TINYINT(1) NOT NULL DEFAULT '0',
  `expired_at` TIMESTAMP NULL DEFAULT NULL,
  `confirmation_token` VARCHAR(255) NULL DEFAULT NULL,
  `password_requested_at` TIMESTAMP NULL DEFAULT NULL,
  `credentials_expired` TINYINT(1) NULL DEFAULT NULL,
  `credentials_expire_at` TIMESTAMP NULL DEFAULT NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `modified` TIMESTAMP NULL DEFAULT NULL,
  `modified_by` VARCHAR(255) NULL DEFAULT NULL,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 164
DEFAULT CHARACTER SET = latin1;

我试图用这句话将数据从旧数据复制到新数据:

And im trying yo copy the data from the old one to the new one with this sentence:

INSERT INTO `users_new` (`id`, `email`, `display_name`, `first_name`, `middle_name`, `last_name`, `gender`, `redis_token`, `admin`, `api_token`, `password`, `salt`, `enabled`, `last_login`, `locked`, `expired`, `expired_at`, `confirmation_token`, `password_requested_at`, `credentials_expired`, `credentials_expire_at`, `created`, `modified`, `modified_by`, `deleted_at`)
  SELECT `users`.`id`, `users`.`email`, `users`.`display_name`, `users`.`first_name`, `users`.`middle_name`, `users`.`last_name`, `users`.`gender`, if(`users`.`token`, `users`.`token`, md5(1)), `users`.`admin`, md5(1), md5(1), md5(1), 1, NULL, 0, 0, NULL, md5(1), NULL, 0, NULL, NOW(), NOW(), 'UPDATE SCRIPT', NULL
  FROM users;

但是我得到这个错误:

[Err] 1292-截断了错误的DOUBLE值: '4043a809f18ffc8be824064934797d3a'

[Err] 1292 - Truncated incorrect DOUBLE value: '4043a809f18ffc8be824064934797d3a'

推荐答案

这是引发的那些糟糕的错误之一，可能与实际问题无关.我的建议是更改您的

This is one of those really crappy errors that gets thrown that might not have anything to do with the actual problem. My suggestion would be to change your

if(`users`.`token`, `users`.`token`, md5(1)) 

到

 COALESCE(`users`.`token`, md5(1))

然后查看它是否不能自行修复.

And see if it doesn't fix itself.

这篇关于Err 1292-截断了错误的DOUBLE值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！