MySQL CSV file ( load and export ) 笔记

来源：互联网发布：朋友借身份证开淘宝店编辑：程序博客网时间：2024/06/08 12:31

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_bookcharacter 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)

0 0