# 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

Flash and XML[c] A Developer[ap]s Guide
ISBN: 201729202
EAN: N/A
Year: 2005
Pages: 160