1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
|
call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns.");
call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`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, body);
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 Engine-independent statistics collected
test.articles analyze Warning Engine-independent statistics are not collected for column 'body'
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);
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;
|
