相关 SQLite 数据类型(如 INT、 INTEGER、 SMALLINT 和 TINYINT)之间的区别是什么？

在 SQLite3中创建表时，当遇到所有可能包含相似内容的数据类型时，我会感到困惑，所以有人能告诉我下列数据类型之间的区别吗？

INT, INTEGER, SMALLINT, TINYINT


DEC, DECIMAL


LONGCHAR, LONGVARCHAR


DATETIME, SMALLDATETIME

是否有一些文档列出了最小值。麦克斯。各种数据类型的容量？例如，我猜想 smallint的最大值比 tinyint大，但是比整数小，但是我不知道这些容量是什么。

88865 次浏览
小开
最佳答案

SQLite, technically, has no data types, there are storage classes in a manifest typing system, and yeah, it's confusing if you're used to traditional RDBMSes. Everything, internally, is stored as text. Data types are coerced/converted into various storage locations based on affinities (ala data types assigned to columns).

The best thing that I'd recommend you do is to :

  1. Temporarily forget everything you used to know about standalone database datatypes

  2. Read the above link from the SQLite site.

  3. Take the types based off of your old schema, and see what they'd map to in SQLite

  4. Migrate all the data to the SQLite database.

Note: The datatype limitations can be cumbersome, especially if you add time durations, or dates, or things of that nature in SQL. SQLite has very few built-in functions for that sort of thing. However, SQLite does provide an easy way for you to make your own built-in functions for adding time durations and things of that nature, through the sqlite3_create_function library function. You would use that facility in place of traditional stored procedures.

小开

Most of those are there for compatibility. You really only have integer, float, text, and blob. Dates can be stored as either a number (unix time is integer, microsoft time is float) or as text.

小开

The difference is syntactic sugar. Only a few substrings of the type names matter as for as the type affinity is concerned.

  • INT, INTEGER, SMALLINT, TINYINT → INTEGER affinity, because they all contain "INT".
  • LONGCHAR, LONGVARCHAR → TEXT affinity, because they contain "CHAR".
  • DEC, DECIMAL, DATETIME, SMALLDATETIME → NUMERIC, because they don't contain any of the substrings that matter.

The rules for determining affinity are listed at the SQLite site.

If you insist on strict typing, you can implement it with CHECK constraints:

CREATE TABLE T (
N   INTEGER CHECK(TYPEOF(N) = 'integer'),
Str TEXT CHECK(TYPEOF(Str) = 'text'),
Dt  DATETIME CHECK(JULIANDAY(Dt) IS NOT NULL)
);

But I never bother with it.

As for the capacity of each type:

  • INTEGER is always signed 64-bit. Note that SQLite optimizes the storage of small integers behind-the-scenes, so TINYINT wouldn't be useful anyway.
  • REAL is always 64-bit (double).
  • TEXT and BLOB have a maximum size determined by a preprocessor macro, which defaults to 1,000,000,000 bytes.
小开

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-16LE).

BLOB. The value is a blob of data, stored exactly as it was input.

小开

As an addition to answer from dan04, if you want to blindly insert a NUMERIC other than zero represented by a TEXT but ensure that text is convertible to a numeric:

your_numeric_col NUMERIC CHECK(abs(your_numeric_col) <> 0)

Typical use case is in a query from a program that treats all data as text (for uniformity & simplicity, since SQLite already does so). The nice thing about this is that it allows constructs like this:

INSERT INTO table (..., your_numeric_column, ...) VALUES (..., some_string, ...)

which is convenient in case you're using placeholders because you don't have to handle such non-zero numeric fields specially. An example using Python's sqlite3 module would be,

conn_or_cursor.execute(
"INSERT INTO table VALUES (" + ",".join("?" * num_values) + ")",
str_value_tuple)  # no need to convert some from str to int/float

In the above example, all values in str_value_tuple will be escaped and quoted as strings when passed to SQlite. However, since we're not checking explicitly the type via TYPEOF but only convertibility to type, it will still work as desired (i.e., SQLite will either store it as a numeric or fail otherwise).


提交答案