일반적으로 DB에서 특정 단어가 포함된 레코드를 찾기 위해서 LIKE 를 주로 사용한다. 

그러나 LIKE는 '%단어%'와 같이 사용할 때, 인덱스를 사용하지 않기 때문에 속도가 느려진다. 

또한 LIKE를 여러 필드 (예를 들어, title과 keyword 등)에 동시에 적용하면서 여러 단어를 검색어로 전달하면 쿼리가 복잡해지기도 한다. 

> select * from board 

> where (title like '%학교%' or body like '%학교%') 

> and (title like '%취업%' or body like '%취업%');

이를 대체하기 위해서 정규표현식을 적용할 수도 있다. 

그러나 정규표현식도 마찬가지로 인덱스를 거치지 않고 풀 스캔(Full Scan)이 일어나기 때문에 느리다. 

> select * from board 

> where (title REGEXP '학교' or body REGEXP '학교') 

> and (title REGEXP '취업' or body REGEXP '취업');

Match ~ Against 를 활용한 전문 검색 (Full-Text Search)

이런 경우, 활용할 수 있는 것이 바로 MySQL의 전문 검색(Full-Text Search)이다. 

위 예제는 다음과 같이 간단하게 쿼리할 수 있다. 

> select * from board 

> where match(title,body) against('+학교* +취업*' in boolean mode);

title과 body에 학교와 취업이 모두 포함된 레코드를 리턴해 주는 것이다. 

+는 AND를 나타내고 -는 OR를 나타내므로, 더 많은 결과를 원할 경우, '+학교* - 취업*'  형태로 사용할 수도 있다. 

MySQL의 Full-Text Search는 자연어 처리 모드도 제공하고 있다. 

그래서 다음과 같은 쿼리도 가능하다. 

> select * from board 

> where match(title,body) against('대학생 취업 문제는 무엇일까요?')

Match ~ Against 활용을 위한 설정

Full-Text Search를 위해서 테이블 생성시나 alter table로 전문 검색할 필드를 지정해 줘야 한다. 

crate table board (

 id int not null primary key,

 title varchar(200),

 body text,

 fulltext (title,body));

그리고 full-text search는 mysql 버전 5.5 이하는 MyISAM에서만 사용할 수 있다. 

5.6 이상 버전은 InnoDB에서도 사용할 수 있다. 

마지막으로 match against를 사용할 때, 처음에는 위와 같은 2글자 검색에 대한 결과는 나오지 않는다. 

기본적으로 full-text search가 4글자 이상에서만 검색되도록 설정되어 있기 때문이다. 

한글에서는 두글자 검색도 자주 일어나므로 위 설정을 2로 변경해야 보다 의미가 있다. 

다음 명령어로 확인해 보자. 

> show variables like 'ft_min_word_len';




이 값을 수정하기 위해서는 my.ini(윈도우), my.cnf(리눅스) 파일을 수정해야 한다. 

[mysqld]

ft_min_word_len = 2

설정 파일을 수정한 후, mysql 서버를 재시작하고 ft_min_word_len 값을 확인하면 2로 수정되었을 것이다. 

이상으로 mysql의 전문 검색 기능을 살펴봤다. 

속도 측면에서는 분명히 장점이 있는 듯하다. 

그리고 자연어 처리가 되는 것으로 봐서 내부적으로 형태소 분석이 포함되어 있는 것 같다.  

다만 검색 품질이 얼마나 괜찮은지는 좀 더 테스트를 해봐야 할 것으로 보인다. 

Mac에서 MySQL 설치 후, 삭제하는 방법에 대한 정리입니다. 

한번에 삭제하는 명령이 없으니 콘솔에서 아래 명령어를 하나씩 실행하면 됩니다. 

sudo rm /usr/local/mysql

sudo rm -rf /usr/local/mysql*

sudo rm -rf /Library/StartupItems/MySQLCOM

sudo rm -rf /Library/PreferencePanes/My*

rm -rf ~/Library/PreferencePanes/My*

sudo rm -rf /Library/Receipts/mysql*

sudo rm -rf /Library/Receipts/MySQL*

sudo rm -rf /var/db/receipts/com.mysql.*

sudo vi /etc/hostconfig 

-> MYSQLCOM=_YES_ 라인 삭제

복사할 수 있도록 명령어를 스크립트로 첨부합니다. 

sudo rm /usr/local/mysql sudo rm -rf /usr/local/mysql* sudo rm -rf /Library/StartupItems/MySQLCOM sudo rm -rf /Library/PreferencePanes/My* rm -rf ~/Library/PreferencePanes/My* sudo rm -rf /Library/Receipts/mysql* sudo rm -rf /Library/Receipts/MySQL* sudo rm -rf /var/db/receipts/com.mysql.* sudo vi /etc/hostconfig

MySQL Workbench 5.2.42 버전을 사용하고 있는데요. 

update나 delete 쿼리를 전달하니 다음과 같은 오류가 발생했습니다. 

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

safe mode로 설정되어 있어서 key 칼럼을 사용하지 않을 경우, 업데이트를 못한다는 것인데요. 

관련 내용을 검색해 보니 설정을 바꾸면 된다고 하네요. 

http://mysqlblog.fivefarmers.com/2010/09/01/workbench-called-me-a-dummy/

• Go to Edit -> Preferences
• Select the SQL Editor tab
• Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”

그런데 버전이 달라졌는지.. SQL Editor에는 저 항목이 없더라구요. 

찾아보니 SQL Queries 탭에 있었습니다. 

아래 그림에서 빨간색 부분의 체크박스를 제거하니 제대로 동작하네요. 

MySQL에서 Index를 설정할 경우, 오라클처럼 Hint를 활용해서 사용자가 지정하는 Index를 실행하도록 구성할 수 있습니다. 

인덱스를 잘 설정하려면 사용할 쿼리에 대한 실행계획을 볼 줄 알아야 합니다.

MySQL에서 실행계획을 살펴보는 명령어는 explain 입니다.

> explain
> select ~~

이런 형태로 사용하면 쿼리 결과가 아니라 다음과 같은 실행 계획이라는 것이 나타납니다.  

1 row in set (0.00 sec)

해당 정보에 대해서는 다음 사이트에 보면 한글로 잘 정리해 놓았으니 참고하시기 바랍니다. 
오늘 정리할 내용은 explain이 아니므로 http://blog.wowpc.net/30 을 참고하세요~~

자~ 그럼 인덱스 사용과 관련된 내용을 정리해 보겠습니다.

먼저 현재 테이블에 설정된 인덱스는 다음과 같은 명령어로 살펴볼 수 있습니다. 

> SHOW INDEX FROM 테이블명

USE INDEX, FORCE INDEX, IGNORE INDEX를 사용해서 강제적으로 사용할 인덱스를 지정할 수 있습니다.
또한 FOR ORDER BY, FOR GROUP BY를 이용하여 정렬이나 그룹 지정에 필요한 인덱스도 지정할 수 있습니다.

다시 정리하면 다음과 같습니다. 

USE INDEX (인덱스명)
IGNORE INDEX (인덱스명)
FORCE INDEX (인덱스명)

USE INDEX FOR ORDER BY (인덱스명)
USE INDEX FOR GROUP BY (인덱스명)

예를 들어보면 다음과 같이 사용하실 수 있습니다. 

> SELECT * FROM 테이블명 IGNORE INDEX (인덱스명1, 인덱스명2) WHERE 조건절;
> SELECT * FROM 테이블명 USE INDEX FOR ORDER BY (인덱스명3);

그런데 제가 테스트 해보니 DESC로 지정된 ORDER BY 인덱스가 계속 안되는 겁니다. 살펴보니 다음과 같았습니다. 

헐.. 현재는 인덱스에서 DESC 관련 지원이 안되는가 봅니다. 

이와 관련해서 해당 필드에 -1을 곱해서 ASC로 처리할 수 있다는 글들도 있던데.. 
그렇게까지 하면서 인덱스 정렬을 해야 할까 하는 생각이 들어서~
MySQL에서는 DESC 필드에 대한 인덱스를 통한 정렬을 안하기로 했네요.. 

참고하시기 바랍니다. 

 

MySQL의 설정이 어떻게 되어 있는지 확인하는 명령어를 간단하게 한번 정리합니다. 
가끔 언어 설정이나 버전 등의 정보가 궁금한 경우가 있더라구요.

MySQL 상태 확인

--------------

MySQL 버전 확인

status로도 버전을 확인할 수 있지만 버전만 별도로 확인하는 내용입니다. 

 

요즘 App을 개발하다보면 지도를 활용하는 어플이 많습니다. 
특히 현재 위치에서 다른 위치 사이의 거리를 계산해야 하는 경우가 있는데요..

Google API를 사용할 경우, distanceBetween과 같은 메소드를 활용할 수도 있을 겁니다.

오늘 정리할 내용은 MySQL 쿼리를 활용해서 두 위치 사이의 거리를 계산하는 겁니다.
오랜만에 보는 몇가지 수학함수를 사용하면 간단히 결과가 나오네요..

다음과 같이 가정하고 쿼리를 만들어 보도록 하겠습니다.

현재 위치
위도 : nowLat
경도 : nowLng

지정 위치
위도: setLat
경도: setLng

> select (6371 * acos( cos( radians(nowLat) ) * cos( radians( setLat ) ) * cos( radians( setLng ) - radians(nowLng) ) + sin( radians(nowLat) ) * sin( radians( setLat ) ) ) ) as distance;

위와 같이 하면 거리에 대한 결과가 나옵니다.

실제 사례를 들어서 확인해 보도록 하죠.. 

현재 위치
위도 : 37.486273
경도 : 126.995882

지정 위치
위도: 35.177286
경도: 126.900247

위 숫자를 넣어서 실행해 보도록 하겠습니다.

> select  (6371 * acos( cos( radians(37.486273) ) * cos( radians( 35.177286 ) ) * cos( radians( 126.900247 ) - radians(126.900247) ) + sin( radians(37.486273) ) * sin( radians( 35.177286 ) ) ) ) as distance;

결과는 다음과 같습니다.

소수점 2자리까지만 출력하기 위해 ROUND 함수를 처리해 봤습니다.

> select  ROUND(6371 * acos( cos( radians(37.486273) ) * cos( radians( 35.177286 ) ) * cos( radians( 126.900247 ) - radians(126.900247) ) + sin( radians(37.486273) ) * sin( radians( 35.177286 ) ) ), 2) as distance; 

256.75 Km라는 결과가 나옵니다.

위 예제를 잘 활용하면 MySQL을 활용하는 분들은 DB에서 거리 계산하는데 보다 쉬울 것 같네요.. 
소스를 가져갈 수 있도록 다시 한번 정리할께요..

  > select (6371 * acos( cos( radians(nowLat) ) * cos( radians( setLat ) ) * cos( radians( setLng ) - radians(nowLng) ) + sin( radians(nowLat) ) * sin( radians( setLat ) ) ) ) as distance; > select ROUND(6371 * acos( cos( radians(37.486273) ) * cos( radians( 35.177286 ) ) * cos( radians( 126.900247 ) - radians(126.900247) ) + sin( radians(37.486273) ) * sin( radians( 35.177286 ) ) ), 2) as distance;

PHP와 MySQL을 사용하는 웹 호스팅에서 데이터베이스의 모든 테이블을 지우려고 할 경우, Drop Database 문을 활용하지 못하는 경우가 많습니다.

그렇다고 수많은 테이블을 하나씩 지우는 것도 방법이 아닐 듯 하네요..

그래서 다음과 같은 PHP 프로그램을 통해서 처리할 수 있을 것 같습니다.

먼저 "show table"을 사용해서 전체 테이블 정보를 가져오고 이어서 "drop table" 명령으로 하나씩 지우면 되겠죠.

다음 소스를 참고하세요~

<?php $db_host=""; $db_id=""; $db_password=""; $db_dbname=""; $db_conn = mysql_connect($db_host,$db_id,$db_password) or die("데이터베이스 연결에 실패하였습니다."); mysql_select_db($db_dbname,$db_conn); $result = mysql_query("show table status from $db_dbname"); while($dbData=mysql_fetch_array($result)) { $drop_result = mysql_query("drop table $dbData[Name]"); } ?>

MySQL 중지 및 재시작과 관련해서 가끔 할 때마다 까먹어서 한번 정리 봅니다. 

MySQL 중지와 관련해서 kill 하는 방법도 있지만 전 아래 방법이 더 나은 것 같습니다.

> mysqld_safe & 

MySQL 쿼리 결과를 파일로 저장할 필요가 가끔 있습니다.
,를 구분자로 한 CSV 파일로 저장하면 엑셀에서 열어서 편집도 가능하죠..

간략하게 구문을 정리해 봅니다.

SELECT col1, col2, col3 INTO OUTFile '저장할파일명' FIELDS TERMINATED BY ',' FROM 테이블명;

INTO OUTFILE '저장할파일명' : SELECT 쿼리 결과를 저장할 파일을 지정합니다.
FIELDS TERMINATED BY '구분자': 각 Column을 구분할 구분자를 지정합니다.

예를 들어서 다음과 같이 쿼리하면 저장이 됩니다.
member 테이블에서 userid, name, email, status 필드 값을 추출해서 mini.csv로 저장합니다.

> select userid, name, email, status into outfile 'mini.csv' fields terminated by ',' from member;

이 경우, mini.csv 파일은 어디에 저장될까요?
기본적으로 mysql을 설치한 디렉토리에 저장됩니다.
제가 테스트 해보니 mysql 설치 디렉토리 하위의 data 폴더 밑에 DB이름으로 된 폴더 밑에 있더라구요.
즉, D:\dev\mysql\data\mini 하위에 mini.csv 파일이 저장되더군요.

다음과 같이 절대 경로로 입력해서 원하는 위치에 저장할 수도 있습니다.

윈도우의 경우
> select userid, name, email, status into outfile 'C:\mini.csv' fields terminated by ',' from member;

리눅스의 경우
> select userid, name, email, status into outfile '~/mini.csv' fields terminated by ',' from member;

MySQL의 DB 백업 및 복구에 대해서 간단하게 정리해 봅니다.

MySQL 데이터 백업

백업: mysqldump -u DB계정 -p DB명 > 저장할파일명
복구: mysql -u DB계정 -p DB명 < 저장한파일명

DB 백업 예제
>mysqldump -u root -p mini > mini.sql
Enter password:

DB 생성 예제
> mysqladmin -u root -p create mini
Enter password:

DB 복구 예제
> mysql -u root -p mini < mini.sql
Enter password:

데이터베이스(예, mini)의 특정 테이블(예, member) 백업과 복구

> mysqldump -u root -p mini member > mini_member.sql
Enter password:

> mysql -u root -p mini < mini_member.sql
Enter password:

이외에도 여러개의 데이터베이스를 한번에 백업하고 복구하거나 전체 데이터베이스를 할 수도 있지만 해당 내용은 다음과 같은 옵션으로 처리할 수 있습니다. 

-databases DB1 DB2 DB3
-all-databases

자세한 내용은 메뉴얼을 보세요.. 맨위에 소개한 두가지면 왠만하면 대부분 처리되지 않을까 합니다.