| 1 |
drop table if exists t1, t2; |
| 2 |
CREATE TABLE t1 ( |
| 3 |
id VARCHAR(255) NOT NULL PRIMARY KEY, |
| 4 |
sujet VARCHAR(255), |
| 5 |
motsclefs TEXT, |
| 6 |
texte MEDIUMTEXT, |
| 7 |
FULLTEXT(sujet, motsclefs, texte) |
| 8 |
) ENGINE = InnoDB; |
| 9 |
INSERT INTO t1 VALUES('123','toto','essai','test'); |
| 10 |
INSERT INTO t1 VALUES('456','droit','penal','lawyer'); |
| 11 |
INSERT INTO t1 VALUES('789','aaaaa','bbbbb','cccccc'); |
| 12 |
CREATE TABLE t2 ( |
| 13 |
id VARCHAR(255) NOT NULL, |
| 14 |
author VARCHAR(255) NOT NULL |
| 15 |
) ENGINE = InnoDB; |
| 16 |
INSERT INTO t2 VALUES('123', 'moi'); |
| 17 |
INSERT INTO t2 VALUES('123', 'lui'); |
| 18 |
INSERT INTO t2 VALUES('456', 'lui'); |
| 19 |
ANALYZE TABLE t1; |
| 20 |
ANALYZE TABLE t2; |
| 21 |
select round(match(t1.texte,t1.sujet,t1.motsclefs) against('droit'),5) |
| 22 |
from t1 left join t2 on t2.id=t1.id; |
| 23 |
round(match(t1.texte,t1.sujet,t1.motsclefs) against('droit'),5) |
| 24 |
0 |
| 25 |
0 |
| 26 |
0.22764 |
| 27 |
0 |
| 28 |
select match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) |
| 29 |
from t1 left join t2 on t2.id=t1.id; |
| 30 |
match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE) |
| 31 |
0 |
| 32 |
0 |
| 33 |
0.22764469683170319 |
| 34 |
0 |
| 35 |
drop table t1, t2; |
| 36 |
create table t1 (venue_id int(11) default null, venue_text varchar(255) default null, dt datetime default null) ENGINE = InnoDB; |
| 37 |
Warnings: |
| 38 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 39 |
insert into t1 (venue_id, venue_text, dt) values (1, 'a1', '2003-05-23 19:30:00'),(null, 'a2', '2003-05-23 19:30:00'); |
| 40 |
create table t2 (name varchar(255) not null default '', entity_id int(11) not null auto_increment, primary key (entity_id), fulltext key name (name)) engine= innodb; |
| 41 |
Warnings: |
| 42 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 43 |
insert into t2 (name, entity_id) values ('aberdeen town hall', 1), ('glasgow royal concert hall', 2), ('queen\'s hall, edinburgh', 3); |
| 44 |
ANALYZE TABLE t1; |
| 45 |
ANALYZE TABLE t2; |
| 46 |
select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen' in boolean mode) and dt = '2003-05-23 19:30:00'; |
| 47 |
venue_id venue_text dt name entity_id |
| 48 |
1 a1 2003-05-23 19:30:00 aberdeen town hall 1 |
| 49 |
select * from t1 left join t2 on venue_id = entity_id where match(name) against('aberdeen') and dt = '2003-05-23 19:30:00'; |
| 50 |
venue_id venue_text dt name entity_id |
| 51 |
1 a1 2003-05-23 19:30:00 aberdeen town hall 1 |
| 52 |
select * from t1 left join t2 on (venue_id = entity_id and match(name) against('aberdeen' in boolean mode)) where dt = '2003-05-23 19:30:00'; |
| 53 |
venue_id venue_text dt name entity_id |
| 54 |
1 a1 2003-05-23 19:30:00 aberdeen town hall 1 |
| 55 |
NULL a2 2003-05-23 19:30:00 NULL NULL |
| 56 |
select * from t1 left join t2 on (venue_id = entity_id and match(name) against('aberdeen')) where dt = '2003-05-23 19:30:00'; |
| 57 |
venue_id venue_text dt name entity_id |
| 58 |
1 a1 2003-05-23 19:30:00 aberdeen town hall 1 |
| 59 |
NULL a2 2003-05-23 19:30:00 NULL NULL |
| 60 |
drop table t1,t2; |
| 61 |
create table t1 (id int not null primary key, d char(200) not null, e char(200), fulltext (d, e)) ENGINE = InnoDB; |
| 62 |
insert into t1 values (1, 'aword', null), (2, 'aword', 'bword'), (3, 'bword', null), (4, 'bword', 'aword'), (5, 'aword and bword', null); |
| 63 |
ANALYZE TABLE t1; |
| 64 |
select * from t1 where match(d, e) against ('+aword +bword' in boolean mode); |
| 65 |
id d e |
| 66 |
2 aword bword |
| 67 |
4 bword aword |
| 68 |
5 aword and bword NULL |
| 69 |
create table t2 (m_id int not null, f char(200), key (m_id), fulltext (f)) engine = InnoDB; |
| 70 |
insert into t2 values (1, 'bword'), (3, 'aword'), (5, ''); |
| 71 |
ANALYZE TABLE t2; |
| 72 |
select * from t1 left join t2 on m_id = id where match(d, e, f) against ('+aword +bword' in boolean mode); |
| 73 |
ERROR HY000: Incorrect arguments to MATCH |
| 74 |
drop table t1,t2; |
| 75 |
CREATE TABLE t1 ( |
| 76 |
id int(10) NOT NULL auto_increment, |
| 77 |
link int(10) default NULL, |
| 78 |
name mediumtext default NULL, |
| 79 |
PRIMARY KEY (id), |
| 80 |
FULLTEXT (name) |
| 81 |
) ENGINE = InnoDB; |
| 82 |
Warnings: |
| 83 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 84 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 85 |
INSERT INTO t1 VALUES (1, 1, 'string'); |
| 86 |
INSERT INTO t1 VALUES (2, 0, 'string'); |
| 87 |
CREATE TABLE t2 ( |
| 88 |
id int(10) NOT NULL auto_increment, |
| 89 |
name mediumtext default NULL, |
| 90 |
PRIMARY KEY (id), |
| 91 |
FULLTEXT (name) |
| 92 |
) ENGINE = InnoDB; |
| 93 |
Warnings: |
| 94 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 95 |
INSERT INTO t2 VALUES (1, 'string'); |
| 96 |
ANALYZE TABLE t1; |
| 97 |
ANALYZE TABLE t2; |
| 98 |
SELECT t1.*, MATCH(t1.name) AGAINST('string') AS relevance |
| 99 |
FROM t1 LEFT JOIN t2 ON t1.link = t2.id |
| 100 |
WHERE MATCH(t1.name, t2.name) AGAINST('string' IN BOOLEAN MODE); |
| 101 |
ERROR HY000: Incorrect arguments to MATCH |
| 102 |
DROP TABLE t1,t2; |
| 103 |
CREATE TABLE t1 (a INT) ENGINE = InnoDB; |
| 104 |
CREATE TABLE t2 (b INT, c TEXT, KEY(b), FULLTEXT(c)) ENGINE = InnoDB; |
| 105 |
INSERT INTO t1 VALUES(1); |
| 106 |
INSERT INTO t2(b,c) VALUES(2,'castle'),(3,'castle'); |
| 107 |
ANALYZE TABLE t1; |
| 108 |
ANALYZE TABLE t2; |
| 109 |
SELECT * FROM t1 LEFT JOIN t2 ON a=b WHERE MATCH(c) AGAINST('+castle' IN BOOLEAN MODE); |
| 110 |
a b c |
| 111 |
DROP TABLE t1, t2; |