Intro!
mysql workbench 툴을 이용하여 테이블 내 데이터를 엑셀파일로 뽑아내거나 엑셀파일을 이용하여 대량의 데이터를 table로 insert 할 수 있습니다.
==== 작업에 사용된 소프트웨어 및 버전 ====
- MySQL 8.0.33
- MySQL Workbench 8.0
- Microsoft Excel 2022
=================================
● Table Export
1. 엑셀파일로 export할 테이블을 조회합니다.
※ 참고: 테스트하는 테이블 정보는 아래와 같습니다.
2. 아래 그림처럼 Export 버튼을 누릅니다.
3. 원하는 위치에다가 테이블에서 export한 CSV파일을 저장합니다.
4. 엑셀을 실행하고 [데이터]탭-[텍스트/CSV 에서]를 선택하여, 테이블에서 Export했던 CSV 파일을 불러옵니다.
5. 데이터변환 팝업화면이 나오면 아래 그림과 같이 파일 원본, 구분 기호, 데이터 형식 검색 선택을 설정 한 후 아래 데이터변환 버튼을 클릭합니다.
※ 만약 컬럼 데이터 중 한글내용이 있다면 한글깨짐 방지를 위해 파일 원본을 [650001: 유니코드(UTF-8)] 로 선택
6. 데이터변환 버튼을 누르면 엑셀 파워쿼리 편집기가 나타납니다. 여기서는 테이블에서 사용된 내용이 열, 행 데이터로 표현됩니다.
데이터 확인 후 [닫기 및 로드]를 선택합니다.
7. 최종적으로 테이블 데이터 내용이 엑셀에 반영됩니다. 이후부터는 컬럼내용에 맞춰 자유롭게 편집하시면 됩니다.
● Table import
1. import하기 전 엑셀 첫 행의 내용이 import 대상 테이블의 컬럼명, 컬럼순서와 맞는지 확인합니다.
100까지 import하기로 하겠습니다.
2. 다른 이름으로 저장을 합니다. 저장 시 파일 형식을 [CSV (쉼표로 분리)(*.csv)] 로 선택합니다.
※ 만약 CSV UTF-8(쉼표로 분리) 라는 게 있으면 이건 선택하지 않습니다.
3. Workbench에서 import 대상 테이블을 select하고 아래 그림처럼 import 버튼을 누릅니다.
4. Import 팝업창이 나오면 브라우저 버튼을 눌러 엑셀파일에서 CSV파일로 저장한 파일을 선택합니다. 파일 선택 후 아래 next버튼을 누릅니다.
5. 어떻게 테이블에 import할지 선택합니다.
Use existing table을 체크 후 import하고자 할 테이블을 선택 후 아래 next버튼을 누릅니다.
※ Use existing table을 체크할 경우 스키마에 존재하는 테이블을 선택하여 import를 진행합니다.
※ create new table을 선택할 경우 스키마 선택 후 테이블명을 지정하여 import합니다.
※ 만약 Truncate table before import 옵션을 사용할 경우 아래 주의사항을 참고하시기 바랍니다.
6. 인코딩방식, import 대상 컬럼을 선택합니다. 인코딩방식은 utf-8로 지정하고, 엑셀 열의 내용을 모두 import할 경우 모든 컬럼을 체크합니다.
7. 아래 next 버튼을 누르면 최종적으로 import를 진행합니다.
8. Import가 정상적으로 진행됐다면 아래 그림처럼 체크표시가 됩니다.
9. Import결과를 확인하고 아래 finish버튼을 눌러 종료합니다.
10. Import 이후 테이블 select시 import 내용을 확인할 수 있습니다.
● import시 주의 사항
1. 기본키가 없는 테이블은 select 시 import 버튼이 나타나지 않습니다.
그런데 기본키가 있는 테이블로 select한 이후 import버튼 누르고 팝업창에서 import 대상 테이블 선택시 PK가 없는 테이블을 선택하고 import를 진행할 수는 있습니다.
2. Import시 아래 그림과 같이 쓸 데 없는 열이 보일 수 있습니다.
위의 그림처럼 만약 엑셀작업 시 미사용 열에 아무 값이나 입력하고 지울 경우, import시 사용하는 컬럼으로 인식하는 경우가 생깁니다.
이 경우 미사용 컬럼은 체크해제한 후 import를 진행시키거나 아래처럼 미사용 컬럼열을 선택 후 삭제처리하시기 바랍니다.
3. import 시 엑셀파일의 첫 행 내용과 DB 테이블의 컬럼을 잘 맞춰서 import 합니다. 특히 엑셀에서 텍스트형식의 "01" 값을 DB에 import할 경우 컬럼 type이 int/bigint일 경우 "01"이 아닌 "1"로 import 될 수 있으므로 주의.
4. 만약 Auto-commit transaction 옵션이 해제되어 있을 경우, import 설정 중 Truncate table before import 체크 진행시 import 에러가 발생할 수 있습니다.
Truncate table before import 옵션을 사용하고자 할 경우, 먼저 Query메뉴>Auto-commit transactions 을 선택하여 체크한 후에 진행합니다.
※ 테이블을 Truncate 하게 되므로 import 이전의 데이터를 남기지 않고 rollback도 불가능하니 신중히 선택하시기 바랍니다.
