“文本"与“文本"之间的区别和“字符串"SQLite 中的数据类型 [英] Difference Between "Text" and "String" datatype in SQLite

问题描述

我的 Android 应用程序有一个 SQLite 数据库.我注意到我不小心使用String"数据类型而不是Text"数据类型定义了一个表.这是带字符串的代码:

I have a SQLite database with my Android application. I have noticed I accidentally defined a table using a "String" datatype instead of "Text" datatype. Here is the code with string:

private final String LISTDATES_CREATE = "create table if not exists ListDates (_id integer primary key autoincrement, ListName string not null, UpdateDate string not null);";

这有效.它从未抛出错误，我可以存储和检索数据.但是，我在文档或 Internet 上找不到对 SQLite 中字符串"数据类型的任何引用.通常，所有字符串类型的数据都用文本"定义，如下所示:

This works. It has never thrown an error and I can store and retrieve data. However I can't find any reference to a "String" datatype in SQLite in the documentation or on the internet. Typically, all string type data is defined with "text" like so:

private final String LISTDATES_CREATE = "create table if not exists ListDates (_id integer primary key autoincrement, ListName text not null, UpdateDate text not null);";

所以我的问题是，用字符串"数据类型定义的字段与文本"数据类型有什么区别?有区别吗?如果是这样，使用一种或另一种的后果(如果有)是什么?

So my question is, what is the difference between a field defined with a "string" datatype versus a "text" datatype? Is there a difference? If so, what are the consequences, if any, of using one or the other?

推荐答案

这里需要注意的微妙之处是 SQLite 不强制您放入列中的值的数据类型.这意味着您可以将文本放入数字字段中，依此类推.

The subtle thing to note here is that SQLite does not enforce the data type of values you put into columns. That means that you can put text into a numeric field, and so on.

要了解两个 SQL 语句之间的区别，请查看 2.1 列亲和性的确定部分，它将您提供的列类型映射到 SQLite 使用的存储类.

To understand the difference between your two SQL statements, check out section 2.1 Determination Of Column Affinity, which maps the column types you provide to the storage classes SQLite uses.

在这种情况下，类型 string 通过规则 5 映射到存储类 NUMERIC.在代码中将该字段声明为 text 会告诉DBMS 使用 TEXT 存储类.同样，由于 SQLite 不强制执行列的类型，因此当您注意到将字符串存储为 NUMERIC 列时，您的代码可能会运行良好.

In this case, the type string gets mapped to storage class NUMERIC via rule 5. Declaring the field as text in code would tell the DBMS to use the TEXT storage class. Again, since SQLite does not enforce the types of columns, your code will probably run fine when storing Strings as a NUMERIC column, as you note.

作为替代示例，您可以定义一个类型为 INTERESTING STUFF 的列，该列将通过规则 1 映射到 INTEGER 存储类.

As an alternative example, you could define a column with type INTERESTING STUFF, and that would be mapped to the INTEGER storage class, via rule 1.

总的来说，将 text 用于表定义可能是个好主意.

Overall, it's probably a good idea to just use text for your table definition.

这篇关于“文本"与“文本"之间的区别和“字符串"SQLite 中的数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！