MySQL: Large VARCHAR vs. TEXT?

Ho una tabella dei messaggi in MySQL che registra i messaggi tra gli utenti. A parte i tipici ID e tipi di messaggi (tutti i tipi interi) ho bisogno di salvare il testo del messaggio attuale come VARCHAR o TEXT. Sto impostando un limite front-end di 3000 caratteri, il che significa che i messaggi non verranno mai inseriti nel db più a lungo di questo.

C’è un motivo per andare con VARCHAR (3000) o TEXT? C’è qualcosa nel solo scrivere VARCHAR (3000) che sembra un po ‘contro-intuitivo. Ho avuto altri post simili su Stack Overflow, ma sarebbe utile per ottenere viste specifiche per questo tipo di memorizzazione di messaggi comuni.

  • TEXT e BLOB sono archiviati fuori dalla tabella con la tabella che ha solo un puntatore alla posizione della memoria effettiva.

  • VARCHAR è memorizzato in linea con la tabella. VARCHAR è più veloce quando la dimensione è ragionevole, il cui compromesso è più veloce dipende dai tuoi dati e dal tuo hardware, vorresti confrontare un scenario reale con i tuoi dati.

Aggiorna Se VARCHAR o TEXT sono archiviati in linea o fuori registro, dipende dalla dimensione dei dati, dalle dimensioni delle colonne, dal formato riga e dalla versione di MySQL. Non dipende da “testo” o “varchar”.

Puoi pronosticare quanto tempo dovrebbe essere l’input dell’utente?

VARCHAR (X)

Caso: nome utente, email, paese, object, password


TESTO

Caso: messaggi, email, commenti, testo formattato, html, codice, immagini, collegamenti


MEDIUMTEXT

Caso: grandi corpi JSON, libri di lunghezza corta o media, archi CSV


LONGTEXT

Caso: libri di testo, programmi, anni di file di registro, harry potter e il calice di fuoco, registrazione scientifica della ricerca

Giusto per chiarire la migliore pratica:

  1. I messaggi in formato testo dovrebbero quasi sempre essere memorizzati come TEXT (finiscono per essere arbitrariamente lunghi)

  2. Gli attributi di stringa devono essere memorizzati come VARCHAR (il nome utente di destinazione, l’object, ecc.).

Capisco che hai un limite di front end, che è fantastico finché non lo è. * grin * Il trucco è pensare al DB come separato dalle applicazioni che si connettono ad esso. Solo perché un’applicazione limita i dati, non significa che i dati siano intrinsecamente limitati.

Di cosa parlano i messaggi stessi che li costringe a non essere più di 3000 caratteri? Se si tratta solo di un vincolo di applicazione arbitrario (ad esempio, per una casella di testo o qualcosa del genere), utilizzare un campo TEXT nel livello dati.

Disclaimer: non sono un esperto di MySQL … ma questa è la mia comprensione dei problemi.

Penso che TEXT sia memorizzato al di fuori della riga mysql, mentre penso che VARCHAR sia memorizzato come parte della riga. Esiste una lunghezza massima della riga per le righe mysql. In questo modo è ansible limitare la quantità di altri dati che è ansible memorizzare in una riga utilizzando VARCHAR.

Anche a causa di VARCHAR che fa parte della riga, sospetto che le query che guardano a quel campo saranno leggermente più veloci di quelle che utilizzano un blocco di testo.

Risposta breve: nessuna pratica, prestazioni o conservazione, differenza.

Risposta lunga:

Non c’è essenzialmente alcuna differenza (in MySQL) tra VARCHAR(3000) (o qualsiasi altro limite di grandi dimensioni) e TEXT . Il primo troncerà a 3000 caratteri ; quest’ultimo troncerà a 65535 byte . (Faccio una distinzione tra byte e caratteri perché un personaggio può assumere più byte).

Per limiti più piccoli in VARCHAR , ci sono alcuni vantaggi rispetto a TEXT .

  • “minore” significa 191, 255, 512, 767 o 3072, ecc., a seconda della versione, del contesto e del CHARACTER SET .
  • INDEXes sono limitati nella misura in cui una colonna può essere indicizzata. (767 o 3072 byte , questa è la versione e le impostazioni dipendenti)
  • Le tabelle intermedie create da SELECTs complessi vengono gestite in due modi diversi: MEMORY (più veloce) o MyISAM (più lento). Quando sono coinvolte colonne “grandi”, la tecnica più lenta viene selezionata automaticamente. (Cambiamenti significativi in ​​arrivo nella versione 8.0, quindi questo object di pallottola è sobject a modifiche).
  • Relativamente all’elemento precedente, tutti i tipi di dati TEXT (al contrario di VARCHAR ) salgono direttamente a MyISAM. Cioè, TINYTEXT è automaticamente peggiore per le tabelle temporanee generate rispetto al VARCHAR equivalente. (Ma questo porta la discussione in una terza direzione!)
  • VARBINARY è come VARCHAR ; BLOB è come TEXT .

Confutazione ad altre risposte

La domanda originale chiedeva una cosa (quale tipo di dati usare); la risposta accettata ha risposto a qualcos’altro (archiviazione off-record). Quella risposta è ormai obsoleta.

Quando questo thread è stato avviato e ha risposto, c’erano solo due “formati di riga” in InnoDB. Poco dopo, sono stati introdotti altri due formati ( DYNAMIC e COMPRESSES ).

La posizione di archiviazione per TEXT e VARCHAR() si basa sulla dimensione , non sul nome del tipo di dati . Per una discussione aggiornata sulla memorizzazione on / off-record di colonne di testo / blob di grandi dimensioni, vedere questo .

Le risposte precedenti non insistono abbastanza sul problema principale: anche in query molto semplici come

 (SELECT t2.* FROM t1, t2 WHERE t2.id = t1.id ORDER BY t1.id) 

può essere richiesta una tabella temporanea e, se è coinvolto un campo VARCHAR , viene convertito in un campo CHAR nella tabella temporanea. Pertanto, se nella tabella sono presenti 500.000 righe con un campo VARCHAR(65000) , questa colonna utilizzerà solo 6.5 * 5 * 10 ^ 9 byte. Tali tabelle temporanee non possono essere gestite in memoria e vengono scritte su disco. Ci si può aspettare che l’impatto sia catastrofico.

Fonte (con metriche): https://nicj.net/mysql-text-vs-varchar-performance/ (Questo si riferisce alla gestione di TEXT vs VARCHAR nel motore di archiviazione MyISAM “standard” (?). Potrebbe essere diverso in altri, ad es. InnoDB.)