CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...') ,
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
CREATE FULLTEXT INDEX idx on articles (title, body);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
3 Optimizing MySQL In this tutorial we will show ...
SELECT COUNT(*) FROM articles
WHERE MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
COUNT(*)
2
SELECT * FROM articles
WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
3 Optimizing MySQL In this tutorial we will show ...
SELECT COUNT(IF(MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
AS count FROM articles;
count
2
ANALYZE TABLE articles;
Table Op Msg_type Msg_text
test.articles analyze status OK
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
id title body
6 MySQL Security When configured properly, MySQL ...
1 MySQL Tutorial DBMS stands for DataBase ...
2 How To Use MySQL Well After you went through a ...
3 Optimizing MySQL In this tutorial we will show ...
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('DBMS Security' IN BOOLEAN MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
6 MySQL Security When configured properly, MySQL ...
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
id title body
5 MySQL vs. YourSQL In the following database comparison ...
DROP INDEX idx ON articles;
CREATE FULLTEXT INDEX idx on articles (title, body);
CREATE FULLTEXT INDEX idx1 on articles (title);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
3 Optimizing MySQL In this tutorial we will show ...
DROP INDEX idx ON articles;
DROP INDEX idx1 ON articles;
CREATE FULLTEXT INDEX idx1 on articles (title);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
drop table articles;
CREATE TABLE articles (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
create unique index FTS_DOC_ID_INDEX on articles(FTS_DOC_ID);
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...') ,
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
CREATE FULLTEXT INDEX idx on articles (title, body);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID title body
1 MySQL Tutorial DBMS stands for DataBase ...
3 Optimizing MySQL In this tutorial we will show ...
drop table articles;
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...') ,
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
CREATE FULLTEXT INDEX idx on articles (title);
Warnings:
Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
CREATE FULLTEXT INDEX idx2 on articles (body);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Can't find FULLTEXT index matching the column list
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
1 MySQL Tutorial DBMS stands for DataBase ...
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
3 Optimizing MySQL In this tutorial we will show ...
drop index idx2 on articles;
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Can't find FULLTEXT index matching the column list
CREATE FULLTEXT INDEX idx2 on articles (body);
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
3 Optimizing MySQL In this tutorial we will show ...
UPDATE articles set title = 'aaaa'
WHERE MATCH(title) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
id title body
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('aaaa' IN NATURAL LANGUAGE MODE);
id title body
1 aaaa DBMS stands for DataBase ...
drop table articles;
CREATE TABLE articles (
FTS_DOC_ID BIGINT UNSIGNED NOT NULL ,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx on articles (title);
INSERT INTO articles VALUES (9, 'MySQL Tutorial','DBMS stands for DataBase ...');
UPDATE articles set title = 'bbbb' WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Invalid InnoDB FTS Doc ID
UPDATE articles set title = 'bbbb', FTS_DOC_ID=8 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
ERROR HY000: Invalid InnoDB FTS Doc ID
UPDATE articles set title = 'bbbb', FTS_DOC_ID=10 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (title) AGAINST ('bbbb' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID title body
10 bbbb DBMS stands for DataBase ...
SELECT * FROM articles WHERE MATCH (title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID title body
CREATE FULLTEXT INDEX idx2 ON articles (body);
SELECT * FROM articles WHERE MATCH (body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
FTS_DOC_ID title body
10 bbbb DBMS stands for DataBase ...
UPDATE articles set body = 'bbbb', FTS_DOC_ID=11 WHERE MATCH(body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
drop table articles;
create table `articles`(`a` varchar(2) not null)engine=innodb;
create fulltext index `FTS_DOC_ID_INDEX` on `articles`(`a`);
ERROR HY000: Index 'FTS_DOC_ID_INDEX' is of wrong type for an InnoDB FULLTEXT index
create unique index `a` on `articles`(`a`);
drop table articles;
CREATE TABLE wp(
FTS_DOC_ID bigint PRIMARY KEY,
title VARCHAR(255) NOT NULL DEFAULT '',
text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
(1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
(2, 'How To Use MySQL Well','After you went through a ...');
CREATE FULLTEXT INDEX idx ON wp(title, text);
ERROR HY000: Column 'FTS_DOC_ID' is of wrong type for an InnoDB FULLTEXT index
DROP TABLE wp;
CREATE TABLE wp(
FTS_DOC_ID bigint unsigned PRIMARY KEY,
title VARCHAR(255) NOT NULL DEFAULT '',
text MEDIUMTEXT NOT NULL ) ENGINE=InnoDB;
INSERT INTO wp (FTS_DOC_ID, title, text) VALUES
(1, 'MySQL Tutorial','DBMS stands for DataBase ...'),
(2, 'How To Use MySQL Well','After you went through a ...');
CREATE FULLTEXT INDEX idx ON wp(title, text);
SELECT FTS_DOC_ID, MATCH(title, text) AGAINST ('database')
FROM wp;
FTS_DOC_ID MATCH(title, text) AGAINST ('database')
1 0.0906190574169159
2 0
DROP TABLE wp;