Database Reference
In-Depth Information
You could just about store the entire text of this topic in one MEDIUMTEXT database
record!
BLOB
Don't worry, you are not losing your mind from looking at too many datatypes! A BLOB in
MySQL is a Binary Large Object, and is used for storing data. For instance, you could store
a jpeg image within a BLOB column. BLOBs conform to the same size constraints as the
TEXT columns mentioned in Table 3.4, but are called TINYBLOB, BLOB, MEDIUMBLOB
and LONGBLOB. A BLOB column is essentially the same as the corresponding TEXT col-
umn, but when you are searching or ordering it the BLOB will be case sensitive and the
TEXT will not be.
Both BLOB and TEXT objects are really pointers to areas of storage on the server, and so
are not physically stored within the table. This explains why operations on this type of col-
umn are slower than when searching normal column types.
Date Types
One of the commonest things that you store within tables are dates and times. An example
of this would be a weblog, where every access to a website would have the date and time of
that access stored within a text file or a log table in a database.
DATETIME
DATETIME is the date type that I use the most. This allows you to store the date and the
time in one column. You define a DATETIME column simply as follows:
columnname DATETIME
Please refer to Chapter 11 on working with dates and times for details how to insert val-
ues into this column type.
When you retrieve a DATETIME value, MySQL returns a string of the format “YYYY-
MM-DD HH:MM:SS” irrespective of how you have inserted the date/time in the first place.
If you only insert the date part into such a column the HH:MM:SS are set by default to
00:00:00. If you insert a date that is invalid, such as 40 February 2003, MySQL stores this
date as “0000-00-00 00:00:00” to show that there has been an error somewhere in the date.
You can store values in this datatype for all dates and times between the year 1000 and the
year 9999.
TIMESTAMP
TIMESTAMP works in a similar way to DATETIME, in that it stores both the date and time
in a single column. However, the TIMESTAMP datatype has the added functionality that it
will automatically update itself under certain conditions. This is useful if you want to keep
a record of when a database row was created or last updated. Whenever you create a new
Search WWH ::
Custom Search