MySQL Funciones: cadena, numérica, definida por el usuario, almacenada

¿Qué son MySQL ¿Funciones?

MySQL puede hacer mucho más que simplemente almacenar y recuperar datos. También puede realizar manipulaciones en los datos antes de recuperarlo o guardarlo. Ahí es donde MySQL Aquí entran en juego las funciones. Las funciones son simplemente fragmentos de código que realizan una operación y devuelven un resultado. Algunas funciones aceptan parámetros, mientras que otras no.

Veamos brevemente un ejemplo. Por defecto, MySQL guarda los tipos de datos de fecha en el formato “AAAA-MM-DD”. Supongamos que hemos creado una aplicación y nuestros usuarios quieren que la fecha se devuelva en el formato “DD-MM-AAAA”. Podemos usar el MySQL función integrada DATE_FORMAT para lograr esto. DATE_FORMAT es una de las funciones más utilizadas en MySQLy lo analizaremos en detalle más adelante en esta lección.

Cualquiera que sea su tipo, una función siempre devuelve un único valor, puede aceptar cero o más parámetros dentro de paréntesis, y puede utilizarse en cualquier lugar donde se permita una expresión — en una lista SELECT, una cláusula WHERE o una cláusula ORDER BY.

Por qué usar MySQL ¿Funciones?

Ahora que sabemos qué es una función, la siguiente pregunta es por qué deberíamos trasladar este trabajo a la base de datos.

Como muestra el diagrama anterior, una función toma un valor de entrada, aplica la lógica una sola vez dentro del motor de la base de datos y devuelve un único resultado a cada aplicación que lo solicita.

Los programadores podrían estar pensando: "¿Para qué molestarse con...?" MySQL ¿Funciones? El mismo efecto se puede lograr con un lenguaje de programación o de scripting. Es cierto que podemos lograrlo escribiendo un procedimiento en el programa de aplicación.

Volviendo a nuestro ejemplo de FECHA, para que nuestros usuarios obtengan los datos en el formato deseado, la capa de negocio tendría que realizar el procesamiento necesario por sí misma.

Esto se convierte en un problema cuando la aplicación tiene que integrarse con otros sistemas. cuando usamos MySQL funciones como DATE_FORMAT, esa funcionalidad está integrada en la base de datos, y cualquier aplicación que necesite los datos los obtiene en el formato requerido. Reduce la necesidad de rehacer el trabajo en la lógica de negocio y disminuye las inconsistencias de los datos..

Otra razón para considerar MySQL Su función es ayudar a reducir el tráfico de red en aplicaciones cliente/servidor.La capa de negocio solo necesita llamar a la función almacenada, sin tener que transferir las filas sin procesar a través de la red para manipularlas. En promedio, el uso de funciones puede mejorar considerablemente el rendimiento general del sistema.

Tipos de MySQL Funciones

Una vez aclarados el "qué" y el "por qué", podemos ahora analizar las tres familias de funciones. MySQL Ofrece: funciones integradas, funciones almacenadas y funciones definidas por el usuario.

Funciones integradas

MySQL viene incluido con una serie de funciones integradas, funciones que ya están implementadas en el MySQL servidor. Nos permiten realizar muchos tipos de manipulación de los datos y se dividen en los siguientes grupos de uso común.

• Funciones de cadena – operar en tipos de datos de cadena
• Funciones numéricas – operar con tipos de datos numéricos
• Funciones de fecha – operar con tipos de datos de fecha
• Funciones agregadas – operar con todos los tipos de datos anteriores y producir conjuntos de resultados resumidos.
• Otras funciones – MySQL También admite otros tipos de funciones integradas, pero en esta lección nos limitaremos a los grupos mencionados anteriormente.

Analicemos ahora en detalle cada uno de los grupos mencionados anteriormente. Explicaremos las funciones más utilizadas con nuestra base de datos de ejemplo “Myflixdb”.

Funciones de cadena

Las funciones de cadena operan sobre valores de texto. En nuestra tabla de películas, los títulos se almacenan utilizando una combinación de letras mayúsculas y minúsculas. Supongamos que queremos una consulta que devuelva los títulos en mayúsculas. La función "UCASE" toma una cadena como parámetro y convierte cada letra a mayúscula, como se muestra en el siguiente script.

SELECT `movie_id`, `title`, UCASE(`title`) AS `upper_case_title` FROM `movies`;

AQUÍ

• UCase(`título`) es la función integrada que toma el título como parámetro y lo devuelve en mayúsculas.
• COMO `título_en_mayúsculas` Esto asigna un alias a la columna calculada, de modo que el conjunto de resultados contiene un encabezado legible en lugar de la expresión sin procesar.

Ejecutando el script anterior en MySQL La comprobación realizada en Workbench con Myflixdb nos proporciona los resultados que se muestran a continuación.

Dos compañeros merecen ser recordados junto con UCase: LCASO convierte una cadena a minúsculas y CONCAT une dos o más cadenas en una sola. Para obtener la lista completa, consulte la MySQL Referencia a la función de cadena.

Funciones numéricas

Como se mencionó anteriormente, las funciones numéricas operan sobre tipos de datos numéricos. También podemos realizar cálculos matemáticos con datos numéricos directamente en nuestras sentencias SQL.

Operadores aritméticos

MySQL Admite los siguientes operadores aritméticos, que se pueden utilizar para realizar cálculos en sentencias SQL.

A continuación se muestran ejemplos de cada operador.

División entera (DIV) — DIV descarta la parte fraccionaria y devuelve solo el número entero.

SELECT 23 DIV 6;

Al ejecutar el script anterior obtenemos 3.

Operador de división (/) — a diferencia de DIV, el operador de división conserva la parte decimal del resultado.

SELECT 23 / 6;

Al ejecutar el script anterior obtenemos 3.8333.

Subtracoperador de ción (-)

SELECT 23 - 6;

Al ejecutar el script anterior obtenemos 17.

Operador de suma (+)

SELECT 23 + 6;

Al ejecutar el script anterior obtenemos 29.

Operador de multiplicación (*)

SELECT 23 * 6 AS `multiplication_result`;

Resultado:

Operador módulo (% o MOD)

El operador módulo divide N entre M y nos da el resto. Veamos un ejemplo con el operador módulo, usando los mismos valores que en los ejemplos anteriores.

SELECT 23 % 6;
-- OR, equivalently:
SELECT 23 MOD 6;

Al ejecutar cualquiera de los scripts obtenemos 5.

Veamos ahora algunas de las funciones numéricas comunes en MySQL.

FLOOR Esta función elimina los decimales de un número y lo redondea al número entero más cercano. El siguiente script muestra cómo usarlo.

SELECT FLOOR(23 / 6) AS `floor_result`;

Resultado:

REDONDA Esta función redondea un número al entero más cercano. Dado que 23 / 6 da como resultado 3.8333, REDONDEAR devuelve 4, mientras que FLOOR devuelve 3; no son intercambiables.

SELECT ROUND(23 / 6) AS `round_result`;

Resultado:

RAND Esta función genera un número aleatorio. Su valor cambia cada vez que se llama a la función. El siguiente script muestra cómo usarla.

SELECT RAND() AS `random_result`;

Funciones de fecha

Las funciones de fecha operan con tipos de datos de fecha y fecha y hora. DATE_FORMAT es la función que resuelve el problema de “AAAA-MM-DD frente a DD-MM-AAAA” descrito en la introducción.

FORMATO DE FECHA El script toma dos parámetros: el valor de fecha a formatear y una cadena de formato creada a partir de marcadores de posición. El siguiente script devuelve cada fecha de lanzamiento en el formato día-mes-año que solicitaron nuestros usuarios.

SELECT `title`, DATE_FORMAT(`date_released`, '%d-%m-%Y') AS `formatted_date`
FROM `movies`;

A continuación se enumeran los marcadores de posición de formato más utilizados.

Otras tres funciones de fecha aparecen constantemente en el trabajo diario:

• CURDATE() Devuelve la fecha actual como AAAA-MM-DD.
• AHORA() devuelve la fecha actual y en las transacciones.
• DATEDIFF(d1, d2) Devuelve el número de días entre dos fechas, que es la base de cualquier informe de alquileres vencidos.

Para ver la lista completa, consulte la MySQL Referencia de la función de fecha y hora.

Funciones almacenadas

Las funciones integradas cubren los casos más comunes. Cuando una regla de negocio es más específica, escribimos la nuestra propia, y para eso sirven las funciones almacenadas.

Las funciones almacenadas se comportan igual que las funciones integradas, con la diferencia de que usted mismo las define. Una vez creada, una función almacenada puede utilizarse en sentencias SQL como cualquier otra función. La sintaxis básica se muestra a continuación.

CREATE FUNCTION sf_name ([parameter(s)])
RETURNS data_type
[DETERMINISTIC | NOT DETERMINISTIC]
BEGIN
    -- procedural statements
END

AQUÍ

• “CREAR FUNCIÓN sf_name ([parámetro(s)])” es obligatorio y le dice al MySQL El servidor creará una función llamada `sf_name` con parámetros opcionales definidos dentro de los paréntesis.
• “DEVUELVE tipo_de_dato” Es obligatorio y especifica el tipo de datos que devuelve la función.
• “DETERMINISTA” Declara que la función devuelve el mismo valor siempre que se le proporcionen los mismos argumentos. “NO DETERMINISTA” declara lo contrario.
• “COMIENZA… FIN” Envuelve el código procedimental que ejecuta la función.

Supongamos que queremos saber qué películas alquiladas han vencido su fecha de devolución. Podemos crear una función almacenada que acepte la fecha de devolución como parámetro y la compare con la fecha actual del servidor. Si la fecha actual es posterior a la fecha de devolución, la película está vencida y devolvemos "Sí"; de lo contrario, devolvemos "No".

DELIMITER |
CREATE FUNCTION sf_past_movie_return_date (return_date DATE)
RETURNS VARCHAR(3)
NOT DETERMINISTIC
BEGIN
    DECLARE sf_value VARCHAR(3);
    IF CURDATE() > return_date THEN
        SET sf_value = 'Yes';
    ELSEIF CURDATE() <= return_date THEN
        SET sf_value = 'No';
    END IF;
    RETURN sf_value;
END|
DELIMITER ;

⚠️ Advertencia: no etiquete esta función como DETERMINISTA. El cuerpo llama a CURDATE(), por lo que el mismo argumento puede devolver "No" hoy y "Sí" mañana. Declarar una función dependiente del tiempo como DETERMINISTIC engaña al optimizador y no es seguro para la replicación basada en sentencias. NO DETERMINISTA cada vez que el cuerpo llama a CURDATE(), NOW() o RAND().

Al ejecutar el script anterior se crea la función almacenada `sf_past_movie_return_date`. Ahora vamos a probarla.

SELECT `movie_id`, `membership_number`, `return_date`, CURDATE(),
       sf_past_movie_return_date(`return_date`) AS `is_overdue`
FROM `movierentals`;

Ejecutando el script anterior en MySQL Workbench contra myflixdb nos da los siguientes resultados.

Observe las dos filas NULL. Cuando `return_date` es NULL, ambas comparaciones se evalúan como NULL en lugar de VERDADERO o FALSO, por lo que ninguna de las ramas IF se ejecuta y la función devuelve NULL, el resultado esperado, ya que una película no devuelta no tiene fecha de devolución con la que compararla.

Funciones definidas por el usuario

Cuando SQL por sí solo no es lo suficientemente rápido, MySQL permite una tercera opción. Las funciones definidas por el usuario (UDF) se escriben en un lenguaje compilado como C or C++Integradas en una biblioteca compartida y registradas en el servidor, una vez añadidas, se invocan como cualquier otra función. Dado que una UDF se ejecuta como código nativo dentro del proceso del servidor, resulta idónea para cálculos complejos; sin embargo, un error en ella puede provocar el bloqueo del servidor, por lo que las UDF se utilizan con mucha menos frecuencia que las funciones almacenadas.

Funciones integradas, almacenadas o definidas por el usuario: ¿Cuál debería usar?

Las tres familias devuelven un único valor y pueden invocarse desde cualquier sentencia SQL, pero difieren en quién las escribe, dónde se ejecutan y el nivel de riesgo que conllevan. La siguiente tabla resume esas diferencias.

Como regla general, empieza con una función integrada. Si ninguna se ajusta a tus necesidades, escribe una función almacenada para que la regla quede centralizada. Utiliza una función definida por el usuario solo cuando una función almacenada sea notablemente lenta.

Preguntas Frecuentes