Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.2.9.1 SELECT ... INTO 構文

SELECTSELECT ... INTO 形式を使用すると、クエリー結果を変数に格納したり、ファイルに書き込んだりできます。

  • SELECT ... INTO var_list はカラム値を選択し、それらを変数に格納します。

  • SELECT ... INTO OUTFILE は、選択された行をファイルに書き込みます。カラムおよび行ターミネータを指定すると、特定の出力形式を生成できます。

  • SELECT ... INTO DUMPFILE は、単一行をファイルに形式設定なしで書き込みます。

SELECT の構文の説明 (セクション13.2.9「SELECT 構文」を参照してください) では、INTO 句がステートメントの最後の近くに示されています。INTO はまた、select_expr リストの直後に使用することもできます。

ネストされた SELECT はその結果を外側のコンテキストに返す必要があるため、このような SELECT では INTO 句を使用してはいけません。

INTO 句は、1 つ以上の変数のリストを指定できます。この変数には、ユーザー定義変数、ストアドプロシージャーやストアドファンクションのパラメータ、またはストアドプログラムのローカル変数を指定できます。(準備済み SELECT ... INTO OUTFILE ステートメント内では、ユーザー定義変数のみが許可されます。セクション13.6.4.2「ローカル変数のスコープと解決」を参照してください。)

選択された値は変数に割り当てられます。変数の数がカラム数に一致している必要があります。クエリーは、単一行を返すようにしてください。クエリーが行を返さない場合は、エラーコード 1329 で警告が発生し (No data)、変数値は変更されないままになります。クエリーが複数の行を返す場合は、エラー 1172 が発生します (結果が 2 行以上です)。このステートメントが複数の行を取得する可能性がある場合は、LIMIT 1 を使用して結果セットを単一行に制限できます。

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

ユーザー変数名では大文字と小文字を区別しません。セクション9.4「ユーザー定義変数」を参照してください。

SELECTSELECT ... INTO OUTFILE 'file_name' 形式は、選択された行をファイルに書き込みます。このファイルはサーバーホスト上で作成されるため、この構文を使用するには FILE 権限が必要です。file_name を既存のファイルにすることはできません。これにより、特に /etc/passwd などのファイルやデータベーステーブルが破棄されることが回避されます。character_set_filesystem システム変数は、ファイル名の解釈を制御します。

SELECT ... INTO OUTFILE ステートメントは、主に、テーブルをサーバーマシン上のテキストファイルに非常にすばやくダンプできるようにすることを目的にしています。結果として得られるファイルをサーバーホスト以外のホスト上で作成する場合は通常、そのサーバーホストのファイルシステムを基準にしたファイルへのパスを記述する方法が存在しないため、SELECT ... INTO OUTFILE は使用できません。

ただし、MySQL クライアントソフトウェアがリモートマシンにインストールされている場合は、代わりに mysql -e "SELECT ..." > file_name などのクライアントコマンドを使用してクライアントホスト上にファイルを生成できます。

また、サーバーのファイルシステム上でネットワークにマップされたパスを使用してリモートホスト上のファイルの場所にアクセスできる場合も、結果として得られるファイルをサーバーホストとは異なるホスト上に作成できます。この場合は、ターゲットホスト上に mysql (または、その他の何らかの MySQL クライアントプログラム) が存在する必要はありません。

SELECT ... INTO OUTFILE は、LOAD DATA INFILE を補完するものです。カラム値は、CHARACTER SET 句で指定されている文字セットに変換されて書き込まれます。このような句が存在しない場合、値は binary 文字セットを使用してダンプされます。事実上、文字セットの変換は実行されません。結果セットにカラムが複数の文字セットで含まれている場合は、出力データファイルにもそのまま含まれるため、そのファイルを正しくリロードできない可能性があります。

このステートメントの export_options 部分の構文は、LOAD DATA INFILE ステートメントで使用されるのと同じ FIELDS および LINES 句で構成されています。FIELDS および LINES 句 (それぞれのデフォルト値と許可される値を含む) については、セクション13.2.6「LOAD DATA INFILE 構文」を参照してください。

FIELDS ESCAPED BY は、特殊文字を書き込む方法を制御します。FIELDS ESCAPED BY 文字が空でない場合、その文字は、出力上で次の文字の前に付けられるプリフィクスとして、あいまいさを避けるために必要な場合に使用されます。

  • FIELDS ESCAPED BY 文字

  • FIELDS [OPTIONALLY] ENCLOSED BY 文字

  • FIELDS TERMINATED BY および LINES TERMINATED BY 値の最初の文字

  • ASCII NUL (0 の値のバイト。エスケープ文字のあとに実際に書き込まれる文字は 0 の値のバイトではなく、ASCII の0です)

FIELDS TERMINATED BYENCLOSED BYESCAPED BY、または LINES TERMINATED BY 文字は、そのファイルを確実に読み戻すことができるように、エスケープする必要があります。ASCII NUL は、一部のページャーで見やすくなるようにエスケープされます。

結果として得られるファイルは SQL 構文に準拠する必要はないため、ほかは何もエスケープする必要がありません。

FIELDS ESCAPED BY 文字が空である場合は、どの文字もエスケープされず、NULL\N ではなく、NULL として出力されます。特に、データ内のフィールド値に今指定したリスト内のいずれかの文字が含まれている場合、空のエスケープ文字を指定することはおそらく適切な方法ではありません。

多くのプログラムで使用されているカンマ区切り値 (CSV) 形式のファイルを生成する例を次に示します。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

INTO OUTFILE の代わりに INTO DUMPFILE を使用した場合、MySQL はエスケープ処理を実行することなく、カラムや行の終了のない 1 行のみをファイルに書き込みます。これは、ファイルに BLOB 値を格納する場合に役立ちます。

注記

INTO OUTFILE または INTO DUMPFILE によって作成されたファイルはすべて、サーバーホスト上のすべてのユーザーから書き込み可能です。これは、MySQL サーバーが、それを実行しているアカウントを持つユーザー以外のどのユーザーによって所有されるファイルも作成できないためです。(これらの理由のために、mysqldroot としては決して実行しないでください。) したがって、このファイルは、その内容を操作できるようにすべてのユーザーから書き込み可能である必要があります。

secure_file_priv システム変数が空以外のディレクトリ名に設定されている場合、書き込まれるファイルはそのディレクトリ内に存在する必要があります。

イベントスケジューラによって実行されるイベントの一部として実行された SELECT ... INTO ステートメントのコンテキストでは、診断メッセージ (エラーだけでなく、警告も含みます) がエラーログに (Windows ではアプリケーションイベントログにも) 書き込まれます。詳細は、セクション20.4.5「イベントスケジューラのステータス」を参照してください。


User Comments
  Posted by Jorge Lumbreras on November 18, 2011
Is a Example... very simple.. que no tiene encabezados (No headers)

select * from control_pagos
into outfile 'c:\\data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

para aplicar este procedimiento el usuario debe de tener privilegios de GRANT / FILE.

Este ejemplo le agregamos los encabezados (with headers)

select 'idPago','fecha','lead','idAlumno','idTipoPago','idGpo',
'idTaller','idDocente','pagoImporte','NoFactura','facturaImporte',
'mes','formaPago','observaciones' union all
(select id_control_pagos, fecha, lead, id_alumno, id_concepto_pago, id_Gpo,id_Taller,
id_docente, Pagoimporte, NoFactura, FacturaImporte, Mensualidad_No, FormaPago,
Observaciones from control_pagos
into outfile 'c:\\data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n');

  Posted by Mauricio Aristizabal on March 23, 2012
OK, this is weird, I don't know if it's just a setting in my server or bad docs (unlikely), but I just spent a couple hours trying to get a select into [vars] working inside a function before I realized other people were not using @, and that got it working. So if you're having trouble, try (reusing docs example above):

SELECT id, data INTO x, y FROM test.t1 LIMIT 1;

instead of

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;

  Posted by Linda McGarry on February 25, 2013
I wrote a generic procedure to export to CSV for Excel pivot tables - pass in the schema, table and output filename/path and it creates and executes the SQL. You will need to localise the date export format for your country and the line endings (I'm on Windows). I also assume you are calling the SQL from a command line that can delete any existing files before creating the new CSV file.

-- create procedure to export entire table to CSV with header for Excel pivot tables
-- - input: schema and table for export, file path for CSV file
--------------------------------------------------------------------------------
delimiter $$

drop procedure if exists test.export_csv_proc
$$

create procedure test.export_csv_proc(IN vSchema varchar(200), IN vTable varchar(200), IN vFilePath varchar(2000))

begin
--------------------------------------------------------------------------------
-- declare variables

declare vColumn varchar(200); -- field name
declare vDataType char(20); -- field data type
declare vOrdinalPosition smallint unsigned default 0; -- counter based on information_schema data
declare vMaxFields smallint unsigned default 0; -- number of fields in export table
declare vHeaderSQL text default ''; -- hold text for header export SQL
declare vDataSQL text default ''; -- hold text for data export SQL
declare vOutputSQL text default ''; -- combine all SQL into final code for exporting table
--------------------------------------------------------------------------------
-- create cursor to get list of columns for export

declare cur_header cursor for
select distinct
c.column_name,
c.data_type,
c.ordinal_position
from information_schema.columns as c
where c.table_schema = vSchema
and c.table_name = vTable
order by
c.ordinal_position
;
--------------------------------------------------------------------------------
-- how many columns are in the export table

set @pSQL = concat("
select @vFields:= max(c.ordinal_position)
from information_schema.columns as c
where c.table_schema = '", vSchema,
"' and c.table_name = '", vTable, "'"
);

prepare stmt from @pSQL;
execute stmt;
deallocate prepare stmt;

set vMaxFields = @vFields;
--------------------------------------------------------------------------------
-- create SQL export text
-- - add double quote enclosure only for character fields
-- - format date fields for Excel import (DD/MM/YYYY)
-- - add comma after all but last field

open cur_header;

repeat
fetch cur_header into vColumn, vDataType, vOrdinalPosition;

set vHeaderSQL = concat(vHeaderSQL, '\'"\',', '\'', vColumn, '\'', ',\'"\'');

case
when vDataType in ('char','varchar') then set vDataSQL = concat(vDataSQL, '\'"\',ifnull(`', vColumn, '`,\'\'),\'"\'');
when vDataType in ('date','datetime','timestamp') then set vDataSQL = concat(vDataSQL, 'ifnull(date_format(`', vColumn, '`,\'%d/%m/%Y %T\'),\'\')');
else set vDataSQL = concat(vDataSQL, 'ifnull(`', vColumn, '`,\'\')');
end case;

if vOrdinalPosition < vMaxFields
then set vHeaderSQL = concat(vHeaderSQL, ',', '",", '),
vDataSQL = concat(vDataSQL, ',', '",", ')
;
end if;

until vOrdinalPosition = vMaxFields
end repeat;

close cur_header;
--------------------------------------------------------------------------------
-- create CSV SQL text

set vOutputSQL = concat(
'select concat (',
vHeaderSQL,
') UNION select concat (',
vDataSQL,
') from ', vSchema, '.', vTable,
' into outfile "', vFilePath,
'" lines terminated by "\\r\\n"'
);
--------------------------------------------------------------------------------
-- execute created statement

set @pSQL = vOutputSQL;

prepare stmt from @pSQL;
execute stmt;
deallocate prepare stmt;
--------------------------------------------------------------------------------
end
$$

delimiter ;
--------------------------------------------------------------------------------
--call test.export_csv_proc('test_schema', 'test_table', 'c:/temp/test.csv');

  Posted by Graham Wideman on July 20, 2014
On Windows, it may not be obvious what path separator character(s) to use when specifying the file path in SELECT INTO OUTFILE.

First, if you use backslash, you must double them, as you would in writing a literal string in C or C-related languages:

SELECT whatever INTO OUTFILE 'D:\\Temp\somefile'...

Because MySQL doesn't overwrite that file, you may be persuaded to change the filename each time around, by using a variable (perhaps a timestamp) for the file name:

SET @filepath = CONCAT('D:\\Temp\somefile\\TheFile_', @variablepart);
SELECT whatever INTO OUTFILE @filepath...

However, this isn't legal syntax because a variable isn't allowed at that location. So instead you might move to preparing the SELECT .. INTO statement. That works, but then the filepath string goes through two steps of unescaping, so you'll need to double escape the path separators (ie: four backslashes).

SET @filepath = CONCAT('D:\\\\Temp\somefile\\\\TheFile_', @variablepart);

Probably a better solution is to use a single forward slash instead, which MySQL apparently converts to the platform-appropriate separator when calling the OS API.

SET @filepath = CONCAT('D:/Temp/somefile/TheFile_', @variablepart);

Sign Up Login You must be logged in to post a comment.