进入mysql命令行
如果出现不允许外部文件的导入可以
在mysql客户端输入:
set global local_infile=1;
准备好实验数据
public static void main(String[] args) throws IOException {
BuffwTest buffwTest=new BuffwTest();
for (int i = 0; i < 10; i++) {
buffwTest.writeFile();
}
}
public void writeFile() throws IOException {
File file = new File("D:/database.txt");
FileWriter fileWriter=new FileWriter(file,true);
BufferedWriter bufferedWriter1=new BufferedWriter(fileWriter);
for (int i = 0; i < 50000; i++) {
bufferedWriter1.write("\n2022-09-19 10:04:14||0||1||699||0||2022-09-19 10:34:14||2022-09-19 10:04:14");
}
fileWriter.close();
}
执行完成 文件大小约36MB
大约50万条数据
执行sql 下面的部分语句的意思是:
文件位置
需要插入的表
字符编码
字段分隔符
文本限定符
转义符
记录分隔符
每一行文本按顺序对应的表字段
load data local infile 'D:/database.txt'
into table `order`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '||'
OPTIONALLY ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
(date,user_id,state,actualPayment,busId,endTime,createTime)
影响行数
插入成功