SQLite 列类型的灵活性/限制性如何? [英] How flexible/restricive are SQLite column types?
问题描述
最近关于 SQLite 中列类型的灵活性存在一些争论.因此问题是，SQLite 列类型的灵活性如何?
Recently there has been some debate regarding the flexibility of column types in SQLite. Hence the question, How flexible are SQLite column types?
一个论点是类型仅限于主要的五种，即 TEXT、NUMERIC、INTEGER、REAL 和 BLOB，以及官方文档中的命名列类型，即:-
One argument was that types are restricted to the the main five, namely, TEXT, NUMERIC, INTEGER, REAL and BLOB, and additionally the named column types in the official documentation i.e. :-
INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8,CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVECHARACTER(70), NVARCHAR(100), CLOB, 未指定数据类型 (BLOB), DOUBLE,双精度、浮点数、小数 (10,5)、布尔值、日期和日期日期时间.
另一个论点是该列表是一个示例列表，并且列类型更加灵活，5 条规则(如下)几乎普遍适用.
Another argument was that the list was a list of examples and that column types are more flexible with the 5 rules (as below) being applied virtually universally.
3.1.色谱柱亲和性的测定
3.1. Determination Of Column Affinity
列的亲和性由声明的类型决定列，按照以下规则按顺序显示:
The affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:
1) 如果声明的类型包含字符串INT"，则分配给它 INTEGER 关联.
1) If the declared type contains the string "INT" then it is assigned INTEGER affinity.
2) 如果列的声明类型包含任何字符串CHAR"、CLOB"或TEXT"，则该列具有 TEXT 亲和性.注意类型 VARCHAR 包含字符串CHAR"，因此被分配文本亲和力.
2) If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
3) 如果列的声明类型包含字符串BLOB"，或者如果未指定类型，则该列具有亲和性 BLOB.
3) If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
4) 如果列的声明类型包含任何字符串REAL"、FLOA"或DOUB"，则该列具有 REAL 关联.
4) If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
5) 否则，亲和力为 NUMERIC.
5) Otherwise, the affinity is NUMERIC.
注意确定列亲和性的规则顺序是重要的.声明类型为CHARINT"的列将匹配规则 1 和 2 但第一条规则优先，因此列亲和力将为整数.
Note that the order of the rules for determining column affinity is important. A column whose declared type is "CHARINT" will match both rules 1 and 2 but the first rule takes precedence and so the column affinity will be INTEGER.
那么 SQLite Column Types 的优缺点是什么?
推荐答案
SQLite 列类型是灵活的(动态的)，主要是为了迎合其他数据库管理系统使用的刚性列类型的采用/适应.
SQLite column types are flexible (dynamic), primarily, it appears to cater for the adoption/adaptation of rigid column types used by other database Management Systems.
>
注意！这个 Asnwer 不推荐使用奇怪和美妙的列类型.
1) 实际上，您可以为列类型使用几乎任何名称，但是有一些限制.
1) You can actually use virtually any name for a column type, there are however some limitations.
2) 列类型是列定义中的第二个值，例如CREATE TABLE table (columnname columntype .....,..)，虽然它可能被有意或无意中省略注意见5a)
2) Column type is the 2nd value in the column definition e.g. CREATE TABLE table (columnname columntype .....,....), although it may be omitted intentionally or perhaps inadvertently Note see 5a)
3) 第一个限制是 mycolumnINTEGER PRIMARY KEY 或 mycolumnINTEGER PRIMARY KEY AUTOINCREMENT 是一种特殊的列类型.该列是 rowid 的别名，它是一个唯一的数字标识符(AUTOINCREMENT 强加了一个规则，rowid 必须大于表的最后使用的 rowid，例如，如果一行使用 id (9223372036854775807)，那么任何后续添加行的尝试都将导致 SQLITE FULL 错误.).SQLite 自动增量
3) The first limitation is that mycolumnINTEGER PRIMARY KEY or mycolumnINTEGER PRIMARY KEY AUTOINCREMENT is a special column type. The column is an alias for the rowid which is a unique numeric identifier (AUTOINCREMENT imposes a rule that the rowid must be greater than the last used rowid for the table e.g. if a row uses id (9223372036854775807), then any subsequent attempts to add a row will result in an SQLITE FULL error. ). SQLite Autoincrement
4) 其他限制是列类型不能混淆 SQLite 解析器.例如，PRIMARY、TABLE、INDEX 的列类型将导致 SQLite 异常(语法错误(代码 1))，例如当使用 INDEX 列类型时:-
4) Other limitations are that the column type mustn't confuse the SQLite parser. For example a column type of PRIMARY, TABLE, INDEX will result in an SQLite exception (syntax error (code 1)) e.g. when a column type of INDEX is used then:-
android.database.sqlite.SQLiteException: near "INDEX": syntax error (code 1):
发生.
5) 列类型不是强制性的，例如 CREATE TABLE mytable (...,PRIMARY_COL,.... 在这种情况下 PRAGMA TABLE_INFO(tablename) 将不显示类型，例如(第三行).
5) A column type is not mandatory, for example CREATE TABLE mytable (...,PRIMARY_COL,.... in which case a PRAGMA TABLE_INFO(tablename) will show no type e.g. (3rd Line).
08-08 07:56:23.391 13097-13097/? D/TBL_INFO: Col=cid Value=8
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=name Value=PRIMARY_COL
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=type Value=
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=notnull Value=1
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=dflt_value Value=null
08-08 07:56:23.391 13097-13097/? D/ TBLINFO: Col=pk Value=0
5a) 在某些情况下，SQLite 解析器会跳到有效的关键字，例如CREATE TABLE mytable (mycolumn NOT NULL,... 导致 NOT NULL 用于指示 NOT NULL 列和 类型 被视为无类型(上面的 table_info 实际上来自这样的用法).
5a) In some cases the SQLite Parser will skip to valid KEYWORDS e.g. CREATE TABLE mytable (mycolumn NOT NULL,... results in NOT NULL being used to indicate a NOT NULL column and the type being taken as no type (the table_info above was actually from such a usage).
6) 类型不限于单个单词，例如VARYING CHARACTER(255) 或 THE BIG BAD WOLF 可以指定为一个类型，从这里可以看出table_info 提取:-
6) A type is not limited to a single word e.g. VARYING CHARACTER(255) or THE BIG BAD WOLF can be specified as a type as can be seen from this table_info extract :-
08-08 08:23:26.423 4799-4799/? D/ TBLINFO: Col=type Value=THE BIG BAD WOLF
在 SQLite 中使用非标准列类型的原因！
简而言之，没有没有理由，正如开头所说，列类型的灵活性似乎主要是为了满足其他数据库管理系统对 SQL 的轻松适应.
The reason to use non-standard column types in SQLite!
In short there is no reason, as stated at first, the flexibility of column types appears to be primarily to cater for the easy adaptation of SQL from other Database Management Systems.
列类型本身几乎没有影响，因为数据将根据 SQLite 确定的存储类来存储.除了rowid(参见上面的3))任何列都可以包含任何类型的值.
Column types themselves have little effect as data will be stored according to the what SQLite determines as the storage class to be used. With the exception of rowid (see 3) above) any column can hold values of any type.
存储为 Blob 的数据除外，必须使用 cursor.getBlob 检索，并且 cursor.getBlob 不能用于未存储为 BLOB 的数据(getBlob 不会失败数据存储为文本)，您可以使用任何 cursor.get???? 方法检索数据(所有这些都不一定有用).
With the exception of data stored as a Blob, which must be retrieved using the cursor.getBlob and that cursor.getBlob cannot be used for data not stored as a BLOB (getBlob doesn't fail with data stored as TEXT), You can very much retrieve data (all be it not necessarily useful) using any of the cursor.get???? methods.
以下是一些示例:-
对于添加了数据 long myINT = 556677888; 的列(通过 ContentValues 例如 cv1.put(columnanme,myINT));
For a column where the data long myINT = 556677888; is added (via ContentValues e.g. cv1.put(columnanme,myINT));
然后:-
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Column=INTEGER_COL<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>556677888<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>5.56677888E8<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>5.566779E8<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>15104<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Unable to handle with getBlob.
getShort 不返回存储值，getBlob 无法获取存储值.
对于 Double myREAL = 213456789.4528791134567890109643534276; :-
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Column=REAL_COL<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>213456789<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>6037<<
08-08 09:19:03.658 13575-13575/mjt.soqanda D/ColTypes: Unable to handle with getBlob.
对于 String myTEXT = "The Lazy Quick Brown Fox Jumped Over the Fence 或类似的东西.";
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: Column=TEXT_COL<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>The Lazy Quick Brown Fox Jumped Over the Fence or something like that.<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>0.0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>0.0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>0<<
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ColTypes: VALUE AS BLOB >>[B@2f9e811e<<
这是一个非常荒谬的例子，列类型为 my_char_is_not_a_char_but_an_int，根据 PRAGMA TABLE_INFO :-
And here's a pretty ridiculous example with a column type of my_char_is_not_a_char_but_an_int as per PRAGMA TABLE_INFO :-
08-08 09:19:03.657 13575-13575/mjt.soqanda D/TBL_INFO: Col=cid Value=7
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=name Value=my_char_is_not_a_char_but_an_int_COL
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=type Value=my_char_is_not_a_char_but_an_int
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=notnull Value=0
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=dflt_value Value=null
08-08 09:19:03.657 13575-13575/mjt.soqanda D/ TBLINFO: Col=pk Value=0
结果(按照上面的Double"存储)是:-
Results (stored as per 'Double' above) are:-
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: Column=my_char_is_not_a_char_but_an_int_COL<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS INT >>213456789<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS LONG >>213456789<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS STRING >>2.13457e+08<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS DOUBLE >>2.134567894528791E8<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS FLOAT >>2.1345678E8<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: VALUE AS SHORT >>6037<<
08-08 09:19:03.659 13575-13575/mjt.soqanda D/ColTypes: Unable to handle with getBlob.
以上内容基于以下内容:-SQLite 版本 3 中的数据类型SQLite 自动增量PRAGMA 声明
The above was based upon the following:- Datatypes In SQLite Version 3 SQLite Autoincrement PRAGMA Statements
代码在运行 API22 的 GenyMotion 模拟设备上测试/运行，最小版本为 14，目标为 26.
Code was tested/run on a GenyMotion emulated device running API22 compiled with a min version of 14 and target of 26.
这篇关于SQLite 列类型的灵活性/限制性如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
