Learning Mysql

| Level | Code | Message |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'fruit_name' at row 1 |
+---------+------+-------------------------------------------------+
1 row inset 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 inset 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 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.

Download 4.24 Mb.

Share with your friends: