MySQL Data types

In the creation of the table, it is necessary to clearly define the field corresponding to the data type.

MySQL main data types are divided into numeric types, string (text) type, time and date types and other types of several categories.

Numeric types

Type Description example
tinyint Storage range: -128 to 127 or 0 to 255 tinyint(1)
smallint Storage range: -32768 to 32767 or 0 to 65535 smallint(3)
mediumint Storage range: - 8388608 to 8388607 or 0 to 16777215 mediumint(5)
int Storage range: - 2147483648 to 2147483647 or 0 to 4294967295 int(10)
bigint Storage range: - 9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615 bigint(10)
float Minimum non-zero value: ± 1.175494351E - 38, the same as double for high precision occasions float(3,1)
double Minimum non-zero value: ± 2.2250738585072014E - 308 double(10,5)
decimal range of values, since the display size and decimal places in brackets, depending on the accuracy of the request is not high but very high accuracy requirements of the occasion decimal(10,2)

Supplementary explanation

In the int (integer) family, only integer values ​​can be stored, and the size of the display (M) can be specified in parentheses afterwards, and is assigned by default if not specified. If the display width of the actual value is greater than the set value, the actual value is displayed without truncation to fit the display size. Such as smallint (3) in the 3 is the display size, which shows the value of three (not including the number) The int type can specify the UNSIGNED attribute, that is, unsigned (non-negative), so there are two types of storage In the float, double, and decimal types, you can not specify the UNSIGNED attribute. The display size contains decimal precision (D), that is, float (3,1) saves the range from -99.9 to 99.9 Decimal must specify the display size (M) and the decimal precision (D), float and double are optional On the basis of possible range of values, select the smaller type as possible to improve efficiency and save storage space, such as age, select tinyint (3). The same principle applies to character types

The string (text) type

Type Description example
char supports a fixed-length string of up to 255 characters char(100)
varchar supports variable-length strings with a maximum length of 65535 characters varchar(1000)
tinytext supports variable-length strings of up to 255 characters tinytext
supports variable-length strings with a maximum length of 65535 characters text
Supports variable-length strings with a maximum length of 16777215 characters mediumtext
Supports variable-length strings with a maximum length of 4294967295 characters longtext
enum enumerated type, can store up to 65,535 members, commonly used in the value is limited and fixed occasion enmu("M", "F")
set Collection type, which can store up to 64 members set("value1", "value2", ...)

Char and varcha need to specify the length, the difference is that char is always stored in accordance with the specified length, and varchar is based on the actual length of the string plus a byte allocation space.

Date Time Type

Type Description example
date The date value represented by YYYY-MM-DD format date
time time value in hh: mm: ss format time
datetime YYYY-MM-DD date and time values in hh: mm: ss format datetime
timestamp YYYYMMDDhhmmss formatted timestamp value timestamp
year Year value in YYYY format year

Copyright 2014-2017 by All Rights Reserved.