issuhub.com

Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

(singke) #1 
Size Space

CHAR(X)

255

bytes

X bytes

VARCHAR(X)

255

bytes

X+1

byte

TINYTEXT (^255)
bytes


X+1

byte

TINYBLOB (^255)
bytes


X+2

bytes

TEXT

65535

bytes

X+2

bytes

BLOB

65535

bytes

X+2

bytes

MEDIUMTEXT (^) 1.6MB X+3
bytes
MEDIUMBLOB (^) 1.6MB X+3
bytes
LONGTEXT (^) 4.2GB X+4
bytes
LONGBLOB
4.2GB X+4
bytes


CHAR and VARCHAR


Out of all these types, the VARCHAR and CHAR types are the most used by far. The difference between them
is that the VARCHAR is a variable length and the CHAR is not. CHAR types are used for fixed lengths. You
would use this type when your values do not change much. If you declare a CHAR(10), all values stored in
this column will be 10 bytes long, even if it is only 3 characters long. MySQL pads this value to fit the size
that was declared. The VARCHAR type does the opposite. If you declare a VARCHAR(10) and store a value
that is only 3 characters long, the total amount of storage space is 4 bytes (the length plus one).
The advantage of using CHAR types is that tables that contain these fixed values are processed faster
that those that are made up of VARCHAR types. The disadvantage of using CHAR types is wasted space.
The choice is up to you.
As a rule, VARCHAR and CHAR types cannot be used in the same table. MySQL automatically changes
the types to VARCHAR when you mix the two. The only exception is when you use small VARCHAR types.
Small is defined as values of 4 or less. If you do, MySQL converts them to CHAR types. For example, if
you have declared the following:
CREATE TABLE Test (Fixed_Col CHAR(5), Var_Col VARCHAR(15))
MySQL will automatically convert the Fixed_Col column to a variable length. Conversely, if you
declare the following:
CREATE TABLE Test (Var_Col1 VARCHAR(3))
MySQL will convert the column to a CHAR.


TEXT and BLOB


TEXT and BLOB variable length types that can store large amounts of data. You would use these types when
you want to store images, sounds, or large amounts of text, such as Web pages or documents. These types
are also good for storing values that vary in size from row-to-row. For example, if you were storing the
contents of a