|
1
by brian
clean slate |
1 |
drop table if exists t1, t2; |
2 |
CREATE TABLE t1 ( |
|
3 |
id int(10) unsigned NOT NULL auto_increment, |
|
4 |
q varchar(255) default NULL, |
|
5 |
PRIMARY KEY (id) |
|
6 |
);
|
|
7 |
INSERT INTO t1 VALUES (1,'aaaaaaaaa dsaass de'); |
|
8 |
INSERT INTO t1 VALUES (2,'ssde df s fsda sad er'); |
|
9 |
CREATE TABLE t2 ( |
|
10 |
id int(10) unsigned NOT NULL auto_increment, |
|
11 |
id2 int(10) unsigned default NULL, |
|
12 |
item varchar(255) default NULL, |
|
13 |
PRIMARY KEY (id), |
|
14 |
FULLTEXT KEY item(item) |
|
15 |
);
|
|
16 |
INSERT INTO t2 VALUES (1,1,'sushi'); |
|
17 |
INSERT INTO t2 VALUES (2,1,'Bolo de Chocolate'); |
|
18 |
INSERT INTO t2 VALUES (3,1,'Feijoada'); |
|
19 |
INSERT INTO t2 VALUES (4,1,'Mousse de Chocolate'); |
|
20 |
INSERT INTO t2 VALUES (5,2,'um copo de Vodka'); |
|
21 |
INSERT INTO t2 VALUES (6,2,'um chocolate Snickers'); |
|
22 |
INSERT INTO t2 VALUES (7,1,'Bife'); |
|
23 |
INSERT INTO t2 VALUES (8,1,'Pizza de Salmao'); |
|
24 |
SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),6) |
|
25 |
as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; |
|
26 |
q item id x |
|
27 |
aaaaaaaaa dsaass de sushi 1 1.923787 |
|
28 |
aaaaaaaaa dsaass de Bolo de Chocolate 2 0.000000 |
|
29 |
aaaaaaaaa dsaass de Feijoada 3 0.000000 |
|
30 |
aaaaaaaaa dsaass de Mousse de Chocolate 4 0.000000 |
|
31 |
ssde df s fsda sad er um copo de Vodka 5 0.000000 |
|
32 |
ssde df s fsda sad er um chocolate Snickers 6 0.000000 |
|
33 |
aaaaaaaaa dsaass de Bife 7 0.000000 |
|
34 |
aaaaaaaaa dsaass de Pizza de Salmao 8 0.000000 |
|
35 |
SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) |
|
36 |
as x FROM t1, t2 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; |
|
37 |
q item id x |
|
38 |
aaaaaaaaa dsaass de sushi 1 1 |
|
39 |
aaaaaaaaa dsaass de Bolo de Chocolate 2 0 |
|
40 |
aaaaaaaaa dsaass de Feijoada 3 0 |
|
41 |
aaaaaaaaa dsaass de Mousse de Chocolate 4 0 |
|
42 |
ssde df s fsda sad er um copo de Vodka 5 0 |
|
43 |
ssde df s fsda sad er um chocolate Snickers 6 0 |
|
44 |
aaaaaaaaa dsaass de Bife 7 0 |
|
45 |
aaaaaaaaa dsaass de Pizza de Salmao 8 0 |
|
46 |
SELECT t1.q, t2.item, t2.id, round(MATCH t2.item AGAINST ('sushi'),6) |
|
47 |
as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; |
|
48 |
q item id x |
|
49 |
aaaaaaaaa dsaass de sushi 1 1.923787 |
|
50 |
aaaaaaaaa dsaass de Bolo de Chocolate 2 0.000000 |
|
51 |
aaaaaaaaa dsaass de Feijoada 3 0.000000 |
|
52 |
aaaaaaaaa dsaass de Mousse de Chocolate 4 0.000000 |
|
53 |
ssde df s fsda sad er um copo de Vodka 5 0.000000 |
|
54 |
ssde df s fsda sad er um chocolate Snickers 6 0.000000 |
|
55 |
aaaaaaaaa dsaass de Bife 7 0.000000 |
|
56 |
aaaaaaaaa dsaass de Pizza de Salmao 8 0.000000 |
|
57 |
SELECT t1.q, t2.item, t2.id, MATCH t2.item AGAINST ('sushi' IN BOOLEAN MODE) |
|
58 |
as x FROM t2, t1 WHERE (t2.id2 = t1.id) ORDER BY x DESC,t2.id; |
|
59 |
q item id x |
|
60 |
aaaaaaaaa dsaass de sushi 1 1 |
|
61 |
aaaaaaaaa dsaass de Bolo de Chocolate 2 0 |
|
62 |
aaaaaaaaa dsaass de Feijoada 3 0 |
|
63 |
aaaaaaaaa dsaass de Mousse de Chocolate 4 0 |
|
64 |
ssde df s fsda sad er um copo de Vodka 5 0 |
|
65 |
ssde df s fsda sad er um chocolate Snickers 6 0 |
|
66 |
aaaaaaaaa dsaass de Bife 7 0 |
|
67 |
aaaaaaaaa dsaass de Pizza de Salmao 8 0 |
|
68 |
drop table t1, t2; |