Mysql使用命令行将sql格式及csv格式导入数据库

一、Mysql使用命令行将sql格式导入数据库

1.进入MySQL：mysql -u xxx -p xxx
2.在Navicat中新建空数据库，如新建一个名为news的目标数据库 

或者在命令行中：create database news character set utf8mb4 collate utf8mb4_general_ci;
3.输入：mysql>use news;
6.导入文件：mysql>source news.sql;(英文目录）

二、Mysql使用命令行将csv格式导入数据库

1、登录mysql并临时修改权限：

msql -local-infile=1 -uroot -p

2、导入：

LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/saminfo.csv" REPLACE INTO TABLE saminfo FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n';

注意：要将待导入文件放入C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/目录下

通过show variables like "secure_file_priv"可查看默认目录

3、若出现load data 数据被截断为error1261,可以show variables like "sql_mode"

Set sql_mode ='';

否则，容易出现如下错误：

ERROR 1261 (01000): Row 1 does not contain data for all columns

ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns