Valores por defecto por tipo de dato en MySQL

El valor por defecto para un campo de una tabla se indica luego del tipo de dato mediante

DEFAULT <valor>

donde valor en la mayoría de los casos debe ser una constante. Digo en la mayoría porque si el campo es un TIMESTAMP (o un DATETIME desde Mysql 5.6), entonces podemos utilizar CURRENT_TIMESTAMP como valor por defecto.

Se dice que un valor por defecto es explícito si queda indicado mediante un DEFAULT en la definición del campo.

Antes de empezar les aclaro que debemos hacer un análisis independiente para comprender el funcionamiento en un campo de tipo TIMESTAMP.

Para los restantes, si un campo no tiene definido un valor por defecto, entonces Mysql lo determina de la siguiente manera:

  • Si el campo soporta el valor NULL, se lo define con un DEFAULT NULL explícito.
  • Si el campo se define como NOT NULL (NO soporta el valor NULL), depende de la versión de Mysql:

Para ver los valores por defecto explícitos de los campos de una tabla podemos utilizar:

SHOW CREATE TABLE <NOMBRE_TABLA>

 

Agregar y modificar registros en Mysql >= 5.02

Cuando se intenta hacer un INSERT o REPLACE sin un valor para el campo, o un UPDATE con NULL, Mysql trata el caso según el modo:

  • Si está activado el modo estricto y
    • la tabla es transaccional: se genera un error y se revierte la acción
    • la tabla no es transaccional: se genera un error pero si sucede en la segunda fila o posteriores de una sentencia que afecta a varias filas múltiple, las previas se habrán insertado!
  • Si NO está activado el modo estricto: Mysql establece el valor implícito según el tipo de dato del campo

Valores implícitos según el tipo de campo

  • Textos:
    • ENUM: el primer valor
    • BLOB: no puede tener un valor por defecto
    • TEXT: no puede tener un valor por defecto
    • Cadenas de caracteres (variantes de CHAR y BINARY): ” cadena vacía
  • Numérico (variantes de INT, FLOAT, DOUBLE, DECIMAL, NUMERIC): 0
    • Excepción si son INT o FLOAT con autoincrement: próximo valor en la secuencia
  • Fechas y horas: el valor “cero” según el tipo
    • DATE: 0000-00-00
    • TIME: 00:00:00
    • DATETIME: 0000-00-00 00:00:00
    • YEAR: 0000
    • Mysql >= 5.6.6 con explicit_defaults_for_timestamp activado (ver funcionamiento de TIMESTAMP):
      • TIMESTAMP: 0000-00-00 00:00:00

Funcionamiento de TIMESTAMP

Hasta Mysql 5.6.6 el tipo de dato TIMESTAMP se comporta de una manera completamente distinta al resto:

  • Un campo TIMESTAMP que no fue declarado explícitamente como NULL ni con un DEFAULT, se establece como su valor por defecto:
    • si es el primer campo TIMESTAMP de la tabla: CURRENT_TIMESTAMP (fecha y hora actuales)
    • si es un campo TIMESTAMP, pero no el primero: 0000-00-00 00:00:00

Desde Myqsl 5.6.6 en adelante, este uso de TIMESTAMP con un DEFAULT implícito está desaprobado y temporalmente existirá un warning para activar la variable explicit_defaults_for_timestamp de manera que el comportamiento sea:

  • Un campo TIMESTAMP:
    • no funciona con un DEFAULT CURRENT_TIMESTAMP a menos que se lo haya indicado explícitamente
    • declarado como NOT NULL y sin un DEFAULT explícito, se comporta como el resto de los tipos de dato (SIN un DEFAULT explícito)

En futuras versiones se mantendrá sólo este comportamiento y desaparecerá la variable mencionada.

Anuncios

Modo estricto de MySQL

A partir de la versión 5, existe la posibilidad de ejecutar Mysql en modo estricto. Básicamente lo que controla es cómo Mysql debe responder cuando ciertas sentencias como INSERT o UPADATE intentan establecer valores inválidos o no indican valores requeridos.

Cuándo aplica el modo estricto

Consultando el modo actual de Mysql podremos saber si aplica o no el modo estricto:

  • Si el resultado contiene “TRADITIONAL”, “STRICT_ALL_TABLES” o “STRICT_TRANS_TABLES”, el modo estricto está activado. Según cual de los dos valores esté establecido le comportamiento final difiere un poco (no entraremos en detalles ahora).

Por ejemplo, si la salida es:

STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

entonces, el modo estricto está activado porque aparece el modo “STRICT_TRANS_TABLES”.

  • Si por el contrario, no aparece ninguno de estos dos valores en el resultado, entonces el modo estricto no está establecido.

Detalle del funcionamiento del modo estricto

  • Para tablas transaccionales (ej. InnoDB): se genera un error por valores faltantes o inválidos en una sentencia que cambia datos. La misma es abortada y se vuelven atrás los cambios realizados.
  • Para tablas no transaccionales (ej.MyISAM):
    • Si el valor incorrecto ocurre en la primer fila a ser insertada o actualizada, la sentencia se aborta y la tabla no se modifica.
    • Si la sentencia inserta o modifica varias filas y el valor incorrecto ocurre en la segunda fila o posterior, depende del modo estricto que esté en uso:
      • En STRICT_ALL_TABLES: Mysql retorna un error e ignora el resto de las filas! Como las filas anteriores han sido insertadas o actualizadas, el resultado es un cambio parcial. Para evitar esto, se recomienda utilizar sentencias que afecten a una sola línea y puedan ser abortadas sin modificar la tabla.
      • En STRICT_TRANS_TABLE:Mysql genera un alerta y continúa procesando la sentencia
        • Si el valor es inválido, se convierte al valor válido más cercano según el tipo de columna y se inserta.
        • Si el valor falta, se inserta el “valor por defecto para el tipo de dato de la columna”.

También afecta el manejo de la división por cero, fechas cero y fechas con ceros:

  • Mysql < 5.7.4: trabajando en conjunción con los modos NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO
  • Mysql >= 5.7.4: tomando el comportamiento de los tres modos indicados arriba

Conocer el modo actual de MySQL (y cambiarlo)

El modo actual de Mysql se encuentra en la variable de sistema sql_mode.

Conocer el modo actual

Para ver el modo en el que Mysql está trabajando, debemos consultar la variable de sistema sql_mode. Para ello, abrimos una conexión al servidor mysql y ejecutamos:

SELECT @@global.sql_mode; #Modo global, a nivel servidor

SELECT @@session.sql_mode; #Modo de la sesión en curso

Estas son algunas de las formas de consultar el valor de una variable de sistema.

Ejemplo:

mysql> SELECT @@global.sql_mode;
+----------------------------------------------------------------+
| @@global.sql_mode                                              |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Cambiar el modo

Si deseamos cambiar el modo por otro debemos modificar la variable de sistema sql_mode. Antes de proceder, necesitamos conocer los modos disponibles según la versión de Mysql con la que estemos trabajando. A continuación abrimos una conexión al servidor mysql y ejecutamos:

SET @@global.sql_mode = 'MODOS_SEPARADOS_POR_COMA';

SET @@session.sql_mode = 'MODOS_SEPARADOS_POR_COMA';

Estas son algunas de las formas de establecer el valor de una variable de sistema.

Ejemplo:

mysql> SET @@global.sql_mode= 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

Luego, reiniciamos la conexión con el servidor mysql.