MySQL CSV file 


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)