Debian Codegraph @

mysql-test/suite/innodb_fts/r/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 CREATE TABLE t1 (
470 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
471 a VARCHAR(200),
472 b TEXT
473 ) CHARACTER SET = utf8, ROW_FORMAT=COMPRESSED, ENGINE = InnoDB;
474 INSERT INTO t1 (a,b) VALUES
475 ('MySQL Tutorial','DBMS stands for DataBase VÐƷWİ...') ,
476 ('How To Use MySQL Well','After you went through a ...'),
477 ('Optimizing MySQL','In this tutorial we will show ...');
478 ALTER TABLE t1 ADD FULLTEXT INDEX idx (a,b);
479 SHOW CREATE TABLE t1;
480 Table Create Table
481 t1 CREATE TABLE `t1` (
482 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
483 `a` varchar(200) DEFAULT NULL,
484 `b` text DEFAULT NULL,
485 PRIMARY KEY (`id`),
486 FULLTEXT KEY `idx` (`a`,`b`)
487 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci ROW_FORMAT=COMPRESSED
488 SELECT count(*) FROM information_schema.innodb_sys_tables WHERE name LIKE "%FTS_%" AND space !=0;
489 count(*)
490 11
491 INSERT INTO t1 (a,b) VALUES
492 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
493 ('MySQL vs. YourSQL','In the following database comparison ...'),
494 ('MySQL Security','When configured properly, MySQL ...');
495 ANALYZE TABLE t1;
496 SELECT * FROM t1 WHERE MATCH (a,b)
497 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
498 id a b
499 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
500 3 Optimizing MySQL In this tutorial we will show ...
501 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
502 id a b
503 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
504 select * from t1 where MATCH(a,b) AGAINST("+-VÐƷWİ" IN BOOLEAN MODE);
505 ERROR 42000: syntax error, unexpected '-'
506 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
507 id a b
508 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
509 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
510 id a b
511 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
512 2 How To Use MySQL Well After you went through a ...
513 3 Optimizing MySQL In this tutorial we will show ...
514 5 MySQL vs. YourSQL In the following database comparison ...
515 6 MySQL Security When configured properly, MySQL ...
516 select *, MATCH(a,b) AGAINST("mysql stands" IN BOOLEAN MODE) as x from t1 ORDER BY id;
517 id a b x
518 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143
519 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
520 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
521 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
522 5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186
523 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
524 select * from t1 where MATCH a,b AGAINST ("+database* +VÐƷW*" IN BOOLEAN MODE);
525 id a b
526 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
527 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
528 id a b
529 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
530 id a b
531 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
532 2 How To Use MySQL Well After you went through a ...
533 3 Optimizing MySQL In this tutorial we will show ...
534 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
535 5 MySQL vs. YourSQL In the following database comparison ...
536 6 MySQL Security When configured properly, MySQL ...
537 ALTER TABLE t1 DROP INDEX idx;
538 CREATE FULLTEXT INDEX idx on t1 (a,b);
539 SELECT * FROM t1 WHERE MATCH (a,b)
540 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
541 id a b
542 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
543 3 Optimizing MySQL In this tutorial we will show ...
544 select * from t1 where MATCH(a,b) AGAINST("+tutorial +VÐƷWİ" IN BOOLEAN MODE);
545 id a b
546 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
547 select * from t1 where MATCH(a,b) AGAINST("+dbms" IN BOOLEAN MODE);
548 id a b
549 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
550 select * from t1 where MATCH(a,b) AGAINST("+Mysql +(tricks never)" IN BOOLEAN MODE);
551 id a b
552 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
553 select * from t1 where MATCH(a,b) AGAINST("+mysql -(tricks never)" IN BOOLEAN MODE) ORDER BY id;
554 id a b
555 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
556 2 How To Use MySQL Well After you went through a ...
557 3 Optimizing MySQL In this tutorial we will show ...
558 5 MySQL vs. YourSQL In the following database comparison ...
559 6 MySQL Security When configured properly, MySQL ...
560 select *, MATCH(a,b) AGAINST("mysql VÐƷWİ" IN BOOLEAN MODE) as x from t1 ORDER BY id;
561 id a b x
562 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ... 0.6055193543434143
563 2 How To Use MySQL Well After you went through a ... 0.000000001885928302414186
564 3 Optimizing MySQL In this tutorial we will show ... 0.000000001885928302414186
565 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ... 0.000000001885928302414186
566 5 MySQL vs. YourSQL In the following database comparison ... 0.000000001885928302414186
567 6 MySQL Security When configured properly, MySQL ... 0.000000003771856604828372
568 select * from t1 where MATCH a,b AGAINST ('"security mysql"' IN BOOLEAN MODE);
569 id a b
570 select * from t1 where MATCH(a,b) AGAINST ("VÐƷWİ" WITH QUERY EXPANSION) ORDER BY id;
571 id a b
572 1 MySQL Tutorial DBMS stands for DataBase VÐƷWİ...
573 2 How To Use MySQL Well After you went through a ...
574 3 Optimizing MySQL In this tutorial we will show ...
575 4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
576 5 MySQL vs. YourSQL In the following database comparison ...
577 6 MySQL Security When configured properly, MySQL ...
578 INSERT INTO t1 (a,b) VALUES ('test query expansion','for database ...');
579 INSERT INTO t1 (a,b) VALUES
580 ('test proximity search, test, proximity and phrase',
581 'search, with proximity innodb');
582 INSERT INTO t1 (a,b) VALUES
583 ('test proximity fts search, test, proximity and phrase',
584 'search, with proximity innodb');
585 INSERT INTO t1 (a,b) VALUES
586 ('test more proximity fts search, test, more proximity and phrase',
587 'search, with proximity innodb');
588 SELECT * FROM t1
589 WHERE MATCH (a,b)
590 AGAINST ('"proximity search"@2' IN BOOLEAN MODE);
591 id a b
592 8 test proximity search, test, proximity and phrase search, with proximity innodb
593 SELECT * FROM t1
594 WHERE MATCH (a,b)
595 AGAINST ('"proximity search"@1' IN BOOLEAN MODE);
596 id a b
597 SELECT * FROM t1
598 WHERE MATCH (a,b)
599 AGAINST ('"proximity search"@3' IN BOOLEAN MODE) ORDER BY id;
600 id a b
601 8 test proximity search, test, proximity and phrase search, with proximity innodb
602 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
603 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
604 SELECT * FROM t1
605 WHERE MATCH (a,b)
606 AGAINST ('"test proximity"@5' IN BOOLEAN MODE) ORDER BY id;
607 id a b
608 8 test proximity search, test, proximity and phrase search, with proximity innodb
609 9 test proximity fts search, test, proximity and phrase search, with proximity innodb
610 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
611 SELECT * FROM t1
612 WHERE MATCH (a,b)
613 AGAINST ('"more test proximity"@2' IN BOOLEAN MODE);
614 id a b
615 SELECT * FROM t1
616 WHERE MATCH (a,b)
617 AGAINST ('"more test proximity"@3' IN BOOLEAN MODE);
618 id a b
619 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
620 SELECT * FROM t1
621 WHERE MATCH (a,b)
622 AGAINST ('"more fts proximity"@03' IN BOOLEAN MODE);
623 id a b
624 10 test more proximity fts search, test, more proximity and phrase search, with proximity innodb
625 UPDATE t1 SET a = UPPER(a) , b = UPPER(b) ;
626 UPDATE t1 SET a = UPPER(a) , b = LOWER(b) ;
627 select * from t1 where MATCH(a,b) AGAINST("+tutorial +dbms" IN BOOLEAN MODE);
628 id a b
629 1 MYSQL TUTORIAL dbms stands for database vðʒwi...
630 select * from t1 where MATCH(a,b) AGAINST("+VÐƷWİ" IN BOOLEAN MODE);
631 id a b
632 1 MYSQL TUTORIAL dbms stands for database vðʒwi...
633 SELECT * FROM t1 WHERE MATCH (a,b)
634 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) ORDER BY id;
635 id a b
636 1 MYSQL TUTORIAL dbms stands for database vðʒwi...
637 3 OPTIMIZING MYSQL in this tutorial we will show ...
638 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
639 DELETE FROM t1 WHERE MATCH (a,b) AGAINST ('"proximity search"@14' IN BOOLEAN MODE);
640 SELECT * FROM t1 WHERE MATCH (a,b)
641 AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE);
642 id a b
643 SELECT * FROM t1 ORDER BY id;
644 id a b
645 2 HOW TO USE MYSQL WELL after you went through a ...
646 4 1001 MYSQL TRICKS 1. never run mysqld as root. 2. ...
647 5 MYSQL VS. YOURSQL in the following database comparison ...
648 6 MYSQL SECURITY when configured properly, mysql ...
649 7 TEST QUERY EXPANSION for database ...
650 DROP TABLE t1;
651 CREATE TABLE t1 (
652 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
653 a VARCHAR(200),
654 b TEXT
655 ) CHARACTER SET = utf8, ENGINE=InnoDB;
656 INSERT INTO t1 (a,b) VALUES
657 ('Я могу есть стекло', 'оно мне не вредит'),
658 ('Мога да ям стъкло', 'то не ми вреди'),
659 ('Μπορῶ νὰ φάω σπασμένα' ,'γυαλιὰ χωρὶς νὰ πάθω τίποτα'),
660 ('Příliš žluťoučký kůň', 'úpěl ďábelské kódy'),
661 ('Sævör grét', 'áðan því úlpan var ónýt'),
662 ('うゐのおくやま','けふこえて'),
663 ('いろはにほへど ちりぬる','あさきゆめみじ ゑひもせず');
664 INSERT INTO t1 (a,b) VALUES
665 ('MySQL Tutorial','request [email protected] ...') ,
666 ('Trial version','query performace @1255 minute on 2.1Hz Memory 2GB...') ,
667 ('when To Use MySQL Well','for free faq [email protected] ...');
668 CREATE FULLTEXT INDEX idx on t1 (a,b);
669 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("вредит χωρὶς") ORDER BY id;
670 id a b
671 1 Я могу есть стекло оно мне не вредит
672 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
673 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("оно" WITH QUERY EXPANSION);
674 id a b
675 1 Я могу есть стекло оно мне не вредит
676 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE) ORDER BY id;
677 id a b
678 1 Я могу есть стекло оно мне не вредит
679 2 Мога да ям стъкло то не ми вреди
680 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+γυαλιὰ +tutorial" IN BOOLEAN MODE);
681 id a b
682 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+tutorial +(Мога τίποτα)" IN BOOLEAN MODE);
683 id a b
684 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
685 id a b
686 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
687 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("ちりぬる" WITH QUERY EXPANSION);
688 id a b
689 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
690 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("+あさきゆめみじ +ゑひもせず" IN BOOLEAN MODE);
691 id a b
692 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
693 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("うゐのおく*" IN BOOLEAN MODE);
694 id a b
695 6 うゐのおくやま けふこえて
696 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
697 id a b
698 5 Sævör grét áðan því úlpan var ónýt
699 SELECT * FROM t1
700 WHERE MATCH (a,b)
701 AGAINST ('"γυαλιὰ χωρὶς"@2' IN BOOLEAN MODE);
702 id a b
703 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
704 SELECT * FROM t1
705 WHERE MATCH (a,b)
706 AGAINST ('"query performace"@02' IN BOOLEAN MODE);
707 id a b
708 9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
709 SELECT * FROM t1
710 WHERE MATCH (a,b)
711 AGAINST ('"πάθω τίποτα"@2' IN BOOLEAN MODE);
712 id a b
713 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
714 SELECT * FROM t1
715 WHERE MATCH (a,b)
716 AGAINST ('"あさきゆめみじ ゑひもせず"@1' IN BOOLEAN MODE);
717 id a b
718 SELECT * FROM t1
719 WHERE MATCH (a,b)
720 AGAINST ('"あさきゆめみじ ゑひもせず"@2' IN BOOLEAN MODE);
721 id a b
722 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
723 ALTER TABLE t1 DROP INDEX idx;
724 CREATE FULLTEXT INDEX idx on t1 (a,b);
725 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
726 id a b
727 7 いろはにほへど ちりぬる あさきゆめみじ ゑひもせず
728 UPDATE t1 SET a = "Pchnąć w tę łódź jeża" , b = "lub osiem skrzyń fig" WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
729 UPDATE t1 SET a = "В чащах юга жил-был цитрус? Да", b = "но фальшивый экземпляр! ёъ" WHERE MATCH(a,b) AGAINST ("вред*" IN BOOLEAN MODE);
730 DELETE FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
731 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("あさきゆめみじ ゑひもせず");
732 id a b
733 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST ("łódź osiem");
734 id a b
735 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
736 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("вред*" IN BOOLEAN MODE);
737 id a b
738 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("фальшив*" IN BOOLEAN MODE) ORDER BY id;
739 id a b
740 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
741 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
742 SELECT * FROM t1 WHERE MATCH(a,b) AGAINST("+Sævör +úlpan" IN BOOLEAN MODE);
743 id a b
744 SELECT * FROM t1
745 WHERE MATCH (a,b)
746 AGAINST ('"łódź jeża"@2' IN BOOLEAN MODE);
747 id a b
748 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
749 SELECT * FROM t1 ORDER BY id;
750 id a b
751 1 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
752 2 В чащах юга жил-был цитрус? Да но фальшивый экземпляр! ёъ
753 3 Μπορῶ νὰ φάω σπασμένα γυαλιὰ χωρὶς νὰ πάθω τίποτα
754 4 Příliš žluťoučký kůň úpěl ďábelské kódy
755 6 うゐのおくやま けふこえて
756 7 Pchnąć w tę łódź jeża lub osiem skrzyń fig
757 8 MySQL Tutorial request [email protected] ...
758 9 Trial version query performace @1255 minute on 2.1Hz Memory 2GB...
759 10 when To Use MySQL Well for free faq [email protected] ...
760 DROP TABLE t1;
761 CREATE TABLE t1(ID INT PRIMARY KEY,
762 no_fts_field VARCHAR(10),
763 fts_field VARCHAR(10),
764 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
765 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
766 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
767 ID no_fts_field fts_field
768 1 AAA BBB
769 UPDATE t1 SET fts_field='anychange' where id = 1;
770 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
771 ID no_fts_field fts_field
772 1 AAA anychange
773 UPDATE t1 SET no_fts_field='anychange' where id = 1;
774 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
775 ID no_fts_field fts_field
776 1 anychange anychange
777 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
778 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
779 ID no_fts_field fts_field
780 1 anychange other
781 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
782 ID no_fts_field fts_field
783 DROP INDEX f on t1;
784 UPDATE t1 SET fts_field='anychange' where id = 1;
785 UPDATE t1 SET no_fts_field='anychange' where id = 1;
786 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where id = 1;
787 CREATE FULLTEXT INDEX f ON t1(FTS_FIELD);
788 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
789 ID no_fts_field fts_field
790 1 anychange other
791 DROP TABLE t1;
792 CREATE TABLE t1(`FTS_DOC_ID` serial,
793 no_fts_field VARCHAR(10),
794 fts_field VARCHAR(10),
795 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
796 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
797 UPDATE t1 SET fts_field='anychange' where FTS_DOC_ID = 1;
798 ERROR HY000: Invalid InnoDB FTS Doc ID
799 UPDATE t1 SET fts_field='anychange', FTS_DOC_ID = 2 where FTS_DOC_ID = 1;
800 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
801 FTS_DOC_ID no_fts_field fts_field
802 2 AAA anychange
803 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
804 FTS_DOC_ID no_fts_field fts_field
805 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
806 SELECT * FROM t1 WHERE MATCH(fts_field) against("anychange");
807 FTS_DOC_ID no_fts_field fts_field
808 2 anychange anychange
809 UPDATE t1 SET no_fts_field='anychange', fts_field='other' where FTS_DOC_ID = 2;
810 ERROR HY000: Invalid InnoDB FTS Doc ID
811 SELECT * FROM t1 WHERE MATCH(fts_field) against("other");
812 FTS_DOC_ID no_fts_field fts_field
813 UPDATE t1 SET FTS_DOC_ID = 1 where FTS_DOC_ID = 2;
814 ERROR HY000: Invalid InnoDB FTS Doc ID
815 DROP INDEX f ON t1;
816 UPDATE t1 SET fts_field='newchange' where FTS_DOC_ID = 2;
817 UPDATE t1 SET no_fts_field='anychange' where FTS_DOC_ID = 2;
818 SELECT * FROM t1;
819 FTS_DOC_ID no_fts_field fts_field
820 2 anychange newchange
821 DROP TABLE t1;
822 CREATE TABLE t1(ID INT PRIMARY KEY,
823 no_fts_field VARCHAR(10),
824 fts_field VARCHAR(10),
825 FULLTEXT INDEX f(fts_field), index k(fts_field)) ENGINE=INNODB;
826 CREATE TABLE t2(ID INT PRIMARY KEY,
827 no_fts_field VARCHAR(10),
828 fts_field VARCHAR(10),
829 FULLTEXT INDEX f(fts_field),
830 INDEX k2(fts_field),
831 FOREIGN KEY(fts_field) REFERENCES
832 t1(fts_field) ON UPDATE CASCADE) ENGINE=INNODB;
833 INSERT INTO t1 VALUES (1, 'AAA', 'BBB');
834 INSERT INTO t2 VALUES (1, 'AAA', 'BBB');
835 update t1 set fts_field='newchange' where id =1;
836 SELECT * FROM t1 WHERE MATCH(fts_field) against("BBB");
837 ID no_fts_field fts_field
838 SELECT * FROM t2 WHERE MATCH(fts_field) against("BBB");
839 ID no_fts_field fts_field
840 SELECT * FROM t1 WHERE MATCH(fts_field) against("newchange");
841 ID no_fts_field fts_field
842 1 AAA newchange
843 SELECT * FROM t2 WHERE MATCH(fts_field) against("newchange");
844 ID no_fts_field fts_field
845 1 AAA newchange
846 DROP TABLE t2;
847 DROP TABLE t1;
848 CREATE TABLE t1(id INT PRIMARY KEY,
849 fts_field VARCHAR(10),
850 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
851 CREATE TABLE t2(id INT PRIMARY KEY,
852 fts_field VARCHAR(10),
853 FULLTEXT INDEX f(fts_field)) ENGINE=INNODB;
854 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');
855 INSERT INTO t2 values (1,'100'),(2,'200'),(3,'300'),(4,'400'),(5,'500'),(6,'600'), (7,'700'),(8,'800');
856 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo');
857 UPDATE t1, t2 set t1.fts_field = CONCAT(t1.fts_field, 'foo') WHERE t1.fts_field = "100foo";
858 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');
859 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foofoo");
860 id fts_field
861 1 100foofoo
862 SELECT * FROM t1 WHERE MATCH(fts_field) against("100foo");
863 id fts_field
864 SELECT * FROM t1 WHERE MATCH(fts_field) against("100");
865 id fts_field
866 SELECT * FROM t2 WHERE MATCH(fts_field) against("400fooxoo");
867 id fts_field
868 4 400fooxoo
869 SELECT * FROM t2 WHERE MATCH(fts_field) against("100");
870 id fts_field
871 1 100
872 SELECT * FROM t2 WHERE MATCH(fts_field) against("200");
873 id fts_field
874 SELECT * FROM t2 WHERE MATCH(fts_field) against("400");
875 id fts_field
876 DROP TABLE t1;
877 DROP TABLE t2;
878
879 BUG#13701973/64274: MYSQL THREAD WAS SUSPENDED WHEN EXECUTE UPDATE QUERY
880
881 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
882 CREATE TABLE t1 (
883 t1_id INT(10) UNSIGNED NOT NULL,
884 t2_id INT(10) UNSIGNED DEFAULT NULL,
885 PRIMARY KEY (t1_id),
886 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
887 ON DELETE CASCADE ON UPDATE CASCADE
888 ) ENGINE=InnoDB;
889 CREATE TABLE t2 (
890 t1_id INT(10) UNSIGNED NOT NULL,
891 t2_id INT(10) UNSIGNED NOT NULL,
892 t3_id INT(10) UNSIGNED NOT NULL,
893 t4_id INT(10) UNSIGNED NOT NULL,
894 PRIMARY KEY (t2_id),
895 FOREIGN KEY (t1_id) REFERENCES t1 (t1_id),
896 FOREIGN KEY (t3_id) REFERENCES t3 (t3_id)
897 ON DELETE CASCADE ON UPDATE CASCADE,
898 FOREIGN KEY (t4_id) REFERENCES t4 (t4_id)
899 ) ENGINE=InnoDB;
900 CREATE TABLE t3 (
901 t3_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
902 payload char(3),
903 PRIMARY KEY (t3_id)
904 ) ENGINE=InnoDB;
905 INSERT INTO t3 VALUES (1, '100');
906 CREATE TABLE t4 (
907 t2_id INT(10) UNSIGNED DEFAULT NULL,
908 t4_id INT(10) UNSIGNED NOT NULL,
909 PRIMARY KEY (t4_id),
910 FOREIGN KEY (t2_id) REFERENCES t2 (t2_id)
911 ON DELETE CASCADE ON UPDATE CASCADE
912 ) ENGINE=InnoDB;
913 SET FOREIGN_KEY_CHECKS=1;
914 UPDATE t3 SET payload='101' WHERE t3_id=1;
915 SET FOREIGN_KEY_CHECKS=0;
916 DROP TABLE t1;
917 DROP TABLE t2;
918 DROP TABLE t3;
919 DROP TABLE t4;
920 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;