Why does SQLite store strings as text in BLOB columns, rather than bytes?-CodePudding

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

CodePudding user response：

Why does SQLite store strings as TEXT in BLOB 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.
- shock! and horror! _(...yes)
- From the docs (emphasis mine):
  
  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.
- Read more here: https://www.sqlite.org/flextypegood.html
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:
1. Add STRICT to your CREATE TABLE DDL. This instructs SQLite to respect column types, just like a traditional RDBMS.
  - i.e. CREATE TABLE tbl ( a BLOB NOT NULL ) STRICT;
  - You must be running SQLite 3.37 (dated 2021-11-27) or later to use STRICT tables.
2. Simply don't insert incorrectly-typed values in the first place.
3. Use explicit CHECK constraints to enforce data-type restrictions and other data integrity checks, like value ranges, string length, etc.