1、准备工作如下:
onspaces -c -S sbspace1 -p /data/sbspace1 -o 0 -s 1024000
修改ONCONFIG配置文件SYSSBSPACENAME参数以及SBSPACENAME为sbspace1
onmode -wf SYSSBSPACENAME=sbspace1
onmode -wf SBSPACENAME=sbspace1
2、添加相关vp参数:
VPCLASS jvp,num=1
VPCLASS bts,num=1
3、创建带日志数据库
echo "create database testdb in datadbs1 with buffered log;" | dbaccess - -
4、执行命令注册bts(这一步可不做,创建表时会自动注册)
blademgr
>list testdb
>show modules
>register bts.3.10 testdb
>register ifxrltree.2.00 testdb
>register spatial.8.21.FC7 testdb
>list testdb
5、创建表,并插入数据
create table lab1(text_data lvarchar(2000),b int);
create index lab1_bts_idx on lab1 (text_data bts_lvarchar_ops) using bts;
bts_lvarchar_ops的构成是bts_索引字段的数据类型_ops
insert into lab1 values ('ssl aa bb',1);
insert into lab1 values ('ssl cc dd',2);
insert into lab1 values ('ssl ee ff',3);
insert into lab1 values ('ssl gg hh',4);
6、查询验证
select b from lab1 where bts_contains(text_data,'gggggg');
bts_contains函数有两个变量,第一个是字段的数据类型,第二个是需要匹配的字符串。
7、灵活的匹配方式
and匹配有两种形式:
select b from lab1 where bts_contains(text_data,'gggggg') and bts_contains(text_data,'hh');
select b from lab1 where bts_contains(text_data,'gggggg and hh');
or匹配有两种形式:
select b from lab1 where bts_contains(text_data,'gggggg') or bts_contains(text_data,'hh');
select b from lab1 where bts_contains(text_data,'gggggg or hh');
还支持正则表达式匹配:
select * from lab1 where bts_contains(text_data,'h?');
select * from lab1 where bts_contains(text_data,'h*');
除了lvarchar还支持blob智能大对象:
建立测试用表:
create table btstest
( col1 integer, col2 clob );
导入测试数据:
INSERT INTO BTSTEST VALUES (1,filetoclob('/home/informix/etc/onconfig.std','server'));
INSERT INTO BTSTEST VALUES (2,filetoclob('/etc/hosts','server'));
create index btsidx on btstest (col2 bts_clob_ops) using bts ;
select col1 from btstest where bts_contains(col2,'BUFFERPOOL');
详见《IBM Informix 11.5 Extensibility - Basic Text Search(BTS)》