Working with Database Structures - Learning MySQL - page 198

mysql> INSERT INTO Contacts VALUES('Saleh', ' not sure');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Contacts VALUES('Susan', ' not sure');
Query OK, 1 row affected (0.00 sec)
You should use the BLOB or TEXT types described later if you don't want this be-
havior.
That concludes our discussion of the six common column types used in MySQL. You'll
find examples using some of these types in “The Sample Music Database,” later in this
chapter. The remainder of this section covers the other type choices available in
MySQL, beginning with the other choices for numeric values.
Other integer types
In “Common column types,” we saw the INT type for storing integer numbers. In this
section, we'll look at a few other integer types that you can use. We recommend that
you always choose the smallest possible type to store values. For example, if you're
storing age values, choose TINYINT instead of the regular INT . Smaller types require less
storage space; this reduces disk and memory requirements and speeds up the retrieval
of data from disk. Indeed, column type tuning is a key step that professional database
tuners use in optimizing database applications.
Here is the list of the integer types—besides INT —that you can choose from. Be aware
that the general issues described for INT apply to these types as well:
BOOLEAN
A type introduced in MySQL 4.1 that stores a Boolean value of false (zero) or true
(nonzero). For example, it might be used to store whether a person is alive (true)
or dead (false), a customer is active (true) or inactive (false), or whether a customer
wants to receive emails (true) or not (false). The BOOLEAN type has the synonyms
BOOL and BIT . It is equivalent to TINYINT(1) , and so requires one byte of storage
space; you can achieve more compact, one-bit Boolean values by using CHAR(0) , as
described previously.
TINYINT[( width )] [UNSIGNED] [ZEROFILL]
Stores integer (whole number) values in the range -128 to 127. The width ,
UNSIGNED , and ZEROFILL options behave as for INT . When UNSIGNED is used, a column
can store values in the range 0 to 255. A TINYINT column requires one byte of storage
space.
SMALLINT[( width )] [UNSIGNED] [ZEROFILL]
Stores integer (whole number) values in the range -32,768 to 32,767. The width ,
UNSIGNED , and ZEROFILL options behave as for INT . When UNSIGNED is used, a column
can store values in the range 0 to 65,535. A SMALLINT column requires two bytes of
storage space.