MySQL - 파일을 데이터로 포함시킨 노래방DB 구현

안녕하세요 :)

 

MySQL을 주제로 한 다섯번째 포스팅은 이전 포스팅 내용과 몇가지 추가적인 내용을 합하여 음악정보 사이트의 데이터베이스를 만들어 보겠습니다.

 

 

이번 포스팅에서는 노래방기계 데이터 베이스를 만들어 볼계획입니다.

 

저는 뭐든 만들어보면서 학습하는 방법이 개발실력 향상에 도움이 많이 된다고 느끼는 편입니다.

그래서 어떤것을 만들어보면 좋을까 생각하던중 노래방 데이터베이스 테이블이 생각났습니다.

 

 

노래방기계 DB에는 어떠한 행으로 구성된 테이블이 있어야 할까요?

 

 

첫번째로 음악을 구분하기 위한 번호가 있으면 좋을것 같습니다.

다들 노래방가면 좋아하는 노래 번호 하나쯤은 외우고있자나요 ㅎ, 하지만 노래의 번호는 겹치는것이 하나도 없는것이 특징입니다. 각각의 노래마다 고유한 번호가 부여되죠!

 

두번째로 노래의 이름있을것 같습니다. 노래의 이름은 중복이 허용될수 있죠!

 

세번째는 노래를 부른 가수명이 있습니다. 가수명도 중복이 당연히 허용될것 같습니다.

 

네번째는 가사가 있습니다. 노래를 검색할때는 나오지 않지만 데이터마다 고유한 가사 파일을 포함하고 있어서 선택이 될시 가사가 화면에 나타나죠

 

다섯번째는 노래 파일이 있을것입니다. 노래파일또한 데이터 행에 포함이 되어야 합니다. 

 

위와 같은 정보를 엑셀을 통해 구상한다면 다음과 같을것 같습니다.

 

 

 

위와 같이다섯가지의 열로 구분을 지어야 할것같습니다. 여기서 번호,이름,가수명 까지는 데이터 타입을 정의할수 있겠지만 가사와 노래파일의 경우 어떻게 해야할까요 ㅎ... 이 부분을 해결하기 위해 이번 포스팅에서 새로 등장하는 데이터 타입인 LONGTEXT 와 LONGBLOB이 있습니다.

 

이 두개의 데이터 타입은 각각 텍스트 파일과 여러가지 형식의 파일을 데이터로 포함시킬수 있도록 해주는 자료형입니다.

 

LONGTEXT 와 LONGBLOB)

위에 설명한 것과 같이 텍스트와 파일을 데이터로 받아들이기위한 자료형이며 INSERT 시 LOAD_FILE(파일경로)함수를 통해 파일을 자료형에 초기화 해주며 사용합니다.

 

그럼 위와 같이 데이터베이스(스키마)를 작성해보겠습니다. 이번에도 물론 개발환경은 우분투 리눅스의 터미널환경에서 진행하겠습니다.

 

먼저 karaokeDB라는 이름으로 노래방DB를 만들어 주었습니다.

 

USE를 사용하여 데이터 베이스를 선택해 주었습니다.

다음은 테이블을 만들어 주겠습니다 이게 가장 중요합니다. 아래와 같습니다.

 

위와 같이 총 다섯개의 열을 포함한 테이블을 만들어 주었고 마지막에 DEFAULT CHARSET = utf8mb4로 해준것은

데이터를 한글로 입력해도 깨지지 않고 잘 입력될수 있도록 하기 위해서 입니다.

 

그럼 이제 엑셀표에 있는 한행을 INSERT해주고 결과를 SELECT 해보겠습니다.

 

일단 INSERT는 계획대로 잘 된것을 확인할수 있습니다.

 

조회를 해보면 하하... NULL값으로 뜨며 잘 입력되지 않은것을 확인할수 있습니다.

 

당황스럽죠 ㅎ... 열심히 구글링하고 돌아야겠네요 ㅎㅎ... 구글링을 통해 원인을 찾았습니다.

 

파일이 데이터베이스에 업로드되지 않는 이유는 두가지가 존재합니다.

 

1. 파일의 크기제한이 걸려있어서 제한조건에 맞지않는 파일의 INSERT 시도

2. 보안상 허용된 디렉터리에서의 업로드가 아닌 다른 디렉터리에서 업로드 시도

 

그럼 두가지를 한번 확인해보겠습니다. 쿼리문은 다음과 같습니다.

 

첫번째 이유를 확인해 보려 조회해보니 파일 용량 제한값이 충분히 큽니다.

제 파일은 저정도로 대용량이되는것이 없습니다.

 

그렇다면 두번째 이유를 확인해보겠습니다.

 

두번째 이유인 디렉터리 접근 보안상의 문제 인거 같네요 해결 해보겠습니다.

 

먼저 파일 업로드가 허용되는 디렉터리를 설정해주기위해 /etc/mysql/my.cnf 파일을 편집해주어야합니다.

제일 마지막행을 위와 같이 사용할 디렉터리경로로 입력해주세요 그리고 mysql 서비스를 재시작해주어야 합니다.

 

여기서 제가 많이 애를 먹었습니다. 처음에는 /root 로 경로를 지정해주었습니다.

하지만 그렇게 하였을때 아래 사진에서 제일 마지막 결과의 1번행과 2번행의 결과가

/root의 경로로 파일을 INSERT했을때입니다.

 

/root 디렉터리에서의 파일 업로드 제한은  /etc/mysql/my.cnf로 해제 할수 있지만 실질적으로 테이블을

SELECT 했을때는 NULL값에서 변화되지 않았습니다.

 

그래서 /var/tmp디렉터리로 파일을 옮겨준 이후 다시 INSERT 를 해주니 결과를 잘 확인할수 있었습니다.

 

 

 

 

결과적으로 위의 세번째 행과 같이 파일을 데이터 처럼 삽입할수 있는것에 성공하였습니다. 내용에 들어있는 영문은 제가 임의로 막 타자로 적은 내용이기 떄문에 크게 신경쓰시지 않아도 됩니다!

 

그럼 이제부터 파일의 내용과 경로를 제대로 잘 정리하고 테이블을 지웠다가 다시 선언하여 새로운 상태에서 구현해보겠습니다.

 

먼저 중구난방으로 INSERT 된 테이블이 싹 지우기 위해 DROP table 쿼리문을 사용하겠습니다.

 

 

그리고 처음 선언한것과 똑같이 쿼리문을 작성하겠습니다. 지우고나서 다시 생성하였으니까 완벽하게 초기화 되었을것입니다.

 

 

이제 INSERT를 해줄것인데 /var/tmp 디렉터리에 파일들을 이쁘게 정리해서 해야겠죠

 

먼저 각각의 TXT 파일에 <노래제목 가사 파일>이라는 내용을 입력해주었습니다

 

skit.txt를 cat명령으로 확인하면 다음과 같습니다.

그리고 /var/tmp로 경로를 옮겨 주었습니다.

 

그리고 mp3파일들이 들어있는 디렉터리도 같은 위치로 옮겨주었습니다.

 

구분을 위해 mp3파일도 <노래이름 파일>이라는 텍스트를 입력해 주었습니다. 실제 mp3파일은 절대 이렇지 않지만

데이터의 구분을 위해 이번엔 이렇게 하겠습니다.

 

그리고 나서 skit 이라는 노래의 정보를 한행을 테스트삼아 INSERT 해주었습니다.

그 결과 아래와 같이 파일들이 데이터베이스로 잘 삽입된것을 확인할수 있었습니다.

 

 

이제 INSERT 작업을 반복하여 총 10개의 노래에 대한 데이터행을 삽입하겠습니다.

 

 

테이블의 모든 데이터를 조회한 결과는 다음과 같습니다.

 

 

 

이로서 파일을 포함시켜 노래방 데이터 베이스와 얼추 형식이 비슷한 테이블을 만들어 보았습니다.