MySQL CSV file ( load and export ) 笔记

最新推荐文章于 2021-09-30 16:24:53 发布

shenyan008

最新推荐文章于 2021-09-30 16:24:53 发布

阅读量1.5k

点赞数

分类专栏： DB

本文链接：https://blog.csdn.net/shenyan008/article/details/17260021

版权

DB 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1. load from CSV files:

摘自：http://stackoverflow.com/questions/4202564/how-to-insert-selected-columns-from-csv-file-to-mysql-using-load-data-infile

    LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE t1 
    FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'  
    (@col1,@col2,@col3,@col4) set name=@col4,id=@col2 ;

 @col1,2,3,4 are variables to hold csv file columns (assume 4 ) name,id are table columns

2. export to CSV file:

    SELECT order_id,product_name,qty FROM orders
    INTO OUTFILE '/tmp/orders.txt'

3. col名字不能用desc:


  
  
   
   load data local infile '/tmp/f_books.csv' into table cmread_book
character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\r\n'
(@col1, @col2) set id=@col1,desc=@col2;

   
   用desc作为列名，上面的语句会出错：
   
   ERROR 1064 (42000): You have an error in your SQL syntax; ... near 'desc=@col2...

   
   

  
  
4. 导入csv的时候，会出现 skipped: xxx:

 Query OK, 61116 rows affected (0.66 sec) Records: 66896  Deleted: 0  Skipped: 5780  Warnings: 0

这是有些列含有 unique 属性

5. mysqlimport example (csv可以直接通过mysqlimport导入到数据库)：

mysqlimport --columns=id,name,url,download_url,is_free,book_id --host=localhost --user=root -p --delete --fields-terminated-by=, --fields-enclosed-by=\" --lines-terminated-by=\n --verbose --debug --local cmread /tmp/cmread_chapter.csv > /tmp/chapters_import.log;

6. 权限问题（导出时）

导出数据需要FILE权限：

GRANT FILE ON *.* TO 'asdfsdf'@'localhost';

不能对某个数据库指定FILE权限，FILE是全局权限，如： GRANT FILE ON text_db.* TO 'asdfsdf'@'localhost'; 这会出现：Access denied for user 'asdfsdf'@'localhost' (using password: YES)