MYSQL column type reference _ MySQL

For MYSQL column types, refer to this appendix to introduce each column type provided by MySQL. For more information about how to use each type, see chapter 2nd. Unless otherwise specified, the listed types already exist in MySQL3.21.0.
The following conventions describe the type name:
Brackets.
M maximum display width. Unless otherwise stated, M should be an integer between 1 and 255.
D. number of decimal places of the decimal part type. D is an integer between 0 and 30. D should be less than or equal to M-2. Otherwise, the M value is adjusted to D + 2.
In ODBC terms, M and D are called "precision" and "decimal places ".
One or more of the following information is provided to describe each type:
Brief description of this type.
Optional attribute keywords related to this type in the create table or alter table statement. The attributes are listed alphabetically, but this order does not necessarily correspond to the order used in create table or alte r table. For the syntax of these statements, see Appendix D. The attributes listed in the description for each column type are attributes other than the general attributes.
The maximum length allowed by the column values of the allowed length string type.
Value range: the range of values that can be expressed by a value or a date or time type. For the integer value type, two value ranges are given, because the integer column can be signed or unsigned, and the values of the signed or unsigned values are different.
For date and time types, if the column is inserted with an invalid value, it is saved as a "zero" value.
The default value of the d e fa u lt attribute is not explicitly given in the default value type description.
The number of bytes required to store the value of this type. For some types, this value is fixed. This value is variable in some types based on the values stored in the column.
For string types, the comparison is case sensitive. This is effective for sorting and indexing because these operations are performed based on comparison.
Synonyms of this type.
Comment out some miscellaneous descriptions about this type.
Attributes of common attributes apply to all types or almost all types. They are listed here, rather than in descriptions of each type. You can specify the NULL or not null attribute for any type. You can also specify d e fa u lt default_value for all types.

MYSQL value type

MySQL provides integer and floating-point numeric types. You can select a type based on the range of the value to be expressed.
If the AUTO_INCREMENT attribute is specified for the integer type, the column must be a primary key or UNIQUE index. Inserting NULL into the AUTO_INCREMENT column inserts a value greater than the current maximum value in the column. If the UNSIGNED attribute is specified, the corresponding integer type cannot be negative.
If the ZEROFILL attribute is specified, the leading zero-padding numeric value is used to display the column width.
1. TINYINT [(M)]
Note: very small integers
Allowed attributes: AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
Value range:-128 to 127 (-27 to 27-1), or if it is u n s I G N E D, then 0 to 2 5 5 (0 to 2 8-1)
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirement: 1 byte
2. SMALLINT [(M)]
Description: a small integer.
Allowed attributes: 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 it is u n s I G N E D, then 0 to 6 5 5 3 5 (0 to 216-1)
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirements: 2 bytes
3. MEDIUMINT [(M)]
Description: an integer of medium size.
Allowed attributes: AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
Value range:-8388608 to 8 3 8 8 6 07 (-22 3 to 22 3-1), or if it is u n s I G N E D, then 0 to 16 7 7 2 15 (0 to 22 4-1)
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirements: 4 bytes
4. INT [(M)]
Description: an integer of the standard size.
Allowed attributes: AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
Value range:-2147483648 to 2 14 7 4 8 3 6 4 7 (-231 to 2 3 1-1), or if it is u n s I G N E D, then 0 to 4 2 9 4 9 6 7 2 9 5 (0 to 2 3 2-1)
Default value: if the column is NULL, it is NULL. if the column is not full, it is 0.
Storage requirements: 4 bytes
Synonym: I N T E G E R [(M)]
5. BIGINT [(M)]
Description: large integer
Allowed attributes: AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
Value range:-9223372036854775808 to 9 2 3 3 7 2 0 3 6 8 5 4 7 5 8 07 (-263 to 2 6 3-1 ), or if it is u n s I G N E D, then 0 to 18 4 4 6 7 4 07 3 7 0 9 5 5 16 15 (0 to 26 4-1)
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirements: 8 bytes
6. FLOAT [(M, D)]
(Small floating point number; single precision (precision less than d o u B L E)
Allowed attributes: Z E R O F I L
Value range: minimum non-zero value: ± 1. 75494351E-38; maximum non-zero value: ± 3. 4 0 2 8 2 3 4 6 E + 3 8
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirements: 4 bytes
Synonym: before MySQL 3.23, FLOAT (4) is a synonym for FLOAT with default M and D values.
Note: After MySQL3.23, FLOAT (4) is of the true floating point type (the value is stored as the complete precision allowed by the hardware, but not rounded to the default decimal place)
7. DOUBLE [(M, D)]
Note: big FLOAT points; double precision (more accurate than FLOAT)
Allowed attributes: Z E R O F I L
Value range: the minimum non-zero value is ± 2. 2 5 07 3 8 5 5 07 2 0 14 E-308; the maximum non-zero value is ± 1. 7 9 7 6 9 3 13-4 8 6 2 3 15 7 E + 3 0 8
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirements: 8 bytes
Synonym: double precision [(M, D)] and r e a l [(M, D)] are synonyms of d o u B L E [(M, D. Before MySQL 3.23, FLOAT (8) is a synonym for FLOAT with default M and D values.
Note: After MySQL3.23, FLOAT (8) is of the true floating point type (the value is stored as the complete precision allowed by the hardware, but not rounded to the default decimal place ).
8. DECIMAL (M, D)
Note: The floating point number stored as a string (each digit, decimal point, or "-" occupies 1 byte ).
Allowed attributes: Z E R O F I L
Value range: the maximum value range is the same as that of DOUBLE. the valid value range of a given DECIMAL type is determined by M and D. If D is zero, the column value has no decimal point or decimal part.
Default value: if the column can be NULL, it is NULL. if the column is not null, it is 0.
Storage requirement: for versions earlier than MySQL3.23, the value is M bytes, while for versions later than MySQL3.23, the value is M + 2 bytes.
Synonym: n u m e r I C (M, D)
Note: After MySQL3.23, the value of M is in line with the ansi SQL standard and does not include characters or the number of bytes occupied by the decimal point.

MYSQL string type

MySQL string type is often used to store text. it is not only common, but also can store any data. You can use string types to store variable-Max-length values, and you can choose whether to be case sensitive when processing values.
1. CHAR (M)
Description: a fixed-length string ranging from 0 to M bytes. Before MySQL 3.23, M should be an integer between 1 and 255. MySQL 255 and later versions, M should be an integer between 0 and. Strings shorter than M characters are stored with spaces on the right side. When storing strings that are longer than M characters, the cut is a string of M length. Remove the trailing space when retrieving the value.
Allowed attributes: B I N A RY