Qual è la differenza tra i tipi di dati SQLite correlati come INT, INTEGER, SMALLINT e TINYINT?

Quando creo una tabella in SQLite3, mi confondo quando sono confrontato con tutti i possibili tipi di dati che implicano contenuti simili, quindi qualcuno potrebbe dirmi la differenza tra i seguenti tipi di dati?

INT, INTEGER, SMALLINT, TINYINT DEC, DECIMAL LONGCHAR, LONGVARCHAR DATETIME, SMALLDATETIME 

C’è qualche documentazione da qualche parte che elenca il min./max. capacità dei vari tipi di dati? Ad esempio, suppongo che smallint contenga un valore massimo maggiore di tinyint , ma un valore inferiore rispetto a un intero, ma non ho idea di quali siano queste capacità.

SQLite , tecnicamente, non ha tipi di dati, ci sono classi di archiviazione in un sistema di digitazione manifest, e sì, è confuso se sei abituato ai tradizionali RDBMS . Tutto, internamente, è memorizzato come testo. I tipi di dati sono forzati / convertiti in varie posizioni di memoria in base alle affinità (tipi di dati ala assegnati alle colonne).

La cosa migliore che ti consiglio di fare è:

  1. Dimentica temporaneamente tutto ciò che sapevi dei tipi di dati del database standalone

  2. Leggi il link sopra dal sito SQLite .

  3. Prendi i tipi in base al tuo vecchio schema e guarda a cosa SQLite in SQLite

  4. Migrare tutti i dati nel database SQLite .

Nota: le limitazioni dei tipi di dati possono essere ingombranti, specialmente se si aggiungono durate di tempo, o date o elementi di tale natura in SQL . SQLite ha pochissime funzioni integrate per quel genere di cose. Tuttavia, SQLite fornisce un modo semplice per creare le proprie funzioni incorporate per aggiungere durate di tempo e cose di quella natura, attraverso la funzione della libreria sqlite3_create_function . Dovresti usare quella funzione al posto delle tradizionali procedure memorizzate.

La differenza è lo zucchero sintattico. Solo alcune sottostringhe dei nomi dei tipi sono importanti per quanto riguarda l’affinità di tipo.

  • INT, INTEGER, SMALLINT, TINYINT → affinità INTEGER, perché contengono tutti “INT”.
  • Affinità LONGCHAR, LONGVARCHAR → TEXT, perché contengono “CHAR”.
  • DEC, DECIMAL, DATETIME, SMALLDATETIME → NUMERIC, perché non contengono nessuna delle sottostringhe che contano.

Le regole per determinare l’affinità sono elencate nel sito SQLite .

Se insisti sulla tipizzazione rigorosa, puoi implementarla con i vincoli CHECK :

 CREATE TABLE T ( N INTEGER CHECK(TYPEOF(N) = 'integer'), Str TEXT CHECK(TYPEOF(Str) = 'text'), Dt DATETIME CHECK(JULIANDAY(Dt) IS NOT NULL) ); 

Ma non mi preoccupo mai di ciò.

Per quanto riguarda la capacità di ciascun tipo:

  • INTEGER è sempre firmato a 64 bit. Si noti che SQLite ottimizza la memorizzazione di piccoli numeri interi dietro le quinte, quindi TINYINT non sarebbe comunque utile.
  • REAL è sempre a 64 bit ( double ).
  • TEXT e BLOB hanno una dimensione massima determinata da una macro del preprocessore, che per impostazione predefinita è 1.000.000.000 di byte.

La maggior parte di quelli ci sono per compatibilità. In realtà hai solo numero intero, float, testo e blob. Le date possono essere memorizzate come numero (unix time è intero, microsoft time è float) o come testo.

NULL . Il valore è un valore NULL.

INTEGER . Il valore è un numero intero con segno, memorizzato in 1, 2, 3, 4, 6 o 8 byte a seconda della grandezza del valore.

REAL . Il valore è un valore in virgola mobile, memorizzato come numero in virgola mobile IEEE a 8 byte.

TEXT Il valore è una stringa di testo, memorizzata utilizzando la codifica del database (UTF-8, UTF-16BE o UTF-16LE).

BLOB . Il valore è un blob di dati, memorizzato esattamente come è stato inserito.

Come aggiunta alla risposta di dan04, se vuoi inserire ciecamente un NUMERIC diverso da zero rappresentato da un TEXT ma assicurati che il testo sia convertibile in un valore numerico:

 your_numeric_col NUMERIC CHECK(abs(your_numeric_col) <> 0) 

Il caso d’uso tipico è in una query di un programma che tratta tutti i dati come testo (per uniformità e semplicità, poiché SQLite lo fa già). La cosa bella di questo è che permette costrutti come questo:

 INSERT INTO table (..., your_numeric_column, ...) VALUES (..., some_string, ...) 

che è conveniente nel caso in cui si utilizzano segnaposto perché non è necessario gestire tali campi numerici diversi da zero appositamente. Un esempio che usa il modulo sqlite3 di Python sarebbe,

 conn_or_cursor.execute( "INSERT INTO table VALUES (" + ",".join("?" * num_values) + ")", str_value_tuple) # no need to convert some from str to int/float 

Nell’esempio precedente, tutti i valori in str_value_tuple saranno sfuggiti e quotati come stringhe quando passati a SQlite. Tuttavia, poiché non stiamo controllando esplicitamente il tipo tramite TYPEOF ma solo la convertibilità per digitare , funzionerà comunque come desiderato (ad esempio, SQLite lo memorizzerà come numerico o fallirà in altro modo).