slow things down considerably. TEXT and BLOB types are very large and do not make good candidates
for indexes.Miscellaneous Types
There are basically three miscellaneous types; ENUM, SET, and DATE/TIME types. These are lumped all
together here because they do not quite fit in with the previous two types. I will cover the SET and ENUM
types today. Day 16, "MySQL and Time," includes information about the DATE/TIME types. That's the day
you will learn about all the MySQL date and time features.
ENUM Type
The ENUM type is an enumerated list. Meaning, that this column can only store one of the values that are
declared in the given list. The ENUM column can contain only one of these values. The syntax for declaring
an ENUM type is as follows:
CREATE TABLE Test(
Return ENUM('Y','N') DEFAULT 'N',
Size ENUM('S','M','L','XL','XXL'),
Color ENUM('Black','Red','White')
)
You may have up to 65,535 items in your enumerated list. ENUM types make a good choice for combo
boxes on Web pages or anywhere where a person must choose from a list of values. Remember, an
ENUM type must either contain a value from the list or NULL. If you try to insert a value that is not in the
list, a blank value will inserted.SET Type
The SET type is very similar to the ENUM type. The SET type, like the ENUM type, stores a list of values. The
difference is that in a SET type, you can choose more than one option to store. A SET type can contain up to
64 items. SET types are a good choice for options on a Web page where a user can select more then one
value. The syntax for creating a SET type looks like the following:
CREATE Table Test(
Advertiser SET('Web Page','Television','Newspaper')
)The column created from this statement would be able to hold the following values:
"Web Page"
"Television,Newspaper'
" "
When you insert a record into a SET column, you insert the values using one set of quotation marks with
a comma separating the values. For example, to insert a record containing two values from the previous
example's table, the syntax would look like the following:
INSERT INTO Test (Advertiser) values('Web Page, Television')
For this reason, never use a SET value that contains a comma because it will definitely mess things up
for you.
The reason the SET and ENUM values are grouped into miscellaneous types is because they look and
act like strings, but MySQL stores them as numbers. For this reason, they are processed more
efficiently than a regular string. They also can be manipulated using numeric operations. For example,
you can use the number that MySQL uses to retrieve the values stored in the table. Look at the
following example:
SELECT * FROM Test WHERE Advertiser = 1
This statement will return all values from Test where the Advertiser equals one Web page. To see
the values that MySQL uses, you can use the following statement:
SELECT Advertiser, Advertiser +0 FROM Test
The SET and ENUM types are very useful column types and should be considered when designing your
database. Remember that ENUM types allow only one choice from the list, and SET types allow more
than one choice.