There are several types of data: integers, floating-point numbers , strings, temporal measurements, and binary objects. There are several types of each kind of variable, each one using a different amount of memory and storing a different range of values.
Integers are whole numbers. They have a range of 256 Number_Of_Bytes and can be either unsigned or signed numbers. (In the latter case, the maximum value is half the unsigned maximum because the numbers are almost evenly split between positive and negative.) There are several types of INTs, each having a different size (Table 12.3).
Table 12.3. Different-Sized INTs
Because floating-point numbers store digits in scientific notation, fractional values can be used. There are two kinds of floating-point numbers: one is twice as large as the other. A FLOAT uses 4 bytes. The other, called a DOUBLE, uses 8. A third type, DECIMAL, stores double data, but as a string rather than as a binary value. In some cases this wins speed at the cost of storage space (Table 12.4).
Table 12.4. Ranges and Sizes of Various Floats
Range is not the only difference between FLOAT and DOUBLE. They also have different degrees of precision. Not infrequently an application works well within the bounds of a FLOAT but uses DOUBLE for the accuracy it needs.
Strings are chains of characters . There are three kinds: CHAR, VARCHAR, and TEXT. CHAR and VARCHAR hold short strings. Unlike VARCHAR, every CHAR in a column has the same length. This wastes space but saves time in accessing the table.
Each TEXT holds as many characters as can be counted by an INT it is named after (Table 12.5).
Table 12.5. Different String Types
Temporal objects include DATETIME (which locates any second within 10 millenia) and its components : DATE and TIME as well as the internally updated variable TIMESTAMP (Table 12.6).
Table 12.6. Temporal Ranges and Sizes
BLOBs are large binary files that can hold literally anything digital: pictures, sounds, machine code, and so on. The TEXT variables in MySQL version 3.23 are implemented as BLOBs (Table 12.7).
Table 12.7. BLOB Sizes
Sets and Enums
An ENUM contains a string. It must be one of the symbols defined in the ENUM declaration (e.g., an ENUM called gender might have "Female" and "Male"). A SET is similar but each record can have zero, one, or more of the named symbols (Table 12.8).
Table 12.8. ENUMs and SETs