[FULL TEXT INDEX / SEARCH] mysql 구축 및 DBeaver 연동, 에러 해결 및 테이블 생성 후 테스트

 

1. mysql 다운로드 받기 https://dev.mysql.com/downloads/installer/

MySQL :: Download MySQL Installer

1) 다운로드 클릭 후 설치

'mysql-installer-web-community-8.0.16.0.msi' 다운로드 하면 간편하게 설치할 수 있음

 

2) 기본정보 설정

PORT 번호 : 기본값은 3306

ROOT PASSWORD / 사용자 추가 및 비밀번호 기입

 

* 설치 참고 블로그 => https://ghostweb.tistory.com/100

 

2. HOME 설정 및 DB 생성

 

1) 내컴퓨터 > 우클릭 > 속성 > 고급시스템설정 > 환경변수 > 시스템변수 > PATH에 'C:\Program Files\MySQL\MySQL Server 8.0\bin' 추가

 

2) CMD 에서 'mysql -u root -p' 치고 비밀번호 입력하여 연동되는지 확인

 

3) 데이터 베이스 생성 => create database 데이터베이스명;

생성된 데이터베이스 확인 =>  show databases; 

데이터베이스 삭제(참고용) => drop database 데이터베이스명;

 

3. dbeaver 설치

community 다운로드 후 설치 https://dbeaver.io/download/

Download | DBeaver Community

4. dbeaver와 mysql 연동

 1) database navigator 부분에 우클릭 > create > connection > 검색창에 mysql 입력

MySQL 8+ 를 클릭하기 ('mysql-installer-web-community-8.0.16.0.msi'는 8버전이기 때문에 'MySQL 8+' 클릭)

 

database명 / user명 / password 기입 > 완료

 

드라이버가 없는 경우 시스템 안내에 따라 드라이브 다운로드하기

 

---

- 유의 사항 에러

1) 'Public Key Retrieval is not allowed' => 비밀번호가 너무 쉬운 경우에 발생하니 비밀번호 수정 후 다시 접속하기

 1-1) 상세 에러 : Exception in thread "main" java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

 1-2) 윈도우키 + cmd로 mysql 접속 후 사용자 비밀번호 변경

SET PASSWORD FOR '아이디'@'%' = PASSWORD('패스워드');

 

2) 서버 타임존(time zone) 에러 => 스키마의 타임서버존에 'UTC' 붙여주기 / 8버전에서만 에러가 나는 것 같음

 2-1) 상세 에러 exception is java.sql.SQLException: The server time zone value '´ëÇѹα¹ Ç¥ÁؽÃ' is unrecognized or represents more than one time zone.

 2-2) context-datasource.xml 파일에 UTC 넣기 

<property value="jdbc:mysql://127.0.0.1:3306/sample?serverTimezone=UTC" name="url"></property>

 2-2) dbeaver에서 수정하기

 

연결된 mysql 서버에 우클릭 edit connection

driver properties 클릭하고 servertimezone 찾아서 UTC 넣기

 

---

5. full text index 설정 변경

window에 설치한 mysql의 경우 "C:\ProgramData\MySQL\MySQL Server 8.0"의 'my.ini'의 설정을 읽어서 mysql이 실행된다.

 

따라서 my.ini 파일을 노트패드나 에디트플러스 등으로 열어서 수정해 준다.

 

ft_min_word_len=2
innodb_ft_min_token_size=2

혹시 해당 설정 내용이 없으면, 그냥 넣어준다.

 

mysql 실행을 중지하고 다시 재시작하면 해당 설정으로 변경 된다.

 

mysql에서 제대로 변경이 되었는지 확인해 본다.

 

show variables like '%ft_min%' 

 

---

6. full text index / search 테이블 생성 및 검색

# 테이블 생성
create table raw_data(
	ID bigint(20) not null,
	FILE_TITLE TEXT not null,
	TOTAL_PAGES BIGINT(10),
	DATA_PAGE BIGINT(10) not NULL,
	DATA_LINE BIGINT(10),
	SENTENCE LONGTEXT, 
	primary key (ID, DATA_PAGE, DATA_LINE), 
	fulltext key SENTENCE (SENTENCE)  -- full text index할 데이터 컬럼명
	) ENGINE=MyISAM default CHARSET=UTF8
;
alter table raw_data
add fulltext FILE_TITLE (FILE_TITLE) -- full text index할 데이터 컬럼 추가
;

- full text index할 테이블이 잘 생성되었는지 확인한다 

 => key에 'MUL'이 설정되어 있으면 잘 된 것이고, 아니면 테이블 drop하고 다시 create 해야 한다.

 

데이터 넣기 : 파이썬으로 연동해서 데이터 넣었음(생략)

 

# full_index 및 like 비교
select b.id, b.total_pages, b.data_page, b.data_line, b.like_sentence, a.full_index
from 
(select `ID`, `TOTAL_PAGES`, `DATA_PAGE`, `DATA_LINE`, `SENTENCE` as full_index
	from raw_data
	where match(sentence) against('material')) A 
right join
(select `ID`, `TOTAL_PAGES`, `DATA_PAGE`, `DATA_LINE`, `SENTENCE` as like_sentence
	from raw_data 
	where sentence like '%material%') B
on A.full_index = B.like_sentence
	and a.id = b.id
	and a.total_pages = b.total_pages
	and a.data_page = b.data_page
	and a.data_line = b.data_line

조회하면 like보다 full text index의 데이터 수가 더 적다.

 

그 이유는 'materials'와 같이 복수형은 검색되지 않기 때문이다.

 

이런 경우 'materials'를 추가한다.

 

# full_index 및 like 비교
select b.id, b.total_pages, b.data_page, b.data_line, b.like_sentence, a.full_index
from 
(select `ID`, `TOTAL_PAGES`, `DATA_PAGE`, `DATA_LINE`, `SENTENCE` as full_index
	from raw_data
	where match(sentence) against('material materials')) A 
right join
(select `ID`, `TOTAL_PAGES`, `DATA_PAGE`, `DATA_LINE`, `SENTENCE` as like_sentence
	from raw_data 
	where sentence like '%material%') B # 87개
on A.full_index = B.like_sentence
	and a.id = b.id
	and a.total_pages = b.total_pages
	and a.data_page = b.data_page
	and a.data_line = b.data_line