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:
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. |