Funciones en MySQL

Ángel Monteagudo
 Error 1418

DELIMITER // Esta función da un error 1418. se debe a que tu

servidor tiene activado el logging binario (binary logging), pero la
función que estás tratando de crear no especifica una característica de
CREATE FUNCTION SalarioMensual(salarioAnual seguridad, como DETERMINISTIC, NO SQL, o READS SQL DATA. Esto es una
DECIMAL(10,2)) medida de seguridad en MySQL para garantizar que las réplicas sean
consistentes con el servidor maestro. En un entorno de replicación, las
RETURNS DECIMAL(10,2) acciones realizadas en el servidor maestro se registran en el log binario
y luego se replican en los servidores esclavos. Para que esta replicación
BEGIN sea consistente y confiable, es crucial que las operaciones que se
replican sean predecibles y produzcan los mismos resultados en los
servidores esclavos que en el maestro. MySQL requiere que
RETURN salarioAnual / 12; especifiques explícitamente si la función es determinista o no cuando el
binary logging está habilitado. Esto se debe a que, en un entorno de
END // replicación, es crucial que las funciones produzcan resultados
consistentes en todos los nodos (servidores maestro y esclavos) para
DELIMITER ; evitar inconsistencias en los datos replicados. Como dice el manual:
“When you create a stored function, you must declare either
that it is deterministic or that it does not modify data.
Otherwise, it may be unsafe for data recovery or replication.
By default, for a CREATE FUNCTION statement to be
accepted, at least one of DETERMINISTIC, NO SQL, or
READS SQL DATA must be specified explicitly. Otherwise an
error occurs"
Binary Logging
Si los desactivas no se registran los cambios en la BD el log con lo cual no podrás
hacer una auditoría de cambios. También afecta a la recuperación ya que no
podrás volver a un estado anterior de la BD.

Los rollbacks locales seguirán funcionando pero en entornos distribuidos los

rollbacks ya no funcionarían.
 Solución al error 1418
Solución 1: le decimos a MySQL que confíe en los creadores
de funciones y que registre en el binlog las declaraciones CREATE Solución 2
FUNCTION sin verificar si son deterministas o no.
Hacer lo mismo que la solución 1 pero añadiendolo
en el fichero de configuración [Link]:
SET GLOBAL log_bin_trust_function_creators = 1;
log_bin_trust_function_creators = 1;

Solución 3: añadiendo antes de BEGIN una de estas 3 opciones:

DETERMINIST o
NO SQL o
READS SQL DATA
CREATE FUNCTION
SalarioMensual(salarioAnual DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN salarioAnual / 12;
END;
 Modificadores o atributos de la función

❖ DETERMINISTIC

❖ NO SQL

❖ READS SQL DATA

❖ MODIFIES SQL DATA

Función doble
DELIMITER //
CREATE function doble (x INT) returns INT
deterministic no sql
BEGIN
RETURN 2*x;
END //
DELIMITER ;

Llamamos a la función: select doble(5);

Tipos de función: DETERMINISTIC

CREATE FUNCTION Cuadrado(numero INT)

RETURNS INT
DETERMINISTIC
BEGIN
RETURN numero * numero;
END;
Tipos de función: NOT DETERMINISTIC
La función rand() de MySQL genera un número aleatorio entre 0 y 1. Ejemplo: select rand();
Podemos aprovechar esa función que ya viene definida en el sistema para hacer nosotros una función que genere un número aleatorio entre 0
y 100:

CREATE FUNCTION GenerarAleatorio()

RETURNS INT

NOT DETERMINISTIC

BEGIN

RETURN RAND() * 100;

END;
 Tipos de función: READS SQL DATA
Usa READS SQL DATA para funciones que leen datos de la base de datos pero no los modifican.
Vamos a crear una función para contar el número de empleados

versión 1 versión 2
CREATE FUNCTION contar_empleados()
CREATE FUNCTION contar_empleados() RETURNS INT
READS SQL DATA
BEGIN
RETURNS INT RETURN (SELECT COUNT(*) INTO contador FROM
empleado);
READS SQL DATA END;

BEGIN

DECLARE contador INT; versión 3

create function contador() returns int
SELECT COUNT(*) INTO contador FROM empleado; reads sql data
begin
declare num int;
RETURN contador; set num=(select count(id) from empleado);
return num;
END; end //
delimiter ;
 Tipos de función: READS SQL DATA
Usa READS SQL DATA para funciones que leen datos de la base de datos pero no los modifican.

delimiter //

CREATE FUNCTION ContarEmpleados(codDepart INT)

RETURNS INT

READS SQL DATA

BEGIN

DECLARE contador INT;

SELECT COUNT(*) INTO contador FROM empleado WHERE cod_dep = codDepart;

RETURN contador;

END //

delimiter ;

select ContarEmpleados (2);

 Tipos de función: NO SQL
Usa NO SQL cuando la función no utiliza sentencias SQL. Es típicamente para cálculos o
procesamiento de datos.

CREATE FUNCTION Suma (a INT, b INT)

RETURNS INT
NO SQL
BEGIN
RETURN a + b;
END;
Llamamos a la función: select suma(5,15)
 Tipos de función: MODIFIES SQL DATA
Usa MODIFIES SQL DATA para funciones que cambian los datos en la base de datos aunque generalmente se
recomienda usar procedimientos almacenados para modificaciones de datos ya que las funciones están diseñadas
para ser seguras desde el punto de vista de la replicación y del logging binario (este log registra todos los cambios en
la base de datos (como sentencias INSERT, UPDATE, DELETE) en un formato binario. Este log se utiliza luego para
replicar los cambios en los servidores esclavos o para recuperar el estado de la base de datos hasta un punto
específico en el tiempo.). Por lo tanto, se restringe que las funciones realicen cambios en los datos para asegurar la
predictibilidad y la coherencia en la replicación. (los procedimientos pueden comenzar y finalizar transacciones, lo cual
es crucial para operaciones que modifican datos, mientras que las funciones están más limitadas y generalmente se
ejecutan dentro del contexto de una transacción existente.

CREATE FUNCTION ActualizarSalario(idEmpleado INT, nuevoSalario DECIMAL(10,2))

RETURNS VARCHAR(255)
MODIFIES SQL DATA
BEGIN
UPDATE empleado SET salario = nuevoSalario WHERE id = idEmpleado;
RETURN 'Salario actualizado correctamente';
END;
 Combinando modificadores
Si deseas comunicar ambas características, es decir, que la función es DETERMINISTIC y también NO SQL, puedes declarar
ambas en la definición de la función. En el lenguaje SQL de MySQL, es perfectamente válido y a veces necesario especificar
más de un modificador para describir con precisión el comportamiento de una función.

Para la función SumarDosNumeros(), que es tanto determinista como no realiza operaciones SQL, puedes declararla así:

CREATE FUNCTION SumarDosNumeros(a INT, b INT)

RETURNS INT

DETERMINISTIC

NO SQL

BEGIN

RETURN a + b;

END;
¿Cómo va a usar el SGBD los modificadores para optimizar la consulta?

DETERMINISTIC
● Optimización de la Caché: Si una función es DETERMINISTIC, el SGBD sabe que puede cachear el resultado de la
función para un conjunto dado de parámetros de entrada y reutilizar este resultado para llamadas subsiguientes con
los mismos argumentos, mejorando así el rendimiento.
● Replicación Consistente: En un entorno replicado, saber que una función es determinista asegura que producirá el
mismo resultado en el servidor maestro y en los esclavos, lo que es crucial para mantener la coherencia de los
datos.
¿Cómo va a usar el SGBD los modificadores para optimizar la consulta?

NO SQL
● Evitar Controles Innecesarios: Al indicar que una función no ejecuta ninguna sentencia SQL, el SGBD no necesita
realizar comprobaciones adicionales para el acceso a la base de datos o la coherencia de la transacción, lo que
puede simplificar la ejecución de la función.
● Uso en Contextos Restringidos: Esta información es útil en contextos donde las operaciones de base de datos están
restringidas, permitiendo al SGBD ejecutar la función sin preocuparse por efectos secundarios en los datos.
¿Cómo va a usar el SGBD los modificadores para optimizar la consulta?

READS SQL DATA

● Planificación de la Ejecución: Al saber que una función solo lee datos (pero no los modifica), el SGBD puede
optimizar la ejecución de la función, especialmente en lo que respecta al locking (bloquear tabla o fila) y la visibilidad
de los datos, sin preocuparse por los posibles cambios en los datos.
¿Cómo va a usar el SGBD los modificadores para optimizar la consulta?

MODIFIES SQL DATA

● Gestión de Transacciones y Logging: Si una función modifica datos, el SGBD debe asegurarse de que estas modificaciones se
manejen correctamente en términos de transacciones y logging (proceso de registrar modificaciones de la BD en los logs del
sistema, lo cual es crucial para la replicación y la auditoría) , especialmente para la replicación(en configuraciones
maestro-esclavo, los cambios realizados en el servidor maestro deben replicarse en los servidores esclavos) y la
recuperación de datos (en caso de fallos del sistema o corrupción de datos, los logs pueden ser utilizados para restaurar la
base de datos a un estado anterior. Esto se conoce como recuperación hasta un punto en el tiempo (point-in-time recovery)).
● Estrategias de Locking: El SGBD puede aplicar estrategias de locking adecuadas para prevenir condiciones de carrera y
mantener la integridad de los datos.
Ejemplos prácticos sobre la BD empleados
Función 1: CalcularEdad

DELIMITER //
CREATE FUNCTION CalcularEdad(fechaNacimiento DATE)
RETURNS INT
DETERMINISTIC
BEGIN
RETURN TIMESTAMPDIFF(YEAR, fechaNacimiento, CURDATE());
END //
DELIMITER ;
Función 2: EsJefe

DELIMITER //
CREATE FUNCTION EsJefe(idEmpleado INT)
RETURNS BOOLEAN
READS SQL DATA
BEGIN
DECLARE esJefe BOOLEAN DEFAULT FALSE;
SELECT COUNT(*) INTO esJefe FROM departamento WHERE director = idEmpleado;
RETURN esJefe;
END //
DELIMITER ;

Como no pusimos si es o no determinista toma el valor por defecto que es: NOT
DETERMINISTIC
Función 3: SalarioMensual

DELIMITER //
CREATE FUNCTION SalarioMensual(salarioAnual DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
RETURN salarioAnual / 12;
END //
DELIMITER
Función 4: ContarEmpleadosDepartamento

DELIMITER //
CREATE FUNCTION ContarEmpleadosDepartamento(codigoDepartamento INT)
RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
DECLARE contador INT;
SELECT COUNT(*) INTO contador FROM empleado WHERE cod_dep = codigoDepartamento;
RETURN contador;
END //
DELIMITER ;
Función 5: PresupuestoPromedioDepartamentos

DELIMITER //
CREATE FUNCTION PresupuestoPromedioDepartamentos()
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE promedio DECIMAL(10,2);
SELECT AVG(presupuesto) INTO promedio FROM departamento;
RETURN promedio;
END //
DELIMITER ;
Función 6. Función que pasa de euros a dólares
DELIMITER //
CREATE function euro_to_dolar (x INT, tipo_cambio DECIMAL(3,2)) returns
FLOAT
deterministic no sql
BEGIN
RETURN x*tipo_cambio;
END //
DELIMITER ;

select euro_to_dolar(5, 1.10);