<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">종종 파일 데이터를 MySQL의 특정 테이블에 넣고싶을때가 있다.</span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">이는 그 작업을 위한 간단한 스크립트 이다.</div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;"><br /></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">입력 쿼리를 하나의 String으로 축적하여 한방에 날리는 것이다.</div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">그 쿼리수가 너무나 많을경우에는 시간이 오래걸릴 수 있으므로 적당히 count를 나누어서 여러번 실행시키는것도 좋은방법이다.</div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;"><br /></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><br /></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">==============&nbsp;<b>file_to_mysql.sh&nbsp;</b><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">readFile=$1</span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;"><br /></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><b style="color: #009900; line-height: 15.6px;"># MySQL information</b></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">HOST="localhost"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">USER="root"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">PWD="root"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">DB="test_db"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">TARGET_TABLE="test_table"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">if [ $# -ne 1 ]; then</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; echo "Usage: &lt;filePath&gt;"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; echo "Description: Read file and save into DB"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; exit</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">fi</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">query=""</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">count=0</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">tmpFile="file_to_mysql.tmp"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><b><font color="#009900"># remove DOS newline character</font></b></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">tr -d '\r' &lt; $readFile &gt; $tmpFile</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><font color="#009900"><b># accumulate queries</b></font></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">while read line ; do</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"># &nbsp;echo "$line"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; data1=`echo $line | awk '{print $1}'` &nbsp;<font color="#009900"><b># line의 첫번째 element 선택</b></font></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; data1=`echo $data1 | sed -e 's/^ *//' -e 's/ *$//'` &nbsp;<b style="font-size: 9pt; line-height: 15.6px; color: #009900;"># element의 앞뒤 공백 제거</b></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; data2=`echo $line | awk '{print $2}'`</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; data2=`echo $data2 | sed -e 's/^ *//' -e 's/ *$//'`</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"># &nbsp;echo "$data1"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"># &nbsp;echo "$data2"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; if [ -z $data1 ]; then</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; &nbsp; continue</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; else</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; &nbsp; query=$query"INSERT IGNORE INTO $TARGET_TABLE (field1,field2) VALUES ('$data1', '$data2');"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; &nbsp; count=$[count+1]</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">&nbsp; fi</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">done &lt; $tmpFile</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><font color="#009900"><b># insert data into database</b></font></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">mysqlInfo="-h$HOST -u$USER -p$PWD $DB"</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><font color="#ff0000"><b>echo "$query" | mysql $mysqlInfo&nbsp;</b></font><b style="font-size: 9pt; line-height: 15.6px; color: #009900;"># 쿼리 실행하는 부분</b></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;"><br /></div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">rm $tmpFile 2&gt; /dev/null</div>
   <div style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline;">echo "[FINISHED] DB Insert finished."</div>
   </div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;"><br /></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">위와 같이&nbsp;<b>file_to_mysql.sh</b>&nbsp;를 생성하였을때,</div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;">data1 과 data2를 라인단위로 공백을 두고 가지고 있는 파일&nbsp;<b>insert_data.txt</b>를&nbsp;<span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">DB에 넣는 방법은 아래와 같다.</span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><br /></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;"><br /></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">[shell]$&nbsp;<b><font color="#3333ff">./file_to_mysql.sh insert_data.txt</font></b></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;"><br /></span></div>
<div style="margin: 0px; padding: 0px; font-size: 13px; border: 0px; vertical-align: baseline; color: #1f1f1f; font-family: &quot;맑은 고딕&quot;, &quot;malgun gothic&quot;, 돋움, dotum, AppleGothic, sans-serif; background-color: #ffffff;"><span style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span><span style="margin: 0px; padding: 0px; font-size: 9pt; border: 0px; vertical-align: baseline; line-height: 15.6px;">==============</span></div>