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

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.