在mySQL工作台数据库中,表之一具有纬度,经度和区域属性
lat:十进制(10,8)
lng:十进制(11,8)
地区:int(4)
我正在尝试将数据从.csv文件导入此表
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')
解决方法:
您已启用严格的sql模式,并且尝试将空字符串(”)传递为插入中小数字段的值.空字符串是数字字段的无效值,在strict sql mode中,如果您尝试将无效数据插入到列中,而不是提供警告并使用特定列数据的默认值(数字列为0),则mysql会生成错误.类型:
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.
在开始导入之前,请删除会话的严格sql模式:
SET SESSION sql_mode = ''