Cassandra的CRUD

建表

CREATE TABLE sys_user (
    id text,
    add_time timestamp,
    username text,
    email list<text>,
    phone set<text>,
    pwd text,
    PRIMARY KEY (id, add_time, username)
) WITH CLUSTERING ORDER BY (add_time DESC,username ASC)
 AND caching={'keys':'ALL', 'rows_per_partition':'NONE'}
 AND compaction={'class':'xxxxx', 'max_threshold':'32', 'min_threshold':'4'}
 AND compression={'chunk_length_in_kb':'64', 'class':'xxxx'}
 AND dclocal_read_repair_chance=0.1
 AND speculative_retry='xxxx';

PS:

• PRIMARY KEY (id, add_time, username) ：id为分区键，add_time和username为集群键
• 主键=分区键+集群键
• 创建主键的时候，第一个字段是分区键
• WITH CLUSTERING ORDER BY (add_time DESC,username ASC)对集群键进行排序，后面查询排序的时候必须按照此顺序和排序方式

增

insert into sys_user(id,add_time,username) values ('1','2023-01-07T02:06:40.725+0000','ccc')

PS:在执行增加操作时，主键（分区键和集群键）必须添加上，否则会报错

删

delete from sys_user where id='1' and add_time ='2023-01-07T02:06:40.725+0000' and username='ccc'

PS：注意where后的顺序，分区键 ——> 集群键，键与键直接不可跨越
错误示例：

delete from sys_user where id='1' and  username='ccc'

改

update sys_user
set pwd = 'ddd'
where id='1' and add_time ='2023-01-07T02:06:40.725+0000' and username='ccc'

PS：set后不可带主键（分区键+集群键）

查：

select * from sys_user where id='1' and add_time='2023-01-07T02:06:40.725+0000' and username='ace'

PS:在进行order by 的时候注意select * from sys_user where id=‘1’ order by add_time desc,username
order by后字段的排序方式必须按照表定义的那个顺序

使用like模糊查询

配置需要模糊查询的列

create custom index phone_idx ON staff (phone) USING 'org.apache.xxx.index.sasi.SASIIndex' WITH OPTIONS = {'analyzer_class': 'org.apache.xxx.index.sasi.analyzer.StandardAnalyzer', 'case_sensitive': 'false'};

xxx:cassandra
输入以上语法后，才可针对phone进行like查询