05
авг.
Data Types in MySQL
Some developers(including me) do not remember by heart all types of commonly used fields in MySQL, so I decided to make this memo, really useful information.Integer data types
Type | Byte | Range |
TINYINT | 1 | -128 ... +127 |
SMALLINT | 2 | -32 768 ... +32 767 |
MEDIUMINT | 3 | -8 388 608 ... +8 388 607 |
INT | 4 | -2 147 483 648 ... +2 147 483 647 |
BIGINT | 8 | -9 223 372 036 854 775 808 ... +9 223 372 036 854 775 807 |
The number in parentheses after the type field indicates only the number of characters that will be returned from the database if the field attribute is specified ZEROFIL or UNSIGNED ZEROFILL, this attribute adds zeros to the return number to the value indicated in parentheses after the field type, for example, if a field is declared as INT (8) ZEROFIL and added value of 666, then the SELECT this field returns the value "00000666".
Remember that if you specify a numeric field attribute [UNSIGNED], then the field can not contain negative values, but the maximum value is TWICE as much!
Floating-point numbers
Type | Description | Range |
FLOAT |
Single precision.
Can not be unsigned. |
–3.402823466E+38 ... –1.175494351E-38
|
DOUBLE,
DOUBLE PRECISION |
Double precision | -1.7976931348623157E+308 ... -2.2250738585072014E-308 |
REAL | Same as DOUBLE | |
DECIMAL | Fractional number stored as a string | Works like a data type CHAR, the number is stored as a string, using one character for each digit value. Symbol of the decimal point and a negative number symbol "-" is not counted in length. If a decimal value is 0, the value will not have a decimal point or fractional part. The maximum size for DECIMAL value is the same as for DOUBLE, but the actual range for the DECIMAL column may be limited in the choice of the length and decimals. |
NUMERIC | Same as DECIMAL |
Strings
Type |
Range |
Description |
VARCHAR |
0 ... 255
(since 5.0.3 is 0 ... 65 535) |
Occupies exactly as many bytes as characters stored in the value of
+1 byte to store the number of characters occupied. In parenthesis indicates the maximum number of characters stored. |
CHAR |
0 ... 255
|
Occupies exactly as many bytes as indicated in parentheses when creating the field.
Unlike VARCHAR, always takes the same number of bytes, and does not take one extra byte to store the length value. |
TINYTEXT |
255 characters
|
TINYBLOB - case-sensitive alternative. |
TEXT | 65 535 characters |
BLOB - case-sensitive alternative. |
MEDIUMTEXT |
16 777 215 characters
|
MEDIUMBLOB - case-sensitive alternative. |
LONGTEXT | 4 294 967 295 characters |
LONGBLOB - case-sensitive alternative. |
Date and time
Type | Description |
DATE | Date in the format YYYY-MM-DD |
TIME | Time in the format HH:MM:SS |
TIMESTAMP | Date and time (stored in the format of timestamp), is displayed in the format YYYY-MM-DD HH:MM:SS |
DATETIME | Date and time in the format YYYY-MM-DD HH:MM:SS |
If you have questions - write in the comments, I will answer all!
Comments (0)
Leave a comment