Invalid decimal (integer) value: '' mySQL

Question

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

lat: decimal (10.8)

lng: decimal (11.8)

area: int (4)

I am trying to import data from a CSV file into 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', 'PO Box 1111') 

Answer 1

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

Strict mode determines how MySQL handles invalid or missing values ​​in such as INSERT or UPDATE. The value may be invalid for several reasons. For example, it may have invalid data type for the column, or it may be out of range. The value is absent when the new row to be inserted does not contain a value for non-NULL that does not have an explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if a value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.

If strict mode does not work, MySQL inserts adjusted values ​​for invalid or missing values ​​and issues warnings (see section 13.7.5.40, “SHOW WARNINGS Syntax”). In strict mode, you can create this behavior using INSERT IGNORE or UPDATE IGNORE.

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

 SET SESSION sql_mode = ''