MySQL Stored Procedure Programming

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.

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.

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 ...