MySQL十进制三位整数,十进制（整数）值不正确：''mySQL

In mySQL workbench database, one of the tables has latitude, longitude and district attributes

lat: decimal (10,8)

lng: decimal (11,8)

district: int(4)

I'm trying to import data from .csv file to this table

ERROR 1366: 1366: Incorrect decimal value: '' for column 'lat' at row 1

SQL Statement:

ERROR 1366: 1366: Incorrect integer value: '' for column 'district' at row 1

SQL Statement:

INSERT INTO `db`.`myTable` (`id`, `name_en`, `icon`, `lat`, `lng`, `district`, `city`, `postal_code`)

VALUES ('686', 'Name',?, '', '', '','1', 'P.O. Box 1111')

解决方案

You have strict sql mode enabled, and you try to pass an empty string ('') as value for decimal fields in the insert. Empty string is an invalid value for a numeric field and in strict sql mode mysql generates an error if you try to insert an invalid data into a column, rather than providing a warning and use the default value (0 for numeric columns) of the particular column's data type:

Strict mode controls how MySQL handles invalid or missing values in

data-change statements such as INSERT or UPDATE. A value can be

invalid for several reasons. For example, it might have the wrong data

type for the column, or it might be out of range. A value is missing

when a new row to be inserted does not contain a value for a non-NULL

column that has no explicit DEFAULT clause in its definition. (For a

NULL column, NULL is inserted if the value is missing.) Strict mode

also affects DDL statements such as CREATE TABLE.

If strict mode is not in effect, MySQL inserts adjusted values for

invalid or missing values and produces warnings (see Section

13.7.5.40, “SHOW WARNINGS Syntax”). In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE.

Remove the strict sql mode for the session before starting the import:

SET SESSION sql_mode = ''