[Mysql] INTO OUTfile 엑셀로 다운로드 받기!(검색결과 엑셀로!)

---

 

안녀어어엉

흠.. 오늘은 얼마전에

개발했던건으로 한번 포스팅을 해볼까해

난이도는 낮은편이야!

 

흠... 낮은편 맞아!

한번 볼까 ?

---

INTO OUTfile 라는 친구는

쿼리의 결과값을 파일형태로 저장을 해주는 친구야!

 

저장되는 디렉토리는

기존에 /data/mysql/mysql_data 에 저장이 되는거야!

이곳은 mysql이 설치되어있는 곳인데

이쪽으로 절대경로가 잡혀 있고

다른 디렉토리로 설정 할 수 없어!

 

준비해야할 친구를 볼까 ?

IIS서버라면 괜찮지만 Linux의 경우는 

FileZilla를 활용해서 FTP에 접속해야해,

또한 권한도 -rw-rw-rw-가 되어있어야지

 

차질없이 진행이 된다는걸 말해주고 싶어

(필요시에만 사용하길 권장해! 보안문제가 있으니!)

 

작업해둔것들,,,ㅋㅋ

---

이제 INTO OUTfile 설명법을 보자

 

난 PHP와 mysql을 혼합해서 작성을 해봤어

sql만 필요하다만 쿼리만 봐도 무관해 ㅎㅎ

 

<?
include_once "[ 생략 ] include_head_all.php";

	$쿼리변수 = "select 매장코드,매장이름 from 테이블명	WHERE 매장코드 NOT IN ('0','-1');";
	$sql_stats_act->query($쿼리변수);
	$data_arr = array();
	while($data3 = $sql_stats_act->fetch()) $data_arr[] = $data3;

// 1~12월만큼 반복문 처리
for($i=1;$i<13;$i++){
	//month 형태를 1~12가 아닌 01~12형태로 변경 ( 데이터가 그렇게 되어있어서 T_T )
	$month = strlen($i)>1?$i:"0".$i;
	foreach($data_arr as $_data_arr) {
		$market = $_data_arr[마켓코드];
		$m_name = $_data_arr[마켓명];
		
		//이부분이야!
		$sel = "SELECT * INTO OUTFile '[mysql 저장 파일안에, 이 파일을 저장할 디렉토리]".iconv('utf8','euc-kr',$m_name)."_".$month.".csv' CHARACTER SET euckr FIELDS TERMINATED BY ','	
				FROM (
				SELECT 컬럼명
					
					FROM dual 

					UNION ALL

					SELECT	컬럼명

					FROM 테이블 a
					JOIN 테이블 b
					ON a.컬럼명 = b.컬럼명
					WHERE LEFT(a.컬럼명,4) = '2019'
					AND	  SUBSTR(a.컬럼명,5,2) = '".$month."'
					AND	  a.컬럼명 = '".$market."'

					UNION ALL

					SELECT	컬럼명
					FROM 테이블 a
					JOIN 테이블 b
					ON a.컬럼명 = b.컬럼명
					WHERE LEFT(a.컬럼명,4) = '2019'
					AND	  SUBSTR(a.컬럼명,5,2) = '".$month."'
					AND	  a.컬럼명 = '".$market."')x
					order by  x.`컬럼명`;
					";

		$sel_cnt = "SELECT count(*) cnt FROM 테이블명 WHERE LEFT(컬럼명,4) = '2019' and SUBSTR(컬럼명,5,2) = '".$month."' AND 컬럼명 in ('".$_data_arr[depot_code]."')";

	$data_arr_cnt = array();
	$sql_stats_act->query($sel_cnt);
	while($data3_cnt = $sql_stats_act->fetch()) $data_arr_cnt[] = $data3_cnt;

	print_r($data_arr_cnt);
		if($data_arr_cnt[0]['cnt'] > 0 ){
			//$sql_stats_act->query($sel);
			//echo iconv('utf8','euc-kr',$sel_b.$sel)."<br><br>";
		}else{
//			echo "FF";
		}


	}
}
include_once "[ 생략 ] /include_end_all.php";
?>

 

대충 이런식으로 짰어.. 실제 적용되어있는 코드다 보니까..

최대한 가린다고 가리느라 ㅠ_ㅠ

 

자자자.. 여기서 중요한 부분을 한번 보자

 

서브쿼리의 첫번째는 

SELECT 컬럼명 FROM dual

 이 녀석인데..

만약 이녀석을 설정 안해주면

 

각 행의 이름이랄까...? 타이틀이 없어지기에

dual이라는 가상의 테이블로 이름을 잡아주고

그걸 첫줄에 타이틀로 출력해주기 위함이야.

 

그다음... 테이블 구조상 어쩔 수 없이 Union all로 처리를 해서 같은 테이블의

양수의 값과 음수의 값을 합산한 결과를 출력하도록 union all을 2개를 써서

dual, 테이블의 양수의값과 음수의 값을 메인 쿼리에서는 SUM을 해줬어

 

이부분이 중요한게 아냐..!

 

INTO OUTFile '[mysql 저장 파일안에, 이 파일을 저장할 디렉토리]".iconv('utf8','euc-kr',$m_name)."_".$month.".csv' CHARACTER SET euckr FIELDS TERMINATED BY ','

 

이 부분이 중요한거야.

"저장될 디렉토리".저장시 한글깨짐을 막기위해 iconv로 인코딩처리와 함께..

마켓이름_월로 파일의 이름을 지정하고

.csv라고 확장자를 기재함으로

 

최종 저장될 파일이름을 예로들면

"나의마켓_10월.csv" 이런식으로 저장이 되는거지

 

그 이후

CHARACTER SET euckr FIELDS TERMINATED BY ','로

캐릭터셋을 지정해주었어..

(여기서 UTF-8로 설정도 해볼걸ㅋㅋㅋ)

 

어쩃든 이렇게 구성이 되어있는데

INTO OUTFile의 경우, SELECT절에 포함이 되어야해,

 

즉 이 녀석은 각 컬럼에 나올 결과값들을 파일형태로 변환하여 저장 시키지..

속도면에서는 이런 대량의 자료를 세분화하고, 검색하여 개별로 다운받는것 보다,

이렇게 프로그램과 섞어주면 일괄적으로 빠르게 처리가 가능하다~는 장점이 있어

 

아 물론 쿼리의 구조나, 양에 따라 다르긴한데

내 기준으로는..

총 대략 100MB/ 200개 좀 넘는 액셀을 각 조건별로 2분이 채 안되게 뽑았으니

좋다고 말하고 싶긴해!

(하나하나 어느 세월에 다 받구 있을랭..)

 

단점으로는 서버의 권한을 열어야 한다는 점인데..

서버 권한을 모두 준다는건 해커들의 침입 경로로 활용이 되기에

조심해야할 부분이거든..!

 

주로 해커들의 침입 경로는 보안이 취약하지만서도..

권한이 열린곳을 막 찾아 헤메고 그 권한을 통해서 이득을 취하다 보니

서버의 권한문제는 예민해 질 수 밖에 없어!

 

그리고 현업에서는 Filezilla로 FTP 접속에 어려움을 겪을 수 있다는거야

 

---

 

 

오늘의 목적은 SQL이니 SQL 기준으로 정리를 해봤어

라이브러리나, api를 통해서 개발도 가능하지만..!

 

이건 지극히, 현업에서 대량의 데이터 요청건을 처리할때,

서버의 권한을 만질 수 있는 범위안에서

일괄적으로 처리할때 좋다! 라고 말해주고 싶어

 

안된다면 조금 더 생각해보고 연구해봤으면 좋겠어!

또한..

"서버? 건들면 나 실직당할걸..!?"

 

이런 사람이 있다면 별수있나..

가상서버 열어야지 모 ㅋㅋ

 

그럼 20000!