This behavior has me scratching my head: apparently, when you store a string into a BLOB column, when you query it it doesn't behave like bytes? And, weirder still, when you attempt to perform a BLOB substring, you have to query a length of 2 to get a single byte?
sqlite> create table wtf (a BLOB);
sqlite> insert into wtf (a) values (NULL);
sqlite> insert into wtf (a) values ('a');
sqlite> insert into wtf (a) values (X'61');
sqlite> select * from wtf;
a
a
sqlite> select a = X'61' from wtf;
0
1
sqlite> select HEX(a) from wtf;
61
61
sqlite> select substr(a, 0, 1) from wtf;
sqlite> select substr(a, 0, 2) from wtf;
a
a
Why does SQLite store strings as
TEXT
inBLOB
columns, rather than bytes?
(I'll disregard your imprecise language: consider that everything stored in a computer is "bytes")
SQLite does not enforce column types.
In SQLite, the datatype of a value is associated with the value itself, not with its column [...] Flexible typing is a feature of SQLite, not a bug.
When you INSERT INTO table ( thisIsANumericColumn ) VALUES ( 'zzz' );
the SQLite engine is perfectly happy to store TEXT
strings as-is, so doing a SELECT thisIsANumericColumn FROM table
will result in your SQLite library (or your application code which consumes SQLite's API) needing to perform implicit type conversions if required, which can break at runtime (so you'd get-away with this in NodeJS or PHP, but not in .NET due to how ADO.NET works).
There are at least 3 possible alternative solutions:
STRICT
to your CREATE TABLE
DDL. This instructs SQLite to respect column types, just like a traditional RDBMS.
CREATE TABLE tbl ( a BLOB NOT NULL ) STRICT;
STRICT
tables.CHECK
constraints to enforce data-type restrictions and other data integrity checks, like value ranges, string length, etc.