SQLite列类型的灵活性/可伸缩性如何? [英] How flexible/restricive are SQLite column types?
问题描述
最近，关于SQLite中列类型的灵活性存在一些争论.因此，问题 SQLite列类型的灵活性如何?
一个论点是类型被限制为主要的五种，即TEXT，NUMERIC，INTEGER，REAL和BLOB，以及官方文档中的命名列类型，即:-
INT, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8, CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), CLOB,no datatype specified (BLOB), DOUBLE, DOUBLE PRECISION, FLOAT, DECIMAL(10,5), BOOLEAN, DATE & DATETIME.
另一个论点是，列表是示例列表，并且列类型更加灵活，实际上已普遍应用了5条规则(如下所示).
3.1.色谱柱亲和力的确定
列的亲和力由声明的类型决定 列，并按照以下规则按照显示的顺序进行操作:
1):如果声明的类型包含字符串"INT"，则将其分配为INTEGER相似性.
2)如果该列的声明类型包含任何字符串"CHAR"，"CLOB"或"TEXT"，则该列具有TEXT关联性.注意 VARCHAR类型包含字符串"CHAR"，并因此被分配 TEXT关联.
3)如果列的声明类型包含字符串"BLOB"，或者未指定任何类型，则该列具有关联BLOB.
4)如果列的声明类型包含任何字符串"REAL"，"FLOA"或"DOUB"，则该列具有REAL亲和力.
5)否则，关联性为NUMERIC.
请注意，用于确定列关联性的规则的顺序为 重要的.声明类型为"CHARINT"的列将同时匹配 规则1和2，但第一个规则优先，因此该列 亲和力将为INTEGER.
那么SQLite列类型的优缺点是什么?
SQLite列类型是灵活的(动态的)，主要是为了适应其他数据库管理系统使用的刚性列类型. >
注意！此Asnwer不建议使用奇怪而奇妙的列类型.
1)实际上，您实际上可以为列类型使用任何名称，但是有一些限制.
2)列类型是列定义中的第二个值，例如CREATE TABLE table (columnname columntype .....,....)，尽管可能有意或无意地将其省略了注意，请参见 5a)
3)第一个限制是mycolumn INTEGER PRIMARY KEY 或mycolumn INTEGER PRIMARY KEY AUTOINCREMENT 是特殊的列类型.该列是 rowid 的别名，该别名是唯一的数字标识符( AUTOINCREMENT施加了一个规则，即 rowid 必须大于上一次使用的行该表的rowid，例如，如果某行使用id(9223372036854775807)，则随后进行的任何添加行的尝试都将导致SQLITE FULL错误.). SQLite自动增量
4)其他限制是列类型不得混淆SQLite解析器.例如，PRIMARY，TABLE，INDEX的列类型将导致SQLite异常(语法错误(代码1))，例如当使用INDEX列类型时，则:-
android.database.sqlite.SQLiteException: near "INDEX": syntax error (code 1):
发生.
5)列类型不是强制性的，例如CREATE TABLE mytable (...,PRIMARY_COL,....，在这种情况下， PRAGMA TABLE_INFO(tablename) 不会显示任何类型，例如(第三行).
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列，并且 type 被视为 no type (上面的table_info实际上是来自这种用法)
6)类型不限于单个单词，例如可以将 VARYING CHARACTER(255) 或 THE BIG BAD WOLF 指定为一种类型，如在此table_info摘录中可以看到的那样:-
08-08 08:23:26.423 4799-4799/? D/ TBLINFO: Col=type Value=THE BIG BAD WOLF
在SQLite中使用非标准列类型的原因！
总之，没有没有的理由，如开头所述，列类型的灵活性似乎主要是为了轻松地将SQL与其他数据库管理系统相适应.
列类型本身影响不大，因为数据将根据SQLite确定为要使用的存储类进行存储.除了 rowid (请参见上面的 3> )外，任何列都可以保存任何类型的值.
除了存储为Blob的数据外，必须使用cursor.getBlob进行检索，并且cursor.getBlob不能用于未存储为BLOB的数据(getBlob不会因存储为TEXT的数据而失败)，您可以使用任何cursor.get????方法来检索数据(尽管不一定有用).
以下是一些示例:-
对于添加了数据long myINT = 556677888;的列(例如通过ContentValues 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 or something like that.";
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<<
这是一个非常荒谬的示例，按照PRAGMA TABLE_INFO的列类型为my_char_is_not_a_char_but_an_int:-
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
结果(按上面的双精度"存储)是:-
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语句
代码已在运行API22的GenyMotion模拟设备上测试/运行，该设备的最低版本为14，目标版本为26.
Recently there has been some debate regarding the flexibility of column types in SQLite. Hence the question, How flexible are SQLite column types?
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), NATIVE CHARACTER(70), NVARCHAR(100), CLOB,no datatype specified (BLOB), DOUBLE, DOUBLE PRECISION, FLOAT, DECIMAL(10,5), BOOLEAN, DATE & DATETIME.
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. 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) If the declared type contains the string "INT" then it is assigned INTEGER affinity.
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) If the declared type for a column contains the string "BLOB" or if no type is specified then the column has affinity BLOB.
4) If the declared type for a column contains any of the strings "REAL", "FLOA", or "DOUB" then the column has REAL affinity.
5) Otherwise, the affinity is NUMERIC.
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.
3.1. Determination Of Column Affinity
So what are the in and outs/rights and wrongs of SQLite Column Types?
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.
Note! this Asnwer is NOT recommending use of weird and wonderful column types.
1) You can actually use virtually any name for a column type, there are however some limitations.
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) 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) 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):
occurs.
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) 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) 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
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.
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.
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.
Here's some examples:-
For a column where the data long myINT = 556677888; is added (via ContentValues e.g. cv1.put(columnanme,myINT));
Then :-
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 does not return to the stored value, getBlob cannot get the stored value.
For 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.
For String myTEXT = "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: 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<<
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
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.
The above was based upon the following:- Datatypes In SQLite Version 3 SQLite Autoincrement PRAGMA Statements
Code was tested/run on a GenyMotion emulated device running API22 compiled with a min version of 14 and target of 26.
这篇关于SQLite列类型的灵活性/可伸缩性如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
