Data Types
Variables in MySQL stored programs can be assigned any of the data types available to columns in MySQL tables. We previewed most of the data types earlier, in Table 3-1.
All variables in MySQL stored programs are scalars , which is to say variables that store only a single item. There are no equivalents to arrays, records, or structures such as you can find in some other programming languages.
3.5.1. String Data Types
MySQL supports two basic string data types : CHAR and VARCHAR. CHAR stores fixed-length strings, while VARCHAR stores variable-length strings. If a CHAR variable is assigned a value shorter than its declared length, it will be blank-padded out to the declared length. This does not occur with VARCHAR variables.
When used in MySQL tables, the choice of CHAR or VARCHAR can be significant because it can affect the amount of disk storage needed. However, in stored programs, the additional memory requirements will be minimal and, use CHARs and VARCHARs can be used interchangeably in all expressions, there is little advantage to either data type. We generally use VARCHARs because they are capable of storing longer strings.
The CHAR data type can store a maximum of 255 bytes, and the VARCHAR a maximum of 65,532 bytes.
3.5.1.1. The ENUM data type
The ENUM data type is used to store one of a set of permissible values. These values can be accessed as their string value or as their indexed position in the set of possibilities. If you attempt to assign a value into an ENUM that does not appear in the list, MySQL will either issue a warning and insert a NULL orif the sql_mode includes one of the "strict" values (see the later section "MySQL 5 "Strict" Mode")issue an error.
Example 3-15 illustrates the use of ENUMs in stored programs.
Example 3-15. Using ENUMs in stored programs
|
CREATE PROCEDURE sp_enums(in_option ENUM('Yes','No','Maybe')) BEGIN DECLARE position INTEGER; SET position=in_option; SELECT in_option,position; END -------------- Query OK, 0 rows affected (0.01 sec) -------------- CALL sp_enums('Maybe') -------------- +-----------+----------+ | in_option | position | +-----------+----------+ | Maybe | 3 | +-----------+----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) -------------- CALL sp_enums(2) -------------- +-----------+----------+ | in_option | position | +-----------+----------+ | No | 2 | +-----------+----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) -------------- CALL sp_enums('What?') -------------- ERROR 1265 (01000): Data truncated for column 'in_option' at row 1 |
3.5.1.2. The SET data type
The SET type is similar to the ENUM type, except that multiple values from the list of allowable values can occur in the variables (see Example 3-16). As with the ENUM type, an attempt to assign a value not in the list will generate an error in "strict" mode, and a warning otherwise.
Example 3-16. Behavior of SET variables in stored programs
|
CREATE PROCEDURE sp_set(in_option SET('Yes','No','Maybe')) BEGIN SELECT in_option; END -------------- Query OK, 0 rows affected (0.00 sec) -------------- CALL sp_set('Yes') -------------- +-----------+ | in_option | +-----------+ | Yes | +-----------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) -------------- CALL sp_set('Yes,No,Maybe') -------------- +--------------+ | in_option | +--------------+ | Yes,No,Maybe | +--------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) -------------- CALL sp_set('Yes,No,Go away') -------------- ERROR 1265 (01000): Data truncated for column 'in_option' at row 1 |
3.5.2. Numeric Data Types
MySQL supports two families of numeric types:
- Exact numeric types such as the INT and DECIMAL types
- Approximate numeric types such as FLOAT
Accurate numeric types store an exact value for a number. The various INT types (INT, BIGINT, TINYINT) differ in that they use different amounts of storage, which therefore restricts the magnitude of the numbers that they can store. Each type can be signed (capable of storing positive or negative numbers) or unsigned, which further restricts the maximum values that the type may store (allowing a variable to be unsigned doubles the maximum possible number that can be stored). Table 3-9 shows the limits for the various integer types.
|
Data type |
Storage (bits) |
Signed maximum |
Unsigned maximum |
|---|---|---|---|
|
TINYINT |
8 |
127 |
255 |
|
SMALLINT |
16 |
32767 |
65535 |
|
MEDIUMINT |
24 |
8388607 |
16777215 |
|
INT |
32 |
2147483647 |
4294967295 |
|
BIGINT |
64 |
9223372036854775807 |
9223372036854775807 |
Floating-point data types (FLOAT, DOUBLE, REAL) store numbers of variable size and precision. In MySQL tables, FLOAT types use 32 bits of storage by default, while DOUBLE uses 64 bits of storage.
Be aware, however, that the floating-point data types store approximate representations of numbers. Most of the time this is unimportant, but in some circumstances you will want to use the precision data types, such as DECIMAL or NUMERIC, to avoid rounding errors that can occur when performing mathematical operations on floating-point numbers.
3.5.3. Date and Time Data Types
MySQL stores date-times with a precision down to one second. In MySQL tables, columns of the DATE data type can store the date part of a date-time only, while the DATETIME can store both a date and a time.
3.5.4. TEXT and BLOB Data Types
In MySQL tables, the TEXT data type can store up to 64K of data, and LONGTEXT can store up to 4,294,967,295 characters. BLOB and LONGBLOB data types can store similar amounts of data, but are able to store binary as well as character data.