Working with Database Structures - Learning MySQL - page 204

| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'fruit_name' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO fruits_set VALUES ('Apple,Orange');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM fruits_set;
+--------------+
| fruit_name |
+--------------+
| Apple |
| |
| Apple,Orange |
+--------------+
3 rows in set (0.01 sec)
Again, note that we can store multiple values from the set in a single field, and that
an empty string is stored for invalid input.
As with numeric types, we recommend that you always choose the smallest possible
type to store values. For example, if you're storing a city name, use CHAR or VARCHAR ,
rather than, say, the TEXT type. Having shorter columns helps keep your table size down,
which in turns helps performance when the server has to search through a table.
Using a fixed size with the CHAR type is often faster than using a variable size with
VARCHAR , since the MySQL server knows where each row starts and ends, and can
quickly skip over rows to find the one it needs. However, with fixed-length fields, any
space that you don't use is wasted. For example, if you allow up to 40 characters in a
city name, then CHAR(40) will always use up 40 characters, no matter how long the city
name actually is. If you declare the city name to be VARCHAR(40) , then you'll use up only
as much space as you need, plus one byte to store the name length. If the average city
name is 10 characters long, this means that using a variable length field will take up 29
fewer bytes per entry; this can make a big difference if you're storing millions of
addresses.
In general, if storage space is at a premium or you expect large variations in the length
of strings that are to be stored, use a variable-length field; if performance is a priority,
use a fixed length.
Keys and Indexes
You'll find that almost all tables you use will have a PRIMARY KEY clause declared in their
CREATE TABLE statement. The reasons why you need a primary key are discussed in
Chapter 4. This section discusses how primary keys are declared, what happens behind
the scenes when you do so, and why you might want to also create other keys and
indexes on your data.