Mysql FullText 를 이용한 한글 검색시 유의 사항

varchar(255)를 일반적으로 사용하고 있으나 facebook에서 명백히 제시한 형태가 없고 변화의 가능성이 있다. 그러므로 text를 이야기하는 사람도 있음.

http://forum.falinux.com/zbxe/index.php?document_srl=550833&mid=lecture_tip 에서 참조

며칠전 MySql을 사용할 때 삽질했던 부분 입니다.

그때그때 발생했던 이벤트에대한 데이터를 DB에 저장하고 리스트로 확인할때에, DB에 저장까지는 했는데 데이터를 꺼내 확인 할때

에 한글이 깨지는 현상이 일어 났습니다.

이것저것 찾아본 결과를 적어 보았습니다.

먼저 MySql콘솔로 들어가서 show variables like ‘c%’; 를 실행시켜보면 다음과 같이 나옵니다.(default일경우)

mysql1-1.jpg 

MySql의 기본설정은 latin1, latin1_swedish_ci 상태입니다.

이벤트 정보에 대한 화면표시 할때 사용 하는 캐릭터설정이 UTF-8 DB설정하고 맞질않아 한글같은 경우는 깨짐 현상이 일어났습니다.(영숫자는 문제없음.)

해결방법으론 캐릭터 설정을 UTF-8로 다 바꾸어주면 됩니다.

어떻게 다음과 같이 ……

윈도우일경우(C:Program FilesMySQLMySQL Server 5.1)의 my.ini파일을 열어 캐릭터 설정을 바꿉니다.

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

[mysqld]

default-character-set=utf8

위와같이 수정한후 DB를 재기동 시킵니다.(제어판-관리도구-서비스-MySQL)

재기동후 먼저MySql콘솔로 들어가서 show variables like ‘c%’; 를 실행시켜보면 다음과 같이 나옵니다.(UTF-8적용됨)

mysql1-2.jpg 

그리고 테이블 생성할때에도 아래와같이 CHARESET=utf8로 해주셔야 됩니다.

CREATE TABLE `testTable` (

  `no` int(11) NOT NULL AUTO_INCREMENT,

  `tem1` varchar(128) DEFAULT NULL,

  `tem2` varchar(8) DEFAULT NULL,

  PRIMARY KEY (`no`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

전부 다 셋팅이 됐으면 한글이 제대로 나올것입니다.(화면에서….)

MySql콘솔이나 DB툴로 데이터를 확인했을때는 한글이 깨져보임.

MySql콘솔이나 DB툴에서도 한글 깨짐 현상이 없게하려면…..

그건 … 다음 시간에 하도록 하겠습니다…^^;

  1. 문자셋(Character Set)
    • 말 그대로 특정 ‘문자(좀 더 정확히는 symbol)’가 컴퓨터에 저장될 때 어떠한 ‘코드’로 인코딩되어 저장될 것인지에 대한 규칙이 정의되어있는 집합을 말함. (A character set is a set of symbols and encodings.) 
    • 대표적인 것으로는 ASCII, 한글을 지원하는 EUC-KR, 유니코드 UTF-8, UTF-16 등이 있음.
    • 문자셋은 자신이 처리할 문자 집합이 얼마나 큰가에 따라 하나의 ‘문자’를 저장하는데 필요한 저장공간의 크기가 달라지게 된다. 또한 항상 모든 문자들을 동일한 크기로 인코딩하는 고정폭 방식의 문자셋이 있는가하면, EUC-KR(1~2바이트)이나 UTF-8(1~3바이트)과 같이 가변폭 방식의 인코딩을 사용하는 문자셋도 있다.
  2. 콜레이션(Collation)
    • 이것은 특정 문자셋에 의해 데이터베이스에 저장된 값들을 검색하거나 정렬 등의 작업을 위해 문자들간에 서로 ‘비교’할때 사용하는 규칙들의 집합이다. (A collation is a set of rules for comparing characters in a character set.) 따라서, 일반적인 Data Type에는 적용되지 않고, CHAR, VARCHAR, TEXT와 같은 데이터 타입을 가지는 칼럼들에 대해서만 적용된다.
    • 주로 대소문자를 구별하는 binary 형식을 사용할 것인지, 대소문자를 구별하지 않는 Case Insensitive 형식을 사용할 것인지로 나누어진다. Case Insensitive 형식을 사용하는 collation들은 모두 collation 이름의 끝에 ‘ci’라는 문자가 붙어있다.

 

[참고]

  1. 특별히, MySQL에서의 문자셋과 collation 에 대한 문제는 단순히 데이터를 저장하는 공간에 대한 관점만이 아니라, MySQL 서버와 MySQL 서버에게 요청을 하는 클라이언트 사이에 주고받는 상호작용에 대한 관점까지도 포함한다는 것을 꼭 기억할 필요가 있다. 
    • 데이터를 저장하는 관점 : MySQL은 Server 레벨, Database 레벨, Table 레벨, Column 레벨까지 총 4개의 레벨을 지원하는 특징을 가지고 있음.
    • 서버와 클라이언트간의 상호작용 관점 : MySQL은 이 관점에서 문자셋과 관련한 총 3개의 서로 다른 타입을 제공한다.
      • character_set_client : 클라이언트에서 문장이 떠날 때 해당 문장은 어떤 문자셋으로 이루어져있는가의 문제
      • character_set_connection : 클라이언트에서 넘어온 문장을 서버에서는 어떤 문자셋을 기준으로 해석해야하는가의 문제.
      • character_set_results : 서버에서 클라이언트로 응답을 넘겨줄때 어떤 문자셋으로 넘겨야하는가의 문제.
    • 따라서, 데이터베이스 레벨에서 euckr 문자셋이 기본값으로 지정되어있는 데이터베이스를 생성한 후 해당 데이터베이스에 데이터를 잘 입력하였는데 이상하게 브라우저로 넘기면 데이터베이스에서 읽어온 문자만 글자가 깨지는 상황이 벌어진다면(브라우저의 인코딩 설정 등은 정상적이라고 가정) 다음과 같이 체크해봐야 한다.
      • Database 레벨, Table 레벨, Column 레벨에서 원하는 문자셋으로 잘 지정되어있는지를 확인
      • client, connection, results 에서의 문자셋도 적절하게 지정되어있는지를 확인. (대개 글자가 깨지는 경우라면, 아마도 이쪽이 잘못 지정되어있을 가능성이 높다)
    • SQL 문장 중에 ‘SET NAMES euckr [COLLATE collation_name]’을 사용하면 앞에서 살펴본 client, connection, results 3가지에 대한 문자셋을 한꺼번에 지정해줄 수 있음. (서버 실행중에도 변경이 가능. 단, 서버 실행중에 SQL 문장으로 변경하는 것은 영구 변경이 아니라 현재 연결에 한해서만 임시로 변경하는 것임)
    • 임시 변경이 아닌 영구 변경을 하려면, MySQL 서버가 처음 시작할 때 참고하는 my.cnf 환경설정 파일내에 ‘init_connect=SET NAMES euckr’ 이런식으로 지정해두면 됨.
    • 이렇게 영구 변경과 임시 변경 방법을 활용하면 좀 더 다양한 응용이 가능해진다.
      • 서버가 시작하는 단계에서 지정하는 경우 : 대개 환경설정 파일인 my.cnf 파일에서 ‘init_connect=SET NAMES euckr’ 명령어로 지정. 이렇게 했을때는 해당 서버를 사용하는 모든 애플리케이션들에게 이 설정이 기본값으로서의 영향을 미친다.
      • 시작 단계가 아니라 서버가 실행중에 데이터베이스 별로 다른 문자셋을 사용하도록 지정하는 경우 : 일단 클라이언트가 서버로 연결을 한 이후에 곧바로 ‘SET NAMES euckr’ 명령어를 실행시켜서 현재 연결에 대한 연결 관련 문자셋들을 수정해놓고 시작한다. 이렇게 하면, 각각의 데이터베이스별로 서로 다른 연결 관련 문자셋을 지정할 수 있게 됨(!!!)
      • 즉, 동일한 서버내에서 대부분의 애플리케이션은 utf-8 문자셋을 사용하는데 특정 애플리케이션만 euckr 문자셋을 사용하고자 할 경우에 위와 같은 방법을 사용하면 가능.
      • 참고로, PHP에서 사용하는 mysqli 확장에서는 ‘SET NAMES …’를 query 메소드로 실행시키는 방법 이외에 ‘set_charset’ 이라는 더 나은 메소드를 통해서 동일한 효과를 얻을 수도 있음.
  2. (질문) PHPMyAdmin에서 새로운 데이터베이스를 생성할 때, 해당 데이터베이스에서 사용할 Default collation 만을 지정하게 되는데… 그렇다면, 해당 데이터베이스이 Default Character Set은 어떻게 되는 것일까?
    • (대답) 뭐… 당연한 이야기겠지만, Default collation 만을 지정하게 되면 해당 collation이 속해있는 Character Set이 자동으로 그 데이터베이스의 Default Character Set으로 지정됨. 물론, PHPMyAdmin의 GUI 방식을 사용하지 않고, 직접 DDL 문장을 사용하면 하나 하나 별도로 지정해줄 수도 있음. 또한, PHPMyAdmin에서 데이터베이스를 생성할 때, Default collation 조차도 지정하지 않게 되면, 당연히 MySQL의 환경설정 파일인 my.cnf(또는 my.ini)에서 지정되어있는 시스템의 기본적인 Character Set이 적용되게 되며 Collation은 해당 Character Set의 Default collation이 적용되게 됨.
FullText 의 개념
mysql 메뉴얼을 읽어보면 알 수있듯이
FullText 검색 기능의 개념 자체는 작은 규모가 아닌 큰 규모의 
문자열 집합을(Large collections) 대상으로 빠르게 해당 검색어를 
검색하기 위해서 만들어진 것입니다.
그리고, 이것은 ‘Like’ 문 기능과는 다르고,
‘Where 필드 = 검색문자열’ 문과도 (어떻게 만드느냐에 따라서) 다를 수 있습니다.
큰 규모라고 하면, 웹 검색엔진을(the Internet with a search engine) 
상상해보시면 될겁니다. 구글(Google)의 경우 30억개 이상의 웹페이지를 
대상으로 검색할 수 있다고 사이트에 명시하고 있습니다. DB 개념으로 
이해하자면, 웹페이지와 같은 큰 문자열을 각각 지니고 있는 30억개 
이상의 레코드들을 검색한다는 것입니다.
이런 방대한 자료에서 어떤 문자열 하나를 찾기위해서 ‘Like’ 문을 쓴다고 상상해보세요.
여기에, 전 세계적으로 엄청난 동시 사용자들을 감안한다면, ‘Like’ 문같은 검색은 
쓸 수가 없겠죠. 실제로 구글에서 여러가지 문자열을 가지고 테스트 
해보시면, 절대 Like 검색방식으로 처리가 되고있지 않다는 것을 알 수 
있습니다.
그리고, ‘Where 필드 = 검색문자열’과 같은 것은, FullText 검색기능을 
내부적으로 어느 정도선까지 지원하도록 개발하느냐에 따라서 틀려질 수 
있습니다.
 
단어를 포함하는 모든 자료를 가져올지, 아니면 단어들중 의미 
있다고 판단되는 단어만을 포함하는 자료를 가져올지 등등… 구글은 
전자의 형태로 개발된 것 같습니다.
당장, ‘a’, ‘are’, ‘this’라는 단어로 검색해보면 엄청난 수의 검색결과를 볼 수 있습니다. MySQL의 FullText 검색은 보통 후자를 택하고 있고,
일종의 자연어 검색(Natural Language Search)을 한다고 밝히고 있습니다.
(단, Boolean Mode도 따로 제공하고 있습니다.)
 
* ‘자연어 검색’에 대해서는 잘 모르지만, 그것과는 별개로, MySQL이 
채택한 검색방식의 핵심을 이해하기 위해서는, 해당부분 MySQL 메뉴얼에서 
자주 쓰인 ‘적절성 값(Revelence Value)’라는 말을 이해해야 될겁니다.
‘적절성 값(Revelence Value)’의 뜻은, 레코드에 포함되어있는 문자열과 
검색하려는 검색어가 얼마나 유사성(Similarity)을 갖고 있는지를 측정한 
것이라고 합니다.(여기에서의 유사성은 단어중 몇글자가 같냐는 식으로 
판단되는 것은 아닙니다.) 그리고, 이 값은 다음과 같은 것들을 계산해서 
나온다고 합니다.
 
레코드안에 들어있는 단어들의 수 
(the number of words in the row) 
그 레코드 안에서 유일한 단어들의 수 
(the number of unique words in that row) 
그 집합 안에서 단어들의  총 갯수 
(the total number of words in the collection) 
특별한 단어를 포함하고 있는 문서들(레코드들)의 수 
(the number of documents (rows) that contain a particular word)
 
‘유사성’이라는 말을 염두에 둔다면, 대충 Revelence Value가 어떻게 계산 
되는지를 유추해볼 수는 있을겁니다. 예를 들어, 검색어로 5개의 단어를 
입력했다면, 5개의 단어들중 각각의 레코드가 몇개의 단어를 포함하고 
있는지, 5개 각각의 단어들이 몇개씩 포함하고 있는지 등에 따라서 
유사성이 틀려질 수 있겠지요. 이 값이 0이면, 유사성이 전혀 없다는 것을 
의미합니다. (Zero relevance means no similarity.)
fulltext 를 이용한 검색 기능 구현 방식은 아래와 같습니다.
 
1. 테이블 생성시에 검색할 필드를 전부 fulltext() 로 잡습니다.
fulltext ( name, subject, contents )
2. 검색 쿼리
select * from table match(name,subject, contents ) against(’“검색단어”’ in boolean mode);
3. 쿼리시 주의점
in boolean mode 선언이 반드시 필요하며, match()의 필드는 fulltext() 에서 선언된 그대로 넣어야합니다.
 
against 에서는 작은따옴표 안에 큰따옴표를 사용해야 온전한 글로 인식되어 검색됩니다.
따옴표가 하나밖에 없다면, or 검색이 되어 원치 않는 결과가 나옵니다
4. 필드 추가시
alter table ‘해당테이블’ add fulltext(‘추가필드’)

Why are you looking at this wiki page?

Are you looking at this page because you cannot access the mysql server installed on your pc/server when you were trying to see if it works well? Or do you receive error messages like the following? :

ERROR 1045: Access denied for user: 'root@localhost' (Using 
password: NO)

or

ERROR 1045: Access denied for user: 'root@localhost' (Using 
password: YES)

To resolve this problem ,a fast and always working way is the “Password Resetting” .

How can I reset my MySQL password?

IconsPage/IconWarning3.png Following this procedure, you will disable access control on the MySQL server. All connexions will have a root access. It is a good thing to unplug your server from the network or at least disable remote access.

To reset your mysqld password just follow these instructions :

  • Stop the mysql demon process using this command :
    •    sudo /etc/init.d/mysql stop
  • Start the mysqld demon process using the –skip-grant-tables option with this command
    •    sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Because you are not checking user privs at this point, it’s safest to disable networking. In Dapper, /usr/bin/mysqld… did not work. However, mysqld –skip-grant-tables did.

  • start the mysql client process using this command
    •    mysql -u root
  • from the mysql prompt execute this command to be able to change any password
    •    FLUSH PRIVILEGES;
  • Then reset/update your password
    •    SET PASSWORD FOR root@'localhost' = PASSWORD('password');
  • If you have a mysql root account that can connect from everywhere, you should also do:
    •    UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
  • Alternate Method:
    •    USE mysql
         UPDATE user SET Password = PASSWORD('newpwd')
         WHERE Host = 'localhost' AND User = 'root';
  • And if you have a root account that can access from everywhere:
    •    USE mysql
         UPDATE user SET Password = PASSWORD('newpwd')
         WHERE Host = '%' AND User = 'root';

For either method, once have received a message indicating a successful query (one or more rows affected), flush privileges:

FLUSH PRIVILEGES;

Then stop the mysqld process and relaunch it with the classical way:

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

When you have completed all this steps ,you can easily access to your mysql server with the password you have set in the step before. An easy way to have a full control of your mysql server is phpmyadmin (www.phpmyadmin.net), software made in php that can give you a web interface that can be very usefull to people that havent got a lot of confidence with bash .To install phpmyadmin on you server you will need to have 4 things:

  • web server apache
  • php
  • mysql server/mysql client installed
  • php_mysql support for apache

All packages can be found browsing synaptic.