MySQL mysql에서 쿼리 결과를 파일로 저장하기

# mysql 계정은 root로 해야함

1) 스크립트 예제 첫번째 (컬럼의 가공없이 그대로)

#!/bin/sh
/usr/local/mysql/bin/mysql -uroot -pkebi -e "
SELECT
        '', USERS_ID, USERS_NAME, USERS_PASSWD, '',
        '', '', '', USERS_SEX, '',

        '', '', '', '', '',
        '', '', '', '', '',

        '', '', '', '', '',
        '', '', '', '', '',

        '', '', '', '', '',
        '', '', '', '', '',

        '', '', '', '', '',
        '', '', '', ''

        INTO OUTFILE '/tmp/users.txt' FIELDS TERMINATED BY ','
FROM
        USERS
ORDER BY
        USERS_IDX
" mail


2) 스크립트 예제 두번째 (컬럼의 내용을 변환하여 출력)

#!/bin/sh
rm -rf /tmp/users.txt
/usr/local/mysql/bin/mysql -uroot -pkebi -e "
SELECT USERS_ID, USERS_NAME, USERS_PASSWD

        INTO OUTFILE '/tmp/users.txt' FIELDS TERMINATED BY ','
FROM
        USERS
WHERE
        USERS_ID LIKE '1%'
OR      USERS_ID LIKE '2%'
OR      USERS_ID LIKE '3%'
OR      USERS_ID LIKE '4%'
OR      USERS_ID LIKE '5%'
OR      USERS_ID LIKE '6%'
OR      USERS_ID LIKE '7%'
OR      USERS_ID LIKE '8%'
OR      USERS_ID LIKE '9%'
OR      USERS_ID LIKE '0%'
OR      USERS_JUMIN1 = NULL OR USERS_JUMIN1 = ''
OR      length(USERS_ID) < 4
OR      length(USERS_ID) > 12
ORDER BY
        USERS_IDX
" mail


3) 스크립트 예제 세번째

#!/bin/sh
/opt/SPOON/mysql/bin/mysql -uroot -pspoon123 -e "
SELECT
        id, name, jumin, dpt_nm, emp_id, IF(LENGTH(jumin) = 14,SUBSTRING(jumin, 8, 7), id )

        INTO OUTFILE '~spoon/users.txt' FIELDS TERMINATED BY ','
FROM
        user_info
ORDER BY
        id
" webmail


4) 스크립트 예제 네번째

rm -rf users_addr.txt
#!/bin/sh
/opt/SPOON/mysql/bin/mysql -uroot -pspoon123 -e "
SELECT
        CONCAT(CONCAT(CONCAT(''',id),'@sookmyung.ac.kr'), '''),
        CONCAT('',' 0'),
        CONCAT(CONCAT(''',name),'''),
        CONCAT(CONCAT(''',mail),'''),
        CONCAT(CONCAT(''',phone),'''),
        CONCAT(CONCAT(''',hphone),'''),
        CONCAT(CONCAT(''',post),'''),
        CONCAT(CONCAT(''',CONCAT(address1,address2)),'''),
        CONCAT(CONCAT(''',office),'''),
        CONCAT(CONCAT(''',o_phone),'''),
        CONCAT(CONCAT(''',o_post),'''),
        CONCAT(CONCAT(''',CONCAT(o_address1,o_address2)),'''),
        CONCAT(CONCAT(''',birthday),''')

        INTO OUTFILE '~spoon/users_addr.txt' FIELDS TERMINATED BY ','
FROM
        user_addr
ORDER BY
        id
" webmail