是否有与 SQL:LIKE 条件相同的 Cassandra 查询？

Is there any query for Cassandra as same as SQL:LIKE Condition?

提问by BobDroid

The LIKEcondition allows us to use wildcards in the where clause of an SQL statement. This allows us to perform pattern matching. The LIKEcondition can be used in any valid SQL statement - select, insert, update, or delete. Like this

在LIKE条件允许我们在SQL语句的where子句中使用通配符。这允许我们执行模式匹配。该LIKE条件可用于任何有效的 SQL 语句 - 选择、插入、更新或删除。像这样

SELECT * FROM users
WHERE user_name like 'babu%';

like the same above operation any query is available for Cassandra in CLI.

与上述操作相同，任何查询都可用于 CLI 中的 Cassandra。

采纳答案by sdolgy

Simple answer: there is no equivalent of LIKE

简单的回答：没有LIKE 的等价物

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlSelect.html

https://docs.datastax.com/en/cql/3.3/cql/cql_reference/cqlSelect.html

Here is the command reference for v0.8:

这是 v0.8 的命令参考：

http://www.datastax.com/docs/0.8/references/cql#cql-reference

http://www.datastax.com/docs/0.8/references/cql#cql-reference

If you maintain another set of rows that hold references to a username:

如果您维护另一组包含对用户名的引用的行：

row: username:bab -> col:babu1, col:babar row: username:babu -> col:babur

行：用户名:bab -> col:babu1, col:babar 行：用户名:babu -> col:babur

Effectively you are cheating by pre-populating all of the results that you would normally search with in the RDBMS world. Storage is cheap in comparison to what it was years ago ... which is why this is now an accepted approach. It's less intensive on the CPU and Memory to retrieve a pre-populated list of information.

通过预先填充您通常在 RDBMS 世界中搜索的所有结果，您实际上是在作弊。与几年前相比，存储便宜……这就是为什么现在这是一种公认​​的方法。检索预先填充的信息列表对 CPU 和内存的占用较少。

回答by nstrelow

Since Cassandra 3.4 (3.5 recommended), LIKE queries can be achieved using a SSTable Attached Secondary Index (SASI).

从 Cassandra 3.4（推荐 3.5）开始，LIKE 查询可以使用 SSTable 附加二级索引（SASI）来实现。

For example:

例如：

CREATE TABLE cycling.cyclist_name ( 
  id UUID PRIMARY KEY, 
  lastname text, 
  firstname text
);

Creating the SASI as follows:

创建 SASI 如下：

CREATE CUSTOM INDEX  fn_prefix ON cyclist_name (firstname)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

Then a prefix LIKE query is working:

然后前缀 LIKE 查询正在工作：

SELECT * FROM cyclist_name WHERE firstname LIKE 'M%';
SELECT * FROM cyclist_name WHERE firstname LIKE 'Mic%';

These examples and more configuration options, like suffix queries, can be found in the documentation

这些示例和更多配置选项，如后缀查询，可以在文档中找到

A more in depth explanation about how SASI works can be found here.

可以在此处找到有关 SASI 如何工作的更深入的解释。

回答by Citrullin

I know: It's a old question but there is a solution for this topic:

我知道：这是一个老问题，但这个话题有一个解决方案：

You can't use like operator in cassandra but you can use range operators and with the range operator you can solve this "like 'whatever%'"

你不能在 cassandra 中使用 like 运算符，但你可以使用范围运算符，并且使用范围运算符你可以解决这个问题“像 'whatever%'”

An example: I have more than one product. Each product has his own partition key (first part of the primary key):

一个例子：我有不止一种产品。每个产品都有自己的分区键（主键的第一部分）：

CREATE TABLE user(productId int, username text, PRIMARY KEY(productId, username));

Now i have some users:

现在我有一些用户：

INSERT INTO user(productId, username) VALUES (1, 'anna');
INSERT INTO user(productId, username) VALUES (1, 'alpha');
INSERT INTO user(productId, username) VALUES (1, 'andreas');
INSERT INTO user(productId, username) VALUES (1, 'alex');
INSERT INTO user(productId, username) VALUES (1, 'bernd');
INSERT INTO user(productId, username) VALUES (1, 'bob');

Now, i want to find all users which have an a at the beginning. In a SQL world i use LIKE 'a%' in Cassandra i use this:

现在，我想找到所有以 a 开头的用户。在 SQL 世界中，我在 Cassandra 中使用 LIKE 'a%' 我使用这个：

SELECT * FROM user WHERE productId = 1 AND username >= 'a' AND username < 'b';

The result:

结果：

productid | username
-----------+----------
     1 |     alex
     1 |    alpha
     1 |  andreas
     1 |     anna

回答by Kamyar

I came across this post while I was searching for a solution to execute WHERE column_name LIKE '%keyword%'in Cassandra. The answers were promising but not quite addressing my issue.

我在寻找WHERE column_name LIKE '%keyword%'在 Cassandra 中执行的解决方案时遇到了这篇文章。答案很有希望，但并没有完全解决我的问题。

CREATE CUSTOM INDEX idx_name ON keyspace.columnfamily (column_name) 
USING 'org.apache.cassandra.index.sasi.SASIIndex' 
WITH OPTIONS = {
'mode': 'CONTAINS', 
'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 
'case_sensitive': 'false'};

In order to make %keyword%(two %s)works, the index must have options with mode: CONTAINSalong with that analyzer_classto make case_sensitiveeffective.

为了使%keyword%（两个 %s）工作，索引必须具有mode: CONTAINS选项以及该analyzer_class以使case_sensitive有效。

回答by Peter Corless

CQL LIKE statements are in Scylla Open Source 3.2 RC1, the release candidate for Scylla, a CQL-compatible database. We'd love feedback before release. Here's the details:

CQL LIKE 语句位于 Scylla Open Source 3.2 RC1 中，Scylla 是一个 CQL 兼容数据库的候选版本。我们希望在发布前获得反馈。以下是详细信息：

• CQL: LIKE Operation #4477

• CQL：喜欢操作 # 4477

The new CQL LIKE keyword allows matching any column to a search pattern, using % as a wildcard. Note that LIKE only works with ALLOW FILTERING.

新的 CQL LIKE 关键字允许将任何列与搜索模式匹配，使用 % 作为通配符。请注意，LIKE 仅适用于 ALLOW FILTERING。

LIKE Syntax support:

LIKE 语法支持：

'_' matches any single character

'_' 匹配任何单个字符

'%' matches any substring (including an empty string)

'%' 匹配任何子字符串（包括空字符串）

'\' escapes the next pattern character, so it matches verbatim

'\' 转义下一个模式字符，因此它逐字匹配

any other pattern character matches itself

任何其他模式字符与自身匹配

an empty pattern matches empty text fields

空模式匹配空文本字段

For example:

例如：

INSERT INTO t (id, name) VALUES (17, ‘Mircevski')

SELECT * FROM t where name LIKE 'Mirc%' allow filtering

INSERT INTO t (id, name) VALUES (17, 'Mircevski')

SELECT * FROM t where name LIKE 'Mirc%' 允许过滤

Source: [RELEASE] Scylla 3.2 RC12

来源：[发布] Scylla 3.2 RC1 2