Incorrect decimal (integer) value: ' ' myS

问题:

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')

回答1:

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 = ''

回答2:

Remove STRICT_TRANS_TABLE from global sql_mode. You might required other attributes such as NO_ZERO_DATE in global sql_mode if already set.

Set this under [mysqld] in my.cnf (or /etc/mysql/mysql.conf.d/mysqld.cnf) depending on your server version.

[mysqld]  
sql_mode = "NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"