PROCEDURA: pokud je nove vkladane cislo vetsi, nez minule, misto INSERT udela UPDATE. – MySQL – Fórum – Programujte.com

Ahoj, rad bych si napsal proceduru, na vkladani aktualizaci uptime.

Pokud je vkladany cas novejsi, nez prechozi udela update posledniho zaznamu. Pokud je starsi, vytvori novy zaznam. Mam to na logovani uptime. Zatim bych rad pro info vypsal po zavolani procedury informaci.

DROP TABLE IF EXISTS test;
CREATE TABLE test (
  id int(11) NOT NULL AUTO_INCREMENT,
  uptime bigint NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;



DELIMITER $$

DROP PROCEDURE IF EXISTS p_insert_new_uptime $$
CREATE PROCEDURE p_insert_new_uptime (
	IN new_uptime LONG
	)
COMMENT 'Pokud je nove vkladane cislo vetsi, nez minule, misto INSERT udela UPDATE.'
BEGIN
	SELECT id, uptime INTO @old_id, @old_uptime
	FROM test
	ORDER BY id DESC
	LIMIT 1;
	
	IF @old_uptime <= new_uptime THEN
		SELECT CONCAT("UPDATE: [old_uptime=", CAST(@old_uptime AS CHAR), "], [new_uptime=", CAST(new_uptime AS CHAR), "]") AS INFO;
		UPDATE test SET uptime=new_uptime WHERE id=@old_id;
	ELSE
		SELECT CONCAT("INSERT: [old_uptime=", CAST(@old_uptime AS CHAR), "], [new_uptime=", CAST(new_uptime AS CHAR), "]") AS INFO;
		INSERT INTO test (uptime) VALUES (new_uptime);
	END IF;
END$$

Kdyz zavolam:

CALL p_insert_new_uptime(UNIX_TIMESTAMP());

vypise se: 

UPDATE: [old_uptime=1459769615], [new_uptime=14597...

pokud ho volam treba po 10s znova, prepise puvodni radek, coz je spravne. Jenze i uplne prvni vlozeni do prazdne tabulky vypise UPDATE: [...

Pokud zavolam: 

CALL p_insert_new_uptime(123);

vlozi se novy radek, ale stale se vypise UPDATE: [...

Muzete mi prosim poradit, co delam spatne?

Pouzivam MySQL 5.5.47 na Debianu 8.

Super, ze si tu naprosto nejdulezitejsi cast logu vyhodil:

vlozi se novy radek, ale stale se vypise UPDATE: [...

Kazdopadne moje podezreni zni jako uvaha:

"jake hodnoty budou v old_uptime a old_id, kdyz se select into neprovede, protoze tam nic neni?"

Overit se to neda, jelikoz si ty hodnoty tak trochu vyteckoval, jak je zvyrazneno v citaci.

#2 KIIV
Nevyteckoval, phpmyadmin mi je useknul a dal tri tecky.

Ted jsem zkusil terminaloveho mysql klienta a vse funguje vyborne: 

mysql> CALL p_insert_new_uptime(UNIX_TIMESTAMP());
+----------------------------------------------------------+
| INFO                                                     |
+----------------------------------------------------------+
| UPDATE: [old_uptime=1459771579], [new_uptime=1459771585] |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> CALL p_insert_new_uptime(UNIX_TIMESTAMP());
+----------------------------------------------------------+
| INFO                                                     |
+----------------------------------------------------------+
| UPDATE: [old_uptime=1459771585], [new_uptime=1459771590] |
+----------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> CALL p_insert_new_uptime(123);
+---------------------------------------------------+
| INFO                                              |
+---------------------------------------------------+
| INSERT: [old_uptime=1459771590], [new_uptime=123] |
+---------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> CALL p_insert_new_uptime(UNIX_TIMESTAMP());
+---------------------------------------------------+
| INFO                                              |
+---------------------------------------------------+
| UPDATE: [old_uptime=123], [new_uptime=1459771606] |
+---------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.02 sec)

mysql> CALL p_insert_new_uptime(147);
+---------------------------------------------------+
| INFO                                              |
+---------------------------------------------------+
| INSERT: [old_uptime=1459771606], [new_uptime=147] |
+---------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

mysql> CALL p_insert_new_uptime(148);
+--------------------------------------------+
| INFO                                       |
+--------------------------------------------+
| UPDATE: [old_uptime=147], [new_uptime=148] |
+--------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> CALL p_insert_new_uptime(UNIX_TIMESTAMP());
+---------------------------------------------------+
| INFO                                              |
+---------------------------------------------------+
| UPDATE: [old_uptime=148], [new_uptime=1459771633] |
+---------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 1 row affected (0.02 sec)

mysql> 

Mohlo za to spatne zobrazeni v phpmyadminu asi. Prikazy jsem volal 100% stejne.

Omlouvam se za zbytecny dotaz, priste zkusim rovnou textoveho klienta.

Kdyz uz jsem dotaz vytvoril, muzete se nekdo prosim podivat na moji proceduru? Je takhle v poradku, nebo delam neco spatne/nevhodne? (Jeste pridam sloupec timestampu, ktery se zavola pouze pri insertu.)

#3 tux.martin
jede to asi proto, ze si uz mel v tabulce data. Jak se to zachova, kdyz bude uplne prazdna?

#4 KIIV
Uplne stejne, pred vkladanim jsem spustil: 

DROP TABLE IF EXISTS test;
CREATE TABLE test (...

DELIMITER $$
DROP PROCEDURE IF EXISTS p_insert_new_uptime $$
CREATE PROCEDURE p_insert_new_uptime (...

Mel bych to nejak osetrit?

#1 tux.martin

Pokud je vkladany cas novejsi, nez prechozi udela update posledniho zaznamu.

Vkládaný čas je přece vždy novější - čas nemůže běžet pozpátku.

#6 Kit
Mam to na logovani uptime zarizeni. Pokud zarizeni bezi, posila svuj uptime, ktery se zvetsuje. Je to pocet sekund od zapnuti.

Po restartu je cislo mensi, takze potrebuji vytvorit novy zaznam.

Jak uz jsem psal, v tabulce chybi par sloupcu.