BIGINT |
BIGINT[( display_size )] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL] |
8 bytes
Largest integer type, supporting range of whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (0 to 18,446,744,073,709,551,615 unsigned). MySQL performs all arithmetic using signed BIGINT or DOUBLE values, but BIGINT has performing arithmetic on unsigned values. You should therefore avoid performing any arithmetic operations on unsigned BIGINT values greater than 9,223,372,036,854,775,807. If you do, you may end up with imprecise results.
DEC |
Synonym for DECIMAL .
DECIMAL |
DECIMAL[( precision , [ scale ])] [ZEROFILL] |
precision + 2 bytes
Stores floating-point numbers where precision is critical, such as for monetary values. DECIMAL types require you to specify the precision and scale. The precision is the number of significant digits in the value. The scale is the number of those digits that come after the decimal point. For example, a BALANCE column declared as DECIMAL(9 , 2) would store numbers with nine significant digits, two of which are to the right of the decimal point. The range for this declaration would be -9,999,999.99 to 9,999,999.99. If you specify a number with more decimal points, it is rounded to fit the proper scale. Values beyond the range of the DECIMAL are clipped to fit within the range.
MySQL actually stores DECIMAL values as strings, not as floating-point numbers. It uses one character for each digit, one character for the decimal points when the scale is greater than 0, and one character for the sign of negative numbers. When the scale is 0, the value contains no fractional part. Prior to MySQL 3.23, the precision actually had to include space for the decimal and sign. This requirement is no longer in place, in accordance with the ANSI specification.
ANSI SQL supports the omission of precision and/or scale where the omission of scale creates a default scale of zero and the omission of precision defaults to an implementation-specific value. In the case of MySQL, the default precision is 10.
DOUBLE |
DOUBLE[( display_size , digits )] [ZEROFILL] |
8 bytes
A double-precision floating-point number. This type stores large floating-point values. DOUBLE columns store negative values from -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and positive numbers from 2.2250738585072014E-308 to 1.7976931348623157E+308.
DOUBLE PRECISION |
Synonym for DOUBLE .
FLOAT |
FLOAT[( display_size , digits )] [ZEROFILL] |
4 bytes
A single-precision floating-point number. This type is used to store small floating-point numbers. FLOAT columns can store negative values between -3.402823466E+38 and -1.175494351E-38, 0, and positive values between 1.175494351E-38 and 3.402823466E+38.
INT |
INT[( display_size )] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL] |
4 bytes
A basic whole number with a range of -2,147,483,648 to 2,147,483,647 (0 to 4,294,967,295 unsigned).
INTEGER |
Synonym for INT .
MEDIUMINT |
MEDIUMINT[( display_size )] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL] |
3 bytes
A basic whole number with a range of -8,388,608 to 8,388,607 (0 to 16,777,215 unsigned).
NUMERIC |
Synonym for DECIMAL .
REAL |
Synonym for DOUBLE .
SMALLINT |
SMALLINT[( display_size )] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL] |
2 bytes
A basic whole number with a range of -32,768 to 32,767 (0 to 65,535 unsigned).
TINYINT |
TINYINT[( display_size )] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL] |
1 byte
A basic whole number with a range of -128 to 127 (0 to 255 unsigned).
String data types store various kinds of text data. There are several types to accommodate data of different sizes. For each size, there is a type that sorts and compares entries in a case-insensitive fashion in accordance with the sorting rules for the default character set. A corresponding binary type performs simple byte-by-byte sorts and comparisons. In other words, binary values are case sensitive. For CHAR and VARCHAR , the binary types are declared using the BINARY attribute. The TEXT types, however, have corresponding BLOB types as their binary counterparts.
BLOB |
Binary form of TEXT .
CHAR |
CHAR( size ) [BINARY] |
Specified by the size value in a range of to 255 (1 to 255 prior to MySQL 3.23)
size bytes
A fixed-length text field. String values with fewer characters than the column's size will be right padded with spaces. The right padding is removed on retrieval of the value from the database.
CHAR(0) fields are useful for backward compatibility with legacy systems that no longer store values in the column.
CHARACTER |
Synonym for CHAR .
CHARACTER VARYING |
Synonym for VARCHAR .
LONGBLOB |
Binary form of LONGTEXT .
LONGTEXT |
LONGTEXT |
0 to 4,294,967,295
Length of value + 4 bytes
Storage for large text values. While the theoretical limit on the size of the text that can be stored in a LONGTEXT column exceeds 4 GB, the practical limit is much less due to limitations of the MySQL communication protocol and the amount of memory available to both the client and server ends of the communication.
MEDIUMBLOB |
Binary form of MEDIUMTEXT .
MEDIUMTEXT |
MEDIUMTEXT |
0 to 16,777,215
Length of value + 3 bytes
Storage for medium- sized text values.
NCHAR |
Synonym of CHAR .
NATIONAL CHAR |
Synonym of CHAR .
NATIONAL CHARACTER |
Synonym of CHAR .
NATIONAL VARCHAR |
Synonym of VARCHAR .
TEXT |
TEXT |
0 to 65,535
Length of value + 2 bytes
Storage for most text values.
TINYBLOB |
Binary form of TINYTEXT .
TINYTEXT |
TINYTEXT |
0 to 255
Length of value + 1 byte
Storage for short text values.
VARCHAR |
VARCHAR(size) [BINARY] |
Specified by the size value in a range of to 255 (1 to 255 prior to MySQL 3.23)
Length of value + 1 byte
Storage for variable-length text. Trailing spaces are removed from VARCHAR values.
MySQL date types are extremely flexible tools for storing date information. They are also extremely forgiving in the belief that it is up to the application, not the database, to validate date values. MySQL only checks that months range from 0 to 12 and dates range from to 31. February 31, 2001, is therefore a legal MySQL date. More useful, however, is the fact that February 0, 2001, is a legal date. In other words, you can use 0 to signify dates in which you do not know a particular piece of the date.
Though MySQL is somewhat forgiving on the input format, you should attempt to format all date values in your applications in MySQL's native format to avoid any confusion. MySQL always expects the year to be the left-most element of a date format. If you assign an illegal value in an SQL operation, MySQL inserts a zero for that value.
MySQL automatically converts date and time values to integer values when used in an integer context.
DATE |
DATE |
YYYY-MM-DD (2001-01-01)
3 bytes
Stores a date in the range of January 1, 1000 (' 1000-01-01 ') to December 31, 9999 (' 9999-12-31 ') in the Gregorian calendar.
DATETIME |
DATETIME |
YYYY-MM-DD hh:mm:ss (2001-01-01 01:00:00)
8 bytes
Stores a specific time in the range of 12:00:00 AM, January 1, 1000 (' 1000-01-01 00:00:00 ') to 11:59:59 P.M., December 31, 9999 (' 9999-12-31 23:59:59 ') in the Gregorian calendar.
TIME |
TIME |
hh:mm:ss (06:00:00)
3 bytes
Stores a time value in the range of midnight (' 00:00:00 ') to one second before midnight (' 23:59:59 ').
TIMESTAMP |
TIMESTAMP[( display_size )] |
YYYYMMDDhhmmss (20010101060000)
4 bytes
A simple representation of a point in time down to the second in the range of midnight on January 1, 1970, to one minute before midnight on December 31, 2037. Its primary utility is keeping track of table modifications. When you insert a NULL value into a TIMESTAMP column, the current date and time are inserted instead. When you modify any value in a row with a TIMESTAMP column, the first TIMESTAMP column will be automatically updated with the current date and time.
YEAR |
YEAR[( size )] |
YYYY (2001)
1 byte
Stores a year of the Gregorian calendar. The size parameter enables you to store dates using 2 digit years or 4 digit years . The range for a YEAR(4) is 1900 to 2155; the range for a YEAR(2) is 1970-2069.The default size is YEAR(4) .
MySQL's complex data types ENUM and SET are just special string types. We list them separately because they are conceptually more complex and represent a lead into the SQL3 data types that MySQL may support in the future.
ENUM |
ENUM( value1 , value2 , ...) |
1-255 members : 1 byte
256-65,535 members: 2 bytes
Stores one value of a predefined list of possible strings. When you create an ENUM column, you provide a list of all possible values. Inserts and updates are allowed to set the column to values only from that list. Any attempt to insert a value that is not part of the enumeration will cause an empty string to be stored instead.
You may reference the list of possible values by index where the index of the first possible value is 0. For example:
SELECT COLID FROM TBL WHERE COLENUM = 0;
Assuming COLID is a primary key column and COLENUM is the column of type ENUM , this SQL will retrieve the primary keys of all rows in which the COLENUM value equals the first value of that list. Similarly, sorting on ENUM columns happens according to index, not string value.
The maximum number of elements allowed for an ENUM column is 65,535.
SET |
SET(value1, value2, ...) |
1-8 members: 1 byte
9-16 members: 2 bytes
17-24 members: 3 bytes
25-32 members: 4 bytes
33-64 members: 8 bytes
A list of values taken from a predefined set of values. A field can contain any numberincluding noneof the strings specified in the SET statement. A SET is basically an ENUM that allows each field to contain more than one of the specified values. A SET , however, is not stored according to index, but as a complex bit map. Given a SET with the members Orange , Apple , Pear , and Banana , each element is represented by an "on" bit in a byte, as shown Table 1-2.
Member | Decimal value | Bitwise representation |
---|---|---|
Orange | 1 | 0001 |
Apple | 2 | 0010 |
Pear | 4 | 0100 |
Banana | 8 | 1000 |
In this example, the values Orange and Pear are stored in the database as 5 (0101).
You can store a maximum of 64 values in a SET column. Though you can assign the same value multiple times in an SQL statement updating a SET column, only a single value will actually be stored.