[MySQL] domande di base sui formati

[Valeria75_bis]

Ciao a tutti, vorrei avere qualche chiarimento in merito al formato di campi utilizzato

(1)
quando come chiave primaria di una tabella è meglio usare un GUID e quando un ID incrementale??


(2)
per un campo di "annotazioni" va bene il formato TEXT?? (in SQL Server userei varchar(MAX)


(3)
per un campo boolean è corretto usare il TINYINT(1)


(4)
per i campi di testo in generale è corretto usare VARCHAR(DIM)


(5)
vorrei tenere traccia, per ogni record, della data di aggiornamento di ogni campo .... io ho sempre usato il timestamp (impostato col CURRENT_TIMESTAMP) ma ho visto che qualcuno usa un campo datetime (impostao a NOW) .... è sbagliata la mia soluzione?


Grazie a tutti per i suggerimenti

[piero.mac]

Originariamente inviato da Valeria75_bis
Ciao a tutti, vorrei avere qualche chiarimento in merito al formato di campi utilizzato

(1)
quando come chiave primaria di una tabella è meglio usare un GUID e quando un ID incrementale??

La differenza principale e' un id_incrementale puo' essere ordinato asc/desc, raggruppato MAX() min(), con un GUID non avrebbe senso fare cio'. Il pericolo di collisioni e' pressoche' inesistente in entrambi i casi. Un id autoincrement deve avere una dimensione del campo numerico sufficiente a contenere questo avanzamento di occupazione per impedire che arrivi a "riavvolgersi" cioe' che il bit piu' significativo vada in overflow ed il contatore ricominci da 0. Il GUID e' un campo char/varchar . I campi numerici sono piu' performanti ed occupano meno spazio.

(2)
per un campo di "annotazioni" va bene il formato TEXT?? (in SQL Server userei varchar(MAX)

Dipende dalla consistenza dell'annotazione. TEXT e' dinamico con dimensione prefissata max 64K, varchar ha a disposizione anche lui 65535 Byte ma condivisi tra tutte le colonne varchar, cioe' in una tabella la somma delle occupazioni in byte dei campi varchar e' 65535 byte. Occhio poi al character set. Se e' multibyte 255 caratteri NON sono 255 byte ma molti di piu'. cambia qualcosa anche a seconda dell'engine.

(3)
per un campo boolean è corretto usare il TINYINT(1)

Si.

(4)
per i campi di testo in generale è corretto usare VARCHAR(DIM)

Si

(5)
vorrei tenere traccia, per ogni record, della data di aggiornamento di ogni campo .... io ho sempre usato il timestamp (impostato col CURRENT_TIMESTAMP) ma ho visto che qualcuno usa un campo datetime (impostao a NOW) .... è sbagliata la mia soluzione?

Se utilizzi il CURRENT TIMESTAMP il campo verra' aggiornato in modo automatico, cioe' puoi ometterlo nella query update, ma aggiornera' SOLO se viene effettuata una modifica ad almeno un campo. Se i valori dell'update sono identici all'esistente non aggiorna (giustamente) il timestamp, ed anche l'update rendera' 0 record aggiornati.
Se aggiorni manualmente con NOW() intanto lo devi dichiarare mentre il primo puo' essere omesso e poi l'aggiornamento della data significhera' anche la modifica di fatto del record e quindi la data verra' aggiornata anche se nessuna altra variazione e' stata fatta al record e l'update rendera' 1 riga modificata anche se questo per la consistenza del record non e' vero.

[luca200]

Originariamente inviato da piero.mac
varchar ha a disposizione anche lui 65535 Byte ma condivisi tra tutte le colonne varchar, cioe' in una tabella la somma delle occupazioni in byte dei campi varchar e' 65535 byte.

Non proprio

The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

[deleted_29]

Originariamente inviato da Valeria75_bis
(1)
quando come chiave primaria di una tabella è meglio usare un GUID e quando un ID incrementale??

La risposta è più complessa di quanto sembra.
Innanzitutto dal punto di vista delle prestazioni GUID è enormemente più lento, e diventa disastrosamente più lento nel caso in cui l'indice relativo non stia in memoria.
Il campo autoincrementante, di converso, genera problemi in fase di portabilità e, sopratutto, di manutenzione-backup-fusione tabelle (e mettiamoci pure replicazione, ma non credo che avrai schiavi)

Dipende, essenzialmente, dalla cardinalità delle tabelle.
Per mini-db (qualche decina di migliaia di righe, tipo ad esempio un archivio fatture) non ci sono differenze sensibili.
Per medi-db la differenza la vedi; per grandi-db semplicemente GUID non funziona.

Va poi ricordato, come già accennato, che GUID non dà ordinamento (in particolare l'ordine di inserimento, il che talvolta può essere utile).

Personalmente uso, a seconda dei casi, 3 approcci diversi e/o contemporanei

1) campo SER. Lo uso però come cursore, non come chiave
2) campo chiave "vera", determinata dai dati, ma non definita come unica e "compattata". Nel caso ad esempio di nomi uso il CRC32 (calcolato da applicazione, non da mysql, ovviamente indiciato) con doppio controllo del tipo
where (nomecrc32='123456') and (nome='pippo') per evitare le collisioni. Con mysql la seconda parte non viene eseguita se la prima non ritorna più di una riga.
In sostanza una ricerca "secca" con = diventa un confronto tra interi, anzichè tra stringhe VARCHAR, il che lo rende notevolmente più veloce.

3) campo IDLINK (codice SHA1 simile a GUID), che però calcolo sempre lato-applicazione, niente funzione mysql, che può essere (talvolta) la chiave primaria delle tabelle (quando son piccole ma ubique e voglio evitare il rischio collisione)

Ricorda che esiste anche la versione "intera" di GUID (se proprio vuoi usarla).

E, se il rate di inserimento è basso, puoi perfino usare un mutex per generarti una tua chiave e verificare PRIMA di farne il post che non sia duplicata.

Ovviamente questo caso non è proponibile nel caso di frequenti inserimenti per la serializzazione delle scritture.

Come vedi, al solito, non c'è UNA risposta "giusta", ce ne son tante a seconda delle situazioni.

- per inciso QUI ad esempio si vede la differenza tra chi progetta di professione magari da un 20 anni db, e chi ci si approccia le prime volte, ovvero il bagaglio di esperienza di tanti problemi diversi e tante soluzioni distinte -

(2)
per un campo di "annotazioni" va bene il formato TEXT?? (in SQL Server userei varchar(MAX)

Dipende, essenzialmente dalla necessità di indicizzarlo (almeno la prima parte, ovviamente), oppure no.
Nel caso "oppure no" (ossia è banalmente un butta-su) va benissimo

(3)
per un campo boolean è corretto usare il TINYINT(1)

E' la scelta sicuramente migliore per la portabilità.
Puoi usare anche un normale INTEGER per piccoli-db, avrai meno problemi a ridefinire la struttura della tabella (per portarla)

(4)
per i campi di testo in generale è corretto usare VARCHAR(DIM)

Dipende essenzialmente da DIM, se è grande, o piccolo, e se varia.
Se è piccolo (es. 5 per mantenere un codice utente) è meglio CHAR(5).
Se è fisso è inutile usare varchar (supponiamo per tenere un codice fiscale), in quanto rende più difficile all'ottimizzatore "capire" dove finiscono i vari campi nelle pagine

(5)
vorrei tenere traccia, per ogni record, della data di aggiornamento di ogni campo .... io ho sempre usato il timestamp (impostato col CURRENT_TIMESTAMP) ma ho visto che qualcuno usa un campo datetime (impostao a NOW) .... è sbagliata la mia soluzione?

Non è sbagliata, non è portabile, il che può essere un problema, o magari no.
Normalmente lo faccio lato applicazione aggiornando "a mano" il campo.
Questo consente di portare su db che NON hanno le funzioni mysql.
---
Personalmente certo sempre di essere il MENO legato alle funzioni del db, e (quando si può) anche dai campi AUTOINC i quali non esistono in tutti gli RDBMS (o meglio vengono normalmente simulati con casini giganteschi, sequenze etc), ed i malefici BOOL (in misura minore le date e le ore, lì generalmente basta fare una funzione lato-applicazione data_to_data_sql(i_tipodatabase:string;i_data:tdat e):string
(come vedi ho messo le _ )

Se però questo non è un problema => è inutile inventare l'acqua calda, usa quello che ti mette a disposizione mysql.

[Valeria75_bis]

Grazie per le risposte dettagliate!!

Effettivamente il problema del GUID era per sincronizzare tabelle in fase di collegamento con un altra base dati.

Un ultimo dubbio, quale formato è meglio utilizzare per creare un trigger?

tr_nometabella_azione

oppure

tr_azione


Grazie ancora!

[deleted_29]

Originariamente inviato da Valeria75_bis

Un ultimo dubbio, quale formato è meglio utilizzare per creare un trigger?

tr_nometabella_azione

oppure

tr_azione


Grazie ancora!

Quello che vuoi, non c'è uno standard preciso nè suggerito.
TI consiglio però, come valutazione generale, di porre grande attenzione ai trigger, i quali non sono "magici" (anzi, essendo eseguiti in mysql per OGNI RIGA normalmente son lenti).

Vanno bene, benissimo, se sai come, quando e perchè verranno attivati.
Altrimenti rischi di legare una "palla al piede" al db.

Se le operazioni non son complicate preferisco evitare i trigger e mettere l'equivalente nell'applicazione, sia per le prestazioni, sia per la portabilità

[Valeria75_bis]

Grazie per la risposta e tutti i suggerimenti dati.

Il problema era legato al fatto che non vorrei incorrere in problemi di nomi... esempio:

se una tabella ha un trigger che cho chiamato

tr_guid

posso usare lo stesso nome per un trigger in una tabella differente????




per quanto riguarda la tua seconda osservazione, per ora uso i trigger solo per generare il guid del record; anche se, come avevi suggerito tu, potrei valutare di gestire questa operazione lato codice, risparmiando un po' di fatica al DB


Grazie ancora

[Valeria75_bis]

un'ultima dritta sul naming per i trigger

se una tabella ha un trigger che ho chiamato

tr_guid

posso usare lo stesso nome per un trigger in una tabella differente????

[deleted_29]

no

[Valeria75_bis]

quindi intendi dire che devo mettere il prefisso col nome della tabella?? intendi questo?

Grazie