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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
|
# This is the basic function tests for innodb FTS
-- source include/have_innodb.inc
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 FTS table
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
# Insert six rows
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 the FTS index
CREATE FULLTEXT INDEX idx on articles (title, body);
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
SELECT COUNT(*) FROM articles
WHERE MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles
WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
SELECT COUNT(IF(MATCH (title, body)
AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
AS count FROM articles;
ANALYZE TABLE articles;
# Boolean search
# Select rows contain "MySQL" but not "YourSQL"
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
# Select rows contain at least one of the two words
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('DBMS Security' IN BOOLEAN MODE);
# Select rows contain both "MySQL" and "YourSQL"
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL +YourSQL' IN BOOLEAN MODE);
DROP INDEX idx ON articles;
# Create the FTS index
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);
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);
drop table articles;
# Create FTS table
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 six rows
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 the FTS index
CREATE FULLTEXT INDEX idx on articles (title, body);
# "the" is in the default stopword, it would not be selected
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
drop table articles;
# Create FTS table
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
) ENGINE=InnoDB;
# Insert six rows
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);
# "the" is in the default stopword, it would not be selected
--error 1191
SELECT * FROM articles WHERE MATCH (title, body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
drop index idx2 on articles;
--error 1191
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
CREATE FULLTEXT INDEX idx2 on articles (body);
SELECT * FROM articles WHERE MATCH (body)
AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
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);
SELECT * FROM articles WHERE MATCH (title)
AGAINST ('aaaa' IN NATURAL LANGUAGE MODE);
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 ...');
# This should fail since we did not supply a new Doc ID
-- error 182
UPDATE articles set title = 'bbbb' WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
# This should fail, since the Doc ID supplied is less than the old value 9
-- error 182
UPDATE articles set title = 'bbbb', FTS_DOC_ID=8 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
# This should be successful
UPDATE articles set title = 'bbbb', FTS_DOC_ID=10 WHERE MATCH(title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
# Check update to be successful
SELECT * FROM articles WHERE MATCH (title) AGAINST ('bbbb' IN NATURAL LANGUAGE MODE);
SELECT * FROM articles WHERE MATCH (title) AGAINST ('tutorial' IN NATURAL LANGUAGE MODE);
CREATE FULLTEXT INDEX idx2 ON articles (body);
SELECT * FROM articles WHERE MATCH (body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
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;
# This create index should fail. FTS_DOC_ID_INDEX is reserved as a unique
# index on FTS_DOC_ID
--error ER_INNODB_FT_WRONG_DOCID_INDEX
create fulltext index `FTS_DOC_ID_INDEX` on `articles`(`a`);
create unique index `a` on `articles`(`a`);
drop table articles;
# We will check validity of FTS_DOC_ID, which must be of an UNSIGNED
# NOT NULL bigint
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 ...');
--error ER_INNODB_FT_WRONG_DOCID_COLUMN
CREATE FULLTEXT INDEX idx ON wp(title, text);
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;
DROP TABLE wp;
|
