CHAR and VARCHARCHAR 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.
TEXT and BLOBA 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:
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 TIMESTAMPThe 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.
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. |