197
Column Data Types
Of course, you should apply common sense to all this freedom. Just because you can
call a database `create database`, it doesn’t that mean that you should.The same prin-
ciple applies as in any other kind of programming—use meaningful identifiers.
Column Data Types
The three basic column types in MySQL are: numeric, date and time, and string.Within
each of these categories are a large number of types.We’ll summarize them here, and go
into more detail about the strengths and weaknesses of each in Chapter 11.
Each of the three types comes in various storage sizes.When choosing a column type,
the principle is generally to choose the smallest type that your data will fit into.
For many data types, when you are creating a column of that type, you can specify
the maximum display length.This is shown in the following tables of data types as M.If
it’s optional for that type, it is shown in square brackets.The maximum value you can
specify for M is 255.
Optional values throughout these descriptions are shown in square brackets.
Numeric Types
The numeric types are either integers or floating point numbers. For the floating point
numbers, you can specify the number of digits after the decimal place.This is shown in
this book as D.The maximum value you can specify for D is 30 or M-2 (that is, the maxi-
mum display length minus two—one character for a decimal point and one for the inte-
gral part of the number), whichever is lower.
For integer types you can also specify if you want them to be UNSIGNED, as shown in
Listing 8.1.
For all numeric types, you can also specify the ZEROFILL attribute.When values from
a ZEROFILL column are displayed, they will be padded with leading zeroes. If you specify
a column as ZEROFILL, it will automatically also be UNSIGNED.
The integral types are shown in Table 8.5. Note that the ranges shown in this table
show the signed range on one line and the unsigned range on the next.
Table 8.5 Integral Data Types
Type Range Storage Description

(bytes)
TINYINT[(M)] -127 128 1 Very small
or 0 255 integers
BIT Synonym for
TINYINT
BOOL Synonym for TINYINT
SMALLINT[(M)] -32768 32767 2 Small integers
or 0 65535
11 525x ch08 1/24/03 3:39 PM Page 197
198
Chapter 8 Creating Your Web Database
MEDIUMINT[(M)] -8388608 3 Medium sized
8388607 integers
or 0 16777215
INT[(M)] -2
31
2
31
-1 4 Regular integers
or 0 2
32
-1
INTEGER[(M)] Synonym for INT
BIGINT[(M)] -2
63
2
63
-1 8 Big integers
or 0 2
64

-1
The floating point types are shown in Table 8.6.
Table 8.6 Floating Point Data Types
Type Range Storage Description
(bytes)
FLOAT(precision) depends on varies Can be used to specify
precision single or double precision
floating point numbers.
FLOAT[(M,D)] ±1.175494351E-38 4 Single precision
±3.402823466E+38 floating point number.
These are equivalent to
FLOAT(4), but with a
specified display width and
number of decimal places.
DOUBLE[(M,D)] ±1. 8 Double precision
7976931348623157E floating point number.
+308 These are equivalent
±2.2250738585072014E to FLOAT(8) but with a
-308 specified display width
and number of decimal
places.
DOUBLE Synonym for
PRECISION[(M,D)] as above DOUBLE[(M, D)].
REAL[(M,D)] as above Synonym for
DOUBLE[(M, D)].
DECIMAL[(M[,D])] varies M+2 Floating point number
stored as
char.The range
depends on
M, the display

width.
NUMERIC[(M,D)] as above Synonym for DECIMAL.
DEC[(M,D)] as above Synonym for DECIMAL.
Table 8.5 Continued
Type Range Storage Description
(bytes)
11 525x ch08 1/24/03 3:39 PM Page 198
199
Column Data Types
Date and Time Types
MySQL supports a number of date and time types.These are shown in Table 8.7.With
all these types, you can input data in either a string or numerical format. It is worth not-
ing that a TIMESTAMP column in a particular row will be set to the date and time of the
most recent operation on that row if you don’t set it manually.This is useful for transac-
tion recording.
Table 8.7 Date and Time Data Types
Type Range Description
DATE 1000-01-01 A date.Will be displayed as YYYY-MM-DD.
9999-12-31
TIME -838:59:59 A time.Will be displayed as HH:MM:SS.Note
838:59:59 that the range is much wider than you will
probably ever want to use.
DATETIME 1000-01-01 A date and time.Will be displayed as
00:00:00
YYYY-MM-DD HH:MM:SS.
9999-12-31
23:59:59
TIMESTAMP[(M)] 1970-01-01 A timestamp, useful for transaction reporting.
00:00:00 The display format depends on the value of
M

(see Table 8.8, which follows).
Sometime The top of the range depends on the limit
in 2037 on UNIX timestamps.
YEAR[(2|4)] 70–69 A year.You can specify 2 or 4 digit format.
(1970–2069) Each of these has a different range,
1901–2155 as shown.
Table 8.8 shows the possible different display types for TIMESTAMP.
Table 8.8 TIMESTAMP Display Types
Type Specified Display
TIMESTAMP YYYYMMDDHHMMSS
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY
11 525x ch08 1/24/03 3:39 PM Page 199
200
Chapter 8 Creating Your Web Database
String Types
String types fall into three groups. First, there are plain old strings, that is, short pieces of
text.These are the CHAR (fixed length character) and VARCHAR (variable length character)
types.You can specify the width of each. Columns of type CHAR will be padded with
spaces to the maximum width regardless of the size of the data, whereas VARCHAR
columns vary in width with the data. (Note that MySQL will strip the trailing spaces
from
CHARs when they are retrieved, and from VARCHARs when they are stored.) There is a
space versus speed trade off with these two types, which we will discuss in more detail in
Chapter 11.

Second, there are TEXT and BLOB types.These come in various sizes.These are for
longer text or binary data, respectively.
BLOBs are binary large objects.These can hold any-
thing you like, for example, image or sound data.
In practice, BLOB and TEXT columns are the same except that TEXT is case sensitive
and BLOB is not. Because these column types can hold large amounts of data, they
require some special considerations.We will discuss this in Chapter 11.
The third group has two special types, SET and ENUM.The SET type is used to specify
that values in this column must come from a particular set of specified values. Column
values can contain more than one value from the set.You can have a maximum of 64
things in the specified set.
ENUM is an enumeration. It is very similar to SET, except that columns of this type can
have only one of the specified values or
NULL, and that you can have a maximum of
65,535 things in the enumeration.
We ’ve summarized the string data types in Tables 8.9, 8.10, and 8.11.Table 8.9 shows
the plain string types.
Table 8.9 Regular String Types
Type Range Description
[NATIONAL] 0 to 255 Fixed length string of length M, where M is
CHAR(M) [BINARY] characters between 0 and 255.The NATIONAL keyword spec-
ifies that the default character set should be used.
This is the default in MySQL anyway, but is
included as it is part of the ANSI SQL standard.
The
BINARY keyword specifies that the data
should be treated as not case insensitive. (The
default is case sensitive.)
CHAR 1 Synonym for CHAR(1)
[NATIONAL]

1 to 255 Same as above, except they are variable length.
VARCHAR(M) characters
[BINARY]
Table 8.10 shows the TEXT and BLOB types.The maximum length of a TEXT field in char-
acters is the maximum size in bytes of files that could be stored in that field.
11 525x ch08 1/24/03 3:39 PM Page 200
201
Further Reading
Table 8.10 TEXT and BLOB Types
Type Maximum Length Description
(characters)
TINYBLOB 2
8
-1 A tiny binary large object (BLOB) field
(that is, 255)
TINYTEXT 2
8
-1 A tiny TEXT field
(that is, 255)
BLOB 2
16
-1 A normal sized BLOB field
(that is, 65,535)
TEXT 2
16
-1 A normal sized TEXT field
(that is, 65,535)
MEDIUMBLOB 2
24
-1 A medium sized BLOB field

(that is, 16,777,215)
MEDIUMTEXT 2
24
-1 A medium sized TEXT field
(that is, 16,777,215)
LONGBLOB 2
32
-1 A long BLOB field
(that is, 4,294,967,295)
LONGTEXT 2
32
-1 A long TEXT field
(that is, 4,294,967,295)
Table 8.11 shows the ENUM and SET types.
Table 8.11 SET and ENUM Types
Type Maximum Values in Set Description
ENUM('value1', 65,535 Columns of this type can only
'value2', ) hold one of the values listed
or
NULL.
SET('value1', 64 Columns of this type can hold
'value2', ) a set of the specified values
or NULL.
Further Reading
For more information, you can read about setting up a database at the MySQL online
manual at />11 525x ch08 1/24/03 3:39 PM Page 201