MySQL数据导出与导入

1.利用CSV存储引擎加载数据

       

mysql> create table csv2(id int not null default 0,name varchar(20) not null,addre varchar(20) not null) engine=csv;
Query OK, 0 rows affected (0.02 sec)

[root@localhost importtest]# more /root/csvtest.txt >csv2.CSV

mysql> select * from csv2;
+-------+--------+----------+
| id | name | addre |
+-------+--------+----------+
| 10001 | nihao | beijing |
| 10002 | nihao1 | beijing1 |
| 10003 | nihao2 | beijing2 |
| 10004 | nihao3 | beijing3 |
+-------+--------+----------+
4 rows in set (0.01 sec)

2.mysqlimport命令行工具导入数据

mysql> create table csv3(id int not null default 0,name varchar(20) not null,addre varchar(20));
Query OK, 0 rows affected (0.04 sec)

[root@localhost ~]# mysqlimport -uroot -p -h 192.168.1.194 -l importtest -f --fields-terminated-by=',' /root/csv3.txt
Enter password:

报错了：
mysqlimport: Error: 1290, The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, when using table: csv3

百度了报错原因：

通过报错的提示可以发现是由于secure-file-priv变量的设置所引起的，查看该变量的设置，默认的路径是/var/lib/mysql-files/

mysql> show variables like '%secure%';
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.02 sec)

 

说明：

secure_file_prive=null   限制mysqld 不允许导入导出

secure_file_priv=/var/lib/mysql-files/   限制mysqld的导入导出只能发生在/var/lib/mysql-files/目录下

secure_file_priv=' '     不对mysqld的导入导出做限制

把导入的文件拷贝过去： 

[root@localhost ~]# cp csv3.txt  /var/lib/mysql-files/

[root@localhost mysql-files]# mysqlimport -uroot -p -h 192.168.1.194 -l importtest --fields-terminated-by=',' /var/lib/mysql-files/csv3.txt
Enter password:
importtest.csv3: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from csv3;
+-------+--------+----------+
| id | name | addre |
+-------+--------+----------+
| 10005 | nihao | beijing |
| 10006 | nihao1 | beijing1 |
| 10007 | nihao2 | beijing2 |
| 10008 | nihao3 | beijing3 |
+-------+--------+----------+
4 rows in set (0.01 sec)

 

 3.SQL语句导入数据

mysql> load data infile '/root/loadfile.txt' into table loadfiletest fields terminated by ',';

报错了：
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

[root@localhost ~]# cp loadfile.txt /var/lib/mysql-files/
[root@localhost ~]# cd /var/lib/mysql-files/
[root@localhost mysql-files]# ls
csv3.txt loadfile.txt

mysql> load data infile '/var/lib/mysql-files/loadfile.txt' into table loadfiletest fields terminated by ',';
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

 导入数据排除前一行

mysql> load data infile '/var/lib/mysql-files/loadfile2.txt' into table loadfiletest2 fields terminated by ',' ignore 1 lines;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from loadfiletest2;
+-------+--------+----------+
| id    | name   | addre    |
+-------+--------+----------+
| 10010 | nihao1 | beijing1 |
| 10011 | nihao2 | beijing2 |
| 10012 | nihao3 | beijing3 |
+-------+--------+----------+
3 rows in set (0.01 sec)

 4.导入Excel

新建一个excel表格，然后另存为csv格式的文件，新建表，导入数据， 参数  FIELDS  TERMINATED BY ',' 字段以逗号结束；

mysql> load data infile '/var/lib/mysql-files/load.csv' into table csv4 fields terminated by ',' ignore 1 lines;
Query OK, 19 rows affected (0.03 sec)
Records: 19  Deleted: 0  Skipped: 0  Warnings: 0

 参数  enclosed by 指定列值的分割符：

[root@localhost mysql-files]# cat load1.csv
ip,ϵͳ,ƽ̨ 
192.168.1.1,"CentOS7.1",ecs01
192.168.1.2,"CentOS7.2",ecs02
192.168.1.3,"CentOS7.3",ecs03
192.168.1.4,"CentOS7.4",ecs04

mysql> load data infile '/var/lib/mysql-files/load1.csv' into table csv5 fields terminated by ',' enclosed by '"' ignore 1 lines ;
Query OK, 4 rows affected (0.02 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

 如果表的列比数据列多该如何导入：

mysql> desc csv6;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ip      | varchar(15) | YES  |     | NULL    |       |
| xitong  | varchar(20) | YES  |     | NULL    |       |
| pingtai | varchar(20) | YES  |     | NULL    |       |
| renyuan | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

[root@localhost mysql-files]# cat load1.csv
ip,ϵͳ,ƽ̨
192.168.1.1,"CentOS7.1",ecs01
192.168.1.2,"CentOS7.2",ecs02
192.168.1.3,"CentOS7.3",ecs03
192.168.1.4,"CentOS7.4",ecs04

mysql> load data infile '/var/lib/mysql-files/load1.csv' into table csv6 fields terminated by ',' enclosed by '"' ignore 1 lines (ip,xitong,renyuan);
Query OK, 3 rows affected (0.10 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

 如果数据的列比表的列多如何导入:用@XX临时变量，

mysql> load data infile '/var/lib/mysql-files/load2.csv' into table csv8 fields terminated by ',' enclosed by '"' (ip,xitoong,pingtai,@mm,
Query OK, 4 rows affected (0.32 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

 5.SQL语句导出数据

mysql> select  * from csv8 into outfile '/tmp/csv8.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

报错了

mysql> show variables like '%secure%'
    -> ;
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
3 rows in set (0.01 sec)

重新导出

mysql> select * from csv8 into outfile '/var/lib/mysql-files/csv88.txt';
Query OK, 4 rows affected (0.00 sec)