MySQL: varchar vs. tinytext-felder: wozu gibt's noch varchar?

[Sven]

guten abend,

also, varchar(m) ermöglicht es meines wissens ja, einen String mit maximal der Länge m Zeichen zu erstellen. Tinytext hingegen einen string mit max. der Länge 256 Zeichen.

Jetzt habe ich gelesen, dass varchar die nichgenutzten Zeichen auch speichern würde, d.h. in einem varchar(10)-feld, in dem "abcdef" steht, wird trotzdem der speicherplatz für 10 Zeichen gespeichert, also 5 Zeichen Speicherplatz werden "vernichtet".

Wenn das richtig ist, dann frag ich mal ganz blöd: Wozu gibt's noch varchar-Felder, wenn sie nur Speicherplatzverschwendung sind?

Grüße,

Sven

PS: Bin gerade dabei, bei all meinen datenbanken alle varchar-felder in tinytext umzuwandeln...

[TomIRL]

guten abend,

also, varchar(m) ermöglicht es meines wissens ja, einen String mit maximal der Länge m Zeichen zu erstellen. Tinytext hingegen einen string mit max. der Länge 256 Zeichen.

Jetzt habe ich gelesen, dass varchar die nichgenutzten Zeichen auch speichern würde, d.h. in einem varchar(10)-feld, in dem "abcdef" steht, wird trotzdem der speicherplatz für 10 Zeichen gespeichert, also 5 Zeichen Speicherplatz werden "vernichtet".

Was passiert wenn Du mal mehr als 256 Zeichen Speichern möchtest?
Was machst Du mit diesen Daten dann?
Allein Dein Posting besteht aus 927 Zeichen wo solte dieses dann gespeichert werden?
TomIRL

[Sven]

Huten Abend TomIRL,

Was passiert wenn Du mal mehr als 256 Zeichen Speichern möchtest?

Will ich nicht. Ansonsten greife ich auf 'text' zurück. mit varchar geht's wohl auch kaum, oder?

Was machst Du mit diesen Daten dann?

'text'.

Allein Dein Posting besteht aus 927 Zeichen wo solte dieses dann gespeichert werden?

'text'.

Gruß,

Sven

[Tom]

Hello,

also, varchar(m) ermöglicht es meines wissens ja, einen String mit maximal der Länge m Zeichen zu erstellen. Tinytext hingegen einen string mit max. der Länge 256 Zeichen.

Jetzt habe ich gelesen, dass varchar die nichgenutzten Zeichen auch speichern würde, d.h. in einem varchar(10)-feld, in dem "abcdef" steht, wird trotzdem der speicherplatz für 10 Zeichen gespeichert, also 5 Zeichen Speicherplatz werden "vernichtet".

Wenn das richtig ist, dann frag ich mal ganz blöd: Wozu gibt's noch varchar-Felder, wenn sie nur Speicherplatzverschwendung sind?

PS: Bin gerade dabei, bei all meinen datenbanken alle varchar-felder in tinytext umzuwandeln...

Harzliche Grüße aus http://www.annerschbarrich.de

Das solltest Du Dir nochmal überlegen. TiniText lässt sich inzwischen zwar auch indizieren, ist aber wesentlich langsamer, als Char und VarChar.

Ich habe das mal ausgetestet und die Unterschiede des nicht indizierten Zugriffs waren erheblich. Das liegt daran, dass die *Text-Typen uber eine zusätzliche Referenz gespeichert werden und nicht direkt im Stream des Datensatzes. Ein "order by" auf ein Textfeld dauert mindestens dreimal so lange wie ein "order by" auf ein VarChar.

Tom

[Sven]

Hallo Tom,

Harzliche Grüße aus http://www.annerschbarrich.de

Harz? ;o)

Das solltest Du Dir nochmal überlegen. TiniText lässt sich inzwischen zwar auch indizieren, ist aber wesentlich langsamer, als Char und VarChar.

indizieren? hmm... ich will nur ausgeben. ;-)

Ich habe das mal ausgetestet und die Unterschiede des nicht indizierten Zugriffs waren erheblich. Das liegt daran, dass die *Text-Typen uber eine zusätzliche Referenz gespeichert werden und nicht direkt im Stream des Datensatzes. Ein "order by" auf ein Textfeld dauert mindestens dreimal so lange wie ein "order by" auf ein VarChar.

Ok, das mit dem "order by" ist ein handfestes Argument. Da es durchaus sein könnte, dass ich irgendwann mal by diesem feld ordern möchte, wäre das - ja, überzeugend.
Das mit der Referenz klingt auch ganz einleuchtend. Obwohl ich für "die" tabelle, die ich gerade gemacht habe, garantiert nicht by diesem feld ordern möchte, d.h. "order by" wäre hierbei irrelevant. Verbraucht eine zusätzliche Referenz mehr Speicherplatz als ein im-stream-speichern oder nicht?

Gruß,

Sven

[Tom]

Hello,

Das mit der Referenz klingt auch ganz einleuchtend. Obwohl ich für "die" tabelle, die ich gerade gemacht habe, garantiert nicht by diesem feld ordern möchte, d.h. "order by" wäre hierbei irrelevant. Verbraucht eine zusätzliche Referenz mehr Speicherplatz als ein im-stream-speichern oder nicht?

Eben nicht. Allerdings ist das alles nicht mehr generisch, sondern bei MySQL hat man schon gewaltig am Rad gedreht. Das beste Beispiel für den theoretischen Einstieg wäre dafür das gute alte dbase mit seinen Memofeldern. Eine dbase-Datei beteht dabei aus

Superhead (fix)
  Head      (variabel)
  Body      (enthält die Datensätze)

Memofile  (separate Textdatei)

Im Body werden die Datensätze, deren Struktur im Head beschrieben wird, angereiht. Die Sätze haben feste Länge und feste Feldstruktur. Das wäre nun vergleichbar mit den CHAR-Feldern von MySQL. Wenn man nun Texte mit variabler Länge speichern will, wird in einem solchen Feld nur eine Referenz in die separate Datei abgespeichert, der Einsprungspunkt auf den Block dieser Datei. Die textdatei ist also (aus Geschwindigkeits- und Adressierungsgründen) ihrerseits in Blöcke eingeteilt. Die Größe kann man einstellen. Ob das bei MySQL auch geht, weiß ich nicht oder ob diese hier inzwischen byteweise auf Seiten verwaltet werden... Ich sagte ja, die haben sich da schon was einfallen lassen.

Jedenfalls ist es einleuchtend, dass eine referenzierte geblockte Speicherung im Mittel weniger Platz benötigt, wenn die Blöcke klien genug gehalten wrden, als wenn man für JEDEN Satz z.B. 4096 Byte vorrätig halten müsste, diese aber nur bei jedem zehnten benutzt.

Ich hoffe, meine wuselige Erklärung hat Dir trotzdem etwas Anregung gegeben. man müsste Bilder dazu malen, dann wäre es verständlicher.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Christian Kruse]

Hallo Sven,

Ich habe das mal ausgetestet und die Unterschiede des nicht
indizierten Zugriffs waren erheblich. Das liegt daran, dass die
*Text-Typen uber eine zusätzliche Referenz gespeichert werden
und nicht direkt im Stream des Datensatzes. Ein "order by" auf
ein Textfeld dauert mindestens dreimal so lange wie ein
"order by" auf ein VarChar.

Ok, das mit dem "order by" ist ein handfestes Argument. Da es
durchaus sein könnte, dass ich irgendwann mal by diesem feld ordern
möchte, wäre das - ja, überzeugend.

Das dauert aber auch so laenger. Es bedeutet fuer jeden Datensatz
einen zusaetzlichen seek() und einen zusaetzlichen read()-Call. Der
Aufwand ist also linear abhaengig von der Groesse des Resultsets.

Verbraucht eine zusätzliche Referenz mehr Speicherplatz als ein
im-stream-speichern oder nicht?

Klar, etwa 4 Byte pro Datensatz. Aber das ist vernachlaessigbar.
Schlimmer finde ich den zusaetzlichen Aufwand.

Grüße,
 CK

[Sven Rautenberg]

Moin!

Jetzt habe ich gelesen, dass varchar die nichgenutzten Zeichen auch speichern würde, d.h. in einem varchar(10)-feld, in dem "abcdef" steht, wird trotzdem der speicherplatz für 10 Zeichen gespeichert, also 5 Zeichen Speicherplatz werden "vernichtet".

Falsch gelesen. CHAR-Felder speichern feste Längen unabhängig von der tatsächlichen Benutzung. MySQL optimiert aber in der Regel CHAR-Felder zu VARCHAR-Feldern - die Doku verrät dir mehr dazu.

PS: Bin gerade dabei, bei all meinen datenbanken alle varchar-felder in tinytext umzuwandeln...

Das solltest du jetzt, wo deine Fehlinformation als solche entlarvt wurde, lieber wieder rückgängig machen.

- Sven Rautenberg

[Christian Kruse]

Hallo Sven,

Jetzt habe ich gelesen, dass varchar die nichgenutzten Zeichen
auch speichern würde, d.h. in einem varchar(10)-feld, in dem
"abcdef" steht, wird trotzdem der speicherplatz für 10 Zeichen
gespeichert, also 5 Zeichen Speicherplatz werden "vernichtet".

Falsch gelesen. CHAR-Felder speichern feste Längen unabhängig von
der tatsächlichen Benutzung.

Falsch. MySQL macht aus varchar-Feldern char-Felder. Es wird immer
die maximale Laenge gespeichert. Das hat uebrigens auch ganz konkrete
Gruende und wird auch in anderen RDBMS (z. B. PostGreSQL) getan:
nimmt man fuer ein Feld eine variable und nicht feste Laenge, kann
die Position eines Datensatzes nicht mehr in einem Zug berechnet
werden, sondern braucht linear viele Zuege.  Bitte lies es im
Sourcecode nach, wenn du mir das nicht glauben solltest. Ich habe
das bereits getan.

MySQL optimiert aber in der Regel CHAR-Felder zu
VARCHAR-Feldern - die Doku verrät dir mehr dazu.

Tatsaechlich kennt MySQL keine char-Felder und verhaelt sich auch in
diesem Fall fehlerhaft. Der Standard sagt, dass Char-Felder
aufgefuellt werden muessen. MySQL tut dies nicht. Varchar-Felder
duerfen nicht aufgefuellt werden, deren Laenge ist (nach aussen hin)
variabel (lt. Standard). Wie das intern gehandhabt wird, wird
nirgendwo vorgeschrieben. MySQL kennt also keine Char-Felder, sondern
nur Varchar-Felder.

Grüße,
 CK

[Sven Rautenberg]

Moin!

Falsch. MySQL macht aus varchar-Feldern char-Felder. Es wird immer
die maximale Laenge gespeichert. Das hat uebrigens auch ganz konkrete
Gruende und wird auch in anderen RDBMS (z. B. PostGreSQL) getan:
nimmt man fuer ein Feld eine variable und nicht feste Laenge, kann
die Position eines Datensatzes nicht mehr in einem Zug berechnet
werden, sondern braucht linear viele Zuege.  Bitte lies es im
Sourcecode nach, wenn du mir das nicht glauben solltest. Ich habe
das bereits getan.

Dann ist die Doku unter http://dev.mysql.com/doc/mysql/en/CHAR.html also falsch?

"The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length from 0 to 255, just as for CHAR columns. (Before MySQL 4.0.2, the length of VARCHAR may be from 1 to 255.) However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. This space removal differs from the standard SQL specification."

Dass MySQL am vorgewählten Typ Änderungen vornimmt, ist jedenfalls laut Doku bekannt: http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html

"- VARCHAR columns with a length less than four are changed to CHAR."
"- If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns."

Tatsaechlich kennt MySQL keine char-Felder und verhaelt sich auch in
diesem Fall fehlerhaft.

Moment. Oben sagst du, MySQL kenne keine VARCHAR-Felder, sondern mache daraus immer CHAR, und jetzt ist es genau umgekehrt? Irgendwie klingt das konfus.

Der Standard sagt, dass Char-Felder aufgefuellt werden muessen. MySQL tut dies nicht.

Naja, der "Standard" interessiert viele DB-Hersteller nicht so richtig. :)

- Sven Rautenberg

[Tom]

Hello,

"- VARCHAR columns with a length less than four are changed to CHAR."
"- If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns."

Nun ist es inteessant, was MySQL macht, wenn man in einem Datensatz alle CHAR und VARCHAR-Felder bis an den Rand füllt, also die volle Länge ausnutzt.

Zu diesem Zweck muss man sich einfach mal die Flatfiles der DB anschauen.

Harzliche Grüße aus http://www.annerschbarrich.de

Tom

[Christian Kruse]

Hallo Sven,

Falsch. MySQL macht aus varchar-Feldern char-Felder. Es wird immer
die maximale Laenge gespeichert. Das hat uebrigens auch ganz
konkrete Gruende und wird auch in anderen RDBMS (z. B. PostGreSQL)
getan: nimmt man fuer ein Feld eine variable und nicht feste
Laenge, kann die Position eines Datensatzes nicht mehr in einem
Zug berechnet werden, sondern braucht linear viele Zuege.  Bitte
lies es im Sourcecode nach, wenn du mir das nicht glauben
solltest. Ich habe das bereits getan.

Dann ist die Doku unter
http://dev.mysql.com/doc/mysql/en/CHAR.html also falsch?

Wenn sie von Version 3 zu 4 das Verhalten nicht ganz gewaltig
geaendert haben, ja.

Tatsaechlich kennt MySQL keine char-Felder und verhaelt sich auch
in diesem Fall fehlerhaft.

Moment. Oben sagst du, MySQL kenne keine VARCHAR-Felder, sondern
mache daraus immer CHAR, und jetzt ist es genau umgekehrt?
Irgendwie klingt das konfus.

Es gibt einen Unterschied zwischen interner und externer Darstellung.
Intern wird nur fixed width verwendet, extern nur variable width.
Intern gibt es nur char, extern nur varchar.

Der Standard sagt, dass Char-Felder aufgefuellt werden muessen.
MySQL tut dies nicht.

Naja, der "Standard" interessiert viele DB-Hersteller nicht so
richtig. :)

Leider.

Grüße,
 CK

[Tom]

Hello Ihr Spezialisten,

nun habe ich das mal zusammengefasst als Beweis für das Verhalten von 3.23.55-max eingefangen...

Satzformat:
ID_FARBE     bigint(3)unsigned
FARB_NAME    varchar(20)

testserver:/var/mysql/data/thomass# hexdump -C FARBE.MYD
00000000  fd 01 00 00 00 00 00 00  00 52 6f 74 20 20 20 20  |.........Rot    |
00000010  20 20 20 20 20 20 20 20  20 20 20 20 20 fd 02 00  |             ...|
00000020  00 00 00 00 00 00 47 65  6c 62 20 20 20 20 20 20  |......Gelb      |
00000030  20 20 20 20 20 20 20 20  20 20 fd 03 00 00 00 00  |          ......|
00000040  00 00 00 47 72 fc 6e 20  20 20 20 20 20 20 20 20  |...Gr.n         |
00000050  20 20 20 20 20 20 20 fd  04 00 00 00 00 00 00 00  |       .........|
00000060  42 6c 61 75 20 20 20 20  20 20 20 20 20 20 20 20  |Blau            |
00000070  20 20 20 20 fd 05 00 00  00 00 00 00 00 56 69 6f  |    .........Vio|
00000080  6c 65 74 74 20 20 20 20  20 20 20 20 20 20 20 20  |lett            |
00000090  20 fd 06 00 00 00 00 00  00 00 57 65 69 df 20 20  | .........Wei.  |
000000a0  20 20 20 20 20 20 20 20  20 20 20 20 20 20 fd 07  |              ..|
000000b0  00 00 00 00 00 00 00 53  63 68 77 61 72 7a 20 20  |.......Schwarz  |
000000c0  20 20 20 20 20 20 20 20  20 20 20 fd 08 00 00 00  |           .....|
000000d0  00 00 00 00 53 69 6c 62  65 72 20 20 20 20 20 20  |....Silber      |
000000e0  20 20 20 20 20 20 20 20  fd 09 00 00 00 00 00 00  |        ........|
000000f0  00 47 6f 6c 64 20 20 20  20 20 20 20 20 20 20 20  |.Gold           |
00000100  20 20 20 20 20 fd ff 00  00 00 00 00 00 00 46 61  |     .........Fa|
00000110  72 62 6c 6f 73 20 20 20  20 20 20 20 20 20 20 20  |rblos           |
00000120  20 20                                             |  |
00000122

Sichtbar ist hier, dass es sich um eine festes Satzformat handelt, was durch die kennung "fd" vor jedem Datensatz angezeigt wird

Dann Änderung durchgeführt auf

Satzformat:
ID_FARBE     bigint(3)unsigned
FARB_NAME    varchar(20)
BEM          varchar(35)

testserver:/var/mysql/data/thomass# hexdump -C FARBE.MYD
00000000  03 00 0f 01 06 fe 01 00  00 00 00 00 00 00 03 52  |...............R|
00000010  6f 74 00 00 03 00 10 00  06 fe 02 00 00 00 00 00  |ot..............|
00000020  00 00 04 47 65 6c 62 00  03 00 10 00 06 fe 03 00  |...Gelb.........|
00000030  00 00 00 00 00 00 04 47  72 fc 6e 00 03 00 10 00  |.......Gr.n.....|
00000040  06 fe 04 00 00 00 00 00  00 00 04 42 6c 61 75 00  |...........Blau.|
00000050  03 00 13 01 06 fe 05 00  00 00 00 00 00 00 07 56  |...............V|
00000060  69 6f 6c 65 74 74 00 00  03 00 10 00 06 fe 06 00  |iolett..........|
00000070  00 00 00 00 00 00 04 57  65 69 df 00 03 00 13 01  |.......Wei......|
00000080  06 fe 07 00 00 00 00 00  00 00 07 53 63 68 77 61  |...........Schwa|
00000090  72 7a 00 00 03 00 12 02  06 fe 08 00 00 00 00 00  |rz..............|
000000a0  00 00 06 53 69 6c 62 65  72 00 00 00 03 00 10 00  |...Silber.......|
000000b0  06 fe 09 00 00 00 00 00  00 00 04 47 6f 6c 64 00  |...........Gold.|
000000c0  03 00 13 01 06 fe ff 00  00 00 00 00 00 00 07 46  |...............F|
000000d0  61 72 62 6c 6f 73 00 00                           |arblos..|
000000d8

Hier ersichtlich, dass jeder Datensatz mit 0x03 beginnt. Varchar-Felder werden rechtsbündig angeordnet. Reserve-Space wird mit 0x00 aufgefüllt. Es sind noch KEINE Daten in der Spalte BEM vorhanden!

Und ein paar Daten in BEM eingetragen

testserver:/var/mysql/data/thomass# hexdump -C FARBE.MYD
00000000  05 00 1c 00 07 00 00 00  00 00 00 00 d8 06 fc 01  |................|
00000010  00 00 00 00 05 00 32 00  07 00 00 00 00 00 00 00  |......2.........|
00000020  f0 02 fc 02 00 00 00 00  03 00 10 00 06 fe 03 00  |................|
00000030  00 00 00 00 00 00 04 47  72 fc 6e 00 03 00 10 00  |.......Gr.n.....|
00000040  06 fe 04 00 00 00 00 00  00 00 04 42 6c 61 75 00  |...........Blau.|
00000050  03 00 13 01 06 fe 05 00  00 00 00 00 00 00 07 56  |...............V|
00000060  69 6f 6c 65 74 74 00 00  03 00 10 00 06 fe 06 00  |iolett..........|
00000070  00 00 00 00 00 00 04 57  65 69 df 00 03 00 13 01  |.......Wei......|
00000080  06 fe 07 00 00 00 00 00  00 00 07 53 63 68 77 61  |...........Schwa|
00000090  72 7a 00 00 03 00 12 02  06 fe 08 00 00 00 00 00  |rz..............|
000000a0  00 00 06 53 69 6c 62 65  72 00 00 00 03 00 10 00  |...Silber.......|
000000b0  06 fe 09 00 00 00 00 00  00 00 04 47 6f 6c 64 00  |...........Gold.|
000000c0  03 00 13 01 06 fe ff 00  00 00 00 00 00 00 07 46  |...............F|
000000d0  61 72 62 6c 6f 73 00 00  07 00 15 00 00 00 03 52  |arblos.........R|
000000e0  6f 74 0d 4d 61 67 20 69  63 68 20 6e 69 63 68 74  |ot.Mag ich nicht|
000000f0  0b 00 21 00 00 00 00 00  00 01 1c 00 00 00 04 47  |..!............G|
00000100  65 6c 62 49 73 74 20 6b  61 75 6d 20 73 69 63 68  |elbIst kaum sich|
00000110  74 62 61 72 20 75 6e 64  20 7a 75 20 09 00 0a 06  |tbar und zu ....|
00000120  6c 61 6e 67 20 66 fc 72  20 64 00 00 00 00 00 00  |lang f.r d......|
00000130

+----------+-----------+-------------------------------------+
| ID_FARBE | FARB_NAME | BEM                                 |
+----------+-----------+-------------------------------------+
|        1 | Rot       | Mag ich nicht                       |
|        2 | Gelb      | Ist kaum sichtbar und zu lang für d |
|        3 | Grün      | NULL                                |
|        4 | Blau      | NULL                                |
|        5 | Violett   | NULL                                |
|        6 | Weiß      | NULL                                |
|        7 | Schwarz   | NULL                                |
|        8 | Silber    | NULL                                |
|        9 | Gold      | NULL                                |
|      255 | Farblos   | NULL                                |
+----------+-----------+-------------------------------------+

Nun bin ich aber gespannt auf Eure Erläuterungen.
Sieht ja so aus, als würde jedes Feld in einer eigenen Seite dargestellt.

Dateiformat ist übrigens MyISAM, sollte man ja dazu sagen. ;-)

Harzliche Grüße aus http://www.annerschbarrich.de

Tom