MySQL Data Types

Before going to deep into the creation of tables, it is important to understand the data types that can be used within MySQL. These data types will be assigned to the columns that will make up the MySQL tables and will need to be understood, not only for ensuring the proper data is being stored but also to ensure a database that performs well. One of the biggest problems associated with database performance is poor design, especially in the choice of data types that are used in the columns. In MySQL the data types available can be broken down into three major categories:

Numeric • Numeric values (Integers, Floating-Point, Fixed-Point and Bit-field) String • Text or binary data

Temporal • Time and dates

Within each category there are numerous specific data types that use varying amounts of memory and disk space, thus having varying effects on performance. Choosing the best data type for the column has a rather small effect on performance in an individual record, but as the database grows these small effects can lead to larger effects. This should be taken into account early in the design process, before they become performance issues.

ABC's of Data Types

A data type refers to what kind of data a value can hold and how that data is stored. When determining the data type that a value should contain, the following "ABC's" should be considered:

A -- Apt - the data needs to be represented in the type most fitting the entity it represents B -- Brief - if you choose the smallest fitting size it saves storage space and helps performance C -- Complete - the type needs to include the best guess for max size this data set will ever grow 84

6.3.1 Numeric Data Type

For storing numeric data, MySQL provides integer data types, floating-point types that store approximate-value (real) numbers, a fixed-point type that stores exact-approximate-value (real) numbers, and a BIT type for bit-field values. When numeric data type is chosen, consider the following factors:

• The range of values the data type represents

• The amount of storage space that column values require

• The column precision and scale for floating-point and fixed-point values

Precision and scale are terms that apply to floating-point and fixed-point values, which can have both an integer part and a fractional part. Precision is the number of significant digits. Scale is the number of digits to the right of the decimal point.

Integer Data Type

The integer data types are summarized in the following table, which indicates the amount of storage per value that each type requires as well as its range. For integer values declared with the UNSIGNED attribute, negative values are not allowed, and the high end of the range shifts upward to approximately double the maximum positive value of the signed range.

Type Storage

Required Signed Range Unsigned Range

TINYINT 1 byte –128 to 127 0 to 255

SMALLINT 2 bytes –32,768 to 32,767 0 to 65,535 MEDIUMINT 3 bytes –8,388,608 to 8,388,607 0 to 16,777,215 INT 4 bytes –2,147,683,648 to 2,147,483,647 0 to 4,294,967,295 BIGINT 8 bytes –9,223,372,036,854,775,808 to

9,223,372,036,854,775,807 0 to 18,446,744,073,709,551,615 Floating-Point Types

The floating-point data types include FLOAT and DOUBLE. Each of these types may be used to represent approximate-value numbers that have an integer part, a fractional part, or both. FLOAT and DOUBLE data types represent values in the native binary floating-point format (IEEE 754) used by the server host's CPU. This is a very efficient type for storage and computation, but values are subject to rounding error.

Type Storage Required Signed Range Unsigned Range FLOAT 4 bytes -3.402823466E+38 to

-1.175494351E-38 0 and 1.175494351E-38 to 3.402823466E+38 DOUBLE 8 bytes -1.7976931348623157E+308 to

-2.2250738585072014E-308 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308

Instructor Notes: In any application, memory usage is an area that must be managed properly to ensure good performance. If memory usage is not considered early on, the consequences can be devastating as the

Fixed-Point Types

The fixed-point data type is DECIMAL. It is used to represent exact-value numbers that have an integer part, a fractional part, or both. DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same number of decimal places and are stored exactly as given when possible. DECIMAL values are not processed quite as efficiently as FLOAT or DOUBLE values (which use the processor's native binary format), but DECIMAL values are not subject to rounding error, so they are more accurate. In other words, there is an accuracy versus speed tradeoff in choosing which type to use. For example, the DECIMAL data type is a popular choice for financial applications involving currency calculations, because accuracy is most important.

The amount of storage required for DECIMAL column values depends on the precision and scale.

Approximately four bytes are required per nine digits on each side of the decimal point. The maximum range is the same as for DOUBLE; the effective range for a given DECIMAL column is determined by precision and scale.

NUMERIC Data Type

The NUMERIC data type in MySQL is a synonym for DECIMAL. (If a column is declared as NUMERIC, MySQL uses DECIMAL in the definition.) Standard SQL allows for a difference between the two types, but in MySQL they are the same. In standard SQL, the precision for NUMERIC must be exactly the number of digits given in the column definition. The precision for DECIMAL must be at least that many digits but is allowed to be more. In MySQL, the precision is exactly as given, for both types.

BIT Types

The BIT data type represents bit-field values. BIT column specifications take a width indicating the number of bits per value, from 1 to 64 bits. For a BIT(n) column, the range of values is 0 to 2 ⁿ – 1, and the storage requirement is approximately INT((n+7)/8) bytes per value.

BIT columns can also be assigned values using numeric expressions. To write literal bit values in binary format, the literal-value notation b'val' can be used, where val indicates a value consisting of the binary digits 0 and 1. For example, b'1111' equals 15 and b'1000000' equals 64. Rule of thumb for storage size:

n=8 takes 1 Byte.

6.3.2 String Data Types

A string data type is a data type modeled on the idea of a formal string. They are commonly used to store text or binary data. Types are available to hold values of varying maximum lengths and can be chosen according to if the values are to be treated as text, binary or integer. Strings are such an important and useful data type that they are implemented in nearly every programming language.

Comparison Values Type Description

Text (Non-Binary) CHAR Fixed-length character string VARCHAR Variable-length character string TEXT Variable-length character string Binary BINARY Fixed-length binary string

VARBINARY Variable-length binary string BLOB Variable-length binary string

Integer ENUM Enumeration consisting of a fixed set of legal values SET Set consisting of a fixed set of legal values

Text String Type Summary

For the storage requirement values, M represents the maximum length of a column. L represents the actual length of a given value, which may be 0 to M.

Type Storage Required Maximum Length CHAR(M) M characters 255 characters

VARCHAR(M) L characters plus 1 or 2 bytes 65,535 characters (subject to limitations) TINYTEXT L characters + 1 byte 255 characters

TEXT L characters + 2 bytes 65,535 characters MEDIUMTEXT L characters + 3 bytes 16,777,215 characters LONGTEXT L characters + 4 bytes 4,294,967,295 characters Binary String Type Summary

For the storage requirement values, M represents the maximum length of a column. L represents the actual length of a given value, which may be 0 to M.

Type Storage Required Maximum Length BINARY(M) M bytes 255 bytes

VARBINARY(M) L bytes plus 1 or 2 bytes 65,535 bytes (subject to limitations) TINYBLOB L + 1 bytes 255 bytes

BLOB L + 2 bytes 65,535 bytes MEDIUMBLOB L + 3 bytes 16,777,215 bytes LONGBLOB L + 4 bytes 4,294,967,295 bytes Integer Values

Type Storage Required Maximum Length ENUM 2^n values = n Bits 65,535 distinct values

SET 1 value = 1 Bit 64 members

6.3.3 Temporal Data Types

Date and time data types are referred to as temporal data types. MySQL provides data types for storing different kinds of temporal information. In the following descriptions, the terms YYYY, MM, DD, hh, mm, and ss stand for a year, month, day of month, hour, minute, and second value, respectively.

Type Storage Required Range

DATE 3 bytes '1000-01-01' to '9999-12-31'

TIME 3 bytes '-838:59:59' to '838:59:59'

DATETIME 8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59' TIMESTAMP 4 bytes '1970-01-01 00:00:00' to mid-year 2037

YEAR 1 byte 1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2))