FULLTEXT 인덱스를 사용가능하게 지원하는데요
https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html
의 예제대로
mysql> CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
('Call me Ishmael.','Herman Melville','Moby-Dick'),
('A screaming comes across the sky.','Thomas Pynchon','Gravity's Rainbow'),
('I am an invisible man.','Ralph Ellison','Invisible Man'),
('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
('It was love at first sight.','Joseph Heller','Catch-22'),
('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
이건 지금 테스트중인 DB에서 고대로 수행하면
고대로 정상으로 나오는데...
CREATE TABLE userlog (
seq BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
userid INT(10) NOT NULL,
startDate TEXT NULL,
startTime VARCHAR(6) NOT NULL DEFAULT '',
endDate VARCHAR(8) NOT NULL DEFAULT '',
endTime VARCHAR(6) NOT NULL DEFAULT '',
PRIMARY KEY (`seq`),
FULLTEXT INDEX `kkk` (`startDate`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
INSERT INTO userlog
(`userid`,`startDate`, `startTime`, `endDate`, `endTime`)
VALUES
(106186, '20160611', '221556', '20160611', '020003'),
(106189, '20160611', '221556', '20160611', '020003'),
(106185, '20160611', '221557', '20160611', '020004'),
(106188, '20160611', '221557', '20160611', '020004'),
(106187, '20160611', '221558', '20160611', '020005'),
(106190, '20160611', '221558', '20160611', '020005');
COMMIT;
SELECT COUNT(*) FROM userlog WHERE MATCH(startDate) AGAINST('2016');
SELECT COUNT(*) FROM userlog WHERE MATCH(startDate) AGAINST('20160');
SELECT COUNT(*) FROM userlog WHERE MATCH(startDate) AGAINST('201606');
SELECT COUNT(*) FROM userlog WHERE MATCH(startDate) AGAINST('2016061');
결과가 모두 0개로 나옵니다 시벌 ㅠㅠㅠㅠㅠ
SELECT COUNT(*) FROM userlog WHERE MATCH(startDate) AGAINST('20160611');
이건 6개로 나오구요 ㅠㅠ
뭐가문제일까여
질문 | 1357명이 읽었어요. 3.136.18.48
