Indexing mixed-type columns in SQLite-CodePudding

I have a column in an SQLite table which contains both numeric and textual values. Is it possible to create partial indices, one for numeric values and one for textual values, so that queries against numeric and textual data would use an appropriate index? Alternatively, is there some "canonical" approach to this problem, that is indexing/querying text/numeric mixed-type columns?

CodePudding user response：

The short answer - everything is actually pretty simple and should just work per answer on the SQLite Forum:

I believe you can simply put your content in the database and then query for it and everything will work fine.

CREATE TABLE t1(a INTEGER PRIMARY KEY, data BLOB);
CREATE INDEX t1data ON t1(data);

You don't need special indexes. You don't need to do anything special in your queries. It will just work.

Don't overthink this.

================================================================

The official docs provide further details, though the relevant sections are on the datatype page, which details comparison/ordering (and, therefore, indexing) operations logic involving mixed-type data in SQLite. A single index is sufficient, because SQLite essentially places all numeric values in one group and textual values in another group. The query involving numeric filters will use numeric comparison against numeric values, and textual filers will use textual comparison against textual values (this is true for exact filters, range filters with constrains on both sides, and pattern text filters.)

To achieve intuitive behavior, however, it is important to declare a mixed-type column as BLOB or NUMERIC (depending whether text literals representing numbers should be stored as numbers), and insert numbers as numeric literals, into a BLOB column. If the column is declared as TEXT, numeric values are stored as text, and numeric queries will not work properly.

Also, collation can still be specified on the BLOB column:

CREATE TABLE t1(a INTEGER PRIMARY KEY, data BLOB COLLATE NOCASE);
CREATE INDEX t1data ON t1(data);

This way, textual queries will ignore the case, and numeric queries will not be affected.