Type Affinity SQLite : comment fonctionnent les types

L'affinité est une préférence, pas une règle stricte

SQLite utilise un typage dynamique. Chaque valeur porte sa propre classe de stockage (NULL, INTEGER, REAL, TEXT, BLOB), et le type déclaré d'une colonne ne restreint pas vraiment ce que vous pouvez y stocker. Le rôle du type déclaré, c'est plutôt de donner à la colonne une affinité — autrement dit, une classe de stockage préférée vers laquelle SQLite va tenter de convertir les valeurs entrantes.

Regardez ce qui se passe quand l'affinité ne suffit pas à empêcher une incohérence :

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'pomme');
INSERT INTO items VALUES ('two', 'banane');

SELECT id, typeof(id), name FROM items;

La deuxième ligne stocke la chaîne 'two' dans une colonne INTEGER. SQLite a tenté de convertir 'two' en nombre, n'y est pas parvenu (ce n'est pas numérique) et l'a quand même enregistré en tant que TEXT. La fonction typeof() révèle la véritable classe de stockage de chaque valeur — et ce n'est pas toujours ce que laisse penser la déclaration de la colonne.

Pour qui vient de Postgres ou de MySQL, c'est déroutant. Mais c'est voulu.

Les cinq affinités de type SQLite

Dans une table non STRICT, chaque colonne se voit attribuer exactement l'une de ces affinités :

• TEXT — privilégie les chaînes de caractères.
• NUMERIC — privilégie les nombres, mais accepte le texte si la conversion échoue.
• INTEGER — comme NUMERIC, mais enregistre en entier toute valeur sans partie fractionnaire.
• REAL — privilégie les nombres à virgule flottante.
• BLOB — aucune préférence, stocke tel quel ce qu'on lui donne.

L'affinité BLOB est aussi appelée « sans affinité » — c'est celle que vous obtenez quand vous ne déclarez aucun type.

CREATE TABLE samples (
    a,                 -- Affinité BLOB (aucun type déclaré)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

Même entrée — la chaîne '42' — cinq types stockés différents. Chaque colonne a converti (ou non) la valeur selon son affinité.

Comment SQLite déduit l'affinité depuis votre déclaration

Voilà le point qui surprend tout le monde : SQLite n'a pas de liste figée de types « valides ». Vous pouvez écrire à peu près n'importe quoi après le nom d'une colonne, et SQLite déterminera l'affinité en cherchant certaines sous-chaînes dans le texte, dans cet ordre :

1. Contient INT → INTEGER
2. Contient CHAR, CLOB ou TEXT → TEXT
3. Contient BLOB, ou aucun type indiqué → BLOB
4. Contient REAL, FLOA ou DOUB → REAL
5. Tout le reste → NUMERIC

C'est tout l'algorithme. Et ça explique pas mal de bizarreries :

CREATE TABLE bizarreries (
    a VARCHAR(255),       -- TEXT  (contient CHAR)
    b BIGINT,             -- INTEGER (contient INT)
    c DOUBLE PRECISION,   -- REAL (contient DOUB)
    d DECIMAL(10,2),      -- NUMERIC (ne correspond à aucun des précédents)
    e BOOLEAN,            -- NUMERIC (ne correspond à aucun des précédents)
    f DATETIME,           -- NUMERIC (ne correspond à aucun des précédents)
    g FLOATING_POINTS,    -- INTEGER (contient INT — surprise !)
    h GIBBERISH           -- NUMERIC (ne correspond à aucun des précédents)
);

SELECT name, type FROM pragma_table_info('bizarreries');

FLOATING_POINTS devient INTEGER parce que la sous-chaîne INT apparaît dans POINTS. La première règle qui matche l'emporte, de haut en bas. C'est pour ça que recopier bêtement des types depuis une autre base de données peut donner un résultat différent de ce que vous attendiez.

L'affinité en pratique : les conversions à l'insertion

L'affinité de type entre vraiment en jeu au moment où SQLite doit décider s'il convertit votre valeur ou s'il la stocke telle quelle. Voici les règles :

• Affinité TEXT : les nombres et les BLOB sont convertis en texte.
• Affinités NUMERIC, INTEGER, REAL : le texte qui ressemble à un nombre est converti ; sinon, il reste du texte.
• Affinité BLOB : rien n'est converti.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'bonjour');
INSERT INTO conv VALUES ('bonjour', 'monde', 'monde');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

Ligne par ligne :

• '123' dans une colonne NUMERIC devient l'entier 123. La conversion texte vers nombre a réussi sans perte.
• '12.5' devient le réel 12.5.
• 'bonjour' reste du texte dans NUMERIC — aucun nombre vers lequel convertir.
• La colonne TEXT convertit les nombres en leur forme chaîne.
• La colonne BLOB stocke tout tel quel, type compris.

La subtilité entre INTEGER et REAL

L'affinité INTEGER se comporte presque comme NUMERIC, à un détail près : une valeur comme 3.0, dépourvue de partie fractionnaire réelle, est stockée comme l'entier 3 pour gagner de la place.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 arrive en tant qu'INTEGER dans les deux colonnes — l'optimisation s'applique aussi pour NUMERIC. 3.5 garde sa partie décimale et reste en REAL. À retenir : ne vous fiez pas à typeof() pour savoir si une colonne a été déclarée INTEGER ou REAL. Cette fonction renvoie ce qui est réellement stocké, et ça peut varier d'une ligne à l'autre.

Quand le type affinity se retourne contre vous

Cette souplesse est pratique… jusqu'au jour où elle ne l'est plus. Deux scénarios problématiques reviennent souvent dans le code en production :

1. Des données invalides passent à travers. Si votre application contient un bug qui envoie 'N/A' dans une colonne INTEGER, SQLite l'accepte sans broncher. Et plus tard, les requêtes qui font des calculs sur cette colonne renvoient des résultats bizarres, voire NULL. Aucune erreur, aucun avertissement — juste une corruption silencieuse.

2. Les comparaisons deviennent imprévisibles. Le tri et les tests d'égalité ne se comportent pas pareil selon la classe de stockage des valeurs :

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

Les entiers sont triés numériquement, puis les valeurs textuelles le sont par ordre lexicographique — et elles arrivent après tous les nombres. Résultat : on obtient d'abord 2, 3, 10 (les entiers dans l'ordre numérique), puis '20', '100' (les chaînes dans l'ordre alphabétique). Pas vraiment ce qu'on attendait, en général.

Si vous maîtrisez vos insertions et que vous validez soigneusement les données, les tables classiques font très bien le job. Sinon — ou si vous préférez tout simplement laisser la base faire respecter les types à votre place — il existe une meilleure option.

La suite : les tables STRICT

SQLite 3.37 a introduit les tables STRICT, qui désactivent l'affinité et rejettent toute valeur qui ne correspond pas au type déclaré. Vous gardez le typage dynamique par défaut quand ça vous arrange, et vous obtenez une validation à la Postgres quand vous en avez besoin. C'est justement le sujet de la page suivante.