MySQL common data types and usage summary

A data type is a basic rule that defines what data can be stored in a column and how that data is actually stored. in practical programming, when choosing the basic type of a column, the basic principle is to choose the smallest type that satisfies the data.

① string data types: fixed-length strings and variable-length strings, respectively.

I. Fixed length string end-of-time character, whose length is the creation table is specified, does not allow more than the specified character data, they allocate as much storage space as specified, char is a fixed-length string type.

Two. Variable long strings store variable length text, some variable length data types have the largest fixed length, while others are completely longer, regardless of which only the specified data is saved (no extra spaces are saved), text is a variable-length string type.

Three. Features and comparisons: variable-length data types are flexible, fixed-length data types are efficient, MySQL processing fixed-length data types is much faster than variable-length columns, and MySQL does not allow indexing of variable-length columns (or variable portions of a column), which can greatly affect performance.

Data type	Description	Main purpose
Char	A fixed-length string of 1~255 characters whose length must be specified at creation time, otherwise MySQL is assumed to be char (1) char (n=1~255) CHAR (8), the character you enter is less than 8 o'clock, and it will be followed by a null value. When you enter a character that is larger than the specified number, it intercepts the characters that are out of the bounds.	Mainly used for headings, authors and other data types with small amounts
Varchar	Variable lengths of up to 255 bytes, such as when you specify varchar (n) on creation, you can store a variable-length string of 0~n characters. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.	Mainly used for indeterminate lengths such as: path, article content (Varchar (800)), etc.
Text	The text field with a maximum length of 64K and a regular size of a variable length.	Article content
Tinytext	Same text with a maximum length of 255 bytes
Meduimtext	Same text with a maximum length of 16K
Longtext	Same text with a maximum length of 4GB (plain text, usually not to 4G)
Blob	Text is case-sensitive, two blobs are not distinguished, others are the same.	A binary type can store any data, such as text, images, multimedia, and so on. Also includes Tityblob,mediumblob,longblob
Two special types of
Enum	Accepts a string from a predefined collection of up to 64K strings	enumeration, which can hold only one of the listed values or null for that type of column
SET	accepts 0 or more strings of a predefined set consisting of up to 64K strings	The value in the specified column must come from a specified value in a specific collection, for example: set (' C ', ' H ', ' P ', ' f ', ' s ', ' j ', ' A ', ' B ') Dede's homepage recommends popular settings

Note: Regardless of the string data type in any form, the string value must be within quotation marks (usually a single quotation mark is better), if the numeric value is used in the calculation, it should be stored in the numeric data type column, if used as a string (such as phone number, postal code) should be saved in the String data type column.

② numeric types: integer and floating-point

Store values, each with a different storage range, and the greater the range of values, the more storage space is required. All numeric types (except bit and Boolean) can be signed or unsigned, and a signed data column can store a positive or negative numeric value, which is signed by default.

I. Integer data type

Data type	Range of values	Storage space	Describe	Main purpose
Tinyint[(m)]	Signed values: 128 to 127 (-2^7 to 2^7–1) Unsigned values: 0 to 255 (0 to 2^8–1)	1 bytes	A very small integer	The tinyint is most appropriate for the age of the person or the number of siblings
Smallint[(m)]	Signed values: 32768 to 32767 (-2^15 to 2^15–1) Unsigned values: 0 to 65535 (0 to 21 6–1)	2 bytes	Small integer	For category ID, column smallint (5)
Mediumint[(m)]	Signed values: 8388608 to 8388607 (-2^23 to 2^23–1) Unsigned values: 0 to 16777215 (0 to 2^24–1)	3 bytes	Medium integer	The "Apply to Article ID" Mediumint can represent a value of millions of and can be used for more types of values, but the storage cost is large
Int[(m)]	Signed values: 2147683648 to 2147683647 (-2^31 to 2^31-1) Unsigned values: 0 to 4294967295 (0 to 2^32–1)	4 bytes	General integer	Applies to Dates pubdate Int (10)
Bigint[(m)]	Signed values: 9223372036854775808 to 9223373036854775807 (-2^63 to 2^63-1) Unsigned values: 0 to 18446744073709551615 (0 to 2^64–1)	8 bytes	Large integer

Two. Floating-point data types

Data type	Range of values	Storage space	Describe	Main purpose
Float	Depends on accuracy	Variable	Used to specify single-precision or double-precision floating-point numbers	Double occupies twice times the space of float. Unless high-precision or wide-range values are particularly required, the data should generally be represented by a float type that uses only half the cost of storage.
Float[(M, D)]	Minimum non 0 value: ±1.175494351e–38	4 bytes	Single-precision floating-point number	Equivalent to float (4), but specifies the display width and the number of decimal places
Double[(M, D)]	Minimum non 0 value: ±2.2250738585072014e–308	8 bytes	Double-precision floating-point number	Equivalent to float (8), but specifies the display width and the number of decimal places
Decimal (M, D)	Signed values: 2147683648 to 2147683647 (-2^31 to 2^31-1) Unsigned values: 0 to 4294967295 (0 to 2^32–1)	4 bytes	General integer	Applies to Dates pubdate Int (10)
Bigint[(m)]	M bytes (MySQL < 3.23), m+2 bytes (mysql > 3.23)	M+2	The range of values depends on M and D	Floating point number, stored in char, range depends on display width m

③ Date and time data types

Data type	Number of bytes stored	Range of values	Describe
DATE	4	1000-01-01--9999-12-31	Shown in YYYY-MM-DD format, if we want to insert the current system time, you can insert current_date or now ().
Time	3	-838:59:59--838:59:59	Although the hour range is generally 0~23, MySQL expands the hour range for time and supports negative values in order to represent certain special intervals. Assigning a value to the time type, the standard format is ' HH:MM:SS ', but not necessarily in this format. If the ' D HH:MM:SS ' format is inserted, it is similar to ' (D*24+HH): Mm:ss '. Inserting ' 2 23:50:50 ', for example, is equivalent to inserting ' 71:50:50 '. If you insert a ' hh:mm ' or ' SS ' format, the effect is to assign a value of zero to other values that are not represented by a bit. For example, insert ' 30 ', which is equivalent to inserting ' 00:00:30 ', if inserting ' 11:25 ', it is equivalent to inserting ' 11:25:00 '. In MySQL, for the ' HHMMSS ' format, the system can be automatically converted to a standard format.
Datetime	8	1000-01-01 00:00:00--9999-12-31 23:59:59	Display in YYYY-MM-DD HH:MM:SS format
TIMESTAMP	4	19700101080001--20380119111407	Timestamp the range of values is small, no datetime value range is large, so enter the value must be guaranteed within the range of timestamp. Its insertion is also similar to inserting other date and time data types. So how does the timestamp type insert the current time? First, you can use Current_timestamp; second, enter NULL, the system automatically enters the current TIMESTAMP; third, without any input, the system automatically enters the current TIMESTAMP. There is a special point: the value of timestamp is associated with the time zone.
Year	1	1901--2155	There are three ways to copy the year type: the first is to insert a 4-bit string directly or 4-bit numbers, and the second is to insert a 2-bit string, in which case inserting ' 00 ' ~ ' 69 ' is equivalent to inserting 2000~2069, or inserting ' 70 ' ~ ' 99 ', which is equivalent to insert 1970~ 1999. In the second case, if it is ' 0 ', it is the same as inserting ' 00 ', which is the 2000; the third is to insert a 2-digit number, which differs from the second (inserting a two-bit string) only: If you insert a number 0, it means 0000, not 2000. So when assigning a value to year type, be sure to distinguish between 0 and ' 0 ', although the difference between the two quotes, but the actual effect is indeed a difference of 2000 years.