Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

62.9. OUTFILE/LOAD DATA INFILE

查询结果输出到文件

SELECT * FROM tablename INTO OUTFILE '/tmp/tablename.txt';
		

使用tee将屏幕输出到文件

		
mysql>tee /home/neo/screen.txt
mysql>select * from user;
mysql>exit
		
		
		
SELECT * INTO OUTFILE '/home/mark/Orders.txt'
  FIELDS
  TERMINATED BY = ','
  FROM Orders
  WHERE Order_Date >= '2000-01-01'
		
		
		
LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;
		
		

62.9.1. Export data to CSV from MySQL

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
			

62.9.2. Import data from CSV file.

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\hx.csv' IGNORE INTO TABLE `tmp`.`creditlog`
CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n'
(`ctime`, `login`, `mode`, `type`, `prevavailcredit`, `change`, `newavailcredit`, `comment`);