Tipos de datos en SQLite versión 2

1.0 falta de tipografía

SQLite es “sin tipo”. Esto significa que puede almacenar cualquier tipo de datos que desee en cualquier columna de cualquier tabla, independientemente del tipo de datos declarado de esa columna. (Vea la única excepción a esta regla en la sección 2.0 a continuación). Este comportamiento es una característica, no un error. Se supone que una base de datos almacena y recupera datos y no debería importarle a la base de datos en qué formato están los datos. El sólido sistema de tipado que se encuentra en la mayoría de los otros motores SQL y codificado en la especificación del lenguaje SQL es un error; es un ejemplo de la implementación se muestra a través de la interfaz. SQLite busca superar este error permitiéndole almacenar cualquier tipo de datos en cualquier tipo de columna y permitiendo flexibilidad en la especificación de tipos de datos.

Un tipo de datos para SQLite es cualquier secuencia de cero o más nombres seguidos opcionalmente por listas entre paréntesis de uno o dos enteros con signo. Tenga en cuenta, en particular, que un tipo de datos puede ser cero o más nombres. Eso significa que un vacio string es un tipo de datos válido en lo que respecta a SQLite. Entonces puede declarar tablas donde el tipo de datos de cada columna se deja sin especificar, así: 

CREATETABLE ex1(a,b,c);

Aunque SQLite permite omitir el tipo de datos, es una buena idea incluirlo en sus declaraciones CREATE TABLE, ya que el tipo de datos a menudo sirve como una buena pista para otros programadores sobre lo que pretende poner en la columna. Y si alguna vez transfiere su código a otro motor de base de datos, ese otro motor probablemente requerirá un tipo de datos de algún tipo. SQLite acepta todos los tipos de datos habituales. Por ejemplo: 

CREATETABLE ex2(
  a VARCHAR(10),
  b NVARCHAR(15),
  c TEXT,
  d INTEGER,
  e FLOAT,
  f BOOLEAN,
  g CLOB,
  h BLOB,
  i TIMESTAMP,
  j NUMERIC(10,5)
  k VARYINGCHARACTER(24),
  l NATIONALVARYINGCHARACTER(16));

Etcétera. Básicamente, cualquier secuencia de nombres seguida opcionalmente por uno o dos enteros con signo entre paréntesis servirá.

2.0 La LLAVE PRIMARIA INTEGER

Una excepción a la falta de tipos de SQLite es una columna cuyo tipo es INTEGER PRIMARY KEY. (Y debe usar “INTEGER” no “INT”. Una columna de tipo INT PRIMARY KEY no tiene tipo como cualquier otra.) Las columnas INTEGER PRIMARY KEY deben contener un entero de 32 bits con signo. Cualquier intento de insertar datos que no sean enteros resultará en un error.

Las columnas INTEGER PRIMARY KEY se pueden utilizar para implementar el equivalente de AUTOINCREMENT. Si intenta insertar un NULL en una columna INTEGER PRIMARY KEY, la columna en realidad se llenará con un número entero que sea uno mayor que el más grande key ya en la mesa. O si el mas grande key es 2147483647, la columna se rellenará con un número entero aleatorio. De cualquier manera, a la columna INTEGER PRIMARY KEY se le asignará un número entero único. Puede recuperar este entero usando el sqlite_last_insert_rowid () Función API o utilizando la last_insert_rowid () Función SQL en una instrucción SELECT posterior.

3.0 Comparación y orden de clasificación

SQLite no tiene tipos con el fin de decidir qué datos se pueden almacenar en una columna. Pero alguna noción de tipo entra en juego al ordenar y comparar datos. Para estos fines, una columna o una expresión puede ser de dos tipos: numérico y texto. La clasificación o la comparación pueden dar resultados diferentes según el tipo de datos que se estén clasificando o comparando.

Si los datos son de tipo texto entonces la comparación está determinada por las funciones estándar de comparación de datos C memcmp () o strcmp (). La comparación mira los bytes de dos entradas uno por uno y devuelve la primera diferencia distinta de cero. Las cadenas terminan en ‘ 000’, por lo que las cadenas más cortas se ordenan antes que las cadenas más largas, como era de esperar.

Para los datos numéricos, esta situación es más compleja. Si ambas entradas parecen números bien formados, entonces se convierten en valores de punto flotante usando atof () y comparado numéricamente. Si una entrada no es un número bien formado, pero la otra sí lo es, entonces se considera que el número es menor que el no número. Si ninguna de las entradas es un número bien formado, entonces strcmp () se utiliza para hacer la comparación.

No se confunda por el hecho de que una columna puede tener un tipo de datos “numérico”. Esto no significa que la columna solo pueda contener números. Simplemente significa que si la columna contiene un número, ese número se ordenará en orden numérico.

Para valores numéricos y de texto, NULL ordena antes que cualquier otro valor. Una comparación de cualquier valor con NULL usando operadores como “<" or ">= “es siempre false.

4.0 Cómo determina SQLite los tipos de datos

Para SQLite versión 2.6.3 y anteriores, todos los valores usaban el tipo de datos numérico. El tipo de datos de texto aparece en la versión 2.7.0 y posteriores. En la secuela, se asume que está utilizando la versión 2.7.0 o posterior de SQLite.

Para una expresión, el tipo de datos del resultado suele ser determinado por el operador más externo. Por ejemplo, los operadores aritméticos (“+”, “*”, “%”) siempre devuelven resultados numéricos. los string operador de concatenación (“||”) devuelve un resultado de texto. Etcétera. Si alguna vez tiene dudas sobre el tipo de datos de una expresión, puede usar la función especial tipo de() Función SQL para determinar cuál es el tipo de datos. Por ejemplo: 

sqlite>SELECT typeof('abc'+123);numeric
sqlite>SELECT typeof('abc'||123);text

Para las columnas de la tabla, el tipo de datos está determinado por la declaración de tipo de la instrucción CREATE TABLE. El tipo de datos es texto si y solo si la declaración de tipo contiene una o más de las siguientes cadenas:

GOTA
CARBONIZARSE
CLOB
TEXTO

La búsqueda de estas cadenas en la declaración de tipos no distingue entre mayúsculas y minúsculas, por supuesto. Si alguna de las cadenas anteriores ocurre en cualquier lugar de la declaración de tipo, entonces el tipo de datos de la columna es texto. Observe que el tipo “VARCHAR” contiene “CHAR” como subcadena, por lo que se considera texto.

Si ninguna de las cadenas anteriores aparece en ninguna parte de la declaración de tipo, entonces el tipo de datos es numérico. Tenga en cuenta en particular que el tipo de datos para las columnas con una declaración de tipo vacía es numérico.

5.0 Ejemplos

Considere las siguientes dos secuencias de comandos: 

CREATETABLE t1(a INTEGERUNIQUE);CREATETABLE t2(b TEXTUNIQUE);INSERTINTO t1 VALUES('0');INSERTINTO t2 VALUES(0);INSERTINTO t1 VALUES('0.0');INSERTINTO t2 VALUES(0.0);

En la secuencia de la izquierda, el segundo inserto fallará. En este caso, las cadenas ‘0’ y ‘0.0’ se tratan como números ya que se insertan en una columna numérica pero 0 == 0.0, lo que viola la restricción de unicidad. Sin embargo, el segundo inserto en la secuencia de la derecha funciona. En este caso, las constantes 0 y 0.0 se tratan como cadenas, lo que significa que son distintas.

SQLite siempre convierte números en flotantes de doble precisión (64 bits) para fines de comparación. Esto significa que una secuencia larga de dígitos que difieren solo en dígitos insignificantes se compararán igual si están en una columna numérica, pero se compararán de forma desigual si están en una columna de texto. Tenemos:

INSERTINTO t1                            INSERTINTO t2
   VALUES('12345678901234567890');VALUES(12345678901234567890);INSERTINTO t1                            INSERTINTO t2
   VALUES('12345678901234567891');VALUES(12345678901234567891);

Como antes, la segunda inserción a la izquierda fallará porque la comparación convertirá primero ambas cadenas en un número de punto flotante y la única diferencia en las cadenas está en el vigésimo dígito que excede la resolución de un flotante de 64 bits. Por el contrario, la segunda inserción a la derecha funcionará porque en ese caso, los números que se insertan son cadenas y se comparan usando memcmp ().

Los tipos numéricos y de texto también marcan la diferencia para la palabra clave DISTINCT: 

CREATETABLE t3(a INTEGER);CREATETABLE t4(b TEXT);INSERTINTO t3 VALUES('0');INSERTINTO t4 VALUES(0);INSERTINTO t3 VALUES('0.0');INSERTINTO t4 VALUES(0.0);SELECTDISTINCT*FROM t3;SELECTDISTINCT*FROM t4;

La instrucción SELECT de la izquierda devuelve una sola fila, ya que ‘0’ y ‘0.0’ se tratan como números y, por lo tanto, no se distinguen. Pero la instrucción SELECT de la derecha devuelve dos filas ya que 0 y 0.0 se tratan como cadenas que son diferentes.