SELECT INTO OUTFILE

You are viewing an old version of this article. View the current version here.

语法

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 ... INTO OUTFILE语句将结果集写入到文件中,并且允许人为指定导出数据时的字段分隔符和行分隔符。

导出的文件必须不能已经存在,它无法覆盖其他文件。用户需要拥有FILE权限来允许该语句。此外,MariaDB需要文件的写入权限。如果系统变量secure_file_priv设置为非空目录名,则只允许向该目录中写入文件。

CHARACTER SET子句指定导出到文件时的字符集。如果不给定该子句,将不会转换字符集(按照binary character set的值)。这种情况下,如果使用了多种字符集(译注:例如不同字段使用不同字符集),则导出的文件也将使用多种字符集,则可能不利于以后的load操作。

SELECT ... INTO OUTFILE导出的文件主要用于LOAD DATA INFILE语句来导入数据。

示例

下面的示例生成一个CSV格式的文件:

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

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.