Debian Codegraph @

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

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