MySQL® Administrator's Bible

Chapter 5. MySQL Data Types

IN THIS CHAPTER

• Looking at MySQL data types

• Choosing SQL modes

• Using NULL values

• Finding an optimal data type for existing data

Choosing optimal data types is an extremely important part of having a database system that runs smoothly and efficiently. For example, comparing numeric types takes less time than comparing character string types, because character string types have character set and collation considerations. In addition, smaller data is faster to process than larger data. If the data is compact enough to fit entirely in memory, there is a huge reduction in disk I/O, making queries perform even better.

In this chapter you find how to define the standard and nonstandard data types that MySQL offers, as well as advice on the best ways to use them.

Looking at MySQL Data Types

In order to store, retrieve, or process data, the data must be assigned a data type. MySQL includes many of the ISO SQL:2003 standard data types, and adds in more data types. The ISO SQL:2003 standard defines seven categories of data types:

• Character String Types

• National Character String Types

• Binary Large Object String Types

• Numeric Types

• Boolean Types

• Datetime Types

• Interval Types

Character String Types

The ISO SQL:2003 standard defines three character string types. Each string type has more than one keyword associated with it; for example, CHARACTER can be abbreviated as CHAR. The standard character string types and their associated keywords are:

• Fixed-width strings:

  • CHARACTER(length)

  • CHAR(length) ...