Trigger MySQL

ConejoLobo #1 Jun '18

Buenas a todos, y gracias de antebrazo por echarme una mano.

Tengo un sistema de captacion de datos que inserta una fila cada segundo en una tabla dos datos, (hora y temperatura)

Como en un par de dias la cosa se descontrola, tenia pensado limitarlo a un numero X de filas, pongamos 100,000 filas.

Se puede hacer un insert para que cuando llegue a la ultima fila, inserte el nuevo valor en la primera, sin borrar todo lo demas?

Asi siempre tendria almacenados los ultimos 100,000 valores.

Se os ocurre alguna manera mas práctica de hacerlo?

Muchas gracias de nuevo por vuestra ayuda !! <3

RaymaN #2 Jun '18 Sureño

Si insertas cada segundo solo hora y temperatura, tendrás 86400 registros como máximo. Pon la hora como clave primaria y haz el insert así

INSERT INTO table (time, temp)
VALUES ('{time}', {temp})
ON DUPLICATE KEY UPDATE temp = VALUES(temp);

1 1 respuesta

ConejoLobo #3 Jun '18

#2 Me gusta la idea, pero asi solo puedo registrar un día, que podría hacer para registrar una semana o un mes?

Por eso habia pensado en los triggers, al llegar a X fila... volver a empezar por la 1

2 respuestas

Maaarc #4 Jun '18 Penitente

#3 Si vuelves a empezar desde la fila uno, nunca tendrás una semana o un mes. Como un dia son las 86400, tendrás un dia y 6h tope.

1 respuesta

ConejoLobo #5 Jun '18

#4 No te entiendo, quiero decir q vuelvo a empezar en la fila 1 cuando llego a la 1000000 o a la 99999999999999, el numero de filas q yo elija en funcion de los días que quiero tener almacenados.

1 respuesta

Maaarc #6 Jun '18 Penitente

#5

Asi siempre tendria almacenados los ultimos 100,000 valores.
— ConejoLobo

Ese valor lo he sacado de tu post, lo que quiero decir es que a la que empiezes a machacar valores empezarás a perder los datos antiguos y no los podrás recuperar. Si en tu tabla el valor más antiguo es de 2 dias, nunca podrás mirar los de hace un mes o de una semana como dices en #3.

Yo lo que haría sería plantearme si la variación de temperatura en un segundo es significativa, o si puedo reducir el número de registros a cada 5-10-15 sec, o hacer triggers i que solo pillen la temperatura cuando esta cambie o que solo la guarde si es un valor diferente al último. Reduciendo la velocidad a la que crece la BBDD y pudiendo reconstruir igualmente los datos a cada segundo aunque no tengas ese valor exacto.

Vamos que yo buscaría la forma de reducir el número de escrituras ampliando así la "vida" de las 100k de líneas o las que sean evitando tener muchas líneas iguales con una información que no me aporta nada y que puedo reconstruir sin problemas.

2 1 respuesta

ConejoLobo #7 Jun '18

#6 Todo lo que dices es muy interesante y lo voy a dar una vuelta, pero al final el problema es el mismo, llegara un momento en que la base de datos llegará al limite deseado, y ahi me gustaría comenzar a escribir por la primera fila, para asi tener al menos 1 dia, 1 semana, 1 mes, 1 año o lo que se desee.

RaymaN #8 Jun '18 Sureño

#3 mi respuesta te la doy porque hablas de hora, que yo entiendo de 00:00:00 a 23:59:59. Si ya hablas de fecha y hora pues no te vale mi solución. Igualmente, para tener solo 100k registros, haz esta consulta tras el insert normal:

DELETE FROM table
WHERE date < DATE_SUB(NOW(), INTERVAL 100000 SECOND)

eXtreM3 #9 Jun '18

Un claro

trigger before insert
delete where min value

es lo que buscas

pineda #10 Jun '18

otra solución posible es un circular buffer / sliding-window

1 respuesta

ConejoLobo #11 Jun '18

#10 Me puedes explicar eso? Creo que es exactamente lo que busco !!!!!

1 respuesta

pineda #12 Jun '18

#11 conceptualmente es una tabla en forma de anillo, conforme vas insertando filas vas machacando las anteriores. Esta es la teoría.
Implementaciones hay muchas distintas, mejores y peores. Incluso hay quien lo hace con particiones.

Realmente para lo que comentas, te bastaría con hacer un trigger after insert, donde borres todo lo que sea id < (nuevo_id - numMax)

O si no quieres borrar, cosa que a mi tampoco me gusta, llenar esa tabla con tantos registros como quieras, y tener una segunda tabla que te guarde el offset.
De esta forma, cuando quieras 'insertar' un nuevo dato, solo tendrías que hacer un update del registro cuyo id sea offset+1 (guardado en la segundas tabla), y incrementar este offset, aplicandole un %maxElements , para que si llegas al final, vuelva a empezar por el primero

pineda #13 Jun '18

de todos modos igual me meto donde no me llaman, pero por que no te planteas usar otra base de datos en lugar de mysql? una timeseries parece perfecta para el caso.
Echale un ojo a influxdb

1

Trigger MySQL

Usuarios habituales

Tags