Diferencias entre MySQL y SQLite (y alguna referencia a PostgreSQL)

Aunque el lenguaje SQL está normalizado, existen diferencias entre los distintos sistemas gestores bases de datos, por lo que algunas consultas no dan los mismos resultados en cada una de ellas.

En las soluciones de los ejercicios incluidos en estos apuntes se utiliza tanto MySQL como SQLite como sistemas gestores de bases de datos, por lo que se comentan aquí algunas de las diferencias entre ellas.

Nombre de las bases de datos

En MySQL el nombre de la base de datos no puede contener guiones (-) ni puntos (.), pero en SQLite sí.

A partir del curso 2017/18, como en estos apuntes se utilizan guiones (-) en los nombres de los ficheros, los nombres de las bases de datos SQLite utilizan guiones (ya que se guardan en ficheros). Los nombres de las bases de datos en MySQL utilizan guiones bajos (_).

Longitud de los campos

Si se guarda un dato más largo de la longitud del campo definida al crear la tabla, MySQL trunca la cadena, pero SQLite no. Por ejemplo, si se crea una tabla con un campo nombre VARCHAR (5) y se guarda la cadena abcdefghij, en MySQL se guardará la cadena abcde, mientras que en SQLite se guardará la cadena abcdefghij.

En las soluciones de los ejercicios incluidos en estos apuntes se ha intentado corregir esta diferencia validando los datos recibidos, de manera que si un dato recibido es más largo que el tamaño definido en la base de datos se muestra un aviso al usuario y no se guarda el dato.

Para calcular la longitud de las cadenas, se debe utilizar la función mb_strlen($cadena, "UTF-8") que tiene en cuenta los caracteres que ocupan más de un byte en UTF-8 (por ejemplo, las vocales acentuadas).

Mayúsculas, minúsculas y acentos

Al comparar si dos cadenas son idénticas, (WHERE campo=dato) SQLite sí que distingue entre mayúsculas y minúsculas o entre vocales acentuadas o sin acentuar, es decir, que si se busca "pepe", devolvería únicamente "pepe", no "Pepe" o "pepé".

Al comparar si dos cadenas son similares, (WHERE campo LIKE dato) SQLite no distingue entre mayúsculas y minúsculas aunque sí que lo hace entre vocales acentuadas o sin acentuar, es decir, que si se busca "pepe", devolvería únicamente "pepe" o "Pepe", pero no "pepé".

Dependiendo del tipo de cotejamiento elegido al crear la base de datos o al realizar las consultas, MySQL distingue o no entre mayúsculas y minúsculas o entre vocales acentuadas o sin acentuar:

// EJEMPLOS DE CONSULTAS CON COLLATE
$consultaCreaDb = "CREATE DATABASE $dbDb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci";                               // NO distingue

$consultaCreaDb = "CREATE DATABASE $dbDb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_bin";                                      // SÍ distingue

$consulta = "SELECT * FROM $dbTabla
    WHERE nombre LIKE :nombre COLLATE utf8mb4_unicode_ci
    AND apellidos LIKE :apellidos COLLATE utf8mb4_unicode_ci"; // NO distingue

$consulta = "SELECT * FROM $dbTabla
    WHERE nombre LIKE :nombre COLLATE utf8mb4_bin
    AND apellidos LIKE :apellidos COLLATE utf8mb4_bin";        // SÍ distingue

En las soluciones de los ejercicios incluidos en estos apuntes no se ha intentado corregir esta diferencia, por lo que dependiendo de la base de datos utilizada, los programas funcionarán de distinta manera. Por ejemplo, si existe un registro "pepe", si la base de datos es MySQL no se podrá añadir un registro "Pepe", pero si es SQLite sí.

Por completar

Fechas vacías o incompletas

SQLite no dispone de un tipo de datos DATE. En SQLite, las fechas se guardan como cadenas de texto. Al insertar una fecha vacía, SQLite guarda una cadena vacía.

MySQL sí que dispone de un tipo de datos DATE. Y al insertar una fecha vacía, MySQL guarda la cadena "0000-00-00".

En las soluciones de los ejercicios incluidos en estos apuntes se ha intentado corregir esta diferencia, guardando el valor "0000-00-00" cuando se quiere guardar una fecha no definida.

Como SQLite guarda cadenas de texto, en SQLITE se pueden guardar fechas parcialmente definidas (por ejemplo, si no se conoce el mes o el día concreto se puede guardar el valor 00 en esas posiciones). Pero para poder guardar fechas parcialmente definidas en MySQL hay que activar el modo ALLOW_INVALID_DATES. Al realizar la conexión con la base de datos habría que ejecutar la orden siguiente:

$pdo = new PDO(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD);
$pdo->exec("set session sql_mode='ALLOW_INVALID_DATES'");

En las soluciones de los ejercicios incluidos en estos apuntes no se ha intentado corregir esta diferencia, por lo que los campos de fecha sólo incluirán fechas válidas o la fecha vacía "0000-00-00".

El siguiente ejemplo muestra la diferencia de comportamiento entre SQLite y MySQL (desgraciadamente, el servidor en el que se encuentran estos apuntes no permite trabajar con MySQL, aunque se puede descargar el código fuente del ejemplo y probarlo en su ordenador).

Enlace a ejemplo

Campo autonumérico en PostgreSQL

Si se desea crear un campo autonumérico como clave primaria, en SQLite y en MySQL podríamos utilizar las siguientes sintaxis:

// SQLITE
id INTEGER PRIMARY KEY
// MySQL
id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY

En PostgreSQL podríamos utilizar las siguientes sintaxis:

// POSTGRESQL (estilo antiguo, anterior a PostgreSQL 10)
id SERIAL PRIMARY KEY,
// POSTGRESQL (estilo moderno, desde PostgreSQL 10)
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

La diferencia entre las dos últimas sintaxis es que BY DEFAULT permite que la consulta de inserción establezca el campo, mientras que ALWAYS no lo permite (daría error).

En PostgreSQL podemos encontrarnos con un problema cuando insertamos uno o varios registros que incluyen el valor del campo id y a continuación insertamos un registro sin incluir el valor del campo id, ya que se produce un error. El motivo es que PostgreSQL no actualiza el valor del contador interno del campo, por lo que si insertamos un registro con el primer valor libre (es decir, el siguiente al último), al insertar un registro sin incluir el campo id, PostgreSQL aumenta el contador y le asigna el valor que ya hemos utilizado. Curiosamente, si repetimos la inserción sin incluir el campo id como PostgreSQL sigue aumentando el valor de contador, habrá un momento en que el intento tenga éxito, porque habremos llegado a un valor que no ha sido utilizado todavía.

Parece que la solución es pedirle a PostgreSQL que actualice el contador interno después de haber insertado un registro sin incluir el campo id, mediante esta consulta:

En PostgreSQL podríamos utilizar las siguientes sintaxis:

SELECT setval(pg_get_serial_sequence('tabla', 'id'), coalesce(max(id),0) + 1, false) FROM tabla
SELECT setval(pg_get_serial_sequence('tabla', 'id'), max(id)) FROM tabla

Referencias: