确定 SQLite 中列的数据类型

发布于 2024-09-06 22:58:00 字数 472 浏览 1 评论 0原文

我正在开发一个 Android 应用程序，用户可以使用不同的选项来对来自数据库的显示数据进行排序。目前，我传递给 Android 的 query() 方法的 orderBy 字符串如下所示：

"LOWER("+columnName+") ASC"

问题是，如果 columnName 指定的列中的数据类型是整数，则对其调用 LOWER() 将导致它按字母顺序排序，即仅基于最左边的数字，这当然对于数字数据没有任何意义。因此，如果列的数据类型不是整数，我只想应用 LOWER() 。我的想法是这样的：

"CASE WHEN [data type of columnName is integer] THEN "+columnName+" ASC ELSE LOWER("+columName+") ASC END"

括号里的部分是我不知道该怎么做。 SQLite 是否提供函数来确定列的数据类型？

原文

I'm working on an Android App where the user has different options for sorting the displayed data that comes from the database. Currently my orderBy string that I pass to Androids query() method looks like this:

"LOWER("+columnName+") ASC"

The problem with this is that if the data type in the column specified by columnName is integer, calling LOWER() on it will cause it to be sorted alphabetically, i.e. based only on the leftmost digit, which of course doesn't make any sense for numeric data. Hence I only want to apply LOWER() if the data type of the column is not integer. What I have in mind is a statement like this:

"CASE WHEN [data type of columnName is integer] THEN "+columnName+" ASC ELSE LOWER("+columName+") ASC END"

The part in the brackets is what I don't know how to do. Does SQLite provide a function to determine a column's data type?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

放我走吧 2024-09-13 22:58:00

您确实想要列的类型，还是值的类型？（SQLite 是动态类型的，因此区别很重要。）

如果您想要后者，您可以可以使用typeof(columnName)。

回复收藏 0 原文

子栖 2024-09-13 22:58:00

用途：

PRAGMA table_info(table-name);

获取表信息。

Use:

PRAGMA table_info(table-name);

to get table info.

回复收藏 0 原文

杀手六號 2024-09-13 22:58:00

直接取自有关 SQLite 版本 3 的数据类型的 SQLite 文档：

大多数 SQL 数据库引擎（据我们所知，除了 SQLite 之外的所有 SQL 数据库引擎）都使用静态、严格的类型。使用静态类型，值的数据类型由其容器（存储值的特定列）决定。
SQLite 使用更通用的动态类型系统。在 SQLite 中，值的数据类型与值本身相关联，而不是与其容器相关联。 SQLite 的动态类型系统向后兼容其他数据库引擎的更常见的静态类型系统，因为在静态类型数据库上工作的 SQL 语句在 SQLite 中应该以相同的方式工作。然而，SQLite 中的动态类型允许它做传统严格类型数据库中不可能完成的事情。

列亲和性：使用PRAGMA table_info(table-name);。 PRAGMA table_info() 给出一个包含列 的表格cid、名称、类型、notnull、dflt_value 和 pk >。

结果集中的列包括列名、数据类型、列是否可以为 NULL 以及列的默认值。对于不属于主键的列，结果集中的“pk”列为零；对于属于主键的列，“pk”列是主键中列的索引。

值的数据类型：使用typeof(column)查看SQLite实际如何存储值。

改编自第 3.4 节的示例：

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);

-- BLOBs are always stored as BLOBs regardless of column affinity.
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');

-- NULLs are also unaffected by affinity
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);

PRAGMA table_info(t1);

0|t|TEXT|0||0
1|nu|NUMERIC|0||0
2|i|INTEGER|0||0
3|r|REAL|0||0
4|no|BLOB|0||0

的输出：SELECT 的输出 typeof(t), typeof(nu), typeof(i), typeof(r), typeof( no) FROM t1; （注意列中的每个值都有自己的数据类型）：

text|integer|integer|real|text
text|integer|integer|real|real
text|integer|integer|real|integer
blob|blob|blob|blob|blob
null|null|null|null|null

Taken directly from SQLite docs about datatypes for SQLite Version 3:

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.
SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases should work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases.

Column affinity: use PRAGMA table_info(table-name);. PRAGMA table_info() gives a table with columns cid, name, type, notnull, dflt_value, and pk.

Columns in the result set include the column name, data type, whether or not the column can be NULL, and the default value for the column. The "pk" column in the result set is zero for columns that are not part of the primary key, and is the index of the column in the primary key for columns that are part of the primary key.

Datatype of value: Use typeof(column) to see how values are actually stored by SQLite.

Example adapted from section 3.4:

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, TEXT.
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0', '500.0');

-- Values stored as TEXT, INTEGER, INTEGER, REAL, REAL.
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0, 500.0);

-- Values stored as TEXT, INTEGER, INTEGER, REAL, INTEGER.
INSERT INTO t1 VALUES(500, 500, 500, 500, 500);

-- BLOBs are always stored as BLOBs regardless of column affinity.
INSERT INTO t1 VALUES(x'0500', x'0500', x'0500', x'0500', x'0500');

-- NULLs are also unaffected by affinity
INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL);

Output of PRAGMA table_info(t1);:

0|t|TEXT|0||0
1|nu|NUMERIC|0||0
2|i|INTEGER|0||0
3|r|REAL|0||0
4|no|BLOB|0||0

Output of SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1; (notice each value in a column has its own datatype):

text|integer|integer|real|text
text|integer|integer|real|real
text|integer|integer|real|integer
blob|blob|blob|blob|blob
null|null|null|null|null

回复收藏 0 原文

七七 2024-09-13 22:58:00

您在设置表时是否将列声明为整数？否则，sqlite 会将其存储为文本，并且排序将按照您所描述的方式进行。

create table if not exists exampletable (columnName integer);

Did you declare the column as an integer when setting up the table? Otherwise sqlite will store it as text and the sorts will act as you've described.

create table if not exists exampletable (columnName integer);

回复收藏 0 原文

逆光下的微笑 2024-09-13 22:58:00

获取表使用的信息

PRAGMA table_info(table-name);

如果你想要后者，你可以使用

typeof(columnName)

To get information of Table use

PRAGMA table_info(table-name);

If you want the latter, you can use

typeof(columnName)

回复收藏 0 原文

妄断弥空 2024-09-13 22:58:00

SELECT type FROM PRAGMA_TABLE_INFO('tableName') WHERE name = 'columnName';

使用typeof(columnName)您可能并不总是得到相同的结果。我注意到在 blob 上运行测试时，PRAGMA_TABLE_INFO 给我 'BLOB' 但 typeof 返回加密的 'string'.

SELECT type FROM PRAGMA_TABLE_INFO('tableName') WHERE name = 'columnName';

You may not always get the same result back using typeof(columnName). I noticed that when running a test on a blob, PRAGMA_TABLE_INFO gives me 'BLOB' but typeof returns encrypted 'string'.

回复收藏 0 原文

~没有更多了~