====== Snippets MySQL ======

Colección de consultas u operaciones útiles o interesantes de [[informatica:bases_de_datos:mysql|MySQL]].
===== Filtrar con expresiones regulares =====

Buscar registros cuyo campo ''campo1'' comience un numero:

<code mysql>
SELECT * 
FROM tabla
WHERE campo1 regexp '^[0-9]+'
</code>

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

<code mysql>
SELECT * 
FROM tabla
WHERE campo1 regexp '^(15001|15002)+'
</code>

===== Agrupar =====

==== Agrupar por un valor y concatenar los demás en una columna ====

Por ejemplo, si tenemos:

^ servicio_web  ^ email      ^
| 11111         | mail@mail.com   |
| 22222         | mail@mail.com   |
| 33333         | mail@mail.com   |
| 44444         | mail@mail.com   |

Y queremos obtener el siguiente resultado:

^ email         ^ servicio_web      ^
| mail@mail.com  | 11111, 22222, 33333, 44444  |

Usaremos la función [[https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat|GROUP_CONCAT]]:

<code mysql>
SELECT   
    email, 
    GROUP_CONCAT(servicio_web ORDER BY servicio_web ASC SEPARATOR ', ') 
FROM mitabla
GROUP BY email
</code>

==== Búsqueda de valores duplicados ====

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

<code mysql>
SELECT 
    campo1, 
    COUNT(*) c 
FROM mitabla 
GROUP BY campo1
HAVING c > 1
</code>

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

  * [[https://stackoverflow.com/questions/688549/finding-duplicate-values-in-mysql|Finding duplicate values in MySQL]] (Stack Overflow)
===== Fechas =====

==== Conversiones ====

Pasar de marca de tiempo UNIX a formato ISO:

<code mysql>
SELECT FROM_UNIXTIME(fecha) as fecha_iso
</code>

Salida:

<code>
fecha_iso
---------
2020-08-12 00:35:40
</code>

<WRAP center round info 60%>
El tiempo UNIX (Epoch Time) es el número de segundos transcurridos desde el 01/01/1970 (UTC). Por ejemplo, las 10:00 del 01/08/2020 UTC, serían 1596276000 en tiempo UNIX.
</WRAP>

Para convertir una fecha en tiempo UNIX:

<code mysql>
SELECT UNIX_TIMESTAMP('2020-08-01 10:00')
</code>

Para convertir UTC en cierta zona horaria:

<code mysql>
SELECT CONVERT_TZ(fecha_en_utc, 'UTC', 'Europe/Madrid') AS fecha_madrid
FROM tabla
</code>

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

<code mysql>
SELECT SEC_TO_TIME(3600)
-- Devolverá '01:00:00'
</code>

Convertir un string a tipo fecha:

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

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

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

  * ''%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.

[[https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format|Documentación oficial de formateadores de fecha admitidos en MySQL]]
==== Otros ====

Fecha de hoy:

<code mysql>
SELECT CURDATE()
</code>

<WRAP center round tip 60%>
También se puede utilizar la función ''CURRENT_DATE''
</WRAP>

Fecha de ayer:

<code mysql>
SELECT SUBDATE(CURRENT_DATE, 1)
</code>

Hace dos semanas:

<code mysql>
select DATE_SUB(curdate(), INTERVAL 2 WEEK)
</code>

Lunes de la semana actual:

<code mysql>
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) as fecha
</code>

Lunes de la semana pasada:

<code mysql>
SELECT DATE_SUB(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY) AS fecha
</code>

Agrupar por semanas:

<code mysql>
SELECT 
  fecha,
  DATE_FORMAT(fecha, "%x-%v") AS ano_semana
FROM tabla
GROUP BY ano_semana
ORDER BY fecha
</code>

  * ''%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''.

<WRAP center round important 60%>
Si se usa ''WEEK'', hay un problema con la última semana y primera del año. Si no es una semana completa, crea dos registros: una semana con los últimos días del año y otra semana con los primeros días del año siguiente. [[https://en.wikipedia.org/wiki/ISO_8601|Más información sobre la ISO 8601]].
</WRAP>

==== Operaciones ====

Sumar/restart tiempo:

<code mysql>
-- 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)
</code>

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

<code mysql>
SELECT SUBTIME('12:35:00', '1:30:00');

// 11:05:00
</code>

  * [[https://database.guide/subtime-examples-mysql/|SUBTIME() Examples – MySQL]]

Para ordenar, dejando los valores NULL al final:

<code mysql>
SELECT * FROM mitabla
WHERE ...
ORDER BY 
    CASE 
        WHEN mifecha IS NULL THEN 1 
        ELSE 0 
     END, 
     mifecha
</code>

Diferencia entre fechas:

<code mysql>
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
</code>


Diferencia en horas:

<code mysql>
SELECT TIMESTAMPDIFF(HOUR, fecha_inicio, fecha_fin) 
FROM mitabla
</code>
==== Comparaciones ====

Registros de hace más de una hora:

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

===== Idioma =====

==== Fechas ====

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

<code mysql>
SET lc_time_names = 'es_ES'
</code>

<code mysql>
SELECT DATE_FORMAT(fecha, '%b') as mes_abreviado
FROM tabla
</code>

Salida:

<code>
mes_abreviado
-------------
nov
abr
may
jun
dic
</code>

<WRAP center round important 60%>
El cambio durará hasta que finalice la sesión.
</WRAP>

Si queremos ver el valor actual de esa variable:

<code mysql>
SELECT @@lc_time_names;
</code>

  * [[https://dev.mysql.com/doc/refman/8.0/en/locale-support.html|MySQL Server Locale Support]]

===== Extracción de texto =====

==== E-mail ====

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

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

==== Eliminar saltos de línea ====

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

<code sql>
UPDATE test SET log = REPLACE(REPLACE(log, '\r', ''), '\n', '');
</code>

==== Concatenar campos que pueden ser NULL ====

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:

<code sql>
SELECT CONCAT_WS(" ", campo1, campo2, campo3_que_puede_tener_null, campo4)
FROM tabla
</code>

<WRAP center round info 60%>
CONCAT_WS viene de //Concatenate With Separator//.
</WRAP>

  * [[https://es.stackoverflow.com/questions/86721/usar-concat-o-concat-ws-en-mysql|¿Usar CONCAT o CONCAT_WS en MySQL?]]
===== Sistema =====

==== Tamaño bases de datos ====

<code 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
</code>

===== Usuarios =====

==== Crear usuario ====

<code mysql>
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
</code>

==== Dar permisos ====

<code mysql>
GRANT ALL PRIVILEGES ON base_datos.tabla TO 'newuser'@'localhost';

FLUSH PRIVILEGES;
</code>

==== Mostrar usuarios ====

<code mysql>
SELECT user, host FROM mysql.user;
</code>
===== Modificaciones =====

==== Último ID insertado ====

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:

<code php>
(...)
$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));
</code>

==== Modificar valor inicial AUTO_INCREMENT ====

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

<code mysql>
ALTER TABLE mitabla AUTO_INCREMENT=1001;
</code>

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

==== Insertar varios registros a la vez ====

<code mysql>
INSERT INTO nombre_tabla
    (a,b,c)
VALUES
    (1,2,3),
    (4,5,6),
    (7,8,9);
</code>
===== Tablas =====

==== Duplicar tabla ====

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:

<code sql>
CREATE TABLE tabla_copia LIKE tabla_origen
</code>

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

<code sql>
INSERT INTO tabla_copia
SELECT *
FROM tabla_origen
</code>

==== Modificar una tabla con valores de otra tabla ====

<code sql>
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
</code>

  * [[https://stackoverflow.com/questions/5727827/update-one-mysql-table-with-values-from-another|Update one MySQL table with values from another]] (Stack Overflow)

==== Listar tablas de una base de datos ====

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

==== Eliminar tabla ====

<code mysql>
DROP TABLE IF EXISTS <NOMBRE_TABLA>
</code>

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

<code mysql>
DROP TABLE IF EXISTS <TABLA1>, <TABLA2>, <TABLA3>
</code>

==== Obtener nombre columnas ====

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

<code mysql>
DESCRIBE <NOMBRE_TABLA>
</code>

Si queremos obtener el listado de campos separados por coma:

<code mysql>
-- 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>';
</code>

  * [[https://stackoverflow.com/a/1526722|Get table column names in MySQL?]]
===== Administración =====

==== Mostrar lista de procesos ====

<code>
show full processlist;
</code>

Ejemplo de salida:

<code>
+---------+-----------+-------------------+-----------------------+---------+------+-------+-----------------------+
| 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 |
+---------+-----------+-------------------+-----------------------+---------+------+-------+-----------------------+
</code>

==== Detener un proceso ====

Primero buscamos los procesos en ejecución:

<code mysql>
SHOW PROCESSLIST;
</code>

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

<code mysql>
KILL <ID_PROCESO>
</code>

===== Eventos =====

  * [[https://codigofacilito.com/articulos/eventos-mysql|Eventos MySQL]]
==== Crear ====

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

<code mysql>
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()
</code>

==== Mostrar ====

Todos los eventos:

<code mysql>
SHOW EVENTS;
</code>

==== Deshabilitar ====

<code mysql>
ALTER EVENT <NOMBRE_EVENTO> DISABLE
</code>

==== Eliminar ====

<code mysql>
DROP EVENT <NOMBRE_EVENTO>
</code>

==== Última ejecución ====

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

<code mysql>
SELECT EVENT_NAME, INTERVAL_VALUE, INTERVAL_FIELD, LAST_EXECUTED 
FROM INFORMATION_SCHEMA.events
</code>
==== Programador de eventos ====

Activar:

<code mysql>
SET GLOBAL event_scheduler = ON;
</code>

Desactivar:

<code mysql>
SET GLOBAL event_scheduler = OFF;
</code>

Consultar estado:

<code mysql>
SHOW VARIABLES
WHERE VARIABLE_NAME = 'event_scheduler'
</code>