Type Affinity no SQLite: como os tipos funcionam

Afinidade é uma preferência, não uma regra

O SQLite usa tipagem dinâmica. Cada valor carrega sua própria classe de armazenamento (NULL, INTEGER, REAL, TEXT, BLOB), e o tipo declarado de uma coluna não restringe de fato o que pode ser gravado nela. O que o tipo declarado faz é atribuir à coluna uma afinidade - ou seja, uma classe de armazenamento preferida para a qual o SQLite tenta converter os valores que chegam.

Veja o que acontece quando a afinidade não é suficiente para impedir uma divergência de tipos:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'maçã');
INSERT INTO items VALUES ('two', 'banana');

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

A segunda linha guarda a string 'two' em uma coluna INTEGER. O SQLite tentou converter 'two' para número, não conseguiu (não é numérico) e acabou armazenando como TEXT mesmo assim. A função typeof() mostra a classe de armazenamento real de cada valor - e nem sempre bate com o que a declaração da coluna sugere.

Isso costuma pegar de surpresa quem vem do Postgres ou do MySQL. Mas é assim por design.

As cinco afinidades de tipo do SQLite

Toda coluna em uma tabela não-STRICT recebe exatamente uma destas afinidades:

• TEXT - prefere strings.
• NUMERIC - prefere números, mas aceita texto se não conseguir converter.
• INTEGER - igual à NUMERIC, só que valores sem parte fracionária são guardados como inteiros.
• REAL - prefere números de ponto flutuante.
• BLOB - sem preferência, armazena qualquer coisa que você jogar nela.

A afinidade BLOB também é chamada de "sem afinidade" - é o que você ganha quando não declara tipo nenhum na coluna.

CREATE TABLE samples (
    a,                 -- Afinidade BLOB (nenhum tipo declarado)
    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;

A mesma entrada - a string '42' - virou cinco tipos armazenados diferentes. Cada coluna converteu (ou não) o valor de acordo com sua afinidade.

Como o SQLite escolhe a afinidade a partir da sua declaração

Aqui mora a pegadinha que confunde muita gente: o SQLite não tem uma lista fixa de tipos "válidos". Você pode escrever praticamente qualquer coisa depois do nome da coluna, e o SQLite decide a afinidade procurando substrings no texto, nesta ordem:

1. Contém INT → INTEGER
2. Contém CHAR, CLOB ou TEXT → TEXT
3. Contém BLOB, ou não tem tipo nenhum → BLOB
4. Contém REAL, FLOA ou DOUB → REAL
5. Qualquer outra coisa → NUMERIC

É esse o algoritmo inteiro. E ele explica boa parte das esquisitices que você encontra por aí:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT  (contém CHAR)
    b BIGINT,             -- INTEGER (contém INT)
    c DOUBLE PRECISION,   -- REAL (contém DOUB)
    d DECIMAL(10,2),      -- NUMERIC (não corresponde a nenhum dos anteriores)
    e BOOLEAN,            -- NUMERIC (não corresponde a nenhum dos anteriores)
    f DATETIME,           -- NUMERIC (não corresponde a nenhum dos anteriores)
    g FLOATING_POINTS,    -- INTEGER (contém INT - surpresa!)
    h GIBBERISH           -- NUMERIC (não corresponde a nenhum dos anteriores)
);

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

FLOATING_POINTS vira INTEGER porque a substring INT aparece em POINTS. A primeira regra que casa, de cima para baixo, é a que vence. É por isso que copiar tipos de outro banco sem pensar pode te dar um resultado bem diferente do esperado.

Type affinity na prática: conversões no INSERT

A afinidade de tipo entra em jogo principalmente quando o SQLite decide se vai converter o valor ou guardar como está. As regras são:

• Afinidade TEXT: números e BLOBs são convertidos para texto.
• Afinidades NUMERIC, INTEGER e REAL: texto que parece número é convertido; texto que não parece, fica como texto mesmo.
• Afinidade BLOB: nada é convertido.

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

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

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

Linha por linha:

• '123' numa coluna NUMERIC vira o inteiro 123. A conversão de texto para número funcionou sem perda.
• '12.5' vira o real 12.5.
• 'hello' na coluna NUMERIC continua como texto - não há número pra onde converter.
• A coluna TEXT converte os números para sua representação em string.
• Já a coluna BLOB guarda tudo exatamente como veio, tipo e tudo mais.

A pegadinha entre INTEGER e REAL

A afinidade INTEGER se comporta praticamente igual à NUMERIC, mas com um detalhe: um valor como 3.0, que não tem parte fracionária real, acaba armazenado como o inteiro 3 para economizar espaço.

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 cai como INTEGER nas duas colunas - essa otimização também acontece com NUMERIC. Já o 3.5 preserva a parte fracionária e continua como REAL. A moral da história: não confie no typeof() para descobrir se uma coluna foi declarada como INTEGER ou REAL. Ele te diz o que está realmente armazenado, e isso pode variar de linha para linha.

Quando a type affinity te morde

Essa flexibilidade é prática - até deixar de ser. Dois tipos de problema costumam aparecer em código de verdade:

1. Dados ruins entram escondidos. Se a sua aplicação tiver um bug que mande 'N/A' para uma coluna INTEGER, o SQLite guarda numa boa. Depois, qualquer consulta que faça aritmética com essa coluna devolve resultados estranhos ou NULL. Sem erro, sem aviso - corrupção silenciosa.

2. Comparações ficam esquisitas. Ordenação e testes de igualdade tratam valores de storage classes diferentes de formas distintas:

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

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

Inteiros são ordenados numericamente, depois os textos seguem a ordem lexicográfica - e os textos sempre vêm depois de todos os números. O resultado é algo como 2, 3, 10 (os inteiros em ordem numérica) seguido de '20', '100' (as strings em ordem alfabética). Não é o que a maioria das pessoas espera.

Se você controla os INSERTs e valida tudo direitinho, as tabelas comuns dão conta do recado. Mas se não for esse o caso - ou se você simplesmente quer que o próprio banco garanta os tipos pra você - existe uma opção melhor.

A seguir: tabelas STRICT

A versão 3.37 do SQLite trouxe as tabelas STRICT, que desligam a type affinity e rejeitam valores que não batem com o tipo declarado. Você fica com a tipagem dinâmica do SQLite quando quiser, e ganha uma checagem rígida no estilo Postgres quando precisar. É esse o assunto da próxima página.