526互联

Mysql导入csv文件

发布时间 2024-01-05 08:41:00作者: _fun_ny

需要将csv的数据导入mysql，最简单的方式就是使用navicat导入，方便快捷，适用于数据量较小的场景，但是如果碰到GB级别的大文件，直接通过navicat导入十分低效，可以采用mysql 提供的LOAD DATA语法进行导入，具体使用方式如下：

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','  -- 根据实际情况选择分隔符
LINES TERMINATED BY '\n'  -- 根据实际情况选择行结束符
IGNORE 1 LINES;           -- 忽略CSV文件的标题行

这种情况适用于文件的结构和数据库表结构完全相同的情景，并且文件是在mysql服务器中存储，如果文件保存在客户端则需要用LOAD DATA local INFILE '/path/to/your/file.csv'替换。
如果导入数据同时需要处理数据，可以使用这种方式：

LOAD DATA INFILE '/path/to/your/file.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, @age)
SET age = @age + 10;

这种方式需要将csv的字段按照顺序列出，需要直接存储的数据不需要用@标记，直接保存到数据库中，需要处理的数据则需要用@标记。需要直接保存的字段需要和表的名称相同
以下是我需要导入的数据,我的数据是保存在自己的电脑里，数据库使用的是云数据库，所以我将使用LOAD DATA local INFILE语法

ID,经度,纬度,GPS速度,GPS时间,地理位置名称,省,市,县,车辆归属地
4778255022036488870,117.896917,24.614983,60.0,20210509/115744,福建省厦门市海沧区,福建省,厦门市,海沧区,龙岩市
7232026150342157019,117.90361,24.538571,45.3,20210515/053949,福建省厦门市海沧区龙屿东北73米,福建省,厦门市,海沧区,南京市
4729190945200818988,117.903658,24.538541,8.0,20210516/081506,福建省厦门市海沧区龙屿东北76米,福建省,厦门市,海沧区,厦门市
2124055578813527157,117.903747,24.538576,0.0,20210515/052556,福建省厦门市海沧区龙屿东北85米,福建省,厦门市,海沧区,浦东新区
8867088016685007543,117.903803,24.538527,45.0,20210514/051022,福建省厦门市海沧区龙屿东北89米,福建省,厦门市,海沧区,苏州市
2110050212192903236,117.90383,24.538642,45.0,20210508/120352,福建省厦门市海沧区龙屿东北96米,福建省,厦门市,海沧区,漳州市

可以看到，日期格式并不规范，同时经纬度信息我需要用Mysql的Point类型存储，那么我可以使用以下语句导入:

LOAD DATA local INFILE  'E:/a.csv'
INTO TABLE trajectory_data
-- CHARACTER SET gbk  
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID, @longitude, @latitude, speed, @gps_time, location_name, province, city, county, vehicle_location)
SET location = POINT(@longitude, @latitude),
    GPS_time = STR_TO_DATE(@gps_time, '%Y%m%d/%H%i%s');

还有一点需要注意，如果你的文件编码和数据库编码不一致，需要进行编码处理，假设我的文件是GBK，但是数据库编码是UFT-8,可以使用下面的语句:

LOAD DATA local INFILE  'E:/a.csv'
INTO TABLE trajectory_data
CHARACTER SET gbk
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID, @longitude, @latitude, speed, @gps_time, @location_name, @province, @city, @county, @vehicle_location)
SET location = POINT(@longitude, @latitude),
   location_name = CONVERT(@location_name USING utf8),
    province = CONVERT(@province USING utf8),
    city = CONVERT(@city USING utf8),
    county = CONVERT(@county USING utf8),
    vehicle_location = CONVERT(@vehicle_location USING utf8),
    GPS_time = STR_TO_DATE(@gps_time, '%Y%m%d/%H%i%s');