Programing

SELECT INTO OUTFILE을 사용할 때 헤더를 포함 하시겠습니까?

lottogame 2020. 8. 13. 07:39
반응형

SELECT INTO OUTFILE을 사용할 때 헤더를 포함 하시겠습니까?

MySQL을 사용할 때 어떻게 든 헤더를 포함 할 수 INTO OUTFILE있습니까?

이러한 헤더를 직접 하드 코딩해야합니다. 다음과 같은 것 :

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE '/path/outfile'

Joe Steanelli가 제공 한 솔루션은 작동하지만 수십 또는 수백 개의 열이 관련되어있는 경우 열 목록을 만드는 것은 불편합니다. my_schema 에서 my_table 테이블의 열 목록을 가져 오는 방법은 다음과 같습니다 .

-- override GROUP_CONCAT limit of 1024 characters to avoid a truncated result
set session group_concat_max_len = 1000000;

select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
AND TABLE_SCHEMA = 'my_schema'
order BY ORDINAL_POSITION

이제 Joe의 방법에서 결과 행을 첫 번째 문으로 복사하여 붙여 넣을 수 있습니다.

ORDER BY를 사용한 복잡한 선택의 경우 다음을 사용합니다.

SELECT * FROM (
    SELECT 'Column name #1', 'Column name #2', 'Column name ##'
    UNION ALL
    (
        // complex SELECT statement with WHERE, ORDER BY, GROUP BY etc.
    )
) resulting_set
INTO OUTFILE '/path/to/file';

두 개의 쿼리를 작성합니다. 먼저 열 이름 (하드 코드 없음, 조인, 정렬 기준, 사용자 지정 열 이름 등에 문제 없음)이있는 쿼리 출력 (제한 1)을 얻고 두 번째 쿼리 자체를 만들고 파일을 하나의 CSV로 결합합니다. 파일:

CSVHEAD=`/usr/bin/mysql $CONNECTION_STRING -e "$QUERY limit 1;"|head -n1|xargs|sed -e "s/ /'\;'/g"`
echo "\'$CSVHEAD\'" > $TMP/head.txt
/usr/bin/mysql $CONNECTION_STRING -e "$QUERY into outfile '${TMP}/data.txt' fields terminated by ';' optionally enclosed by '\"' escaped by '' lines terminated by '\r\n';"
cat $TMP/head.txt $TMP/data.txt > $TMP/data.csv

lucek의 대답과 함께 준비된 문을 사용하고 CSV의 열 이름이있는 테이블을 동적으로 내보낼 수 있습니다.

--If your table has too many columns
SET GLOBAL group_concat_max_len = 100000000;
--Prepared statement
SET @SQL = ( select CONCAT('SELECT * INTO OUTFILE \'YOUR_PATH\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' ESCAPED BY \'\' LINES TERMINATED BY \'\\n\' FROM (SELECT ', GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")),' UNION select * from YOUR_TABLE) as tmp') from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YOUR_TABLE' AND TABLE_SCHEMA = 'YOUR_SCHEMA' order BY ORDINAL_POSITION );
--Execute it
PREPARE stmt FROM @SQL;
EXECUTE stmt;

lucek에게 감사합니다.

이렇게하면 열 및 / 또는 한도를 정렬 할 수 있습니다.

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT * from (SELECT ColName1, ColName2, ColName3
    FROM YourTable order by ColName1 limit 3) a
    INTO OUTFILE '/path/outfile';

NodeJS의 큰 테이블에서 mysql 쿼리를 실행하는 동안 비슷한 문제에 직면했습니다. 내 CSV 파일에 헤더를 포함하기 위해 따르는 접근 방식은 다음과 같습니다.

  1. OUTFILE 쿼리를 사용하여 헤더없이 파일 준비

        SELECT * INTO OUTFILE [FILE_NAME] FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED 
    BY '\"' LINES TERMINATED BY '\n' FROM [TABLE_NAME]

  2. 포인트 1에서 사용 된 테이블의 열 헤더를 가져옵니다.

        select GROUP_CONCAT(CONCAT(\"\",COLUMN_NAME,\"\")) as col_names from 
    INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = [TABLE_NAME] AND TABLE_SCHEMA 
    = [DATABASE_NAME] ORDER BY ORDINAL_POSITION

  3. prepend-file npm 패키지를 사용하여 1 단계에서 만든 파일에 열 헤더를 추가합니다.

각 단계의 실행은 NodeJS의 promise를 사용하여 제어되었습니다.

Python 또는 R에 익숙하고 테이블이 메모리에 맞을 수있는 경우 이것은 대체 치트입니다.

SQL 테이블을 Python 또는 R로 가져온 다음 CSV로 내 보내면 열 이름과 데이터를 얻을 수 있습니다.

R을 사용하여 수행하는 방법은 다음과 같습니다. RMySQL 라이브러리가 필요합니다.

db <- dbConnect(MySQL(), user='user', password='password', dbname='myschema', host='localhost')

query <- dbSendQuery(db, "select * from mytable")
dataset <- fetch(query, n=-1)

write.csv(dataset, 'mytable_backup.csv')

약간의 속임수이지만 위의 concat 메서드를 사용하기에는 열 수가 너무 길 때 빠른 해결 방법이라는 것을 알았습니다. 참고 : R은 CSV 시작 부분에 'row.names'열을 추가하므로 CSV를 사용하여 테이블을 다시 만들어야하는 경우 해당 열을 삭제하고 싶을 것입니다.

따라서의 모든 열 my_table이 문자 데이터 유형 인 경우 상위 답변 (Joe, matt 및 evilguc)을 함께 결합하여 하나의 '간단한'SQL 쿼리에 헤더를 자동으로 추가 할 수 있습니다.

select * from (
  (select column_name
    from information_schema.columns
    where table_name = 'my_table'
    and table_schema = 'my_schema'
    order by ordinal_position)
  union all
  (select *  // potentially complex SELECT statement with WHERE, ORDER BY, GROUP BY etc.
  from my_table)) as tbl
into outfile '/path/outfile'
fields terminated by ',' optionally enclosed by '"' escaped by '\\'
lines terminated by '\n';

마지막 두 줄은 출력 csv를 만듭니다.

my_table이 매우 크면 속도가 느릴 수 있습니다 .

'include-headers'기능은 아직 내장되어 있지 않은 것 같고 여기에있는 대부분의 "솔루션"은 열 이름을 수동으로 입력해야하며 조인도 고려하지 않아야합니다. 문제를 해결하십시오 .

  • 지금까지 찾은 최고의 대안은 괜찮은 도구를 사용하는 것입니다 (저는 HeidiSQL을 사용합니다 ).
    요청을 입력하고 그리드를 선택한 다음 마우스 오른쪽 버튼을 클릭하고 파일로 내 보냅니다. 깨끗한 내보내기에 필요한 모든 옵션이 있으며 대부분의 요구 사항을 처리해야합니다.

  • 같은 아이디어에서 user3037511의 접근 방식은 잘 작동하며 쉽게 자동화 할 수 있습니다 . 헤더를 얻으려면
    몇 가지 명령 줄로 요청을 시작 하면됩니다. SELECT INTO OUTFILE ...을 사용하거나 제한없이 쿼리를 실행하여 데이터를 얻을 수 있습니다.

    파일로의 출력 리디렉션은 Linux와 Windows 모두에서 매력처럼 작동합니다.


이것은 내가 SELECT FROM INFILE 또는 SELECT INTO OUTFILE을 사용하고 싶을 때 80 %의 시간 을 강조하고 싶을 때, 몇 가지 제한으로 인해 다른 것을 사용 하게됩니다 (여기서는 '헤더 옵션'이 없습니다. AWS-RDS, 누락 된 권한 등)

따라서 나는 op의 질문에 정확히 대답하지는 않지만 그의 요구에 답 해야합니다. :)
편집 : 그리고 그의 질문에 실제로 대답하려면 : 아니오
2017-09-07 현재, 당신은 헤더를 포함 할 수 없습니다. SELECT INTO OUTFILE 명령을 사용하십시오 .

컬럼 (id, time, unit) 이 있는 데이터베이스 테이블 이름 센서 의 예

select ('id') as id, ('time') as time, ('unit') as unit
UNION ALL
SELECT * INTO OUTFILE 'C:/Users/User/Downloads/data.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM sensor

UNION을 사용하면 작동 할 것이라고 생각합니다.

select 'header 1', 'header 2', ...
union
select col1, col2, ... from ...

INTO OUTFILE 구문으로 헤더를 직접 지정하는 방법을 모르겠습니다.

실제로 ORDER BY로도 작동하도록 만들 수 있습니다.

order by 문에서 약간의 속임수가 필요합니다. case 문을 사용하고 헤더 값을 목록에서 먼저 정렬 할 수있는 다른 값으로 대체합니다 (분명히 이것은 필드 유형과 ASC를 정렬하는지 여부에 따라 다릅니다. DESC)

이름 (varchar), is_active (bool), date_something_happens (날짜)라는 세 개의 필드가 있고 두 번째 두 개를 내림차순으로 정렬한다고 가정 해 보겠습니다.

select 
        'name'
      , 'is_active' as is_active
      , date_something_happens as 'date_something_happens'

 union all

 select name, is_active, date_something_happens

 from
    my_table

 order by
     (case is_active when 'is_active' then 0 else is_active end) desc
   , (case date when 'date' then '9999-12-30' else date end) desc

나는 PHP로 코드를 작성하고 있었고 concat 및 union 함수를 사용하는 데 약간의 문제가 있었으며 SQL 변수를 사용하지 않았으며 어떤 방식으로도 작동하지 않았습니다. 여기 내 코드가 있습니다.

//first I connected to the information_scheme DB

$headercon=mysqli_connect("localhost", "USERNAME", "PASSWORD", "information_schema");

//took the healders out in a string (I could not get the concat function to work, so I wrote a loop for it)

    $headers = '';
    $sql = "SELECT column_name AS columns FROM `COLUMNS` WHERE table_schema = 'YOUR_DB_NAME' AND table_name = 'YOUR_TABLE_NAME'";
    $result = $headercon->query($sql);
    while($row = $result->fetch_row())
    {
        $headers = $headers . "'" . $row[0] . "', ";
    }
$headers = substr("$headers", 0, -2);

// connect to the DB of interest

$con=mysqli_connect("localhost", "USERNAME", "PASSWORD", "YOUR_DB_NAME");

// export the results to csv
$sql4 = "SELECT $headers UNION SELECT * FROM YOUR_TABLE_NAME WHERE ... INTO OUTFILE '/output.csv' FIELDS TERMINATED BY ','";
$result4 = $con->query($sql4);

다음은 열 이름에서 동적으로 헤더 제목을 가져 오는 방법입니다. 

/* Change table_name and database_name */
SET @table_name = 'table_name';
SET @table_schema = 'database_name';
SET @default_group_concat_max_len = (SELECT @@group_concat_max_len);

/* Sets Group Concat Max Limit larger for tables with a lot of columns */
SET SESSION group_concat_max_len = 1000000;

SET @col_names = (
  SELECT GROUP_CONCAT(QUOTE(`column_name`)) AS columns
  FROM information_schema.columns
  WHERE table_schema = @table_schema
  AND table_name = @table_name);

SET @cols = CONCAT('(SELECT ', @col_names, ')');

SET @query = CONCAT('(SELECT * FROM ', @table_schema, '.', @table_name,
  ' INTO OUTFILE \'/tmp/your_csv_file.csv\'
  FIELDS ENCLOSED BY \'\\\'\' TERMINATED BY \'\t\' ESCAPED BY \'\'
  LINES TERMINATED BY \'\n\')');

/* Concatenates column names to query */
SET @sql = CONCAT(@cols, ' UNION ALL ', @query);

/* Resets Group Contact Max Limit back to original value */
SET SESSION group_concat_max_len = @default_group_concat_max_len;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Sangam Belose가 제공 한 답변에 추가하고 싶습니다. 그의 코드는 다음과 같습니다.

select ('id') as id, ('time') as time, ('unit') as unit
UNION ALL
SELECT * INTO OUTFILE 'C:/Users/User/Downloads/data.csv'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM sensor

However, if you have not set up your "secure_file_priv" within the variables, it may not work. For that, check the folder set on that variable by:

SHOW VARIABLES LIKE "secure_file_priv"

The output should look like this:

mysql> show variables like "%secure_file_priv%";
+------------------+------------------------------------------------+
| Variable_name    | Value                                          |
+------------------+------------------------------------------------+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\ |
+------------------+------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

You can either change this variable or change the query to output the file to the default path showing.

MySQL alone isn't enough to do this simply. Below is a PHP script that will output columns and data to CSV.

Enter your database name and tables near the top.

<?php

set_time_limit( 24192000 );
ini_set( 'memory_limit', '-1' );
setlocale( LC_CTYPE, 'en_US.UTF-8' );
mb_regex_encoding( 'UTF-8' );

$dbn = 'DB_NAME';
$tbls = array(
'TABLE1',
'TABLE2',
'TABLE3'
);

$db = new PDO( 'mysql:host=localhost;dbname=' . $dbn . ';charset=UTF8', 'root', 'pass' );

foreach( $tbls as $tbl )
{
    echo $tbl . "\n";
    $path = '/var/lib/mysql/' . $tbl . '.csv';

    $colStr = '';
    $cols = $db->query( 'SELECT COLUMN_NAME AS `column` FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "' . $tbl . '" AND TABLE_SCHEMA = "' . $dbn . '"' )->fetchAll( PDO::FETCH_COLUMN );
    foreach( $cols as $col )
    {
        if( $colStr ) $colStr .= ', ';
        $colStr .= '"' . $col . '"';
    }

    $db->query(
    'SELECT *
    FROM
    (
        SELECT ' . $colStr . '
        UNION ALL
        SELECT * FROM ' . $tbl . '
    ) AS sub
    INTO OUTFILE "' . $path . '"
    FIELDS TERMINATED BY ","
    ENCLOSED BY "\""
    LINES TERMINATED BY "\n"'
    );

    exec( 'gzip ' . $path );

    print_r( $db->errorInfo() );
}

?>

You'll need this to be the directory you'd like to output to. MySQL needs to have the ability to write to the directory.

$path = '/var/lib/mysql/' . $tbl . '.csv';

You can edit the CSV export options in the query:

INTO OUTFILE "' . $path . '"
FIELDS TERMINATED BY ","
ENCLOSED BY "\""
LINES TERMINATED BY "\n"'

At the end there is an exec call to GZip the CSV.

SELECT 'ColName1', 'ColName2', 'ColName3'
UNION ALL
SELECT ColName1, ColName2, ColName3
    FROM YourTable
    INTO OUTFILE 'c:\\datasheet.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

참고URL : https://stackoverflow.com/questions/5941809/include-headers-when-using-select-into-outfile

반응형

'Programing' 카테고리의 다른 글

Ruby에서 파일 이름 및 확장자 가져 오기  (0) 2020.08.13
localhost 외부에서 액세스를 허용하는 방법  (0) 2020.08.13
jQuery에서 인덱스로 요소 가져 오기  (0) 2020.08.13
C ++ 11에서 thread_local은 무엇을 의미합니까?  (0) 2020.08.13
Pandas 데이터 프레임은 각 그룹의 첫 번째 행을 가져옵니다.  (0) 2020.08.13

'Programing'의 다른글

  • 현재글SELECT INTO OUTFILE을 사용할 때 헤더를 포함 하시겠습니까?

관련글

댓글

티스토리툴바