mysql - MariaDB/MySQL 数据库中出现错误 : Incorrect integer value: with Inserting tables (. csv 文件)

Question

我正在尝试将酒店表导入数据库。然而，我似乎收到一个错误，我无法花费几个小时来修复它!这是我收到的错误:

整数值不正确:第 1 行“hotelId”列的“wolfinns1”

auto_increment 中的第一列，但它似乎尝试从 cvs 文件中读取，尽管那里没有条目!以下是hotels 表和sql 命令的记录。顺便说一句，我已经将所有文件转换为 Unix 格式。

==== hotesls.csv =============
wolfinns1,midas1st,Fayetteville,NC,9101231234,14
wolfinns2,midas2st,Raleigh,NC,9191231234,15
wolfinns3,midas3st,Los Angeles,CA,3101231234,16
wolfinns4,midas4st,New York,NY,2121231234,17
==========================

这是架构:

+-----------+-----------------+------+-----+---------+------------
| Field     | Type            | Null | Key | Default | Extra   
+-----------+-----------------+------+-----+---------+------------
| hotelId   | int(9) unsigned | NO   | PRI | NULL    | auto_increment 
| name      | varchar(50)     | NO   |     | NULL    |               
| address   | varchar(75)     | NO   |     | NULL    |                
| city      | varchar(50)     | NO   |     | NULL    |                
| state     | char(2)         | NO   |     | NULL    |                
| phone     | varchar(20)     | NO   | UNI | NULL    |                
| managerId | int(9) unsigned | NO   | MUL | NULL    |        
+-----------+-----------------+------+-----+---------+------------

现在发出 sql 命令:

MariaDB [xzheng6]> LOAD DATA LOCAL INFILE 'hotels.csv' INTO TABLE hotels FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' STARTING BY '';
Query OK, 0 rows affected, 16 warnings (0.00 sec) 
Records: 4 Deleted: 0 Skipped: 4 Warnings: 16

这是警告!

MariaDB [xzheng6]> show warnings\g
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'wolfinns1' for column 'hotelId' at row 1 |
| Warning | 1265 | Data truncated for column 'state' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns

提前非常感谢您。CS

Answer 1

来自mysql documentation

By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col_name_or_user_var [, col_name_or_user_var] ...);

因此，提供一个不包含 hotelID 的列列表就可以了。