插入MySQL时，DECIMAL值不正确 [英] Incorrect DECIMAL value when inserting MySQL

问题描述

我得到的错误是：

错误1366（HY000）：不正确的DECIMAL值：列``0''在行
-1

ERROR 1366 (HY000): Incorrect DECIMAL value: '0' for column '' at row -1

我正在尝试归一化数据库并确保数据类型正确。将 BASE_TABLE 中的数据插入名为 Inventors 的新表中。

I'm trying to normalize a database and ensuring that the data types are correct. Inserting data from BASE_TABLE into a new table named Inventors.

这是我要插入的查询。如果我从 select 查询中手动手动一行，它可以正确地插入到Inventors表中。

This is the query I'm using to insert. If I take a single row manually from the select query it can correctly insert into the Inventors table.

但是，像这样运行查询，我立即得到上面的错误。

However, running the query like this I instantly get the error above.

INSERT INTO
    Inventors(ID,Firstname,Middlename,Lastname,Country,Latitude,Longitude)
SELECT DISTINCT
    Inventor_ID as ID,
    Firstname,
    Middlename,
    Lastname,
    Country,
    cast(Latitude as decimal(11,6)) as Latitude,
    cast(Longitude as decimal(11,6)) as Longitude
FROM
    BASE_TABLE

此处是未能插入 select 查询：

ID          Firstname   Middlename  Lastname    Country     Latitude    Longitude
04308666-3  RICHARD     RICHARD     JUNG                    0.000000    0.000000

发明家创建查询：

CREATE TABLE `Inventors` (
  `ID` varchar(55) NOT NULL DEFAULT '',
  `Firstname` varchar(255) DEFAULT NULL,
  `Middlename` varchar(255) DEFAULT NULL,
  `Lastname` varchar(255) DEFAULT NULL,
  `Country` varchar(255) DEFAULT NULL,
  `Latitude` decimal(11,8) DEFAULT NULL,
  `Longitude` decimal(11,8) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BASE_TABLE 创建查询：

CREATE TABLE `BASE_TABLE` (
  `Firstname` varchar(255) DEFAULT NULL,
  `Middlename` varchar(255) DEFAULT NULL,
  `Lastname` varchar(255) DEFAULT NULL,
  `Country` varchar(255) DEFAULT NULL,
  `Zipcode` varchar(255) DEFAULT NULL,
  `Latitude` varchar(255) DEFAULT NULL,
  `Longitude` varchar(255) DEFAULT NULL,
  `InvSeq` int(11) DEFAULT NULL,
  `Patent` varchar(255) DEFAULT NULL,
  `AppYear` year(4) DEFAULT NULL,
  `ApplyYear` year(4) DEFAULT NULL,
  `PubYear` year(4) NOT NULL,
  `AppDate` varchar(255) DEFAULT NULL,
  `Assignee` varchar(255) DEFAULT NULL,
  `AsgNum` varchar(255) DEFAULT NULL,
  `Class` varchar(255) DEFAULT NULL,
  `Coauthor` varchar(255) DEFAULT NULL,
  `Invnum` varchar(255) DEFAULT NULL,
  `Invnum_N` varchar(255) DEFAULT NULL,
  `Record_ID` varchar(255) NOT NULL,
  `Inventor_ID` varchar(255) DEFAULT NULL,
  `Match_Level` tinyint(4) DEFAULT NULL,
  `Company_ID` varchar(255) DEFAULT NULL,
  `Classification` varchar(255) DEFAULT NULL,
  `Citing` mediumint(9) DEFAULT NULL,
  `Cited` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`Record_ID`),
  KEY `Inventor_ID` (`Inventor_ID`),
  KEY `Patent` (`Patent`),
  KEY `Patent_2` (`Patent`,`Inventor_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

有人可以帮我解决这个问题吗？

Can anyone help me on this issue?

推荐答案

我发现了问题。 BASE_TABLE 中的纬度（VARCHAR）和经度（VARCHAR）具有空字符串''的值。

I found the problem. Latitude (VARCHAR) and Longitude (VARCHAR) in BASE_TABLE had the value of an empty string ''. This caused MySQL to cast to an incorrect value for some reason.

我通过将空字符串替换为 NULL 在选择查询中（见下文）。

I solved this by replacing the empty string with NULL in the select query (See below).

INSERT INTO
    Inventors
SELECT DISTINCT
    Inventor_ID as ID,
    Firstname,
    Middlename,
    Lastname,
    Country,
    IF(Latitude='',NULL,CAST(Latitude as decimal(11,6))) as Latitude,
    IF(Longitude='',NULL,CAST(Longitude as decimal(11,6))) as Longitude
FROM
    BASE_TABLE

这篇关于插入MySQL时，DECIMAL值不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！