ON DUPLICATE KEY ma in realtà non UPDATE mai

MarcoGrazia

Utente Attivo
15 Dic 2009
788
16
18
59
Udine
www.stilisticamente.com
Ciao, ho un semplice record con un id primary key auto increment e tanti campi che possono anche avere valori NULL.
Il record contiene gli indirizzi da scrivere sulle buste da inviare via posta.
La simpatica pagina che ho creato, simula una busta da lettera, in cui si inseriscono i dati e poi premendo invio si stampa la busta ( in tre formati! ). Bene!
Quando si preme il tasto INVIA in realtà si inserisce anche l'indirizzo nel database, così quando la prossima volta si deve inviare di nuovo allo stesso indirizzo non bisogna scriverlo da capo, ma con la funzione autocomplete() si richiama quell'indirizzo dal database. Bene!

In fatti fino a qui bene, il problema nasce però quando si invia l'indirizzo, dato che esso può essere preesistente, la funzione MySQL INSERT INTO è corredata da un bel ON DUPLICATE KEY UPDATE, per evitare duplicati, ma eventualmente anzi permettere modifiche ad un record scritto male.
Ma niente!
Ogni record viene sempre iscritto come fosse nuovo, e non va affatto bene.
La query è la seguente:
Codice:
INSERT INTO indirizzario 
( protocollo, titolo, intestazione, via, civico, citta, cap, provincia, sigla_nazionale, nazione ) 
VALUES ( :prot, :titolo, :intes, :via, :civico, :citta, :cap, :prov, :sigla, :naz ) 
ON DUPLICATE KEY UPDATE ID_indirizzi = LAST_INSERT_ID( ID_indirizzi ), 
protocollo = protocollo, titolo = titolo, intestazione = intestazione, via = via, civico = civico, citta = citta, cap = cap, provincia = provincia, sigla_nazionale = sigla_nazionale, nazione = nazione; --
Come si dovrebbe capire dai segna posto, sto usando le funzioni PDO per trasmettere i dati al server MySQL, ma a parte ciò la query funziona, almeno fino all'inserimento dei valori, poi nulla.
Ho provato anche a modificarla in un altro modo:
Codice:
INSERT INTO indirizzario 
( ID_indirizzi, protocollo, titolo, intestazione, via, civico, citta, cap, provincia, sigla_nazionale, nazione ) 
VALUES ( LAST_INSERT_ID( ID_indirizzi ), :prot, :titolo, :intes, :via, :civico, :citta, :cap, :prov, :sigla, :naz ) 
ON DUPLICATE KEY UPDATE  
protocollo = protocollo, titolo = titolo, intestazione = intestazione,  via = via, civico = civico, citta = citta, cap = cap, provincia =  provincia, sigla_nazionale = sigla_nazionale, nazione = nazione; --
Ma niente, non funziona.
Come scrivevo prima, l'unico campo con un primary key è l'ID_indirizzi, per il resto nessun campo è a unique.

Come la risolvo?
 

marino51

Utente Attivo
28 Feb 2013
2.931
166
63
Lombardia
da quello che leggo nelle "insert", posso pensare che hai creato una tabella che contiene il destinatario, che vorresti aggiornare se esistente, ma anche i dati di invio, esempio "protocollo" che non potrà mai essere duplicato (se capisco bene il suo contenuto)

poi dovresti sottostare alla clausola "ON DUPLICATE KEY" lavorando sulla chiave che non deve essere duplicata ( destinatario ? )

forse non ho capito nulla ma mi sembra che sia la logica a scricchiolare
 

MarcoGrazia

Utente Attivo
15 Dic 2009
788
16
18
59
Udine
www.stilisticamente.com
da quello che leggo nelle "insert", posso pensare che hai creato una tabella che contiene il destinatario, che vorresti aggiornare se esistente, ma anche i dati di invio, esempio "protocollo" che non potrà mai essere duplicato (se capisco bene il suo contenuto)

poi dovresti sottostare alla clausola "ON DUPLICATE KEY" lavorando sulla chiave che non deve essere duplicata ( destinatario ? )

forse non ho capito nulla ma mi sembra che sia la logica a scricchiolare
o_O
Non ci ho capito nulla :D

Allora, ho un insieme di record che identificano degli indirizzi, indirizzi da inserire su una busta da inviare via posta.
Quindi le parti "sicure" di qualsiasi indirizzo sono: nome del destinatario, via, città, nazione.
Il resto può cambiare ovviamente, ovvero il numero di protocollo cambia di sicuro dato che posso spedirti una fattura oggi ed una domani, stesso indirizzo ma non il protocollo.
Questo ovviamente mi fa capire anche a me, che la mia query primaria è sbagliata, perché nella clausola ON KEY... eccetera, ci sono dati che non importa verificare.
Il fatto è che proprio non ho compreso il meccanismo di questa clausola.
Pensavo di dover inserire i dati che dovevano cambiare, ma evidentemente sbaglio, forse se ho compreso la logica di ON KEY... è che deve verificare solo se le informazioni contenute in determinati campi, sono identici a quelle già inseriti, e in quel caso fa l'upgrade, altrimenti, inserisce un record da capo.
Facciamo una cosa, a questo punto metto qui la tabella, come l'ho creata.
Codice:
CREATE TABLE `indirizzario` (
  `ID_indirizzi` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `protocollo` varchar(25) DEFAULT NULL,
  `titolo` varchar(50) DEFAULT NULL COMMENT 'Signor, Signora, Dottor, Dottoressa, ecc.',
  `nome` varchar(100) DEFAULT NULL,
  `cognome` varchar(100) DEFAULT NULL,
  `ufficio` varchar(256) DEFAULT NULL,
  `intestazione` varchar(256) DEFAULT NULL,
  `via` varchar(256) DEFAULT NULL,
  `civico` varchar(10) DEFAULT NULL,
  `citta` varchar(256) DEFAULT NULL,
  `cap` varchar(10) DEFAULT NULL,
  `provincia` varchar(5) DEFAULT NULL,
  `sigla_nazionale` varchar(3) DEFAULT NULL,
  `nazione` varchar(256) DEFAULT 'italia',
  `data_registrazione` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID_indirizzi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Elenco non esaustivo di indirizzi postali';
 

Hormus

Utente Attivo
26 Giu 2020
83
10
8
ID_indirizzi verrà creato un id +1 (AUTOINCREMENT) dal valore dell'ultimo id.
La clausola funziona su primary key, unique etc.
Codice:
INSERT INTO indirizzario(ID_indirizzi,protocollo)
VALUES
   (1,1)
ON DUPLICATE KEY UPDATE protocollo = 10;
In questo esempio ho utilizzato solamente due row. Se non esiste ID_indirizzi con valore 1 verrà creato normalmente altrimenti modificato dalla clausola ID_indirizzi compreso ovviamente in 1 poiché la clausola imposta 10 per il nuovo valore di protocollo.
Se ometti ID_indirizzi nel campo della tabella indirizzario e come valore in VALUES crea automaticamente un id + 1.
Dunque la domanda adesso è la seguente , qualè quel dato (dove afferma essere lo stesso indirizzo) che dovrà essere aggiornato se presente? nome cognome via civico citta nazione (singolo o un insieme) ?
nb* se AUTOINCREMENT sarà modificato il puntatore sarà +1 per il successivo.
 
Ultima modifica:

MarcoGrazia

Utente Attivo
15 Dic 2009
788
16
18
59
Udine
www.stilisticamente.com
@Hormus, ogni campo può essere aggiornato, in realtà protocollo è uno dei quei dati che voglio eliminare ( non so perché ma vi piace tanto :p ) ma in realtà lo toglierò, perché come ho scritto nel precedente messaggio a @marino51 è un dato inutile; difficilmente manderai due buste con lo stesso protocollo allo stesso indirizzo.
In realtà gli unici campi interessanti saranno, l'indirizzo e il nome della persona e/o società.
Quindi ricapitolando, il record potrebbe divenire questo, che in realtà rispetto al primo manca solo del campo protocollo:
CREATE TABLE `indirizzario` (
`ID_indirizzi` int(10) unsigned NOT NULL AUTO_INCREMENT,
`titolo` varchar(50) DEFAULT NULL COMMENT 'Signor, Signora, Dottor, Dottoressa, ecc.',
`nome` varchar(100) DEFAULT NULL,
`cognome` varchar(100) DEFAULT NULL,
`ufficio` varchar(256) DEFAULT NULL,
`intestazione` varchar(256) DEFAULT NULL,
`via` varchar(256) DEFAULT NULL,
`civico` varchar(10) DEFAULT NULL,
`citta` varchar(256) DEFAULT NULL,
`cap` varchar(10) DEFAULT NULL,
`provincia` varchar(5) DEFAULT NULL,
`sigla_nazionale` varchar(3) DEFAULT NULL,
`nazione` varchar(256) DEFAULT 'italia',
`data_registrazione` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`ID_indirizzi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Elenco non esaustivo di indirizzi postali';
Tutti questi dati andrebbero necessariamente aggiornati, se si modifica un dato.
Ma ora mi chiedo @Hormus, tu scrivi che bisogna inserire ID_indirizzi, e l'ho fatto, e poi mi scrivi 1, 1 nel tuo esempio, ed io ho inserito LAST_INSERT_ID( ID_indirizzi ) tra i valori da passare, ma non ha funzionato.
D'altra parte non saprei in quale altro modo potrei conoscerne l'ID.
 

Hormus

Utente Attivo
26 Giu 2020
83
10
8
Non è rilevante il dato da cambiare ma il contesto d'uso ON DUPLICATE KEY UPDATE su una key (poiché questo è il modo più semplice per spiegare cosa fa).
C'è una logica, se conosco la key e voglio sovrascrivere il valore userò la clausola citata in precedenza altrimenti se non la conosco perché mai dovrei sovrascrivere?
Dovrai avere delle colonne fisse (o comunque una logica) effettuare una SQL SELECT per recuperare ID_indirizzi per una persona e solo dopo inserisci/sovrascrivi i dati (questo dipende dal risultato della SELECT precedente).
Discorso diverso se vuoi avere solamente un unico ID_indirizzi a questo scopo devi semplicemente recuperare l'ultimo ID_indirizzi inserito e aggiornarlo.
 

Hormus

Utente Attivo
26 Giu 2020
83
10
8
PHP:
<?php
header("Content-Type: text/plain; charset=utf-8");
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db');


/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}


if (!mysqli_set_charset($mysqli, 'utf8')) {
            die ("Error: Unable to set the character set.\n");
}
$sql = 'CREATE TABLE if NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
)';

if ($mysqli->query("$sql") === TRUE) {
    printf("Table devices successfully created.\n");
}
//1
$sql = "INSERT INTO devices(name)
VALUES('Switch 1'),('Switch 1'),('Switch 2')";

if ($mysqli->query("$sql") === TRUE) {
    printf("Insert table devices successfully created.\n");
}
//2
$sql = "INSERT INTO devices(name)
VALUES('Switch 3')
ON DUPLICATE KEY UPDATE name = 'Switch 5'";

if ($mysqli->query("$sql") === TRUE) {
    printf("Insert table devices successfully created.\n");
}
//3
$sql = "SELECT LAST_INSERT_ID()";
if ($result = $mysqli->query("$sql")) {
    if($result->num_rows) {
    while ($row = $result->fetch_row()) {
        printf ("%d\n", $row[0]);
    }
    }
    $result->close();
} else {
    printf("Error message: %s\n", $mysqli->error);
}
//4
$sql = "INSERT INTO devices(id,name)
VALUES(LAST_INSERT_ID(), 'Switch 3')
ON DUPLICATE KEY UPDATE name = 'Switch 6'";

if ($mysqli->query("$sql") === TRUE) {
    printf("Insert table devices successfully created.\n");
} else {
    printf("Error message: %s\n", $mysqli->error);
}

$sql = "SELECT id,name FROM devices LIMIT 10";

if ($result = $mysqli->query("$sql")) {
    printf("Select returned %d rows.\n", $result->num_rows);
    while ($row = $result->fetch_assoc()) {
        printf ("%d (%s)\n", $row["id"], $row["name"]);
    }
    /* free result set */
    $result->close();
}

$sql = "DROP TABLE devices";

if ($mysqli->query("$sql") === TRUE) {
    printf("Delete table devices successfully.\n");
}
/* close connection */
$mysqli->close();
LAST_INDERT_ID recupera l'ultimo AUTOINCREMENT.
Qui vedi 4 rows invece di 5 poiché la 4 verrà aggiornata poiché la 4 primary key è già esistente... se non avessi aggiunto un valore nel VALUES mysql avrebbe aggiunto l'ultimo id+1 :).
Non ho altro modo per spiegare se con il codice.
 
Ultima modifica:

marino51

Utente Attivo
28 Feb 2013
2.931
166
63
Lombardia
suggerisco l'esempio di un'anagrafica la cui chiave (non necessariamente primaria, ma univoca) sia il codice fiscale,

con un form inserisco il codice fiscale e tutti i dati anagrafici a corredo dello stesso

é sottinteso che i dati del form sono i più recenti,

eseguo la insert nella tabella,
se la chiave, codice fiscale, non esiste, viene inserito un nuovo record
se la chiave esiste, viene fatto l'update (ON DUPLICATE KEY) sul record corretto

la "logica" deve essere "robusta", se la chiave é costituita da "cognome nome" già il sistema presenta problemi,
se leggo la guida telefonica di Napoli, trovo 42 pagine di "Esposito Salvatore" é sicuro che "ON DUPLICATE KEY" fallisce,
trovando nel database un solo record con indirizzo casuale (ultimo inserito) a fronte di 42 pagine dell'elenco telefonico
 

MarcoGrazia

Utente Attivo
15 Dic 2009
788
16
18
59
Udine
www.stilisticamente.com
suggerisco l'esempio di un'anagrafica la cui chiave (non necessariamente primaria, ma univoca) sia il codice fiscale,

con un form inserisco il codice fiscale e tutti i dati anagrafici a corredo dello stesso

é sottinteso che i dati del form sono i più recenti,

eseguo la insert nella tabella,
se la chiave, codice fiscale, non esiste, viene inserito un nuovo record
se la chiave esiste, viene fatto l'update (ON DUPLICATE KEY) sul record corretto

la "logica" deve essere "robusta", se la chiave é costituita da "cognome nome" già il sistema presenta problemi,
se leggo la guida telefonica di Napoli, trovo 42 pagine di "Esposito Salvatore" é sicuro che "ON DUPLICATE KEY" fallisce,
trovando nel database un solo record con indirizzo casuale (ultimo inserito) a fronte di 42 pagine dell'elenco telefonico
Perfettamente d'accordo, ma in un "indirizzario" è difficile non avere dei duplicati.
Ad esempio via Roma che c'è in ogni città italiana; allora diventa estremamente problematico gestire il database.
francamente non ho idea di come fanno i navigatori :p

@Hormus scusa ma, non ho capito quante query dovrei fare per verificare se un dato è da iscrivere o da aggiornare.
 

Hormus

Utente Attivo
26 Giu 2020
83
10
8
Dipende dalla relazione con il dato, sappiamo per certo che può esserci un unico ID_indirizzi e normalmente prima effettui una query per prelevare quel medesimo ID_indirizzi.
Se poi vuoi prelevare ogni Mario Rossi e ovvio avrai più di un ID_indirizzi poiché contraddistingue un indirizzario diverso.
 

MarcoGrazia

Utente Attivo
15 Dic 2009
788
16
18
59
Udine
www.stilisticamente.com
Dipende dalla relazione con il dato, sappiamo per certo che può esserci un unico ID_indirizzi e normalmente prima effettui una query per prelevare quel medesimo ID_indirizzi.
Se poi vuoi prelevare ogni Mario Rossi e ovvio avrai più di un ID_indirizzi poiché contraddistingue un indirizzario diverso.
Esatto, ma alla fine sto pensando che è quasi impossibile riuscire a gestirlo in questo modo.
In effetti con l'esempio de via roma mi sono venute in mente due considerazioni, ovvero che non puoi mettere nuique al campo via e ne a quello città, alla fine è un approccio che non porta da nessuna parte, se non che aumento la complessità.
Alla fine o divido la tabella, o come penso di fare per risolvere, ma è sicuramente un tappa buchi, mi aiuto con il javascript, e ti spiego.
Quando sono nel campo del form, dove metto l'intestazione dell'indirizzo, che so Mario Rossi, questo è già un campo con suggeritore, che mi tira giù tutti i Mar... già registrati.
Ora, se scelgo Mario Rossi, che abita a Genova in via del Campo, so già che questo indirizzo è nel database, quindi come lo carico cambio il value del tasto [STAMPA] e invece di salvarlo, se lo aggiorna a prescindere se modifico qualche cosa o meno.
Se invece scrivo alla Gisella che lavora sotto il pilone 4 del ponte San Giorgio, che non può esistere ancora perché il ponte è stato inaugurato ieri, lo salvo.
E' un paliativo, perché se non si usa javascript dovrei ritornare ai metodi che indicavi tu. E in effetti a rigore non bisogna mai affidarsi ciecamente al solo javascript, ma questo progetto facile facile, sta diventando una seccatura.
 

marino51

Utente Attivo
28 Feb 2013
2.931
166
63
Lombardia
credo che costruire un indirizzario, evitando i riferimenti multipli, non sia così semplice,
a milano esiste piazzale Accursio, ma spesso viene chiamato con piazza Accursio
si trova anche Viale Cassale e via Cassala,
poi G. Garibaldi ma anche Giuseppe Garibaldi ed il solo Garibaldi
e via di seguito

la cosa migliore (forse) é fare riferimento al nome già presente nel db (javascript autocomplete)
memorizzando la key-id o zero se non esiste il riferimento
quindi insert x zero, update per diverso, senza usare ON DUPL.....

sarà meno figo, ma più sicuro
 
  • Like
Reactions: MarcoGrazia

macus_adi

Utente Attivo
5 Dic 2017
1.313
88
48
IT/SW
sarà meno figo, ma più sicuro
Sicuramente
In effetti è un casino e me ne sto accorgendo ogni giorno di più
Non è del tutto vero!!!!!!
-----------------------------------

In realtà gli unici campi interessanti saranno, l'indirizzo e il nome della persona e/o società.
Personalmente non ho capito il motivo per il quale vuoi utilizzare " ON DUPLICATE KEY ", in quanto da quello da te scritto non dovrebbe esistere una chiave duplicata, forse per l'update? ma in questo caso sarebbe meglio utilizzare updateOrCreate!!!

Venendo a noi:
non hai pensato alla memorizzazione con utilizzando una morfologica? Indubbiamente la complessità sarà maggiore.... ma il risultato sarà impeccabile...

Nello scenario comune si potrebbe immaginare una situazione di questo genere:

TBL: indirizzario
* [protocollo, titolo, nome, cognome, [ .....]

Tabella Tipizzata
** [type, reference_class, name, sluggable_text]

Tabella associazione indirizzario - morph
*** [collection_name, model_type, model_id, tipizzata_type, tipizzata_id]

Cerco di spiegare.... Prendiamo in esame solo l'Italia... Ogni comune è univoco le strade e i numeri civici no, quindi di conseguenza, immaginando di salvare tutti gli indirizzi italiano nel modo più canonico avremo il db sarà pieno zeppo di indirizzi duplicati, e questo non lo si vuole fare, quindi...

Es:
Roma, Via Roma, 44
Milano, Via Roma, 55
Palermo, Via Roma, 44

utilizzando una morph invece potrà risultare in questo modo:
* 1, abc, titolo, Roma, Via Roma
* 2, ccc, titolo, Milano, Via Roma

** 1. city, CityModel, Roma, roma
** 2, indirizzo, AddressModel, Via Roma, via-roma
** 3, city, CityModel, Milano, milano


*** city, IndirizzarioModel, 1, TipizzataModel, 1
*** address, IndirizzarioModel, 1, TipizzataModel, 2
*** city, IndirizzarioModel, 2, TipizzataModel, 3
*** address, IndirizzarioModel, 1, TipizzataModel, 2
-----------------------------------------------------
Volendo osare si potrebbe eliminare la 3° tabella "morph" ed utilizzare un campo json nella prima facendo riferimento alla Tipizzata per le tipologie inserite.
 

MarcoGrazia

Utente Attivo
15 Dic 2009
788
16
18
59
Udine
www.stilisticamente.com
Vedo con piacere che la discussione interessa non solo me :)

Prendere in esame solo l'Italia nel mio caso non va bene, in effetti dovendo ripensare all'intero schema del database, devo prevedere l'intero pianeta, e non scherzo.
Spediamo e riceviamo posta verso e da ogni dove e la memorizzazione degli indirizzi si è resa ormai obbligatoria, se no si perde più tempo a cercare che a scrivere l'indirizzo sulla busta.

@macus_adi il tuo metodo può essere interessante, ed io sono un fautore delle tabelle, piuttosto che della tabella unica in ogni caso cioè sempre, creando tabellone piene di campi.
Però speravo che in questo caso, siamo alle basi del "programma agenda" bastava una cosa semplice, mentre vedo tabelle pivot.

Avevo detto che avrei tralasciata un po' la cosa per pensarci e anche per sgombrare la mente, ed è quello che sto facendo dedicandomi ad altro, però ogni contributo è significativo, anche perché vedo che da una semplice domanda, questo thread ha preso la via di una bella lezione su cosa fare e cosa non con un database, e questo è bene.