一、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