将值插入 SQLite 表时强制使用数据类型(BLOB 或 TEXT) [英] Forcing a data type (BLOB or TEXT) when inserting values into an SQLite table

问题描述

我最近对 ​​SQLite 数据库中的以下问题感到困惑:我有一个表，其中有两个明显相同的行.但是，以下语句仅检索了两行之一:

I have recently been puzzled by the following problem in an SQLite database: I had a table with two apparently identical rows. However, the following statement retrieved only one of the two rows:

SELECT "mycolumn" FROM "mytable" WHERE "mycolumn" == 'identical values';

显然，mycolumn"中的值是相同的(它们甚至具有相同的 HEX() 值).但是，我发现它们的数据类型不同:

Apparently, the values in "mycolumn" were identical (they even had the same HEX() value). However, I found that their data type were different:

SELECT "mycolumn", TYPEOF("mycolumn"), QUOTE("mycolumn") FROM "mytable";

一行给了我 BLOB，另一行给了 TEXT.

gave me BLOB in one row, TEXT in the other.

SQLite 如何确定是否将值存储为 BLOB 而不是 TEXT?我使用 python2.7-sqlite3(它创建了 BLOB 行)创建了数据库，然后使用 sqlitebrowser 添加了相同"的行.但是，我希望能够强制 python 使用 TEXT 类型(或找到一种与 BLOB 进行比较的方法).有这种方法吗?

How does SQLite determines if it is going to store a value as a BLOB rather than a TEXT? I created the database using python2.7-sqlite3 (which created the BLOB row), then added the "identical" row using sqlitebrowser. However, I would like to be able to force python to use the TEXT type (or find a way to make comparisons with a BLOB). Is there such a way?

推荐答案

SQLite 使用您提供的数据类型存储值.(由于列亲缘关系，可能会发生变化，但这永远不会影响 blob.)

SQLite stores value with the data type that you are giving it. (There might be changes due to column affinity, but this never affects blobs.)

如果要插入文本值，请更改 Python 代码以插入文本值(使用 正确类型).

If you want to insert text values, change you Python code to insert text values (with the proper types).

要修复数据库中的值，只需使用更改后的类型重写它们:

To fix the values in the database, just rewrite them with the changed type:

UPDATE MyTable
SET MyColumn = CAST(MyColumn AS TEXT)
WHERE typeof(MyColumn) = 'blob';

这篇关于将值插入 SQLite 表时强制使用数据类型(BLOB 或 TEXT)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！