MySQL column type reference

Mysql| reference This appendix describes each column type provided by MySQL. See chapter 2nd for detailed instructions on taking advantage of each type. Unless otherwise stated, the listed types are already available in MySQL3.21.0.
A description of the type name is given in the following conventions:
square brackets ([]) optional information.
M Maximum display width. Unless otherwise noted, m should be an integer between 1 and 255.
D the number of decimal digits of a type with a decimal part. D is an integer between 0 and 30. D should be less than or equal to M-2. Otherwise, the value of M will be adjusted to D + 2.
In ODBC terminology, M and D are called "precision" and "decimal digits."
To describe each type, provide one or more of the following information:
DescriptionA brief description of the type.
An optional property keyword that is related to the type in a CREATE TABLE or ALTER TABLE statement for the allowed property. Properties are listed in alphabetical order, but this order does not necessarily correspond to the order used in the CREATE TABLE or Alte R table. See Appendix D for syntax for these statements. The properties listed in the description for each column type are properties other than the common properties.
length allowedThe maximum length allowed for column values of the string type.
Take value rangeThe range of values that the type can represent for numeric or date and time types. For integer numeric types, two ranges of values are given, because the integer sequence can be signed or unsigned, with a different range of signed or unsigned values.
0 ValueFor date and time types, if you insert an illegal value into the column, a value of "0" is stored.
Default ValueThe default value for the D E FA U LT property is not explicitly given in the type description.
Storage RequirementsThe number of bytes required to store the value of this type. For some types, this value is a fixed word. This value is variable in some types depending on the value stored in the column.
CompareFor string types, comparisons are case-sensitive. This is valid for sorting and indexing because these operations are based on comparisons.
SynonymsA synonym for the type name.
NotesSome miscellaneous instructions about the type.
Common PropertiesSome properties apply to all types or to almost all types. They are listed here, not listed in each type of description. You can specify NULL or not NULL properties for any type. You can also specify D E FA U LT default_value for all types.

MySQL Numeric type

MySQL provides numeric types for integers and floating-point numbers. You can select the appropriate type based on the range of values you want to represent.
For integer types, if the Auto_increment property is specified, the column must be a primary KEY or a unique index. Inserting null into the Auto_increment column inserts a value that is greater than the current maximum value in the column. If the unsigned property is specified, the corresponding integer type does not allow negative values.
If the Zerofill property is specified, the value of the numeric type is populated with leading 0 to reach the display width of the column.
1. tinyint[(M)]
Description: Very small integers
Allowed Properties: AUTO _ I N C R e M e n T, UNSIGNED, Zerofill
Value range: 128 to 127 (-27 to 27-1), or if u n S I G n E D, then 0 to 2 5 to 5 (0 to 2 8-1)
Default value: null if the column is nullable; 0 if it is not null;
Storage requirements: 1 bytes
2. smallint[(M)]
Description: Small integer
Allowed Properties: AUTO _ I N C R e M e n T, UNSIGNED, Zerofill
Value range:-32768 to 3 27 6 7 (-215 to 215-1), or if u n S I G n E D, then 0 to 6 5 to 5 3 (5 to 0)
Default value: null if the column is nullable; 0 if it is not null;
Storage requirements: 2 bytes
3. mediumint[(M)]
Description: Medium sized integers
Allowed Properties: AUTO _ I N C R e M e n T, UNSIGNED, Zerofill
Values range:-8388608 to 8 3 8 8 6 07 (-22 3 to 22 3-1), or if you are n S I G n E D, then 0 to 16 7 7 2 of 15 (0 to 22 4-1)
Default value: null if the column is nullable, or 0 if it is not null
Storage requirements: 4 bytes
4. int[(M)]
Description: integer of standard size
Allowed Properties: AUTO _ I N C R e M e n T, UNSIGNED, Zerofill
Range of values:-2147483648 to 2 14 7 4 8 3 6 4 7 (-231 to 2 3 1-1), or if you are n S I G n E D, then 0 to 4 2 9 4 9 of 6 7 2 9 5 (0 to 2 3 2-1)
Default value: null if column is NULL, or 0 if column is not full
Storage requirements: 4 bytes
Synonyms: I N T e G e R [(M)]
5. bigint[(M)]
Description: Large integer
Allowed Properties: AUTO _ I N C R e M e n T, UNSIGNED, Zerofill
Range of values:-9223372036854775808 to 9 2 2 3 3 7 2 0 3 6 8 5 4 7 7 5 8 (07 to 263 2), or if you are n S I G n E D, then 6 to 3-1 0 18 4 is 4 6 7 4 3 7 0 9 5 5 16 15 (0 to 26 4-1)
Default value: null if the column is nullable, or 0 if it is not null
Storage requirements: 8 bytes
6. FLOAT [(M, D)]
Note: small floating-point number; single precision (less than D O U B L E)
Allowed properties: Z E R O F I l l
Value range: The minimum non 0 value is ±1.75494351e-38; The maximum non 0 value is ±3. 4 0 2 8 2 3 4 6 6 E + 3 8
Default value: null if the column is nullable, or 0 if it is not null
Storage requirements: 4 bytes
Synonyms: Before the MySQL3.23 version, float (4) is synonymous with float with default m and D values.
Note: After MySQL3.23, float (4) is a true floating-point type (value is stored as the full precision allowed by the hardware and not rounded to the default number of decimal places)
7. double[(M, D)]
Description: Large floating-point number; double precision (more accurate than float)
Allowed properties: Z E R O F I l l
Value range: The minimum non 0 value is ±2. 2 2 5 Modified 3 8 5 8 5 modified 2 0 E-308; The maximum non 0 value is ±1. 7 9 7 6 9 3 13-4 8 6 2 3 7 E + 3 0 8
Default value: null if the column is nullable, or 0 if it is not null
Storage requirements: 8 bytes
Synonyms: DOUBLE precision[(M, D)] and R E A l [(M, d)] are synonyms for D O U B L E [(M, D)]. Before the MySQL3.23 version, float (8) was synonymous with float with default m and D values.
Note: After MySQL3.23, float (8) is a true floating-point type (the value is stored as the full precision allowed by the hardware and not rounded to the default number of decimal places).
8. DECIMAL (M, D)
Note: Floating-point numbers stored as strings (1 bytes per digit, decimal point, or "-" number).
Allowed properties: Z E R O F I l l
Value range: The maximum range is the same as double; the valid range of values for a given decimal type is determined by M and D. If D is zero, the column value does not have a decimal point or fractional fraction.
Default value: null if the column is nullable, or 0 if it is not null
Storage requirements: For the MySQL3.23 version, M-byte, and MySQL3.23 later version, for M + 2 bytes.
Synonyms: N U m E R I C (M, D)
Note: After MySQL3.23, the value of M is in accordance with the ANSI SQL standard, excluding the number of bytes of symbol characters or decimal points.

MySQL String type

MySQL's string type is commonly used to store text, which is not only generic but can also store any data. A string type is available to store the maximum length variable, and you can choose whether the value is case-sensitive when it is processed.
1. CHAR (M)
Description: A fixed-length string of 0 to m bytes long. Before the MySQL3.23 version, M should be an integer between 1 and 255. After the MySQL3.23 version, M should be an integer between 0 and 255. Strings shorter than m characters are stored in the right fill space. A string that is longer than m characters is clipped to a string with a length of M. When retrieving values, remove the spaces followed.
Allowed properties: B I N A RY
Allowable length: 0 to M bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: M bytes
Comparison: case-insensitive (case-sensitive if you have the B I N A RY property)
2. VARCHAR (M)
Description: A variable long string of 0 to m bytes long. M should be an integer from 1 to 255, or an integer from 0 to 255 from the MySQL3.23. The space followed by the store is removed. When stored, a string cut greater than M characters is a. m character.
Allowed properties: B I N A RY
Allowable length: 0 to M bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value, plus 1 bytes to record length
Comparison: case-insensitive (case-sensitive if you have the B I N A RY property)
3. Tinyblob
Description: Small BLOB value
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 2 5 5 (0 to 28-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 1 bytes for record length
Comparisons: Case Sensitive
4. BLOB
Description: Blob values of the general size
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 6 5 5 3 5 (0 to 216-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 2 bytes for record length
Comparisons: Case Sensitive
5. Mediumblob
Description: Medium Sized BLOB value
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 16 7 7 7 2 15 (0 to 224-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 3 bytes for record length
Comparisons: Case Sensitive
6. Longblob
Description: Large BLOB value
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 4 2 9 4 9 6 7 2 9 5 (0 to 23 2-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 4 bytes for record length
Comparisons: Case Sensitive
7. Tinytext
Description: Small Text value
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 2 5 5 (0 to 2 8-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 2 bytes for record length
Comparison: case-insensitive
8. TEXT
Description: Text values of the general size
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 6 5 5 3 5 (0 to 216-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 2 bytes for record length
Comparison: case-insensitive
9. Mediumtext
Description: Medium-size text values
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 16 7 7 7 2 15 (0 to 22 4-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 3 bytes for record length
Comparison: case-insensitive
Longtext
Description: Large text value
Allowed properties: No other attributes except for common properties
Allowable Length: 0 to 4 2 9 4 9 6 7 2 9 5 (0 to 23 2-1) bytes
Default value: null if the column is nullable, or "" if listed as NOT null;
Storage requirements: Length of value plus 4 bytes for record length
Comparison: case-insensitive
ENUM ("v A l UE 1", "V A l UE 2", ...)
Description: Enumeration, column values can be assigned to a member of the value list
Allowed properties: No other attributes except for common properties
Default value: null if the column is nullable, or the first enumeration value if it is not null
Storage requirements: 1 to 255 members of the enumeration 1 bytes, 255 to 65,535 members of the enumeration 2 bytes
Comparison: case-insensitive (case sensitive before MySQL3.22.1 edition)
SET ("v A l UE 1", "V A l UE 2", ...)
Description: Collection, column values can be assigned to 0 or more members in a value list
Allowed properties: No other attributes except for common properties
Default value: null if the column is nullable, or "" (empty Set) if columns are NOT NULL
Storage requirements: 1 bytes (set of 1 to 8 members), 2 bytes (9 to 16 members of the set), 3 bytes (17 to 2 4 members of the set), 4 bytes (2 5 to 3 2 members of the set) or 8 bytes (3 3 to 6 4 members of the set)
Comparison: case-insensitive (case sensitive before MySQL3.22.1 edition)

MySQL Date and Time type

MySQL provides types that represent time data in various forms. These data have date and time types. There is a special type of timestamp that is automatically updated when the record changes. There is also a type for storing the year, which is used when no full date is required.
1. DAT E
Description: Date of "Y y y y-m m-d D" format
Allowed properties: No other attributes except for common properties
Value range: "10 0 0-0 1-0 1" to "9 9 9 9-12-3 1"
0 Value: "0 0 0 0-0 0-0 0"
Default value: null if the column is nullable, or "0 0 0 0-0 0-0 0" If it is not null
Storage requirements: 3 bytes (MySQL3.22 version previously 4 bytes)
2. Time
Description: The "H h:m m:s S" format (negative value "-HH:MM:SS"); Indicates the time taken, but can also be used as a daily time
Allowed properties: No other attributes except for common properties
Value range: "-8 3 8:5 9:5 9" to "8 3 8:5 9:5 9"
0 Value: "0 0:0 0:0 0"
Default value: null if the column is nullable, or "0 0:0 0:0 0" If it is not null
Storage requirements: 3 bytes
Note: Although "0 0:0 0:0 0" is used as a 0 value when an illegal value is inserted into the time column, it also appears as a valid value within the normal range of values
3. DAT e T I M E
Description: Date and time in the "Y y y y-mm-dd hh:mm:ss" format (required for two parts)
Allowed properties: No other attributes except for common properties
Value range: "1000-01-0100:00:00" to "9999-12-3123:59:59"
0 Value: "0000-00-00 00:00:00"
Default value: null if the column is nullable, or "0 0 0 0-0 0-0 0 0 0:0 0:0 0" if listed as NOT NULL
Storage requirements: 8 bytes
4. Timesta m P [(m)]
Description: Time (date and time) expressed in YYYYMMDDHHMMSS format
Allowed properties: No other attributes except for common properties
Value range: A time of 19,700,101,000,000-2037 years
Default value: Current date and time. Note Describe and show collumns report defaults to NULL
Storage requirements: 4 bytes
NOTE: inserting null into the first T I M E S tamp column of the table will insert the current date and time. Changing the value of other columns in a row causes the first T I M E S tamp column to be updated to the date and time of this modification. The values stored and used in internal calculations are all 14-bit character precision, regardless of the display width. This property does not work if the NOT NULL property is specified
5. Year
Description: Year in YYYY format
Allowed properties: No other attributes except for common properties
Value range: 1900 to 2 15 5
0 Value: 0 0 0 0
Default value: null if the column is nullable, or 0 0 0 0 if it is not null
Storage requirements: 1 bytes
Note: Year was introduced in the MySQL3.22 edition