5.1. Data Type Overview

MySQL enables you to store several different types of data, and it's important to understand what data types are available so that you can define your tables appropriately for the information they'll contain. Generally speaking, data values can be grouped into the following categories:

• Numeric values. Numbers may or may not have a fractional part and may have a leading sign. For example, 14, -428.948, and +739 all are legal numbers. Integer values have no fractional part; columns for values with a fractional part can be declared to have either a fixed or variable number of decimal places. Numeric columns can be declared to be unsigned to prevent negative values from being accepted in the column. A BIT data type holds bit-field values, and a b'nnnn' notation is available for writing literal bit values.

• String values. Strings may be non-binary or binary, to store characters or raw bytes, respectively. Strings that store characters have a character set and collation; they can be case sensitive or case insensitive. Strings are written within quotes (for example, 'I am a string'). String columns can be declared as either fixed length or variable length. BLOB values (binary large objects) are treated as strings in MySQL.

• Temporal values. Temporal values include dates (such as '2005-11-03'), times (such as '14:23:00'), and values that have both a date and a time part ('2005-11-03 14:23:00'). MySQL also supports a special temporal type that represents year-only values efficiently. Date and time values can be written as quoted strings and may sometimes be written as numbers in contexts where numeric temporal values are understood.

MySQL also supports manipulation of spatial values using a set of spatial data types. Spatial types are not covered in this study guide or on the exam. See the MySQL Reference Manual for details.

When you create a table, the declaration for each of its columns includes the column name, a data type that indicates what kind of values the column may hold, and possibly some attributes (options) that more specifically define how MySQL should handle the column. For example, the following statement creates a table named people, which contains an integer-valued numeric column named id and two 30-character string columns named first_name and last_name:

 CREATE TABLE people (     id         INT,     first_name CHAR(30),     last_name  CHAR(30) ); 

The column definitions in that CREATE TABLE statement contain only names and data types. To more specifically control how MySQL handles a column, add attributes to the column definition. For example, to disallow negative values in the id column, add the UNSIGNED attribute. To disallow missing or unknown values in the columns, add NOT NULL to each column definition so that NULL values cannot be stored. The modified CREATE TABLE statement looks like this:

 CREATE TABLE people (     id         INT UNSIGNED NOT NULL,     first_name CHAR(30) NOT NULL,     last_name  CHAR(30) NOT NULL ); 

For additional control over input data handing, you can set the SQL mode to determine how forgiving or strict MySQL Server is about accepting invalid values.

For each of the general data categories (number, string, and temporal), MySQL has several specific data types from which to choose. It's important to properly understand what data types are available for representing data, to avoid choosing a type that isn't appropriate. The following sections describe these data types and their properties. For additional details, see the MySQL Reference Manual.