MySQL数据的导出和导入

导出：

方法一：

mysql mysql -h172.16.160.182 -uroot -p -Ne "use test; select * from items;" > ~/workspace/items.txt 

方法二：

 SELECT * FROM filesql   into  outfile '/var/lib/mysql-files/ratings5.txt'  fields terminated by ','  lines terminated by '\r\n' ;

不重复查询：

mysql mysql -h172.16.160.182 -uroot -p -Ne "use test; select distinct userid  from filesql;" > ~/workspace/tools/userid.txt

导入

：
我的数据格式：

1,122,5,838985046
1,185,5,838983525
1,231,5,838983392
1,292,5,838983421
1,316,5,838983392
1,329,5,838983392

新建一张和你数据对应的表

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info           |
| items          |
| movie          |
+----------------+
3 rows in set (0.00 sec)

mysql> create table filesql (
    -> userid int(10),
    -> itemid int(10),
    -> pre int(10),
    -> time int(20));
Query OK, 0 rows affected (0.01 sec)

mysql> desc  filesql;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| userid | int(10) | YES  |     | NULL    |       |
| itemid | int(10) | YES  |     | NULL    |       |
| pre    | int(10) | YES  |     | NULL    |       |
| time   | int(20) | YES  |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

mysql> load data infile '/var/lib/mysql-files/ratings5.txt' into table filesql fields terminated by ',' lines terminated by '\n' ;
Query OK, 3190 rows affected (0.04 sec)
Records: 3190  Deleted: 0  Skipped: 0  Warnings: 0

load data infile '/var/lib/mysql-files/ratings5.txt' into table filesql fields terminated by ',' lines terminated by '\n' ;

可能遇到的问题：http://blog.csdn.net/ljhandlwt/article/details/52818548
Data truncated for column ‘time’ at row 1
解决：’\n’改为‘\r\n’

导入指定字段：

mysql> desc input_info;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(10) | NO   | PRI | NULL    | auto_increment |
| userid | int(10) | YES  |     | NULL    |                |
| itemid | int(10) | YES  |     | NULL    |                |
| pre    | double  | YES  |     | NULL    |                |
| time   | int(10) | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> load data infile ‘/var/lib/mysql-files/part-m-00000’ into table input_info(userid,itemid,pre,time) ;

Query OK, 3190 rows affected (0.13 sec)
Records: 3190  Deleted: 0  Skipped: 0  Warnings: 0