mysql select out_MySQL SELECT xxx INTO OUTFILE用法

MySQL SELECT xxx INTO OUTFILE用法

1、导出数据报错

mysql> SELECT * INTO OUTFILE '/tmp/t.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM t;

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 | NULL |

+--------------------------+-------+

3 rows in set (0.01 sec)

mysql>

2、解决方法，需要重启数据库

[mysqld]

secure_file_priv=''

3、重新导出，OK

mysql> SELECT * INTO OUTFILE '/tmp/t.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM t;

Query OK, 2 rows affected (0.00 sec)

mysql>

4、查看验证

[mysql@roidb01 ~]$ cat /tmp/t.txt

1,"a"

2,"b"

[mysql@roidb01 ~]$

5、导入成功

mysql> LOAD DATA INFILE "/tmp/t.txt" INTO TABLE trsids.t FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

-> ;

Query OK, 2 rows affected (0.01 sec)

6、验证数据OK

mysql> select * from trsids.t;

+------+------+

| id | name |

+------+------+

| 1 | a |

| 2 | b |

| 1 | a |

| 2 | b |

+------+------+

4 rows in set (0.00 sec)

mysql>

©著作权归作者所有：来自51CTO博客作者roidba的原创作品，如需转载，请注明出处，否则将追究法律责任

好知识，才能预见未来

赞赏

0人进行了赞赏支持