MySQL Data Types

In a table, each column has a type. As we mentioned earlier, an SQL data type is similar to a data type in traditional programming languages. While many languages define a bare-minimum set of types necessary for completeness, SQL goes out of its way to provide types such as DATE that will be useful to everyday users. You could store a DATE type in a more basic numeric type, but having a type specifically dedicated to the nuances of date processing adds to SQL’s ease of use—one of SQL’s primary goals.

Chapter 16 provides a full reference of SQL types supported by MySQL. Table 3-1 is an abbreviated listing of the most common types.

Table 3-1. Common MySQL data types (see Chapter 16 for a full list)

Data type

Description

INT

An integer value. MySQL allows an INT to be either signed or unsigned.

REAL

A floating-point value. This type offers a greater range and more precision than the INT type, but it does not have the exactness of an INT.

CHAR (length)

A fixed-length character value. No CHAR fields can hold strings greater in length than the specified value. Fields of lesser length are padded with spaces. This type is the most commonly used in any SQL implementation.

VARCHAR (length)

A variable-length character value.

TEXT (length)

A variable-length character value.

DATE

A standard date value. The DATE type stores arbitrary dates for the past, present, and future.

TIME

A standard time value. This type stores the time of day independent of a particular date. When used together with a date, a specific date and time can be stored. MySQL additionally supplies a DATETIME type that stores date and time together in one field.

MySQL supports the UNSIGNED attribute for all numeric types. This modifier forces the column to accept only positive (unsigned) numbers. Unsigned fields have an upper limit that is double that of their signed counterparts. For instance, an unsigned TINYINT—MySQL’s single-byte numeric type—has a range of 0 to 255 instead of the -128 to 127 range of its signed counterpart.

MySQL provides more types than those mentioned in Table 3-1. In day-to-day programming, however, you will use these types most often. The size of the data you wish to store plays a large role in the design of your MySQL tables.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset