Colección de consultas u operaciones útiles o interesantes de MySQL.

Buscar registros cuyo campo campo1 comience un numero:

SELECT * 
FROM tabla
WHERE campo1 regexp '^[0-9]+'

Buscar registros cuyo campo campo1 comience por alguno de los números indicados:

SELECT * 
FROM tabla
WHERE campo1 regexp '^(15001|15002)+'

Por ejemplo, si tenemos:

Y queremos obtener el siguiente resultado:

Usaremos la función GROUP_CONCAT:

SELECT   
    email, 
    GROUP_CONCAT(servicio_web ORDER BY servicio_web ASC SEPARATOR ', ') 
FROM mitabla
GROUP BY email

Si queremos ver qué valores están duplicados en cierto campo, podemos hacer la siguiente consulta:

SELECT 
    campo1, 
    COUNT(*) c 
FROM mitabla 
GROUP BY campo1
HAVING c > 1

Nos devolverá los valores de campo1 que se repiten (de 1 a más veces)

• Finding duplicate values in MySQL (Stack Overflow)

Pasar de marca de tiempo UNIX a formato ISO:

SELECT FROM_UNIXTIME(fecha) as fecha_iso

Salida:

fecha_iso
---------
2020-08-12 00:35:40

Para convertir una fecha en tiempo UNIX:

SELECT UNIX_TIMESTAMP('2020-08-01 10:00')

Para convertir UTC en cierta zona horaria:

SELECT CONVERT_TZ(fecha_en_utc, 'UTC', 'Europe/Madrid') AS fecha_madrid
FROM tabla

Convertir segundos a formato de tiempo (tipo de dato TIME):

SELECT SEC_TO_TIME(3600)
-- Devolverá '01:00:00'

Convertir un string a tipo fecha:

SELECT str_to_date('12/12/2022 9:04:12', '%d/%m/%Y %k:%i:%s')

Si partimos de un string como 6/25/2024 2:25:42 AM, para pasarlo a formato fecha:

SELECT str_to_date('6/25/2024 2:25:42 AM', '%c/%e/%Y %l:%i:%s %p')

• %c: número del mes sin cero inicial (del 1 al 12)
• %e: número del día del mes sin cero inicial (del 1 al 31)
• %Y: número del año
• %l: hora sin cero inicial (de 1 a 12)
• %i: minutos (de 00 a 59)
• %s: segundos (de 00 a 59)
• %p: AM o PM.

Documentación oficial de formateadores de fecha admitidos en MySQL

Fecha de hoy:

SELECT CURDATE()

Fecha de ayer:

SELECT SUBDATE(CURRENT_DATE, 1)

Hace dos semanas:

select DATE_SUB(curdate(), INTERVAL 2 WEEK)

Lunes de la semana actual:

SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) as fecha

Lunes de la semana pasada:

SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY) AS fecha

Agrupar por semanas:

SELECT 
  fecha,
  DATE_FORMAT(fecha, "%x-%v") AS ano_semana
FROM tabla
GROUP BY ano_semana
ORDER BY fecha

• %x: año de la semana donde el lunes es el primer día de la semana. Se usa con %v.
• %v: número de la semana (de 01 a 53) donde el lunes es el primer día de la semana. Se usa con %x.

Sumar/restart tiempo:

-- Añadir 1 día a la fecha
SELECT DATE_ADD(fecha, INTERVAL 1 DAY)
 
-- Restar 1 día a la fecha
SELECT DATE_SUB(fecha, INTERVAL 1 DAY)

Para restar tiempo de una expresión de tiempo o fecha+tiempo:

SELECT SUBTIME('12:35:00', '1:30:00');
 
// 11:05:00

• SUBTIME() Examples – MySQL

Para ordenar, dejando los valores NULL al final:

SELECT * FROM mitabla
WHERE ...
ORDER BY 
    CASE 
        WHEN mifecha IS NULL THEN 1 
        ELSE 0 
     END, 
     mifecha

Diferencia entre fechas:

SELECT TIMEDIFF(fecha_fin, fecha_inicio)
FROM mitabla
 
-- fecha_fin: 2021-03-30 09:09:40
-- fecha_inicio: 2021-03-30 08:30:01
-- TIMEDIFF =  00:39:39

Diferencia en horas:

SELECT TIMESTAMPDIFF(HOUR, fecha_inicio, fecha_fin) 
FROM mitabla

Registros de hace más de una hora:

SELECT *
FROM tabla
 WHERE fecha_registro < DATE_SUB(NOW(), INTERVAL '1' HOUR)

Para que el nombre del día, mes y sus abreviaturas aparezcan en cierto idioma:

SET lc_time_names = 'es_ES'

SELECT DATE_FORMAT(fecha, '%b') as mes_abreviado
FROM tabla

Salida:

mes_abreviado
-------------
nov
abr
may
jun
dic

Si queremos ver el valor actual de esa variable:

SELECT @@lc_time_names;

• MySQL Server Locale Support

Extraer una dirección de e-mail de un campo de texto:

SELECT REGEXP_SUBSTR(campo_texto, '([a-zA-Z0-9._%+\-]+)@([a-zA-Z0-9.-]+)\.([a-zA-Z]{2,4})') AS email
FROM tabla

Si tenemos algún campo con saltos de línea (\n en sistemas UNIX y \r\n en Windows), podemos borrarlos con:

UPDATE test SET log = REPLACE(REPLACE(log, '\r', ''), '\n', '');

Si usamos la función CONCAT y algún campo puede tener valor NULL, la función devolverá NULL. Si este no es el comportamiento que queremos, debemos usar CONCAT_WS cuyo primer argumento es el separador:

SELECT CONCAT_WS(" ", campo1, campo2, campo3_que_puede_tener_null, campo4)
FROM tabla

• ¿Usar CONCAT o CONCAT_WS en MySQL?

SELECT table_schema "Base de datos",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "Tamaño (MB)" 
FROM information_schema.tables 
GROUP BY table_schema

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON base_datos.tabla TO 'newuser'@'localhost';
 
FLUSH PRIVILEGES;

SELECT user, host FROM mysql.user;

Si hacemos una inserción/modificación en una tabla donde una columna tiene el atributo AUTO_INCREMENT, podemos utilizar la función mysqli_insert_id() para obtener el ID generado por la consulta de inserción/modificación:

(...)
$query = "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
mysqli_query($link, $query);
 
printf ("Nuevo registro con el id %d.\n", mysqli_insert_id($link));

Si queremos establecer un valor para la clave que contiene AUTO_INCREMENT:

ALTER TABLE mitabla AUTO_INCREMENT=1001;

En las próximas inserciones, la columna con AUTO_INCREMENT se rellenará desde 1001 y siguientes.

INSERT INTO nombre_tabla
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);

Para duplicar una tabla a partir de otra (conservando índices y estructura) se hace en dos consultas pasos: primero haremos crearemos una tabla a partir de la que nos interesa copiar:

CREATE TABLE tabla_copia LIKE tabla_origen

Y ahora añadimos todo lo que había en la tabla origen a su copia:

INSERT INTO tabla_copia
SELECT *
FROM tabla_origen

UPDATE tabla_a_actualizar
INNER JOIN tabla_de_la_que_copiar ON (tabla_a_actualizar.valor = tabla_de_la_que_copiar.valor)
  SET tabla_a_actualizar.campo1 = tabla_de_la_que_copiar.campo1

• Update one MySQL table with values from another (Stack Overflow)

SELECT table_name 
FROM information_schema.tables
WHERE table_schema = '<BASE_DE_DATOS>'

DROP TABLE IF EXISTS <NOMBRE_TABLA>

Si queremos eliminar más de una a la vez:

DROP TABLE IF EXISTS <TABLA1>, <TABLA2>, <TABLA3>

Para ver el listado de campos de una tabla y su tipo:

DESCRIBE <NOMBRE_TABLA>

Si queremos obtener el listado de campos separados por coma:

-- Aumentamos la longitud máxima de 'group_concat' (por defecto, 1024 caracteres)
-- Esto solo se mantiene durante la sesión
SET SESSION group_concat_max_len = 1000000;
 
SELECT group_concat(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '<BASE_DATOS>'
  AND TABLE_NAME = '<NOMBRE_TABLA>';

• Get table column names in MySQL?

show full processlist;

Ejemplo de salida:

+---------+-----------+-------------------+-----------------------+---------+------+-------+-----------------------+
| Id      | User      | Host              | db                    | Command | Time | State | Info                  |
+---------+-----------+-------------------+-----------------------+---------+------+-------+-----------------------+
| 1195431 | postventa | 10.0.42.185:52702 | tiempos_provision_krt | Sleep   | 3559 |       | NULL                  |
| 1195818 | postventa | localhost         | NULL                  | Query   |    0 | init  | show full processlist |
+---------+-----------+-------------------+-----------------------+---------+------+-------+-----------------------+

Primero buscamos los procesos en ejecución:

SHOW PROCESSLIST;

Y luego, cuando tengamos identificada la ID del proceso que nos interesa, lo detenemos:

KILL <ID_PROCESO>

• Eventos MySQL

Ejemplo de evento que elimina registros distintos de la fecha actual diariamente a las 00:01:

CREATE EVENT `eliminar_antiguas_tabla_incidencias_hoy`
  ON SCHEDULE
    EVERY 1 DAY
    STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 MINUTE) 
  ON COMPLETION PRESERVE
  ENABLE
  COMMENT 'Elimina incidencias antiguas de la tabla que guarda incidencias del día en curso'
  DO 
    DELETE FROM qep_tbook.incidencias_hoy WHERE DATE_FORMAT(fecha_creacion, '%Y-%m-%d') <> CURDATE()

Todos los eventos:

SHOW EVENTS;

ALTER EVENT <NOMBRE_EVENTO> DISABLE

DROP EVENT <NOMBRE_EVENTO>

Mostrar los eventos y la última vez que se ejecutaron:

SELECT EVENT_NAME, INTERVAL_VALUE, INTERVAL_FIELD, LAST_EXECUTED 
FROM INFORMATION_SCHEMA.events

Activar:

SET GLOBAL event_scheduler = ON;

Desactivar:

SET GLOBAL event_scheduler = OFF;

Consultar estado:

SHOW VARIABLES
WHERE VARIABLE_NAME = 'event_scheduler'