参考http://www.sqlite.org/datatype3.html
Sqlit中数据类型主要有以下几种:
NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
BLOB. The value is a blob of data, stored exactly as it was input.
补充一点:Date类型的数据需要转换成String类型存放:如Java中可以采用"yyyy-MM-dd HH:mm:ss"类型进行转换。
在SQLite Expert下支持的格式有:
Declared type
Mapped to internal data type
Description
Float
Floating-point numeric field (8 bytes)
DECIMAL
Float
Floating-point numeric field (8 bytes)
DOUBLE
Float
Floating-point numeric field (8 bytes)
DOUBLE PRECISION
Float
Floating-point numeric field (8 bytes)
FLOAT
Float
Floating-point numeric field (8 bytes)
NUMERIC
Float
Floating-point numeric field (8 bytes)
REAL
Float
Floating-point numeric field (8 bytes)
Largeint
64-bit integer field
INTEGER
Largeint
64-bit integer field
SMALLINT
Largeint
64-bit integer field
TINYINT
Largeint
64-bit integer field
WORD
Largeint
64-bit integer field
AUTOINC
Largeint
64-bit integer field
BIGINT
Largeint
64-bit integer field
LARGEINT
Largeint
64-bit integer field
INT64
Largeint
64-bit integer field
CHAR
String
String field
VARCHAR
String
String field
VARCHAR2
String
String field
TEXT
String
String field
DATETEXT
String
String field
NCHAR
WideString
Wide string field
NVARCHAR
WideString
Wide string field
NVARCHAR2
WideString
Wide string field
NTEXT
WideString
Wide string field
CURRENCY
Currency
Money field (8 bytes)
MONEY
Currency
Money field (8 bytes)
SMALLMONEY
Currency
Money field (8 bytes)
BLOB
Blob
Binary Large Object field
Blob
Binary Large Object field
BINARY
Blob
Binary Large Object field
VARBINARY
Blob
Binary Large Object field
CLOB
Memo
Text memo field
MEMO
Memo
Text memo field
DATE
Date
Date field (Borland TDateTime)
TIME
Time
Time field (Borland TDateTime)
DATETIME
DateTime
Date and time field (Borland TDateTime)
TIMESTAMP
DateTime
Date and time field (Borland TDateTime)
BOOLEAN
Boolean
Boolean field
GRAPHIC
Graphic
Graphic field
此外,当改变一个Table的column时,转换数据类型的时候将遵循以下的几个原则:
A column with TEXT affinity stores all data using storage classes NULL, TEXT or BLOB. If numerical data is inserted into a column with TEXT affinity it is converted to text form before being stored.
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, an attempt is made to convert it to an integer or real number before it is stored. If the conversion is successful (meaning that the conversion occurs without loss of information), then the value is stored using the INTEGER or REAL storage class. If the conversion cannot be performed without loss of information then the value is stored using the TEXT storage class. No attempt is made to convert NULL or blob values.
A column that uses INTEGER affinity behaves in the same way as a column with NUMERIC affinity, except that if a real value with no fractional component and a magnitude that is less than or equal to the largest possible integer (or text value that converts to such) is inserted it is converted to an integer and stored using the INTEGER storage class.
A column with REAL affinity behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. (As an internal optimization, small floating point values with no fractional component are stored on disk as integers in order to take up less space and are converted back into floating point as the value is read out.)
A column with affinity NONE does not prefer one storage class over another. No attempt is made to coerce data from one storage class into another. The data is stored on disk exactly as specified.