SQLite驱动支持的SQL 列类型
类型 | 描述 |
---|---|
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, BYTEA | 不限制长度的字符串类型. 二进制数据必须安全地编码存储, 见text类型. |
CHAR(), VARCHAR(), TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT | 不限制长度的字符串类型. 不会被截断或者填充 |
ENUM | 不限制长度的字符串类型. 不像 MySQL, 使用 ENUM代替 VARCHAR不会节省存储空间. |
SET | 不限制长度的字符串类型. In contrast to MySQL, the input is not checked against the list of allowed values. |
YEAR | 不限制长度的字符串类型. MySQL 用1个字节 存储 2 个或者 4 个数字年份, 而SQLite 存为一个字符串. |
TINYINT, INT1, CHAR | A 1 byte type used to store one character, a signed integer between -128 and 127, or an unsigned integer between 0 and 255. |
SMALLINT, INT2 | 2 byte (short) integer type used to store a signed integer between -32768 and 32767 or an unsigned integer between 0 and 65535. |
MEDIUMINT | 3 byte integer type used to store a signed integer between -8388608 and 8388607 or an unsigned integer between 0 and 16777215. |
INT, INTEGER, INT4 | 4字节 (long)整数类型,用来存储一个有符号的整数,范围从-2147483648 到 2147483647, 或者一个有符号的整数,范围从 0到 4294967295. |
BIGINT, INT8, INTEGER PRIMARY KEY | 8 字节 (long long) 整型,用来存储有符号的整数,从 -9223372036854775808 到 9223372036854775807 , 或者无符号的整数,从0到 18446744073709551615. 请看下面关于 INTEGER PRIMARY KEY的讨论(哈哈,rowid就是8个字节的整数) |
DECIMAL, NUMERIC | A string type of unlimited length used to store floating-point numbers of arbitrary precision. |
TIMESTAMP, DATETIME | 用来存储日期/时间的不限制长度的字符串类型. 要求的格式是 ‘YYYY-MM-DD HH:MM:SS’,其他的东西被忽略. |
DATE | 用来存储日期的不限制长度的字符串类型. 要求的格式是 ‘YYYY-MM-DD’, 别的格式的东西会被忽略. |
TIME | 用来存储时间的不限制长度的字符串类型. 要求的格式是 ‘HH:MM:SS’,其他格式的东西会被忽略. |
FLOAT, FLOAT4, REAL | A 4 byte floating-point number. The range is -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. Please note that MySQL treats REAL as an 8 byte instead of a 4 byte float like PostgreSQL. |
DOUBLE, DOUBLE PRECISION, FLOAT8 | An 8 byte floating-point number. The range is -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. |
以上资料翻译自 http://libdbi-drivers.sourceforge.net/docs/dbd_sqlite3/x123.html
关于 INTEGER PRIMARY KEY的讨论,请参考http://zhiwei.li/text/ 上关于SQLite的一些资料