Debian Codegraph @

mysql-test/suite/innodb_fts/r/innodb_fts_stopword_charset.result

raw
1 SELECT @@innodb_ft_server_stopword_table;
2 @@innodb_ft_server_stopword_table
3 NULL
4 SELECT @@innodb_ft_enable_stopword;
5 @@innodb_ft_enable_stopword
6 1
7 SELECT @@innodb_ft_user_stopword_table;
8 @@innodb_ft_user_stopword_table
9 NULL
10 SET NAMES utf8;
11 # Test 1 : default latin1_swedish_ci
12 CREATE TABLE articles (
13 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
14 title VARCHAR(200)
15 ) ENGINE=InnoDB CHARSET=latin1;
16 INSERT IGNORE INTO articles (title) VALUES
17 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
18 ('lṓve'),('LṒVE');
19 CREATE FULLTEXT INDEX ft_idx ON articles(title);
20 SELECT * FROM articles WHERE MATCH (title)
21 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
22 id title
23 1 love
24 2 LOVE
25 3 lòve
26 4 LÒVE
27 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB CHARSET=latin1;
28 INSERT INTO user_stopword VALUES('lòve');
29 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
30 DROP INDEX ft_idx ON articles;
31 CREATE FULLTEXT INDEX ft_idx ON articles(title);
32 SELECT * FROM articles WHERE MATCH (title)
33 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
34 id title
35 SELECT * FROM articles WHERE MATCH (title)
36 AGAINST ('love' IN NATURAL LANGUAGE MODE);
37 id title
38 DROP TABLE articles;
39 DROP TABLE user_stopword;
40 # Test 2 : latin1_general_ci
41 CREATE TABLE articles (
42 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
43 title VARCHAR(200)
44 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
45 INSERT IGNORE INTO articles (title) VALUES
46 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
47 ('lṓve'),('LṒVE');
48 CREATE FULLTEXT INDEX ft_idx ON articles(title);
49 SELECT * FROM articles WHERE MATCH (title)
50 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
51 id title
52 3 lòve
53 4 LÒVE
54 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
55 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci;
56 INSERT INTO user_stopword VALUES('lòve');
57 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
58 DROP INDEX ft_idx ON articles;
59 CREATE FULLTEXT INDEX ft_idx ON articles(title);
60 SELECT * FROM articles WHERE MATCH (title)
61 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
62 id title
63 SELECT * FROM articles WHERE MATCH (title)
64 AGAINST ('love' IN NATURAL LANGUAGE MODE);
65 id title
66 1 love
67 2 LOVE
68 DROP TABLE articles;
69 DROP TABLE user_stopword;
70 # Test 3 : latin1_spanish_ci
71 CREATE TABLE articles (
72 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
73 title VARCHAR(200)
74 ) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
75 INSERT IGNORE INTO articles (title) VALUES
76 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
77 ('lṓve'),('LṒVE');
78 CREATE FULLTEXT INDEX ft_idx ON articles(title);
79 SELECT * FROM articles WHERE MATCH (title)
80 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
81 id title
82 1 love
83 2 LOVE
84 3 lòve
85 4 LÒVE
86 5 löve
87 6 LÖVE
88 7 løve
89 8 LØVE
90 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
91 DEFAULT CHARACTER SET latin1 COLLATE latin1_spanish_ci;
92 INSERT INTO user_stopword VALUES('lòve');
93 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
94 DROP INDEX ft_idx ON articles;
95 CREATE FULLTEXT INDEX ft_idx ON articles(title);
96 SELECT * FROM articles WHERE MATCH (title)
97 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
98 id title
99 SELECT * FROM articles WHERE MATCH (title)
100 AGAINST ('love' IN NATURAL LANGUAGE MODE);
101 id title
102 DROP TABLE articles;
103 DROP TABLE user_stopword;
104 # Test 4 : utf8_general_ci
105 CREATE TABLE articles (
106 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
107 title VARCHAR(200)
108 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
109 INSERT INTO articles (title) VALUES
110 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
111 ('lṓve'),('LṒVE');
112 CREATE FULLTEXT INDEX ft_idx ON articles(title);
113 SELECT * FROM articles WHERE MATCH (title)
114 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
115 id title
116 1 love
117 2 LOVE
118 3 lòve
119 4 LÒVE
120 5 löve
121 6 LÖVE
122 9 lṓve
123 10 LṒVE
124 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
125 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
126 INSERT INTO user_stopword VALUES('lòve');
127 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
128 DROP INDEX ft_idx ON articles;
129 CREATE FULLTEXT INDEX ft_idx ON articles(title);
130 SELECT * FROM articles WHERE MATCH (title)
131 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
132 id title
133 SELECT * FROM articles WHERE MATCH (title)
134 AGAINST ('love' IN NATURAL LANGUAGE MODE);
135 id title
136 DROP TABLE articles;
137 DROP TABLE user_stopword;
138 # Test 5 : utf8_unicode_ci
139 CREATE TABLE articles (
140 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
141 title VARCHAR(200)
142 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
143 INSERT INTO articles (title) VALUES
144 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
145 ('lṓve'),('LṒVE');
146 CREATE FULLTEXT INDEX ft_idx ON articles(title);
147 SELECT * FROM articles WHERE MATCH (title)
148 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
149 id title
150 1 love
151 2 LOVE
152 3 lòve
153 4 LÒVE
154 9 lṓve
155 10 LṒVE
156 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
157 DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci;
158 INSERT INTO user_stopword VALUES('lòve');
159 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
160 DROP INDEX ft_idx ON articles;
161 CREATE FULLTEXT INDEX ft_idx ON articles(title);
162 SELECT * FROM articles WHERE MATCH (title)
163 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
164 id title
165 SELECT * FROM articles WHERE MATCH (title)
166 AGAINST ('love' IN NATURAL LANGUAGE MODE);
167 id title
168 DROP TABLE articles;
169 DROP TABLE user_stopword;
170 # Test 6 : utf8_unicode_ci
171 CREATE TABLE articles (
172 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
173 title VARCHAR(200)
174 ) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
175 INSERT INTO articles (title) VALUES
176 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
177 ('lṓve'),('LṒVE');
178 CREATE FULLTEXT INDEX ft_idx ON articles(title);
179 SELECT * FROM articles WHERE MATCH (title)
180 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
181 id title
182 1 love
183 2 LOVE
184 3 lòve
185 4 LÒVE
186 5 löve
187 6 LÖVE
188 9 lṓve
189 10 LṒVE
190 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
191 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
192 INSERT INTO user_stopword VALUES('lòve');
193 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
194 DROP INDEX ft_idx ON articles;
195 CREATE FULLTEXT INDEX ft_idx ON articles(title);
196 SELECT * FROM articles WHERE MATCH (title)
197 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
198 id title
199 SELECT * FROM articles WHERE MATCH (title)
200 AGAINST ('love' IN NATURAL LANGUAGE MODE);
201 id title
202 DROP TABLE articles;
203 DROP TABLE user_stopword;
204 # Test 7 : gb2312_chinese_ci
205 CREATE TABLE articles (
206 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
207 title VARCHAR(200)
208 ) ENGINE=InnoDB DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
209 INSERT INTO articles (title) VALUES
210 ('相亲相爱'),('怜香惜爱'),('充满可爱'),('爱恨交织');
211 CREATE FULLTEXT INDEX ft_idx ON articles(title);
212 SELECT * FROM articles WHERE MATCH (title)
213 AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE);
214 id title
215 1 相亲相爱
216 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB
217 DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;
218 INSERT INTO user_stopword VALUES('相亲相爱');
219 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
220 DROP INDEX ft_idx ON articles;
221 CREATE FULLTEXT INDEX ft_idx ON articles(title);
222 SELECT * FROM articles WHERE MATCH (title)
223 AGAINST ('相亲相爱' IN NATURAL LANGUAGE MODE);
224 id title
225 SELECT * FROM articles WHERE MATCH (title)
226 AGAINST ('怜香惜爱' IN NATURAL LANGUAGE MODE);
227 id title
228 2 怜香惜爱
229 DROP TABLE articles;
230 DROP TABLE user_stopword;
231 # Test 8 : test shutdown to check if stopword still works
232 CREATE TABLE articles (
233 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
234 title VARCHAR(200)
235 ) ENGINE=InnoDB CHARSET=latin1;
236 INSERT IGNORE INTO articles (title) VALUES
237 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
238 ('lṓve'),('LṒVE');
239 CREATE FULLTEXT INDEX ft_idx ON articles(title);
240 SELECT * FROM articles WHERE MATCH (title)
241 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
242 id title
243 1 love
244 2 LOVE
245 3 lòve
246 4 LÒVE
247 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB CHARSET=latin1;
248 INSERT INTO user_stopword VALUES('lòve');
249 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
250 DROP INDEX ft_idx ON articles;
251 CREATE FULLTEXT INDEX ft_idx ON articles(title);
252 SELECT * FROM articles WHERE MATCH (title)
253 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
254 id title
255 SELECT * FROM articles WHERE MATCH (title)
256 AGAINST ('love' IN NATURAL LANGUAGE MODE);
257 id title
258 # Shutdown and restart mysqld
259 # restart
260 SET NAMES utf8;
261 INSERT IGNORE INTO articles (title) VALUES
262 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
263 ('lṓve'),('LṒVE');
264 SELECT * FROM articles WHERE MATCH (title)
265 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
266 id title
267 SELECT * FROM articles WHERE MATCH (title)
268 AGAINST ('love' IN NATURAL LANGUAGE MODE);
269 id title
270 DROP TABLE articles;
271 DROP TABLE user_stopword;
272 # Test 9 : drop user stopwrod table,test shutdown to check if it works
273 CREATE TABLE articles (
274 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
275 title VARCHAR(200)
276 ) ENGINE=InnoDB CHARSET=latin1;
277 INSERT IGNORE INTO articles (title) VALUES
278 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
279 ('lṓve'),('LṒVE');
280 CREATE FULLTEXT INDEX ft_idx ON articles(title);
281 SELECT * FROM articles WHERE MATCH (title)
282 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
283 id title
284 1 love
285 2 LOVE
286 3 lòve
287 4 LÒVE
288 CREATE TABLE user_stopword(value varchar(30)) ENGINE = InnoDB CHARSET=latin1;
289 INSERT INTO user_stopword VALUES('lòve');
290 SET GLOBAL innodb_ft_server_stopword_table = 'test/user_stopword';
291 DROP INDEX ft_idx ON articles;
292 CREATE FULLTEXT INDEX ft_idx ON articles(title);
293 SELECT * FROM articles WHERE MATCH (title)
294 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
295 id title
296 SELECT * FROM articles WHERE MATCH (title)
297 AGAINST ('love' IN NATURAL LANGUAGE MODE);
298 id title
299 DROP TABLE user_stopword;
300 # Shutdown and restart mysqld
301 # restart
302 SET NAMES utf8;
303 INSERT IGNORE INTO articles (title) VALUES
304 ('love'),('LOVE'),('lòve'),('LÒVE'),('löve'),('LÖVE'),('løve'),('LØVE'),
305 ('lṓve'),('LṒVE');
306 SELECT * FROM articles WHERE MATCH (title)
307 AGAINST ('lòve' IN NATURAL LANGUAGE MODE);
308 id title
309 11 love
310 12 LOVE
311 13 lòve
312 14 LÒVE
313 SELECT * FROM articles WHERE MATCH (title)
314 AGAINST ('love' IN NATURAL LANGUAGE MODE);
315 id title
316 11 love
317 12 LOVE
318 13 lòve
319 14 LÒVE
320 DROP TABLE articles;