fulltext2.result 7.67 KB
Newer Older
1 2 3 4 5
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
i int(10) unsigned not null auto_increment primary key,
a varchar(255) not null,
FULLTEXT KEY (a)
6
) ENGINE=MyISAM;
7 8 9
repair table t1 quick;
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
10 11 12 13 14 15 16 17 18
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
optimize table t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
check table t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
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 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114
select count(*) from t1 where match a against ('aaaxxx');
count(*)
260
select count(*) from t1 where match a against ('aaayyy');
count(*)
250
select count(*) from t1 where match a against ('aaazzz');
count(*)
255
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
260
select count(*) from t1 where match a against ('aaayyy' in boolean mode);
count(*)
250
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
255
select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
count(*)
765
select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
count(*)
765
select count(*) from t1 where match a against ('aaax*' in boolean mode);
count(*)
260
select count(*) from t1 where match a against ('aaay*' in boolean mode);
count(*)
250
select count(*) from t1 where match a against ('aaa*' in boolean mode);
count(*)
765
insert t1 (a) values ('aaaxxx'),('aaayyy');
insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
select count(*) from t1 where match a against ('aaaxxx');
count(*)
261
select count(*) from t1 where match a against ('aaayyy');
count(*)
251
select count(*) from t1 where match a against ('aaazzz');
count(*)
260
insert t1 (a) values ('aaaxxx 000000');
select count(*) from t1 where match a against ('000000');
count(*)
1
delete from t1 where match a against ('000000');
select count(*) from t1 where match a against ('000000');
count(*)
0
select count(*) from t1 where match a against ('aaaxxx');
count(*)
261
delete from t1 where match a against ('aaazzz');
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
261
select count(*) from t1 where match a against ('aaayyy' in boolean mode);
count(*)
251
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
0
select count(*) from t1 where a = 'aaaxxx';
count(*)
261
select count(*) from t1 where a = 'aaayyy';
count(*)
251
select count(*) from t1 where a = 'aaazzz';
count(*)
0
insert t1 (a) values ('aaaxxx 000000');
select count(*) from t1 where match a against ('000000');
count(*)
1
update t1 set a='aaazzz' where match a against ('000000');
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
261
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
1
update t1 set a='aaazzz' where a = 'aaaxxx';
update t1 set a='aaaxxx' where a = 'aaayyy';
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
251
select count(*) from t1 where match a against ('aaayyy' in boolean mode);
count(*)
0
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
262
115 116 117 118 119
drop table t1;
CREATE TABLE t1 (
i int(10) unsigned not null auto_increment primary key,
a varchar(255) not null,
FULLTEXT KEY (a)
120
) ENGINE=MyISAM;
121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
select count(*) from t1 where match a against ('aaaxxx');
count(*)
260
select count(*) from t1 where match a against ('aaayyy');
count(*)
250
select count(*) from t1 where match a against ('aaazzz');
count(*)
255
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
260
select count(*) from t1 where match a against ('aaayyy' in boolean mode);
count(*)
250
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
255
select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz');
count(*)
765
select count(*) from t1 where match a against ('aaaxxx aaayyy aaazzz' in boolean mode);
count(*)
765
select count(*) from t1 where match a against ('aaax*' in boolean mode);
count(*)
260
select count(*) from t1 where match a against ('aaay*' in boolean mode);
count(*)
250
select count(*) from t1 where match a against ('aaa*' in boolean mode);
count(*)
765
insert t1 (a) values ('aaaxxx'),('aaayyy');
insert t1 (a) values ('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz'),('aaazzz');
select count(*) from t1 where match a against ('aaaxxx');
count(*)
261
select count(*) from t1 where match a against ('aaayyy');
count(*)
251
select count(*) from t1 where match a against ('aaazzz');
count(*)
260
insert t1 (a) values ('aaaxxx 000000');
select count(*) from t1 where match a against ('000000');
count(*)
1
delete from t1 where match a against ('000000');
select count(*) from t1 where match a against ('000000');
count(*)
0
select count(*) from t1 where match a against ('aaaxxx');
count(*)
261
delete from t1 where match a against ('aaazzz');
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
261
select count(*) from t1 where match a against ('aaayyy' in boolean mode);
count(*)
251
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
0
select count(*) from t1 where a = 'aaaxxx';
count(*)
261
select count(*) from t1 where a = 'aaayyy';
count(*)
251
select count(*) from t1 where a = 'aaazzz';
count(*)
0
insert t1 (a) values ('aaaxxx 000000');
select count(*) from t1 where match a against ('000000');
count(*)
1
update t1 set a='aaazzz' where match a against ('000000');
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
261
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
1
update t1 set a='aaazzz' where a = 'aaaxxx';
update t1 set a='aaaxxx' where a = 'aaayyy';
select count(*) from t1 where match a against ('aaaxxx' in boolean mode);
count(*)
251
select count(*) from t1 where match a against ('aaayyy' in boolean mode);
count(*)
0
select count(*) from t1 where match a against ('aaazzz' in boolean mode);
count(*)
262
drop table t1;
218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236
set names utf8;
create table t1(a text,fulltext(a)) collate=utf8_swedish_ci;
insert into t1 values('test test '),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test'),
('test'),('test'),('test'),('test'),('test'),('test'),('test'),('test');
delete from t1 limit 1;
237 238 239 240 241
truncate table t1;
insert into t1 values('ab c d');
update t1 set a='ab c d';
select * from t1 where match a against('ab c' in boolean mode);
a
242 243
drop table t1;
set names latin1;
244 245 246 247 248 249 250 251 252 253 254 255
CREATE TABLE t1(a VARCHAR(255) CHARACTER SET gbk, FULLTEXT(a));
SET NAMES utf8;
INSERT INTO t1 VALUES(0xF043616161),(0xBEF361616197C22061616161);
SELECT HEX(a) FROM t1 WHERE MATCH(a) AGAINST(0x97C22061616161 IN BOOLEAN MODE);
HEX(a)
BEF361616197C22061616161
DELETE FROM t1 LIMIT 1;
CHECK TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
SET NAMES latin1;
DROP TABLE t1;