Biblioteca Online : ¡La Suscripción ENI por 9,90 € el primer mes!, con el código PRIMER9. Pulse aquí
¡Acceso ilimitado 24/7 a todos nuestros libros y vídeos! Descubra la Biblioteca Online ENI. Pulse aquí
  1. Libros
  2. PHP y MySQL
  3. Utilizar las funciones MySQL
Extrait - PHP y MySQL Domine el desarrollo de un sitio web dinámico e interactivo (5ª edición)
Extractos del libro
PHP y MySQL Domine el desarrollo de un sitio web dinámico e interactivo (5ª edición) Volver a la página de compra del libro

Utilizar las funciones MySQL

Introducción

Como ya hemos visto, podemos utilizar expresiones en las diferentes cláusulas de las sentencias SQL. Una expresión puede escribirse utilizando columnas, expresiones literales, operadores y funciones SQL.

En este capítulo, presentaremos las funciones SQL utilizadas más a menudo. No mencionaremos todas las funciones existentes (¡hay más de 250!). Tampoco examinaremos sistemáticamente todas las opciones posibles de una función. Para saber más, consulte la documentación de SQL.

La sintaxis general de una función es la siguiente:

nombre_función([argumento][,...]) 

argumento puede ser cualquier expresión cuyo valor se pase como parámetro a la función; argumento también puede invocar otras funciones.

Recuerde: salvo indicación contraria, una expresión que contiene NULL da un resultado NULL.

Funciones de control

En este apartado veremos las funciones siguientes:

IF

Función del tipo «si, entonces, si no» basada en una condición.

IFNULL

Función del tipo «si, entonces, si no» basada en la nulidad de una expresión. 

NULLIF

Devuelve NULL si dos expresiones son iguales.

CASE

Estructura de control condicional de tipo «si, entonces, si no» (generalización de la función IF).

IF

Sintaxis

IF(condición,valor_si_verdadero,valor_si_falso) 

Si la expresión condición es verdadera (TRUE), la función devuelve la expresión valor_si_verdadero; si no (condición = FALSE o NULL), devuelve la expresión valor_si_falso.

Ejemplo

mysql> SELECT  
    ->   titulo,  
    ->   anio_publicacion,  
    ->   IF(anio_publicacion < 2019,'Antiguo','Reciente') antiguedad  
    -> FROM libro  
    -> WHERE id_coleccion = 1;  
+--------------------+------------------+------------+  
| titulo             | anio_publicacion | antiguedad |  
+--------------------+------------------+------------+  
| PHP 7              |             2018 | Antiguo    |  
| PHP 8              |             2021 | Reciente   |  
| Oracle 12c         |             2014 | Antiguo    |  
| Oracle 19c         |             2021 | Reciente   |  
| PHP y MySQL        |             2019 | Reciente   |  
| SAP BusinessObjects|             2021...

Funciones de comparación

En este apartado veremos las funciones siguientes:

LEAST

El valor más pequeño de una lista de valores.

GREATEST

El valor más grande de una lista de valores.

COALESCE

La primera expresión no NULL de una lista de expresiones.

LEAST - GREATEST

Sintaxis

LEAST(expresión1,expresión2[,...]) 
GREATEST(expresión1,expresión2[,...]) 

Las funciones LEAST y GREATEST devuelven respectivamente el valor más pequeño y el más grande de una lista de expresiones.

Ejemplo

mysql> -- Cálculo de un descuento 
mysql> -- de un 5% limitado a 1,5 
mysql> SELECT 
    ->   nombre, 
    ->   precio_siniva, 
    ->   LEAST(ROUND(precio_siniva*5/100,2),1.5) descuento 
    -> FROM coleccion; 
+--------------------------+---------------+-----------+ 
| nombre                   | precio_siniva | descuento | 
+--------------------------+---------------+-----------+ 
| Recursos Informáticos    |         28.48 |      1.42 | 
| Open IT                  |          6.66 |      0.33 | 
| Prácticas Técnicas...

Funciones numéricas

En este apartado veremos las siguientes funciones:

ABS

Valor absoluto de un número.

CEILING,CEIL

Entero más pequeño no inferior a un número.

DIV

Resultado de la división entera de dos números.

FLOOR

Entero más grande no superior a un número.

MOD, %

Resto de la división entera de dos números.

RAND

Número aleatorio superior o igual a 0 y estrictamente inferior a 1.

ROUND

Número redondeado con la precisión solicitada.

TRUNCATE

Número truncado con la precisión solicitada.

En una SELECT, una división por cero da como resultado NULL y genera una alerta. En una actualización (INSERT, UPDATE), una división por cero genera un error si el modo ERROR_FOR_DIVISION_BY_ZERO está activado y asociado al modo estricto (véase la sección El modo SQL del servidor, en el capítulo Introducción a MySQL).

ABS

Sintaxis

ABS(número) 

La función ABS devuelve el valor absoluto de un número.

CEILING - CEIL

Sintaxis

CEIL(número) 

La función CEILING (o su equivalente CEIL) devuelve el entero más pequeño no inferior a un número.

Ejemplo

mysql> SELECT nombre,precio_siniva,CEIL(precio_siniva) FROM coleccion; 
+--------------------------+---------------+---------------------+  
| nombre                   | precio_siniva | CEIL(precio_siniva) |  
+--------------------------+---------------+---------------------+  
| Recursos Informáticos    |         28.48 |                  29 |  
| Open IT                |          6.66 |                   7 |  
| Prácticas Técnicas       |         25.71 |                  26 |  
| Pack Técnico             |         54.19 |                  55 |  
| Epsilon                  |         51.90 |              ...

Funciones de cadenas

En este apartado veremos las funciones siguientes:

CONCAT, CONCAT_WS

Concatenación de cadenas de caracteres.

INSTR

Posición de la primera coincidencia de una cadena dentro de otra cadena.

LEFT, RIGHT

n primeros o n últimos caracteres de una cadena.

LENGTH

Longitud de una cadena.

LOWER, UPPER

Cadena en minúsculas o en mayúsculas.

LPAD, RPAD

Cadena completada a la izquierda o a la derecha por una secuencia de caracteres hasta una cierta longitud.

LTRIM, RTRIM, TRIM

Eliminación de espacios (o de otros caracteres) al principio o al final de la cadena.

REPEAT, SPACE

Cadena construida repitiendo una secuencia de caracteres un cierto número de veces.

REPLACE

Sustitución de todas las coincidencias de una cadena por otra.

SUBSTRING, SUBSTR, SUBSTRING_INDEX

Parte de una cadena.

Recuerde: solo las cadenas de caracteres «binarios» tienen en cuenta las mayúsculas/minúsculas.

CONCAT - CONCAT_WS

Sintaxis

CONCAT(cadena1,cadena2[,...]) 
CONCAT_WS(separador,cadena1,cadena2[,...]) 

La función CONCAT devuelve una cadena de caracteres que concatena todos sus argumentos.

La función CONCAT_WS es una variante de la función CONCAT. El primer argumento es una cadena que se utiliza como separador en la concatenación de otros argumentos.

Ejemplo

mysql> SELECT CONCAT(nombre,' ',apellidos) FROM autor;  
+------------------------------+  
| CONCAT(apellidos,' ',nombre) |  
+------------------------------+  
| Alain BOUCARD                | 
| Stéphane COMBAUDON           |  
| Yann GLINEUR                 |  
| Brice-Arnaud GUERIN          |  
| Olivier HEURTEL              |  
| Sébastien LARDIÈRE           |  
| Cédric NICOLAS               |  
| Thierry PETIBON              |  
+------------------------------+  
8 rows in set (0.00 sec)  
  
mysql> SELECT CONCAT_WS(',',apellidos,nombre) FROM autor;  
+---------------------------------+  
| CONCAT_WS(',',apellidos,nombre)...

Funciones de fechas

En este apartado veremos las funciones siguientes:

ADDDATE, DATE_ADD, DATE_SUB,SUBDATE

Añade o resta un intervalo de tiempo a una fecha.

CURDATE, CURRENT_DATE, UTC_DATE

Fecha actual.

CURTIME, CURRENT_TIME, UTC_TIME

Hora actual.

CURRENT_TIMESTAMP, NOW, LOCALTIME, LOCALTIMESTAMP, SYSDATE, UTC_TIMESTAMP

Fecha/hora actual.

DATE

Extrae la parte de fecha de una fecha/hora.

DATEDIFF

Diferencia en número de días entre dos fechas.

DAYOFWEEK, WEEKDAY, DAYOFMONTH, DAYOFYEAR

Extrae el número del día en la semana, en el mes o en el año de una fecha.

EXTRACT

Extrae un elemento de una fecha.

LAST_DAY

Último día del mes de una fecha.

MONTH

Número de mes de una fecha.

WEEK,WEEKOFYEAR

Número de semana de una fecha.

YEAR

Año de una fecha.

Con la excepción de SYSDATE, las funciones que devuelven la fecha o la hora «actual» se evalúan una vez al principio de la consulta; estas funciones devuelven, pues, la fecha o la hora de inicio de ejecución de la consulta. Por lo tanto, si una función de este tipo es invocada varias veces dentro de una consulta, siempre devuelve el mismo valor.

ADDDATE - DATE_ADD - DATE_SUB - SUBDATE

Sintaxis

ADDDATE(fecha,INTERVAL valor unidad) 
ADDDATE(fecha,número_días) 
DATE_ADD(fecha,INTERVAL valor unidad) 
DATE_SUB(fecha,INTERVAL valor unidad) 
SUBDATE(fecha,INTERVAL valor unidad) 
SUBDATE(fecha,número_días) 

Las funciones ADDDATE, DATE_ADD, DATE_SUB y SUBDATE devuelven una fecha después de añadir o restar un intervalo de tiempo.

En la sintaxis con la palabra clave INTERVAL, unidad especifica la unidad del intervalo (véase más abajo) y valor es una expresión que da el valor del intervalo que debe añadirse o restarse a la fecha (valor puede ser positivo o negativo).

Ejemplos de intervalo

Palabra clave

Valor

SECOND

Segundos

MINUTE

Minutos

HOUR

Horas

DAY

Días

WEEK

Semanas

MONTH

Meses

QUARTER

Trimestres

YEAR

Años

MINUTE_SECOND

Minutos y segundos en formato ‘m:s’

HOUR_SECOND

Horas, minutos y segundos en formato ‘h:m:s’

HOUR_MINUTE

Horas y minutos en formato ‘h:m’

DAY_SECOND

Días, horas, minutos y segundos en formato ‘d h:m:s’

DAY_MINUTE

Días, minutos y segundos en formato ‘d m:s’

DAY_HOUR

Días y horas en formato ‘d h’

YEAR_MONTH...

Funciones de conversión y de formato

En este apartado veremos las funciones siguientes:

BINARY

Conversión de una cadena en cadena binaria.

CAST,CONVERT

Conversión de un dato de un tipo en otro.

DATE_FORMAT

Da formato a una fecha.

FORMAT

Da formato a un número.

STR_TO_DATE

Conversión de una cadena en fecha.

BINARY

Sintaxis

BINARY cadena 

El operador BINARY convierte una cadena en cadena binaria.

Este operador queda obsoleto a partir de la versión 8.0.27 y se eliminará en una versión posterior. En su lugar, se recomienda utilizar la función CAST(... AS BINARY) que se presenta a continuación.

Ejemplo

mysql> -- Búsqueda no sensible a las mayúsculas/minúsculas 
mysql> SELECT precio_siniva FROM coleccion WHERE nombre = 'EPSILON'; 
+---------------+ 
| precio_siniva | 
+---------------+ 
|         51.90 | 
+---------------+ 
1 row in set (0.00 sec) 
 
mysql> -- Búsqueda sensible a las mayúsculas/minúsculas 
mysql> SELECT precio_siniva FROM coleccion WHERE nombre =  
BINARY 'EPSILON'; 
Empty set (0.00 sec) 

CAST - CONVERT

Sintaxis

CAST(expresión AS tipo) 
CONVERT(expresión,tipo) 

Las funciones CAST y CONVERT convierten una expresión de un tipo cualquiera en otro tipo.

tipo puede ser uno de los siguientes valores (lista no exhaustiva):

BINARY[(n)]

Cadena binaria (si es necesario, limitada a n octetos).

CHAR[(n)]

Cadena binaria (si es necesario, limitada a n caracteres).

DATE

Fecha.

DATETIME

Fecha/hora.

DECIMAL [(n[,d)]

Número decimal de coma fija. n indica el número de cifras significativas (10 por defecto, 65 como máximo) y d el número de cifras que hay después de la coma (0 por defecto, 30 como máximo).

DOUBLE

Número de coma flotante con precisión doble. Añadido en la versión 8.0.17.

FLOAT [(p)]

Número de coma flotante. p indica la precisión en bits. MySQL utiliza esta precisión para determinar el tipo que se emplea: FLOAT entre 0 y 24; DOUBLE entre 25 y 53. Añadido en la versión 8.0.17.

SIGNED [INTEGER]

Entero con signo de tipo BIGINT.

TIME

Hora.

UNSIGNED [INTEGER]

Entero sin signo de tipo BIGINT.

YEAR

Año (ver la documentación para las reglas de conver-sión en función...

Funciones de sistema

En este apartado veremos las funciones siguientes:

CURRENT_USER, SESSION_USER, SYSTEM_USER, USER

Usuario actual.

DATABASE, SCHEMA

Base de datos actual.

FOUND_ROWS

Número de filas devueltas por la última sentencia SELECT.

LAST_INSERT_ID

Valor automáticamente generado por una columna de tipo AUTO_INCREMENT en el último INSERT.

ROW_COUNT

Número de filas actualizadas por la última sentencia INSERT, UPDATE o DELETE.

VERSION

Versión de MySQL.

CURRENT_USER - SESSION_USER - SYSTEM_USER - USER

Sintaxis

CURRENT_USER() 
USER() 
SESSION_USER() 
SYSTEM_USER() 

La función CURRENT_USER devuelve el nombre del usuario y el nombre del equipo de la sesión actual, bajo la forma usuario@máquina.

La función USER devuelve el nombre del usuario y el nombre del equipo especificados en la conexión al servidor MySQL, bajo la forma usuario@máquina. Las funciones SESSION_USER y SYSTEM_USER son equivalentes a la función USER.

El resultado de las dos funciones puede ser diferente. Por ejemplo, si un cliente ha sido identificado por el servidor como usuario anónimo, la función CURRENT_USER devolverá un nombre de usuario vacío, mientras que la función USER devuelve el nombre realmente especificado en la cadena de conexión.

Ejemplo

[root@xampp ~]# mysql -u root 
... 
mysql> SELECT CURRENT_USER(),USER(); 
+----------------+----------------+ ...

Funciones de cifrado y de compresión

En este apartado veremos las funciones siguientes:

AES_ENCRYPT, AES_DECRYPT

Cifrar/descifrar datos utilizando el algoritmo AES.

COMPRESS, UNCOMPRESS

Comprimir/descomprimir datos.

MD5, SHA1, SHA, SHA2

Suma de comprobación de una cadena.

PASSWORD

Contraseña cifrada.

Las funciones de cifrado y de descompresión devuelven cadenas binarias; para el almacenamiento en la base de este tipo de datos, es recomendable utilizar una columna de tipo BLOB.

En las herramientas como el cliente mysql, las cadenas binarias se muestran con una notación hexadecimal cuando la opción cliente --binary-as-hex está activa. Esta opción apareció en la versión 8.0.2 y está activada por defecto desde la versión 8.0.19. Cuando esta opción está activa, es posible utilizar las funciones de conversión CAST o CONVERT para mostrar una cadena binaria como una cadena de caracteres.

En el cliente mysql, el comando status permite ver si la opción está activa o no:

mysql> status;  
--------------  
mysql  Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)  
...  Binary data as:        Hexadecimal  
... 

Si la opción no está activa, no aparece la línea Binary data as.

Para desactivar la opción al ejecutar la herramienta mysql, puede añadir --binary-as-hex=off en la línea de comandos (mysql --binary-as-hex=off) o añadir la directiva binary-as-hex = off en un archivo de configuración.

AES_ENCRYPT - AES_DECRYPT

Sintaxis

AES_ENCRYPT(cadena,clave) 
AES_DECRYPT(cadena,clave) 

Las funciones AES_ENCRYPT y AES_DECRYPT cifran y descifran una cadena por medio del algoritmo AES (Advanced Encryption Standard) con una clave de 128 bits por defecto (véase la documentación para usar...

Funciones de agregación

Las funciones de agregación se distinguen porque devuelven una fila de resultado por grupo de filas de entrada.

Estas funciones se utilizan la mayor parte de las veces en las consultas que agrupan los datos (utilización del cláusula GROUP BY, véase el capítulo Técnicas avanzadas con MySQL - Agrupar los datos).

Si estas funciones se utilizan en una consulta que no realiza agrupamiento de datos, lo que hacen es agrupar todas las filas: la función devuelve una sola fila como resultado. En ese caso, la cláusula SELECT de la consulta solo debe contener expresiones que utilicen una función de agregado.

En este apartado veremos las funciones siguientes:

MIN,MAX

Mínimo o máximo.

SUM

Suma.

AVG

Media.

COUNT

Número.

Para todas esas funciones, los valores NULL son ignorados; la presencia de un valor NULL en el cálculo no da un resultado NULL.

MIN - MAX

Sintaxis

MIN(expresión) 
MAX(expresión) 

Las funciones MIN y MAX devuelven respectivamente el mínimo y el máximo de todos los valores de expresión.

Ejemplo

mysql> SELECT MIN(numero_paginas),MAX(numero_paginas) 
     -> FROM libro WHERE id_coleccion = 1; 
+---------------------+---------------------+ 
| MIN(numero_paginas) | MAX(numero_paginas) | 
+---------------------+---------------------+ 
|                 515...