Learning Mysql

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, 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.

Download 4.24 Mb.

Share with your friends: