Debian Codegraph @

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

raw
1 set names utf8;
2 call mtr.add_suppression("\\[Warning\\] InnoDB: A new Doc ID must be supplied while updating FTS indexed columns.");
3 call mtr.add_suppression("\\[Warning\\] InnoDB: FTS Doc ID must be larger than [0-9]+ for table `test`.`t1`");
4 CREATE TABLE t1 (
5 id1 INT ,
6 a1 VARCHAR(200) ,
7 b1 TEXT ,
8 FULLTEXT KEY (a1,b1), PRIMARY KEY (a1, id1)
9 ) CHARACTER SET = utf8 , ENGINE = InnoDB;
10 CREATE TABLE t2 (
11 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
12 a2 VARCHAR(200),
13 b2 TEXT ,
14 FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
15 FULLTEXT KEY (b2,a2)
16 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
17 INSERT INTO t1 (id1,a1,b1) VALUES
18 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
19 (2,'How To Use MySQL Well','After you went through a ...'),
20 (3,'Optimizing MySQL','In this tutorial we will show ...');
21 INSERT INTO t1 (id1,a1,b1) VALUES
22 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
23 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
24 (6,'MySQL Security','When configured properly, MySQL ...');
25 INSERT INTO t2 (a2,b2) VALUES
26 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
27 ('How To Use MySQL Well','After you went through a ...'),
28 ('Optimizing MySQL','In this tutorial we will show ...');
29 INSERT INTO t2 (a2,b2) VALUES
30 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
31 ('MySQL vs. YourSQL','In the following database comparison ...'),
32 ('MySQL Security','When configured properly, MySQL ...');
33 INSERT INTO t2 (a2,b2) VALUES
34 ('MySQL Tricks','1. Never run mysqld as root. 2. ...');
35 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
36 DELETE FROM t1;
37 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
38 ANALYZE TABLE t1;
39 Table Op Msg_type Msg_text
40 test.t1 analyze status Engine-independent statistics collected
41 test.t1 analyze Warning Engine-independent statistics are not collected for column 'b1'
42 test.t1 analyze status OK
43 ANALYZE TABLE t2;
44 Table Op Msg_type Msg_text
45 test.t2 analyze status Engine-independent statistics collected
46 test.t2 analyze Warning Engine-independent statistics are not collected for column 'b2'
47 test.t2 analyze status OK
48 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
49 id1
50 1
51 3
52 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
53 id2
54 1
55 3
56 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
57 id1
58 1
59 2
60 3
61 4
62 5
63 6
64 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
65 id2
66 1
67 2
68 3
69 4
70 5
71 6
72 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
73 id1
74 1
75 2
76 3
77 4
78 5
79 6
80 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
81 id2
82 1
83 2
84 3
85 4
86 5
87 6
88 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
89 id1
90 1
91 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
92 id2
93 1
94 set global innodb_optimize_fulltext_only=1;
95 optimize table t1;
96 Table Op Msg_type Msg_text
97 test.t1 optimize status OK
98 set global innodb_optimize_fulltext_only=0;
99 UPDATE t1 SET a1 = "changing column - on update cascade" , b1 = "to check foreign constraint" WHERE
100 MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
101 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
102 id1
103 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
104 id2
105 3
106 6
107 SELECT id1 FROM t1 WHERE MATCH (a1,b1) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id1;
108 id1
109 1
110 2
111 3
112 4
113 5
114 6
115 SELECT id2 FROM t2 WHERE MATCH (a2,b2) AGAINST ('+update +cascade' IN BOOLEAN MODE) ORDER BY id2;
116 id2
117 1
118 2
119 3
120 4
121 5
122 6
123 SELECT id2 FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
124 id2
125 1
126 2
127 3
128 4
129 5
130 6
131 DROP TABLE t2 , t1;
132 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
133 create table t2 (s1 int, s2 varchar(200),
134 fulltext key(s2),
135 foreign key (s1,s2) references t1 (s1,s2) on update cascade) ENGINE = InnoDB;
136 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
137 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
138 update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
139 commit;
140 select * from t2 where match(s2) against ('Lollipops');
141 s1 s2
142 DROP TABLE t2 , t1;
143 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
144 create table t2 (s1 int, s2 varchar(200),
145 fulltext key(s2),
146 foreign key (s1,s2) references t1 (s1,s2) on delete cascade) ENGINE = InnoDB;
147 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
148 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
149 delete from t1 where s2 <> 'Sunshine';
150 select * from t2 where match(s2) against ('Lollipops');
151 s1 s2
152 DROP TABLE t2 , t1;
153 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
154 create table t2 (s1 int, s2 varchar(200),
155 fulltext key(s2),
156 foreign key (s1,s2) references t1 (s1,s2) on delete set null) ENGINE = InnoDB;
157 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
158 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
159 delete from t1 where s2 <> 'Sunshine';
160 select * from t2 where match(s2) against ('Lollipops');
161 s1 s2
162 DROP TABLE t2 , t1;
163 create table t1 (s1 int, s2 varchar(200), primary key (s1,s2)) ENGINE = InnoDB;
164 create table t2 (s1 int, s2 varchar(200),
165 fulltext key(s2),
166 foreign key (s1,s2) references t1 (s1,s2) on update set null) ENGINE = InnoDB;
167 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
168 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
169 update t1 set s2 = 'Rainbows' where s2 <> 'Sunshine';
170 commit;
171 select * from t2 where match(s2) against ('Lollipops');
172 s1 s2
173 DROP TABLE t2 , t1;
174 create table t1 (s1 bigint unsigned not null, s2 varchar(200),
175 primary key (s1,s2)) ENGINE = InnoDB;
176 create table t2 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL, s2 varchar(200),
177 foreign key (FTS_DOC_ID) references t1 (s1)
178 on update cascade) ENGINE = InnoDB;
179 create fulltext index idx on t2(s2);
180 show create table t2;
181 Table Create Table
182 t2 CREATE TABLE `t2` (
183 `FTS_DOC_ID` bigint(20) unsigned NOT NULL,
184 `s2` varchar(200) DEFAULT NULL,
185 KEY `FTS_DOC_ID` (`FTS_DOC_ID`),
186 FULLTEXT KEY `idx` (`s2`),
187 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE
188 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
189 insert into t1 values (1,'Sunshine'),(2,'Lollipops');
190 insert into t2 values (1,'Sunshine'),(2,'Lollipops');
191 update t1 set s1 = 3 where s1=1;
192 select * from t2 where match(s2) against ('sunshine');
193 FTS_DOC_ID s2
194 3 Sunshine
195 update t1 set s1 = 1 where s1=3;
196 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`FTS_DOC_ID`) REFERENCES `t1` (`s1`) ON UPDATE CASCADE)
197 DROP TABLE t2 , t1;
198 CREATE TABLE t1 (
199 id1 INT ,
200 a1 VARCHAR(200) PRIMARY KEY,
201 b1 TEXT character set utf8 ,
202 FULLTEXT KEY (a1,b1)
203 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
204 CREATE TABLE t2 (
205 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
206 a2 VARCHAR(200),
207 b2 TEXT character set utf8 ,
208 FOREIGN KEY (a2) REFERENCES t1(a1) ON DELETE CASCADE,
209 FULLTEXT KEY (b2,a2)
210 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
211 INSERT INTO t1 (id1,a1,b1) VALUES
212 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
213 (2,'How To Use MySQL Well','After you went through a ...'),
214 (3,'Optimizing MySQL','In this tutorial we will show ...'),
215 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
216 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
217 (6,'MySQL Security','When configured properly, MySQL ...');
218 INSERT INTO t2 (a2,b2) VALUES
219 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
220 ('How To Use MySQL Well','After you went through a ...'),
221 ('Optimizing MySQL','In this tutorial we will show ...'),
222 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
223 ('MySQL vs. YourSQL','In the following database comparison ...'),
224 ('MySQL Security','When configured properly, MySQL ...');
225 DELETE FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
226 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
227 id1 a1 b1
228 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
229 id2 a2 b2
230 SELECT * FROM t1 WHERE a1 LIKE '%tutorial%';
231 id1 a1 b1
232 SELECT * FROM t2 WHERE a2 LIKE '%tutorial%';
233 id2 a2 b2
234 DROP TABLE t2 , t1;
235 call mtr.add_suppression("\\[ERROR\\] InnoDB: FTS Doc ID must be larger than 3 for table `test`.`t2`");
236 CREATE TABLE t1 (
237 id1 INT ,
238 a1 VARCHAR(200) ,
239 b1 TEXT ,
240 FULLTEXT KEY (a1,b1), PRIMARY KEY(a1, id1)
241 ) CHARACTER SET = utf8 , ENGINE = InnoDB;
242 CREATE TABLE t2 (
243 id2 INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
244 a2 VARCHAR(200),
245 b2 TEXT ,
246 FOREIGN KEY (a2) REFERENCES t1(a1) ON UPDATE CASCADE,
247 FULLTEXT KEY (b2,a2)
248 ) CHARACTER SET = utf8 ,ENGINE = InnoDB;
249 INSERT INTO t1 (id1,a1,b1) VALUES
250 (1,'MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
251 (2,'How To Use MySQL Well','After you went through a ...'),
252 (3,'Optimizing MySQL','In this tutorial we will show ...');
253 INSERT INTO t2 (a2,b2) VALUES
254 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
255 ('How To Use MySQL Well','After you went through a ...'),
256 ('Optimizing MySQL','In this tutorial we will show ...');
257 START TRANSACTION;
258 INSERT INTO t1 (id1,a1,b1) VALUES
259 (4,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
260 (5,'MySQL vs. YourSQL','In the following database comparison ...'),
261 (6,'MySQL Security','When configured properly, MySQL ...');
262 INSERT INTO t2 (a2,b2) VALUES
263 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
264 ('MySQL vs. YourSQL','In the following database comparison ...'),
265 ('MySQL Security','When configured properly, MySQL ...');
266 INSERT INTO t2 (a2,b2) VALUES
267 ('MySQL Tricks','1. Never run mysqld as root. 2. ...');
268 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
269 DELETE FROM t1;
270 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`a2`) REFERENCES `t1` (`a1`) ON UPDATE CASCADE)
271 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial') ORDER BY id1;
272 id1 a1 b1
273 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
274 3 Optimizing MySQL In this tutorial we will show ...
275 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial') ORDER BY id2;
276 id2 a2 b2
277 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
278 3 Optimizing MySQL In this tutorial we will show ...
279 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id1;
280 id1 a1 b1
281 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
282 2 How To Use MySQL Well After you went through a ...
283 3 Optimizing MySQL In this tutorial we will show ...
284 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ORDER BY id2;
285 id2 a2 b2
286 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
287 2 How To Use MySQL Well After you went through a ...
288 3 Optimizing MySQL In this tutorial we will show ...
289 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id1;
290 id1 a1 b1
291 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
292 2 How To Use MySQL Well After you went through a ...
293 3 Optimizing MySQL In this tutorial we will show ...
294 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial' WITH QUERY EXPANSION) ORDER BY id2;
295 id2 a2 b2
296 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
297 2 How To Use MySQL Well After you went through a ...
298 3 Optimizing MySQL In this tutorial we will show ...
299 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
300 id1 a1 b1
301 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
302 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"dbms database"@4' IN BOOLEAN MODE) ;
303 id2 a2 b2
304 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
305 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root') ;
306 id1 a1 b1
307 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root') ;
308 id2 a2 b2
309 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('mysqld (+root)' IN BOOLEAN MODE) ;
310 id1 a1 b1
311 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('mysqld (-root)' IN BOOLEAN MODE) ;
312 id2 a2 b2
313 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('root' WITH QUERY EXPANSION) ;
314 id1 a1 b1
315 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('root' WITH QUERY EXPANSION) ;
316 id2 a2 b2
317 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
318 id1 a1 b1
319 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('"database comparison"@02' IN BOOLEAN MODE) ;
320 id2 a2 b2
321 SELECT * FROM t1 ORDER BY id1;
322 id1 a1 b1
323 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
324 2 How To Use MySQL Well After you went through a ...
325 3 Optimizing MySQL In this tutorial we will show ...
326 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
327 5 MySQL vs. YourSQL In the following database comparison ...
328 6 MySQL Security When configured properly, MySQL ...
329 SELECT * FROM t2 ORDER BY id2;
330 id2 a2 b2
331 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
332 2 How To Use MySQL Well After you went through a ...
333 3 Optimizing MySQL In this tutorial we will show ...
334 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
335 5 MySQL vs. YourSQL In the following database comparison ...
336 6 MySQL Security When configured properly, MySQL ...
337 COMMIT;
338 START TRANSACTION;
339 UPDATE t1 SET a1 = "changing column - on UPDATE cascade" , b1 = "to check foreign constraint" WHERE
340 MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
341 COMMIT;
342 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
343 id1 a1 b1
344 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('tutorial (+mysql -VÐƷWİ)' IN BOOLEAN MODE) ;
345 id2 a2 b2
346 3 changing column - on UPDATE cascade In this tutorial we will show ...
347 6 changing column - on UPDATE cascade When configured properly, MySQL ...
348 SELECT * FROM t1 WHERE MATCH (a1,b1) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id1;
349 id1 a1 b1
350 1 changing column - on UPDATE cascade to check foreign constraint
351 2 changing column - on UPDATE cascade to check foreign constraint
352 3 changing column - on UPDATE cascade to check foreign constraint
353 4 changing column - on UPDATE cascade to check foreign constraint
354 5 changing column - on UPDATE cascade to check foreign constraint
355 6 changing column - on UPDATE cascade to check foreign constraint
356 SELECT * FROM t2 WHERE MATCH (a2,b2) AGAINST ('+UPDATE +cascade' IN BOOLEAN MODE) ORDER BY id2;
357 id2 a2 b2
358 1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ...
359 2 changing column - on UPDATE cascade After you went through a ...
360 3 changing column - on UPDATE cascade In this tutorial we will show ...
361 4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ...
362 5 changing column - on UPDATE cascade In the following database comparison ...
363 6 changing column - on UPDATE cascade When configured properly, MySQL ...
364 SELECT * FROM t2 WHERE a2 LIKE '%UPDATE CASCADE%' ORDER BY id2;
365 id2 a2 b2
366 1 changing column - on UPDATE cascade DBMS stands for DataBase VÐƷWİ...
367 2 changing column - on UPDATE cascade After you went through a ...
368 3 changing column - on UPDATE cascade In this tutorial we will show ...
369 4 changing column - on UPDATE cascade 1. Never run mysqld as root. 2. ...
370 5 changing column - on UPDATE cascade In the following database comparison ...
371 6 changing column - on UPDATE cascade When configured properly, MySQL ...
372 DROP TABLE t2 , t1;
373 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
374 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
375 FULLTEXT KEY(s2),
376 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
377 START TRANSACTION;
378 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
379 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
380 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
381 COMMIT;
382 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
383 s1 s2
384 DROP TABLE t2 , t1;
385 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
386 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
387 FULLTEXT KEY(s2),
388 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
389 START TRANSACTION;
390 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
391 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
392 DELETE FROM t1 WHERE s2 <> 'Sunshine';
393 COMMIT;
394 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
395 s1 s2
396 DROP TABLE t2 , t1;
397 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
398 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
399 FULLTEXT KEY(s2),
400 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
401 START TRANSACTION;
402 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
403 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
404 DELETE FROM t1 WHERE s2 <> 'Sunshine';
405 COMMIT;
406 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
407 s1 s2
408 DROP TABLE t2 , t1;
409 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
410 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
411 FULLTEXT KEY(s2),
412 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
413 START TRANSACTION;
414 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
415 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
416 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
417 COMMIT;
418 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
419 s1 s2
420 DROP TABLE t2 , t1;
421 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
422 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
423 FULLTEXT KEY(s2),
424 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE cascade) ENGINE = InnoDB;
425 START TRANSACTION;
426 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
427 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
428 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
429 ROLLBACK;
430 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
431 s1 s2
432 DROP TABLE t2 , t1;
433 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
434 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
435 FULLTEXT KEY(s2),
436 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE cascade) ENGINE = InnoDB;
437 START TRANSACTION;
438 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
439 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
440 DELETE FROM t1 WHERE s2 <> 'Sunshine';
441 ROLLBACK;
442 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
443 s1 s2
444 DROP TABLE t2 , t1;
445 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
446 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
447 FULLTEXT KEY(s2),
448 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on DELETE SET NULL) ENGINE = InnoDB;
449 START TRANSACTION;
450 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
451 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
452 DELETE FROM t1 WHERE s2 <> 'Sunshine';
453 ROLLBACK;
454 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
455 s1 s2
456 DROP TABLE t2 , t1;
457 CREATE TABLE t1 (s1 INT, s2 VARCHAR(200), PRIMARY KEY (s1,s2)) ENGINE = InnoDB;
458 CREATE TABLE t2 (s1 INT, s2 VARCHAR(200),
459 FULLTEXT KEY(s2),
460 FOREIGN KEY (s1,s2) REFERENCES t1 (s1,s2) on UPDATE SET NULL) ENGINE = InnoDB;
461 START TRANSACTION;
462 INSERT INTO t1 VALUES (1,'Sunshine'),(2,'Lollipops');
463 INSERT INTO t2 VALUES (1,'Sunshine'),(2,'Lollipops');
464 UPDATE t1 set s2 = 'Rainbows' WHERE s2 <> 'Sunshine';
465 ROLLBACK;
466 SELECT * FROM t2 WHERE MATCH(s2) AGAINST ('Lollipops');
467 s1 s2
468 DROP TABLE t2 , t1;
469 SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
470 SET GLOBAL innodb_read_only_compressed=OFF;
471 CREATE TABLE t1 (
472 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
473 a VARCHAR(200),
474 b TEXT
475 ) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB;
476 INSERT INTO t1 (a,b) VALUES
477 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
478 ('How To Use MySQL Well','After you went through a ...'),
479 ('Optimizing MySQL','In this tutorial we will show ...');
480 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
481 SHOW CREATE TABLE t1;
482 Table Create Table
483 t1 CREATE TABLE `t1` (
484 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
485 `a` varchar(200) DEFAULT NULL,
486 `b` text DEFAULT NULL,
487 PRIMARY KEY (`id`),
488 FULLTEXT KEY `idx` (`a`,`b`)
489 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci ROW_FORMAT=COMPRESSED
490 SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0;
491 count(*)
492 11
493 INSERT INTO t1 (a,b) VALUES
494 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
495 ('MySQL vs. YourSQL','In the following database comparison ...'),
496 ('MySQL Security','When configured properly, MySQL ...');
497 ANALYZE TABLE t1;
498 SELECT * FROM t1 WHERE MATCH (a,b)
499 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
500 id a b
501 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
502 3 Optimizing MySQL In this tutorial we will show ...
503 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
504 id a b
505 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
506 select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE);
507 ERROR 42000: syntax error, unexpected '-'
508 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
509 id a b
510 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
511 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
512 id a b
513 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
514 2 How To Use MySQL Well After you went through a ...
515 3 Optimizing MySQL In this tutorial we will show ...
516 5 MySQL vs. YourSQL In the following database comparison ...
517 6 MySQL Security When configured properly, MySQL ...
518 select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id;
519 id a b x
520 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143
521 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
522 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
523 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
524 5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186
525 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
526 select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE);
527 id a b
528 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
529 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
530 id a b
531 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
532 id a b
533 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
534 2 How To Use MySQL Well After you went through a ...
535 3 Optimizing MySQL In this tutorial we will show ...
536 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
537 5 MySQL vs. YourSQL In the following database comparison ...
538 6 MySQL Security When configured properly, MySQL ...
539 ALTER TABLE t1 DROP INDEX idx;
540 CREATE FULLTEXT INDEX idx on t1 (a,b);
541 SELECT * FROM t1 WHERE MATCH (a,b)
542 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
543 id a b
544 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
545 3 Optimizing MySQL In this tutorial we will show ...
546 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
547 id a b
548 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
549 select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE);
550 id a b
551 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
552 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
553 id a b
554 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
555 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
556 id a b
557 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
558 2 How To Use MySQL Well After you went through a ...
559 3 Optimizing MySQL In this tutorial we will show ...
560 5 MySQL vs. YourSQL In the following database comparison ...
561 6 MySQL Security When configured properly, MySQL ...
562 select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id;
563 id a b x
564 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143
565 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
566 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
567 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
568 5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186
569 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
570 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
571 id a b
572 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
573 id a b
574 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
575 2 How To Use MySQL Well After you went through a ...
576 3 Optimizing MySQL In this tutorial we will show ...
577 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
578 5 MySQL vs. YourSQL In the following database comparison ...
579 6 MySQL Security When configured properly, MySQL ...
580 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
581 INSERT INTO t1 (a,b) VALUES
582 ('test proximity search, test, proximity and phrase',
583 'search, with proximity innodb');
584 INSERT INTO t1 (a,b) VALUES
585 ('test proximity fts search, test, proximity and phrase',
586 'search, with proximity innodb');
587 INSERT INTO t1 (a,b) VALUES
588 ('test more proximity fts search, test, more proximity and phrase',
589 'search, with proximity innodb');
590 SELECT * FROM t1
591 WHERE MATCH (a,b)
592 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
593 id a b
594 8 test proximity search, test, proximity and phrase search, with proximity innodb
595 SELECT * FROM t1
596 WHERE MATCH (a,b)
597 AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
598 id a b
599 SELECT * FROM t1
600 WHERE MATCH (a,b)
601 AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id;
602 id a b
603 8 test proximity search, test, proximity and phrase search, with proximity innodb
604 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
605 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
606 SELECT * FROM t1
607 WHERE MATCH (a,b)
608 AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id;
609 id a b
610 8 test proximity search, test, proximity and phrase search, with proximity innodb
611 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
612 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
613 SELECT * FROM t1
614 WHERE MATCH (a,b)
615 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
616 id a b
617 SELECT * FROM t1
618 WHERE MATCH (a,b)
619 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
620 id a b
621 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
622 SELECT * FROM t1
623 WHERE MATCH (a,b)
624 AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE);
625 id a b
626 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
627 UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ;
628 UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ;
629 select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE);
630 id a b
631 1 MYSQL TUTORIAL dbms stands for database vðʒwi...
632 select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE);
633 id a b
634 1 MYSQL TUTORIAL dbms stands for database vðʒwi...
635 SELECT * FROM t1 WHERE MATCH (a,b)
636 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
637 id a b
638 1 MYSQL TUTORIAL dbms stands for database vðʒwi...
639 3 OPTIMIZING MYSQL in this tutorial we will show ...
640 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
641 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE);
642 SELECT * FROM t1 WHERE MATCH (a,b)
643 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
644 id a b
645 SELECT * FROM t1 ORDER BY id;
646 id a b
647 2 HOW TO USE MYSQL WELL after you went through a ...
648 4 1001 MYSQL TRICKS 1. never run mysqld as root. 2. ...
649 5 MYSQL VS. YOURSQL in the following database comparison ...
650 6 MYSQL SECURITY when configured properly, mysql ...
651 7 TEST QUERY EXPANSION for database ...
652 DROP TABLE t1;
653 SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
654 CREATE TABLE t1 (
655 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
656 a VARCHAR(200),
657 b TEXT
658 ) CHARACTER SET = utf8, ENGINE=InnoDB;
659 INSERT INTO t1 (a,b) VALUES
660 ('Я могу есть стекло', 'оно мне не вредит'),
661 ('Мога да ям стъкло', 'то не ми вреди'),
662 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
663 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
664 ('Sævör grét', 'áðan því úlpan var ónýt'),
665 ('うゐのおくやま','けふこえて'),
666 ('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
667 INSERT INTO t1 (a,b) VALUES
668 ('MySQL Tutorial','request [email protected] ...') ,
669 ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
670 ('when To Use MySQL Well','for free faq [email protected] ...');
671 CREATE FULLTEXT INDEX idx on t1 (a,b);
672 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id;
673 id a b
674 1 Я могу есть стекло оно мне не вредит
675 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
676 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION);
677 id a b
678 1 Я могу есть стекло оно мне не вредит
679 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id;
680 id a b
681 1 Я могу есть стекло оно мне не вредит
682 2 Мога да ям стъкло то не ми вреди
683 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE);
684 id a b
685 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE);
686 id a b
687 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
688 id a b
689 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
690 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION);
691 id a b
692 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
693 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE);
694 id a b
695 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
696 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE);
697 id a b
698 6 うゐのおくやま けふこえて
699 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
700 id a b
701 5 Sævör grét áðan því úlpan var ónýt
702 SELECT * FROM t1
703 WHERE MATCH (a,b)
704 AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE);
705 id a b
706 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
707 SELECT * FROM t1
708 WHERE MATCH (a,b)
709 AGAINST ('"query performace"@02' IN BOOLEAN MODE);
710 id a b
711 9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
712 SELECT * FROM t1
713 WHERE MATCH (a,b)
714 AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE);
715 id a b
716 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
717 SELECT * FROM t1
718 WHERE MATCH (a,b)
719 AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE);
720 id a b
721 SELECT * FROM t1
722 WHERE MATCH (a,b)
723 AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE);
724 id a b
725 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
726 ALTER TABLE t1 DROP INDEX idx;
727 CREATE FULLTEXT INDEX idx on t1 (a,b);
728 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
729 id a b
730 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
731 UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
732 UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE);
733 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
734 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
735 id a b
736 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem");
737 id a b
738 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
739 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE);
740 id a b
741 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id;
742 id a b
743 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
744 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
745 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
746 id a b
747 SELECT * FROM t1
748 WHERE MATCH (a,b)
749 AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE);
750 id a b
751 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
752 SELECT * FROM t1 ORDER BY id;
753 id a b
754 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
755 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
756 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
757 4 Příliš žluťoučký kůň úpěl ďábelské kódy
758 6 うゐのおくやま けふこえて
759 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
760 8 MySQL Tutorial request [email protected] ...
761 9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
762 10 when To Use MySQL Well for free faq [email protected] ...
763 DROP TABLE t1;
764 CREATE TABLE t1(ID INT PRIMARY KEY,
765 no_fts_field VARCHAR(10),
766 fts_field VARCHAR(10),
767 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
768 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
769 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
770 ID no_fts_field fts_field
771 1 AAA BBB
772 UPDATE t1 SET fts_field='anychange' where id = 1;
773 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
774 ID no_fts_field fts_field
775 1 AAA anychange
776 UPDATE t1 SET no_fts_field='anychange' where id = 1;
777 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
778 ID no_fts_field fts_field
779 1 anychange anychange
780 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
781 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
782 ID no_fts_field fts_field
783 1 anychange other
784 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
785 ID no_fts_field fts_field
786 DROP INDEX f on t1;
787 UPDATE t1 SET fts_field='anychange' where id = 1;
788 UPDATE t1 SET no_fts_field='anychange' where id = 1;
789 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
790 CREATE FULLTEXT INDEX f ON t1(FTS_FIELD);
791 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
792 ID no_fts_field fts_field
793 1 anychange other
794 DROP TABLE t1;
795 CREATE TABLE t1(`FTS_DOC_ID` serial,
796 no_fts_field VARCHAR(10),
797 fts_field VARCHAR(10),
798 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
799 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
800 UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1;
801 ERROR HY000: Invalid InnoDB FTS Doc ID
802 UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1;
803 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
804 FTS_DOC_ID no_fts_field fts_field
805 2 AAA anychange
806 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
807 FTS_DOC_ID no_fts_field fts_field
808 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
809 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
810 FTS_DOC_ID no_fts_field fts_field
811 2 anychange anychange
812 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2;
813 ERROR HY000: Invalid InnoDB FTS Doc ID
814 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
815 FTS_DOC_ID no_fts_field fts_field
816 UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2;
817 ERROR HY000: Invalid InnoDB FTS Doc ID
818 DROP INDEX f ON t1;
819 UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2;
820 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
821 SELECT * FROM t1;
822 FTS_DOC_ID no_fts_field fts_field
823 2 anychange newchange
824 DROP TABLE t1;
825 CREATE TABLE t1(ID INT PRIMARY KEY,
826 no_fts_field VARCHAR(10),
827 fts_field VARCHAR(10),
828 FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB;
829 CREATE TABLE t2(ID INT PRIMARY KEY,
830 no_fts_field VARCHAR(10),
831 fts_field VARCHAR(10),
832 FULLTEXT INDEX f(fts_field),
833 INDEX k2(fts_field),
834 FOREIGN KEY(fts_field) REFERENCES
835 t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB;
836 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
837 INSERT INTO t2 VALUES (1, 'AAA', 'BBB');
838 update t1 set fts_field='newchange' where id =1;
839 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
840 ID no_fts_field fts_field
841 SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB");
842 ID no_fts_field fts_field
843 SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange");
844 ID no_fts_field fts_field
845 1 AAA newchange
846 SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange");
847 ID no_fts_field fts_field
848 1 AAA newchange
849 DROP TABLE t2;
850 DROP TABLE t1;
851 CREATE TABLE t1(id INT PRIMARY KEY,
852 fts_field VARCHAR(10),
853 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
854 CREATE TABLE t2(id INT PRIMARY KEY,
855 fts_field VARCHAR(10),
856 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
857 INSERT INTO t1 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800'),(9,'900'),(10,'1000'),(11,'1100'),(12,'1200');
858 INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800');
859 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo');
860 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo";
861 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'xoo'), t2.fts_field = CONCAT(t1.fts_field, 'xoo') where t1.fts_field=CONCAT(t2.fts_field, 'foo');
862 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo");
863 id fts_field
864 1 100foofoo
865 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo");
866 id fts_field
867 SELECT * FROM t1 WHERE MATCH(fts_field) against("100");
868 id fts_field
869 SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo");
870 id fts_field
871 4 400fooxoo
872 SELECT * FROM t2 WHERE MATCH(fts_field) against("100");
873 id fts_field
874 1 100
875 SELECT * FROM t2 WHERE MATCH(fts_field) against("200");
876 id fts_field
877 SELECT * FROM t2 WHERE MATCH(fts_field) against("400");
878 id fts_field
879 DROP TABLE t1;
880 DROP TABLE t2;
881
882 BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY
883
884 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
885 CREATE TABLE t1 (
886 t1_id INT(10) UNSIGNED NOT NULL,
887 t2_id INT(10) UNSIGNED DEFAULT NULL,
888 PRIMARY KEY (t1_id),
889 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
890 ON DELETE CASCADE ON UPDATE CASCADE
891 ) ENGINE=InnoDB;
892 CREATE TABLE t2 (
893 t1_id INT(10) UNSIGNED NOT NULL,
894 t2_id INT(10) UNSIGNED NOT NULL,
895 t3_id INT(10) UNSIGNED NOT NULL,
896 t4_id INT(10) UNSIGNED NOT NULL,
897 PRIMARY KEY (t2_id),
898 FOREIGN KEY (t1_id) REFERENCES t1 (t1_id),
899 FOREIGN KEY (t3_id) REFERENCES t3 (t3_id)
900 ON DELETE CASCADE ON UPDATE CASCADE,
901 FOREIGN KEY (t4_id) REFERENCES t4 (t4_id)
902 ) ENGINE=InnoDB;
903 CREATE TABLE t3 (
904 t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
905 payload char(3),
906 PRIMARY KEY (t3_id)
907 ) ENGINE=InnoDB;
908 INSERT INTO t3 VALUES (1, '100');
909 CREATE TABLE t4 (
910 t2_id INT(10) UNSIGNED DEFAULT NULL,
911 t4_id INT(10) UNSIGNED NOT NULL,
912 PRIMARY KEY (t4_id),
913 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
914 ON DELETE CASCADE ON UPDATE CASCADE
915 ) ENGINE=InnoDB;
916 SET FOREIGN_KEY_CHECKS=1;
917 UPDATE t3 SET payload='101' WHERE t3_id=1;
918 SET FOREIGN_KEY_CHECKS=0;
919 DROP TABLE t1;
920 DROP TABLE t2;
921 DROP TABLE t3;
922 DROP TABLE t4;
923 #
924 # InnoDB: Failing assertion: result != FTS_INVALID in
925 # fts_trx_row_get_new_state
926 #
927 SET FOREIGN_KEY_CHECKS=1;
928 CREATE TABLE t1 (pk INT PRIMARY KEY,
929 f1 VARCHAR(10), f2 VARCHAR(10),
930 f3 VARCHAR(10), f4 VARCHAR(10),
931 f5 VARCHAR(10), f6 VARCHAR(10),
932 f7 VARCHAR(10), f8 VARCHAR(10),
933 FULLTEXT(f1), FULLTEXT(f2), FULLTEXT(f3), FULLTEXT(f4),
934 FULLTEXT(f5), FULLTEXT(f6), FULLTEXT(f7), FULLTEXT(f8),
935 INDEX(f1), INDEX(f2), INDEX(f3), INDEX(f4),
936 INDEX(f5), INDEX(f6), INDEX(f7), INDEX(f8)) ENGINE=InnoDB;
937 INSERT INTO t1 VALUES (1, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
938 'mariadb', 'mariadb', 'mariadb', 'mariadb'),
939 (2, 'mariadb', 'mariadb', 'mariadb', 'mariadb',
940 'mariadb', 'mariadb', 'mariadb', 'mariadb'),
941 (3, 'innodb', 'innodb', 'innodb', 'innodb',
942 'innodb', 'innodb', 'innodb', 'innodb');
943 ALTER TABLE t1 ADD FOREIGN KEY (f1) REFERENCES t1 (f2) ON DELETE SET NULL;
944 START TRANSACTION;
945 DELETE FROM t1 where f1='mariadb';
946 SELECT * FROM t1;
947 pk f1 f2 f3 f4 f5 f6 f7 f8
948 2 NULL mariadb mariadb mariadb mariadb mariadb mariadb mariadb
949 3 innodb innodb innodb innodb innodb innodb innodb innodb
950 ROLLBACK;
951 ALTER TABLE t1 ADD FOREIGN KEY (f3) REFERENCES t1 (f4) ON DELETE CASCADE;
952 START TRANSACTION;
953 DELETE FROM t1 where f3='mariadb';
954 SELECT * FROM t1;
955 pk f1 f2 f3 f4 f5 f6 f7 f8
956 3 innodb innodb innodb innodb innodb innodb innodb innodb
957 ROLLBACK;
958 ALTER TABLE t1 ADD FOREIGN KEY (f5) REFERENCES t1 (f6) ON UPDATE SET NULL;
959 UPDATE t1 SET f6='update';
960 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
961 ALTER TABLE t1 ADD FOREIGN KEY (f7) REFERENCES t1 (f8) ON UPDATE CASCADE;
962 UPDATE t1 SET f6='cascade';
963 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_3` FOREIGN KEY (`f5`) REFERENCES `t1` (`f6`) ON UPDATE SET NULL)
964 DROP TABLE t1;
965 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
966 #
967 # MDEV-25536 sym_node->table != NULL in pars_retrieve_table_def
968 #
969 CREATE TABLE t1 (f1 TEXT,FULLTEXT (f1)) ENGINE=InnoDB;
970 ALTER TABLE t1 DISCARD TABLESPACE;
971 SET GLOBAL innodb_ft_aux_table='test/t1';
972 SELECT * FROM information_schema.innodb_ft_deleted;
973 DOC_ID
974 DROP TABLE t1;
975 SET GLOBAL innodb_ft_aux_table=DEFAULT;
976 #
977 # MDEV-19522 InnoDB commit fails when FTS_DOC_ID value
978 # is greater than 4294967295
979 #
980 CREATE TABLE t1(
981 FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
982 f1 TEXT, f2 TEXT, PRIMARY KEY (FTS_DOC_ID),
983 FULLTEXT KEY (f1)) ENGINE=InnoDB;
984 INSERT INTO t1 VALUES (1,'txt','bbb');
985 UPDATE t1 SET FTS_DOC_ID = 4294967298;
986 SELECT * FROM t1 WHERE match(f1) against("txt");
987 FTS_DOC_ID f1 f2
988 4294967298 txt bbb
989 SET @@session.insert_id = 100000000000;
990 INSERT INTO t1(f1, f2) VALUES ('aaa', 'bbb');
991 CREATE FULLTEXT INDEX i ON t1 (f2);
992 SELECT * FROM t1 WHERE match(f2) against("bbb");
993 FTS_DOC_ID f1 f2
994 4294967298 txt bbb
995 100000000000 aaa bbb
996 DROP TABLE t1;
997 #
998 # MDEV-30363 Failing assertion: trx->error_state == DB_SUCCESS
999 # in que_run_threads
1000 #
1001 CREATE TABLE server_stopword (value VARCHAR(1))engine=innodb;
1002 SET GLOBAL innodb_ft_server_stopword_table='test/server_stopword';
1003 CREATE TABLE t (t VARCHAR(1) COLLATE utf8_unicode_ci,
1004 FULLTEXT (t))engine=innodb;
1005 TRUNCATE TABLE t;
1006 DROP TABLE t;
1007 DROP TABLE server_stopword;
1008 SET GLOBAL innodb_ft_server_stopword_table= default;