Chapter 11 Data Types

Table of Contents

11.1 Data Type Overview
11.1.1 Numeric Type Overview
11.1.2 Date and Time Type Overview
11.1.3 String Type Overview
11.2 Numeric Types
11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
11.2.2 Fixed-Point Types (Exact Value) - DECIMAL, NUMERIC
11.2.3 Floating-Point Types (Approximate Value) - FLOAT, DOUBLE
11.2.4 Bit-Value Type - BIT
11.2.5 Numeric Type Attributes
11.2.6 Out-of-Range and Overflow Handling
11.3 Date and Time Types
11.3.1 The DATE, DATETIME, and TIMESTAMP Types
11.3.2 The TIME Type
11.3.3 The YEAR Type
11.3.4 YEAR(2) Limitations and Migrating to YEAR(4)
11.3.5 Automatic Initialization and Updating for TIMESTAMP
11.3.6 Fractional Seconds in Time Values
11.3.7 Conversion Between Date and Time Types
11.3.8 Two-Digit Years in Dates
11.4 String Types
11.4.1 The CHAR and VARCHAR Types
11.4.2 The BINARY and VARBINARY Types
11.4.3 The BLOB and TEXT Types
11.4.4 The ENUM Type
11.4.5 The SET Type
11.5 Extensions for Spatial Data
11.5.1 Spatial Data Types
11.5.2 The OpenGIS Geometry Model
11.5.3 Using Spatial Data
11.6 Data Type Default Values
11.7 Data Type Storage Requirements
11.8 Choosing the Right Type for a Column
11.9 Using Data Types from Other Database Engines

MySQL supports a number of data types in several categories: numeric types, date and time types, string (character and byte) types, and spatial types. This chapter provides an overview of these data types, a more detailed description of the properties of the types in each category, and a summary of the data type storage requirements. The initial overview is intentionally brief. The more detailed descriptions later in the chapter should be consulted for additional information about particular data types, such as the permissible formats in which you can specify values.

Data type descriptions use these conventions: