SQLite dataypes lengths?

Question

I'm completely new to SQLite (actually 5 minutes ago), but I do know somewhat the Oracle and MySql backends.

The question: I'm trying to know the lengths of each of the datatypes supported by SQLite, such as the differences between a bigint and a smallint. I've searched across the SQLite documentation (only talks about affinity, only matters it?), SO threads, google... and found nothing.

My guess: I've just slightly revised the SQL92 specifications, which talk about datatypes and its relations but not about its lengths, which is quite obvious I assume. Yet I've come accross the Oracle and MySql datatypes specs, and the specified lengths are mostly identical for integers at least. Should I assume SQLite is using the same lengths?

Aside question: Have I missed something about the SQLite docs? Or have I missed something about SQL in general? Asking this because I can't really understand why the SQLite docs don't specify something as basic as the datatypes lengths. It just doesn't make sense to me! Although I'm sure there is a simple command to discover the lengths.. but why not writing them to the docs?

Thank you!

Answer 1

Is the following a possible answer?:

The datatypes section of the SQLite docs says:

" INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. "

The docs specify the following datatypes for the INTEGER storage type: INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8

Therefore is it correct to assume: TINYINT: 1B SMALLINT: 2B MEDIUMINT: 3B BIGINT: 4B

Which actually maps identically to the MySQL and Oracle datatypes?

Omfg

Answer 2

I'm completely new to the SQLite documentation, but I found it in less than 30 seconds.

Datatypes In SQLite Version 3

Answer 3

In SQLite datatypes don't have lengths, values have lengths. A column you define as TINYINT could hold a BLOB, or visa versa.

Answer 4

SQLite is a bit odd when it comes to field types. You can store any type in any field (I.E. put a blob into an integer field). The way it works for integers is: it depends.

While your application may use a long (64 bits) to store the value, if it is actually <128 then SQLite will only use one byte to store it. If the value is >=128 and <16384 then it will use 2 bytes. The algorithm (as I recall) is that it uses 7 bits of each byte with the 8th bit used to indicate if another byte is needed. This works very well for non-negitive values but causes all negative values to take 9 bytes to store.