MySQL 导出数据select into outfile用法

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/caoxiaohong1005/article/details/72571798

1、select  into  outfield  功能:

导出数据到pc的指定目录下。


2、语法:

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]


语法例子:

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM customers;


3、实际操作例子:Mac环境下

建表:

create table testLoadData(
 id bigint(20) not null auto_increment,
 username char(10) not null,
 age tinyint(3) UNSIGNED not null,
 description  text not null,
 primary key(id),
 unique key(username)
)engine=myisam default charset=utf8;


导入数据:

LOAD DATA local INFILE '/Users/xxx/Downloads/loaddata.txt' IGNORE INTO TABLE testLoadData 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ignore 1 lines (username, age, description);

说明:xxx为本机用户名

查询导入数据:

select * from testLoadData



导出数据:

SELECT  * FROM testLoadData 
INTO OUTFILE '/Users/xxx/Downloads/loaddatass.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

查看对应目录下:可以发现上述数据已经存入到loaddatass.txt内。

4、遇到问题:

(1)


解决办法:修改对应目录下文件的写权限:

终端下输入:


(2)


出现错误原因:sql脚本中的路径下的文件已经存在。实际上sql脚本下的文件应该为MySQL自己去创建的一个文件,而不是去写一个已经存在的文件,所以重新写一个在该目录下没有的文件名即可。

展开阅读全文

没有更多推荐了,返回首页