在 cassandra 中选择 id != null [英] SELECT in cassandra where id != null

问题描述

如何在 cassandra 中查询 != null 列.

How do I query in cassandra for != null columns.

Select * from tableA where id != null;
Select * from tableA where name != null;

然后我想存储这些值并将它们插入到不同的表中.

Then I wanted to store these values and insert these into different table.

推荐答案

我认为 Cassandra 不可能做到这一点.首先，Cassandra CQL 不支持在 WHERE 子句中使用 NOT 或不等于运算符.其次，你的 WHERE 子句只能包含主键列，而主键列不允许插入空值.不过我不确定二级索引，所以我运行了这个快速测试:

I don't think this is possible with Cassandra. First of all, Cassandra CQL doesn't support the use of NOT or not equal to operators in the WHERE clause. Secondly, your WHERE clause can only contain primary key columns, and primary key columns will not allow null values to be inserted. I wasn't sure about secondary indexes though, so I ran this quick test:

create table nullTest (id text PRIMARY KEY, name text);
INSERT INTO nullTest (id,name) VALUES ('1','bob');
INSERT INTO nullTest (id,name) VALUES ('2',null);

我现在有一个表和两行(一个有空数据):

I now have a table and two rows (one with null data):

SELECT * FROM nullTest;

 id | name
----+------
  2 | null
  1 |  bob

(2 rows)

然后我尝试在 name 上创建二级索引，我知道它包含空值.

I then try to create a secondary index on name, which I know contains null values.

CREATE INDEX nullTestIdx ON nullTest(name);

它让我去做.现在，我将对该索引运行查询.

It lets me do it. Now, I'll run a query on that index.

SELECT * FROM nullTest WHERE name=null;
Bad Request: Unsupported null value for indexed column name

再说一次，这是在您无法查询 not null 的前提下完成的，如果您甚至无法查询实际上可能为 null 的列值.

And again, this is done under the premise that you can't query for not null, if you can't even query for column values that may actually be null.

所以，我认为这是不可能的.此外，如果您的主键中可能存在空值，那么您可能需要重新评估您的数据模型.同样，我知道 OP 的问题是关于查询数据在哪里 not null.但正如我之前提到的，Cassandra CQL 没有 NOT 或 != 运算符，所以这将是一个问题.

So, I'm thinking this can't be done. Also, if null values are a possibility in your primary key, then you may want to re-evaluate your data model. Again, I know the OP's question is about querying where data is not null. But as I mentioned before, Cassandra CQL doesn't have a NOT or != operator, so that's going to be a problem right there.

另一种选择是插入一个空字符串而不是一个空字符串.然后，您将能够查询空字符串.但这仍然不能让您克服在主键字段中具有空值的基本设计缺陷.也许如果您有一个复合主键，并且只有其中的一部分(集群列)可能为空(当然不是分区键的一部分).但是您仍然会遇到无法查询非空"(而不是非空)行的问题.

Another option, is to insert an empty string instead of a null. You would then be able to query on an empty string. But that still doesn't get you past the fundamental design flaw of having a null in a primary key field. Perhaps if you had a composite primary key, and only part of it (the clustering columns) had the possibility of being empty (certainly not part of the partitioning key). But you'd still be stuck with the problem of not being able to query for rows that are "not empty" (instead of not null).

注意:此处插入空值仅用于演示目的.这是您应该尽力避免的事情，因为插入空列值将创建一个墓碑.同样，插入大量空值会产生大量墓碑.

NOTE: Inserting null values was done here for demonstration purposes only. It is something you should do your best to avoid, as inserting a null column value WILL create a tombstone. Likewise, inserting lots of null values will create lots of tombstones.

这篇关于在 cassandra 中选择 id != null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！