HomeMySQL Page 8 - Data Definition Language, Part 1
4.10 Column Types - MySQL
Studying for the MySQL Certification exam? This article, the first of two parts, covers roughly 10 percent of the material that will appear on the exam, including general database and table properties, storage engines and table types, and more. It is excerpted from chapter four of the book MySQL Certification Guide written by Paul Dubois et. al. (Sams, 2004, ISBN: 0672326329).
MySQL can work with many different kinds of data. Generally speaking, data values can be grouped into three categories:
Numeric values. Numbers may or may not have a fractional part and may have a leading sign. For example, 14, -428.948, and +739 all are legal numbers. Integer values have no fractional part; columns for values with a fractional part can be declared to have either a fixed or variable number of decimal places. Numeric columns can be declared to be unsigned to prevent negative values from being accepted in the column.
String values. Strings may be case sensitive or case insensitive. Strings may store characters or raw data values that contain arbitrary byte values. Strings are written within quotes (for example, 'I am a string 'or "I am a string "). String columns can be declared as either fixed length or variable length.
Temporal values. Temporal values include dates (such as '2005-11-03 '), times (such as '14:23:00 '), and values that have both a date and a time part ( '2005-11-03 14:23:00 '). MySQL also supports a special temporal type that represents year-only values efficiently. Date and time values can be written as quoted strings and may sometimes be written as numbers in contexts where numeric temporal values are understood.
When you create a table, the declaration for each of its columns includes the column name, a datatype specification that indicates what kind of values the column may hold, and possibly some options that more specifically define how MySQL should handle the column. For example, the following statement creates a table named people, which contains a numeric column named id and two 30-byte string columns named first_name and last_name:
CREATE TABLE people
The column definitions in this CREATE TABLE statement contain only names and column datatype specifications. To control the use of a column more specifically, options may be added to its definition. For example, to disallow negative values in the id column, add the UNSIGNED option. To disallow NULL (missing or unknown) values in any of the columns, add NOT NULL to the definition of each one. The modified CREATE TABLE statement looks like this:
CREATE TABLE people
id INT UNSIGNED NOT NULL,
first_name CHAR(30) NOT NULL,
last_name CHAR(30) NOT NULL
For each of the general datatype categories (number, string, date, and time), MySQL has several specific column types from which to choose. It's important to properly understand the datatypes that are available for representing data, to avoid choosing a column type that isn't appropriate. The following sections provide a general description of the column datatypes and their properties. For additional details, the MySQL Reference Manualprovides an extensive discussion on column datatypes.
4.10.1 Numeric Column Types
MySQL provides numeric column types for integer values, values with a fixed number of decimal places, and floating-point values that have a variable number of decimal places. When you choose a numeric column datatype, consider the following factors:
The range of values the datatype represents
The amount of storage space that column values require
The display width indicating the maximum number of characters to use when presenting column values in query output
The column precision (number of digits before the decimal) for values with a scale
126.96.36.199 Integer Column Types
Integer datatypes include TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. Smaller datatypes require less storage space, but are more limited in the range of values they represent. For example, a TINYINT column has a small range (–128 to 127), but its values take only one byte each to store. INT has a much larger range (–2,147,483,648 to 2,147,483,647) but its values require four bytes each. The integer datatypes are summarized in the following table, which indicates the amount of storage each type requires as well as its range. For integer values declared with the UNSIGNED option, negative values are not allowed, and the high end of the range shifts upward to approximately double the maximum value.
-128 to 127
0 to 255
-32,768 to 32,767
0 to 65,535
-8,388,608 to 8,388,607
0 to 16,777,215
-2,147,683,648 to 2,147,483,647
0 to 4,294,967,295
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
0 to 18,446,744,073,709,551,615
Integer datatypes may be declared with a display width, which affects the number of characters used to display column values in query output. For example, assume that you declare an INT column with a display width of 4 like this:
The result is that values in the century column will usually be displayed four digits wide.
It's important to remember that the display width is unrelated to the range of the datatype. The display width you define for a column affects only the maximum number of digits MySQL will use to display column values. Values shorter than the display width are padded with spaces as necessary. Note also that the display width is not a hard limit; it won't cause output truncation of a value that's too long to fit within the width. Instead, the full value will be shown. For example, assume that you've inserted the number 57622 into the century column. When you SELECT the column in a query, MySQL will display the entire value (57622) rather than just the first four digits of the value.
If you don't specify a display width for an integer type, MySQL chooses a default based on the number of characters needed to display the full range of values for the type (including the minus sign). For example, SMALLINT has a default display width of 6 because the widest possible value is -32768.
188.8.131.52 Floating-Point and Fixed-Decimal Column Types
The floating-point datatypes include FLOAT and DOUBLE. The fixed-point datatype is DECIMAL. Each of these types may be used to represent numeric values that have a scale, or fractional part. FLOAT and DOUBLE datatypes represent floating-point values in the native binary format used by the server host's CPU. This is a very efficient type for storage and computation, but values are subject to rounding error. DECIMAL uses a fixed-decimal storage format: All values in a DECIMAL column have the same number of decimal places. Values are stored in string format using one byte per digit. Numbers represented as strings cannot be processed as quickly as numbers represented in binary, so operations on DECIMAL columns are slower than operations on FLOAT and DOUBLE columns. DECIMAL values are not subject to rounding error when stored, which makes the DECIMAL column type a popular choice for financial applications involving currency calculations. However, be aware that currently MySQL does internal calculations using floating-point arithmetic, which can produce rounding error in the result.
FLOAT and DOUBLE are used to represent single-precision and double-precision floating-point values. They use 4 and 8 bytes each for storage, respectively. By default, MySQL represents values stored in FLOAT and DOUBLE columns to the maximum precision allowed by the hardware, but you can specify a display width and precision in the column definition. The following single-precision column definition specifies a display width of 10 digits, with a precision of 4 decimals:
DECIMAL columns may also be declared with a display width and scale. If you omit them, the defaults are 10 and 0, so the following declarations are equivalent:
If you want to represent values such as dollar-and-cents currency figures, you can do so using a two-digit scale:
The amount of storage required for DECIMAL column values depends on the type. Normally, the number of bytes of storage required per value is equal to the display width plus 2. For example, DECIMAL(6,3) requires 8 bytes: the display width is 6 and 2 bytes are needed to store the sign and decimal point. If the scale is 0, no decimal point needs to be stored, so one fewer byte is required. If the column is UNSIGNED, no sign character needs to be stored, also requiring one fewer byte.
This chapter excerpt is from MySQL Certification Guide by Paul Dubois et al. (Sams, 2004, ISBN: 0672326329 ). Check it out at your favorite bookstore today. Buy this book now.