Types of Data

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

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

TYPE	BYTES	UNSIGNED	SIGNED
TINYINT	1	-128 “127	0 “255
SMALLINT	2	-32768 “32767	0 “65535
MEDIUMINT	3	-8388608 “8388607	0 “16777215
INT or INTEGER	4	-2147483648 “2147483647	0 “4294967295
BIGINT	8	-9223372036854775808 “..5807	0 “18446744073709551615

Floating-Point Numbers

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

TYPE	BYTES	RANGE
FLOAT	4	-10 38 to -10 -38 & 10 -38 to 10 38
DOUBLE, REAL	8	-10 308 to -10 -308 & 10 -308 to 10 308
DECIMAL, NUMERIC	(varies)	-10 308 to -10 -308 & 10 -308 to 10 308

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

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

TYPE	BYTES	NOTES
CHAR	Variable (by column) up to 256	Speed efficiency
VARCHAR	Variable (by string) up to 256	Space efficiency
TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT		See Binary Objects

Temporal

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

TYPE	BYTES	RANGE
DATETIME	8	1000-01-01 00:00:00 to 9999 “12 “31 23:59:59
DATE	4	1000 “01 “01 to 9999 “12 “31
TIME	3	-838:59:59 to 838:59:59
TIMESTAMP	12 or 14	Current time (read-only)

Binary Objects

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

TYPE	BYTES
TINYBLOB	up to 256
BLOB	up to 64K
MEDIUMBLOB	up to 16Meg
LONGBLOB	up to 4Gig

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

TYPE	BYTES	RANGE
ENUM	1 or 2	Up to 64K different ENUMS
SET	1, 2, 3 or 4	Up to 64 individual flags