1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
drop table if exists t1, t2, t3;
create table t1 (c1 int primary key, c2 int, c3 text, fulltext index ft(c3));
insert into t1 values(1,10,"aa ii uu ee oo");
insert into t1 values(2,10,"ka ki ku ke ko");
insert into t1 values(3,10,"aa ii uu ee oo");
insert into t1 values(4,10,"ka ki ku ke ko");
insert into t1 values(5,20,"aa ii uu ee oo");
insert into t1 values(6,20,"ka ki ku ke ko");
insert into t1 values(7,20,"aa ii uu ee oo");
insert into t1 values(8,20,"ka ki ku ke ko");
select * from t1;
c1 c2 c3
1 10 aa ii uu ee oo
2 10 ka ki ku ke ko
3 10 aa ii uu ee oo
4 10 ka ki ku ke ko
5 20 aa ii uu ee oo
6 20 ka ki ku ke ko
7 20 aa ii uu ee oo
8 20 ka ki ku ke ko
select * from t1 where match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
1 10 aa ii uu ee oo
3 10 aa ii uu ee oo
5 20 aa ii uu ee oo
7 20 aa ii uu ee oo
select * from t1 where not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
2 10 ka ki ku ke ko
4 10 ka ki ku ke ko
6 20 ka ki ku ke ko
8 20 ka ki ku ke ko
select * from t1 where match(c3) against("+dummy" in boolean mode) order by c1;
c1 c2 c3
select * from t1 where not match(c3) against("+dummy" in boolean mode) order by c1;
c1 c2 c3
1 10 aa ii uu ee oo
2 10 ka ki ku ke ko
3 10 aa ii uu ee oo
4 10 ka ki ku ke ko
5 20 aa ii uu ee oo
6 20 ka ki ku ke ko
7 20 aa ii uu ee oo
8 20 ka ki ku ke ko
select * from t1 where c1 = 4 and not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
4 10 ka ki ku ke ko
select * from t1 where c1 <= 4 and not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
2 10 ka ki ku ke ko
4 10 ka ki ku ke ko
select * from t1 where c1 > 4 and not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
6 20 ka ki ku ke ko
8 20 ka ki ku ke ko
select * from t1 where c2 = 10 and not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
2 10 ka ki ku ke ko
4 10 ka ki ku ke ko
select * from t1 where c2 >= 15 and not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
6 20 ka ki ku ke ko
8 20 ka ki ku ke ko
select * from t1 where c2 < 15 and not match(c3) against("+uu" in boolean mode) order by c1;
c1 c2 c3
2 10 ka ki ku ke ko
4 10 ka ki ku ke ko
drop table t1;
|
