4 messages in com.mysql.lists.win32Re: char length| From | Sent On | Attachments |
|---|---|---|
| Warren | 21 Feb 2005 19:23 | |
| Petr Vileta | 21 Feb 2005 19:58 | |
| Warren | 21 Feb 2005 20:29 | |
| Mike Rains | 22 Feb 2005 01:10 |
| Subject: | Re: char length![]() |
|---|---|
| From: | Mike Rains (sirh...@gmail.com) |
| Date: | 02/22/2005 01:10:58 AM |
| List: | com.mysql.lists.win32 |
If your maximum length string is 2,400 characters long, you should be using the TEXT data type, and here's why:
CHAR has a fixed-length of whatever you tell it, up to the upper limit of 255 characters; it has no length byte(s) to describe how long that particular piece of data is, and is right-padded with spaces to fill out the string.
Both VARCHAR and TINYTEXT use a single byte length prefix (remember, a byte can hold a single number from 0 to 255, which is x00 to xFF in hexidecimal); thus, they can hold up to 255 characters.
TEXT uses a 2-byte length prefix (x0000 to xFFFF in hex, which translates into 0 to 65,535 in decimal); thus, it can hold up to 64KB.
MEDIUMTEXT uses a 3-byte length prefix (x000000 to xFFFFFF hex, 0 to 16,777,215 deximal); thus, it can hold up to 16MB.
LONGTEXT uses a 4-bytes length prefix (x00000000 to xFFFFFFFF hex, 0 to 4,294,967,295 decimal); thus, it can hold up to 4GB.
Suppose you declare your field a LONGTEXT; storing the string 'A' would require 5 bytes, 4 for the length bytes (x00 00 00 01) and one for the 'A' (x41). Storing that same 'A' in a CHAR(255) field would require 255 bytes, with the first byte being the 'A' (x41) and the rest padded out with spaces (x20 times 254).
Since you said that your maximum string length can be as large as 2,400 characters, you can't use CHAR, nor can you use VARCHAR (or TINYTEXT), because their maximum lengths are only 255 characters.You can use TEXT, whose maximum length is 65,535 characters; you could also use MEDIUMTEXT or LONGTEXT too, but you don't need to waste the extra byte or two per string for each entry, if you know your field won't go over 64KB.
Reference: http://dev.mysql.com/doc/mysql/en/string-type-overview.html




