mysql decimal c new,插入MySQL时，DECIMAL值不正确

The error I'm getting is:

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

-1

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.

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

Here is the row which fails to insert in the select query:

ID Firstname Middlename Lastname Country Latitude Longitude

04308666-3 RICHARD RICHARD JUNG 0.000000 0.000000

Inventors create query:

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 query:

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?

解决方案

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.

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