3 messages in com.mysql.lists.win32Re: Data Types
FromSent OnAttachments
Joelle Tegwen09 Dec 2005 11:53 
SGr...@unimin.com09 Dec 2005 12:22 
Joelle Tegwen09 Dec 2005 13:16 
Subject:Re: Data Types
From:SGr...@unimin.com (SGr@unimin.com)
Date:12/09/2005 12:22:23 PM
List:com.mysql.lists.win32

Response intermingled. Please read below....

Joelle Tegwen <tegw@umn.edu> wrote on 12/09/2005 02:54:06 PM:

One thing is not clear to me in this explanation *Data Type* *Storage Required* |CHAR(/|M|/)| |/|M|/| bytes, 0 |<= /|M|/ <=| 255 |VARCHAR(/|M|/)| /Prior to MySQL 5.0.3/: /|L|/ + 1 bytes, where |/|L|/ <= /|M|/| and 0 |<= /|M|/ <=| 255. /MySQL 5.0.3 and later/: /|L|/ + 1 bytes, where |/|L|/ <= /|M|/| and 0 |<= /|M|/ <=| 256 /or/ /|L|/ + 2 bytes, where |/|L|/ <= /|M|/| and 256 |< /|M|/ <=| 65535 (see note below). |BINARY(/|M|/)| |/|M|/| bytes, 0 |<= /|M|/ <=| 255 |VARBINARY(/|M|/)| /|L|/+1 bytes, where |/|L|/ <= /|M|/| and 0 |<= /|M|/ <=| 255 |TINYBLOB|, |TINYTEXT| /|L|/+1 byte, where /|L|/ < 2^8 |BLOB|, |TEXT| /|L|/+2 bytes, where /|L|/ < 2^16 |MEDIUMBLOB|, |MEDIUMTEXT| /|L|/+3 bytes, where /|L|/ < 2^24 |LONGBLOB|, |LONGTEXT| /|L|/+4 bytes, where /|L|/ < 2^32

|VARCHAR| and the |BLOB| and |TEXT| types are variable-length types. For

each, the storage requirements depend on the actual length of column values (represented by /|L|/ in the preceding table), rather than on the

type's maximum possible size. For example, a |VARCHAR(10)| column can hold a string with a maximum length of 10. The actual storage required is the length of the string (/|L|/), plus 1 byte to record the length of

the string. For the string |'abcd'|, /|L|/ is 4 and the storage requirement is 5 bytes.

(found here: http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html )

is L determined by row or by column?

Based on your examples (below), by row.

So say I've got a field myField TEXT and in it I have the following values "fred" "joe's bar and grill" "Sometimes people enter really really long text that goes on and on for ever. maybe they ever write a whole book. You know how some people are, they're just really verbose so even though most of the data is likely under 256 characters there's no way to guarantee that and the business rules don't allow for forcing this data to be short so you need a longer

data type."

is it like this: row 1: L=5 row 2: L=21 row 3: L=370

Yes, it's like this. Each field is individually sized and only takes up the appropriate ammount of storage on disk. This is not the case with the earlier versions of NDB tables. They were always fixed width but that has recently changed. Besides, I do not think you were asking about NDB's in-memory allocation scheme, were you?

or is it row 1: L=370 row 2: L=370 row 3: L=370

This is an example of neither fixed-width or variable storage as you are asking the database to resize a storage location based on the largest length added to the database. It would be quite inefficient to program a database this way. Imagine if you had 10000 rows of data setup like this and added a new row where the text field was 400 characters long, you would have to resize all 10000 previous rows to match the new value. It's easier to either let each field have its own size (as above) or completely fixed throughout. The advantage of being completely fixed is in lookup speed. You always know exactly how far to move in memory/disk to get to a particular piece of data. The downside to completely fixed field sizes is that it wastes so much space.

If it's the latter, does anyone have suggestions for storing data like this?

Thanks Joelle Tegwen

Good question!

Shawn Green Database Administrator Unimin Corporation - Spruce Pine