CHAR and VARCHAR

CHAR and VARCHAR

CHAR and VARCHAR are character data types. You give a maximum length in parentheses when specifying the column; if you attempt to insert a value longer than the maximum length, it is truncated.

A CHAR column has a fixed length, and values shorter than the maximum length are padded with spaces when stored. When you retrieve the value from a CHAR column, however, the trailing spaces are removed. The storage requirement for a CHAR column is simply 1 byte per character in the padded stored value.

A VARCHAR column has a variable length. Its storage requirement for each row depends on the length of the data item it contains1 byte for each character is required, plus 1 byte that stores the value's length.

You might use a CHAR(2) column to store the two-letter state code in a person's address. In the sample database, we used VARCHAR(40) for a product's name. Forty characters should be more than enough.

Maximum Lengths

You should always take care to ensure that a CHAR or VARCHAR is long enough for any data you can foresee the column holding. If you attempt to store a value longer than the maximum length, it will simply be truncated with no warning.

TEXT and BLOB

A BLOB is a binary large object that can contain a variable amount of data. TEXT is a similar data type for character data. Both types have the following variations:

• TINYTEXT and TINYBLOB have a maximum size of 255 bytes.

• TEXT and BLOB have a maximum size of 65,535 characters or bytes (64KB).

• MEDIUMTEXT and MEDIUMBLOB have a maximum size of 16,777,215 characters or bytes (16MB).

• LONGTEXT and LONGBLOB have a maximum size of 4,294,967,295 characters or bytes (4GB).

You can use a TEXT column to store free text data, such as a comments field. A BLOB column can be used to store true binary data, such as an image or sound clip, as well as free text data that you do not want to be stored using a character set.

DATE, DATETIME, and TIMESTAMP

The DATE data type in MySQL stores date values in the format YYYY-MM-DD. The DATETIME data type also includes a time element, in the format YYYY-DD-MM HH:MM:SS.

Date Validation

A date column does not perform any validation itself beyond checking that the format of a value meets the stated criteria. It is actually possible to store nonsense dates, such as 2005-02-29 or even 2005-99-99, in a date column.

The TIMESTAMP column type has a special property: Its value takes the current date and time whenever you insert a new row or update an existing row, unless you specify another value for the column.

TIMESTAMP columns are useful in tables for which you want an audit trail of when records were last inserted or updated. You do not need to build the time tracking into your applicationsimply let MySQL do the work.