mysql - online - phpmyadmin xampp

MySQL: ¿hace "Actualizar" un cambio permanente en una tabla?

Question

Entonces tengo dos tablas: "Caballos" y "Resultados". "Caballos" enumera una gran cantidad de información sobre cada caballo, incluyendo un lugar llamado "LTE", que se suma a un campo de "Ingresos" de la tabla "Resultados". "Resultados" enumeró todos los resultados de espectáculos de caballos recientes. Utilizo el siguiente código para calcular LTE -

UPDATE horses 
SET horses.LTE = ( SELECT SUM(results.earnings) 
FROM results WHERE horses.hname=results.hname )

Lo cual funciona de maravilla: actualiza la columna LTE. Sin embargo ... Tengo que ejecutar este código CADA vez que agregue nuevos datos a la tabla de "Resultados". Voy a agregar mes de datos para ... bueno, casi siempre.

No quiero tener que ejecutar este código todas las veces. ¿Hay alguna manera de hacer que el código sea "permanente", en un sentido que el campo LTE SABE que solo calcula cada vez que se agrega información nueva? ¿O MySQL no funciona de esta manera?

Aquí hay un vistazo a mis tablas con algunos datos en ellos.

Answer 1

MySQL no funciona de esa manera. Pero puede lograr dicha funcionalidad mediante el uso de desencadenantes . Por ejemplo, puede actualizar su suma cada vez que haya una inserción en la tabla respectiva (y / o actualizar), de esta manera siempre tendrá su suma ''en la memoria caché'' y puede volver a calcular si es necesario.

Si quieres un poco más de análisis sobre los cambios de suma, puedes seguir este patrón , creo que será útil

Answer 2

Puede usar un TRIGGER en INSERT , UPDATE y DELETE para actualizar su tabla:

-- trigger for INSERT (new rows on table result).
DELIMITER |
CREATE TRIGGER ins_result AFTER INSERT ON results
    FOR EACH ROW
    BEGIN
        UPDATE horses SET horses.LTE = (
            SELECT SUM(results.earnings) FROM results WHERE horses.hname = results.hname
        );
    END;
|

-- trigger for UPDATE (changed rows on table result).
CREATE TRIGGER upd_result AFTER UPDATE ON results
    FOR EACH ROW
    BEGIN
        UPDATE horses SET horses.LTE = (
            SELECT SUM(results.earnings) FROM results WHERE horses.hname = results.hname
        );
    END;
|

-- trigger for DELETE (removed rows on table result).
CREATE TRIGGER del_result AFTER DELETE ON results
    FOR EACH ROW
    BEGIN
        UPDATE horses SET horses.LTE = (
            SELECT SUM(results.earnings) FROM results WHERE horses.hname = results.hname
        );
    END;
|

Otra solución podría ser una VIEW :

CREATE VIEW v_horses AS 
    SELECT h.*, SUM(r.earnings) AS ''LTE'' 
    FROM horses h INNER JOIN results r ON h.hname = r.hname
    GROUP BY h.hname;

Con la VIEW creada, puede obtener la información sobre los caballos con la siguiente consulta:

SELECT * FROM v_horses;

En su caso, no utiliza una columna DECIMAL . Entonces, debe convertir la columna VARCHAR para SUM las ganancias. Entonces, en su caso, debe usar la siguiente VIEW :

CREATE VIEW v_horses AS 
    SELECT h.*, SUM(CAST(REPLACE(REPLACE(r.earnings, '','', ''''), ''$'', '''') AS DECIMAL)) AS ''LTE'' 
    FROM horses h INNER JOIN results r ON h.hname = r.hname 
    GROUP BY h.hname;

-- for a specific year (like 2017)
CREATE VIEW v_horses AS 
    SELECT h.*, SUM(CAST(REPLACE(REPLACE(r.earnings, '','', ''''), ''$'', '''') AS DECIMAL)) AS ''LTE'' 
    FROM horses h INNER JOIN results r ON h.hname = r.hname
    WHERE DATE_FORMAT(STR_TO_DATE(r.`Date`, ''%c/%e/%y''), ''%Y'') = 2017
    GROUP BY h.hname;

-- grouped by year (so you can use WHERE on the VIEW):
CREATE VIEW v_horses AS 
    SELECT h.*, SUM(CAST(REPLACE(REPLACE(r.earnings, '','', ''''), ''$'', '''') AS DECIMAL)) AS ''LTE'',
        DATE_FORMAT(STR_TO_DATE(r.`Date`, ''%c/%e/%y''), ''%Y'') AS ''year''
    FROM horses h INNER JOIN results r ON h.hname = r.hname
    GROUP BY h.hname, DATE_FORMAT(STR_TO_DATE(r.`Date`, ''%c/%e/%y''), ''%Y'');

Nota: En caso de utilizar esta VIEW , debe eliminar la columna LTE de la tabla horses .