Databases Reference
In-Depth Information
Records: 3 Duplicates: 0 Warnings: 2
mysql>
SELECT * FROM mytime;
+----+---------------------+
| id | changetime |
+----+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 2006-07-16 01:02:03 |
| 3 | 2006-07-16 01:05:24 |
+----+---------------------+
3 rows in set (0.00 sec)
Note how the current time is stored when we ask to insert a
NULL
value. Now, let's
change the
id
for the first row:
mysql>
UPDATE mytime SET id=4 WHERE id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
SELECT * FROM mytime;
+----+---------------------+
| id | changetime |
+----+---------------------+
| 4 | 2006-07-16 01:05:42 |
| 2 | 2006-07-16 01:02:03 |
| 3 | 2006-07-16 01:05:24 |
+----+---------------------+
3 rows in set (0.00 sec)
As you can see, the timestamp is updated to the current timestamp.
There are other variations on how you can control which column updates
automatically, but if you stick to the previous steps, you'll get the behavior you
want. You can find more examples of using timestamps later in “The Sample Music
Database.”
CHAR[(
width
)]
The most commonly used string type.
CHAR
stores a fixed-length string (such as a
name, address, or city) of length
width
. If a
width
is not provided,
CHAR(1)
is as-
sumed. The maximum value of
width
is 255. With MySQL versions between 4.1.0
and 5.0.2, MySQL accepts values greater than 255 and silently changes the
CHAR
type to the smallest
TEXT
type that is suitable; we discuss the
TEXT
type later in this
section.
You can in fact define a special
CHAR(0) NULL
column that takes up only one bit of
storage. This provides two handy features. First, it allows you to include a dummy
column in a table that doesn't do anything (which might be useful as a placeholder
for a future feature, or to be backward-compatible with an old application). Sec-
ond, it allows you to store one of two values:
NULL
or the empty string
''
, giving
you very compact storage of binary (Boolean) values. To help you understand this
better, let's create a table with a
CHAR(0)
field, and an
id
field to help differentiate
between entries:
