06-MYSQL data type date string
06-MYSQL data type date string ---- Integer type
Integer type
Bytes
Value range
Value range
Tinyint
1
Signed-128
Unsigned 0
Signed 127
No-symbol 255
Smallint
2
Signed-32768
Unsigned 0
Signed 32767
No-symbol 65535
Mediumint
3
Signed-8388608
Unsigned 0
Signed 8388607
No-symbol 1677215
Int, integer
4
Signed-2147483648
Unsigned 0
Signed 2147483647
No-symbol 4294967295
Bigint
8
Signed-263
Unsigned 0
Signed 263-1
Unsigned 264-1
(1) how to select the integer type
The biggest difference between the integer type and the floating point type is whether the decimal point can be expressed. So our integers cannot express decimal places, but floating points can,
The value range of different integer types is different. the value range of tinyint type is 0 ~ 255, if the field does not exceed 255. It is enough to select tinyint, which has the largest value range and is commonly used in the int type.
---- Floating point type and fixed point type
Floating point type
Bytes
Negative Value range
Value range of a non-negative number
Float single precision
4
-3.402823466E + 38 ~
-1.175494351E-38
0 and 1.175494351E-38 ~
3.402823466E + 38
Double
8
-1.7976931348623157E + 308 ~
-2.2250738585072014E-308
0 and 2.225074255072014e-308 ~ 1.7976931348623157E
Fixed point type
Bytes
Description
Decimal (m, d)
M + 2
The maximum value range is the same as that of double. the valid value range of a given decimal is determined by M and D.
(2) how to select the floating point type and the fixed point type
The precision of double type is higher than that of float type, so if we need to be precise to 10 decimal places or more, we will use float type, general use of float type is enough.
In Mysql, the precision of a fixed point is higher than that of a floating point. In addition, an error occurs in a floating point. if you want to have a high requirement on data accuracy, you should select a fixed point.
---- Date and time type
Date and time type
Bytes
Minimum value
Maximum value
Date, year, month, and day
4
1000-01-01
9999-12-31
Datetime, month, day, hour, minute, second
8
1000-01-01 00:00:00
23:59:59, 9999-12-31
Timestamp time zone
4
19700101080001
20380119111407
Time indicates the Time separately.
3
-838: 59: 59
838: 59: 59
Year represents Year separately
1
1901
2155
(4) how to select the time and date types
The year type only indicates the year. if you only record the year, select "year" to save space,
The time type only indicates the time. if you only need to record the time, select only the time type,
The date type only indicates the year, month, and day. if you only need to record the year, month, and day, select only the date type.
If you need to record the year, month, day, and time, you can select the datetime and timestamp types,
The time range indicated by the datetime type is larger than that of the timestamp type. Therefore, it is appropriate to select the datetime type with a large time range,
The timestamp type is selected based on the time zone. if the time to be displayed corresponds to the time zone, select the timestamp type.
---- String type
String type
Bytes
Description
Char (m)
M
M is an integer between 0 and 25 5.
Varchar (m)
M is an integer between 0 and, and the value length is + 1 byte
Tinytext
Length: 0-bytes; value: length + 2 bytes
Text
Length: 0-bytes; value: length + 2 bytes
Mediumtext
Allowed length: 0 ~ The value of 167772150 bytes is the length + 3 bytes.
Longtext
Allowed length: 0 ~ The value of 4294967295 bytes is the length + 4 bytes.
Char (5) for example, this row actually only uses 3 bytes, but it also occupies 5 bytes of space,
Varchar (5). For example, if this row uses only three bytes, it only occupies the length of three bytes.
The Text type is a special string type. text only stores character data, such as news content.
Text contains (tinytext mediumtext, langtext)
---- Binary type
Binary (10)
Varbinary (20)
Stores the string type of common binary characters. The difference is the same as that of char varchar. one occupies the actual bytes and the other occupies the allocated fixed bytes.
(6) text and blob types
The text type is similar to the blob type. text can only store character data and plain text. Select text type
Blob can store binary data and binary data such as pdf images.
---- Enumeration type
The enum value ranges from 0 ~ 65535
Set value range: 0 ~ 64
(5) enum type and set type
The Enum type can have 65535 members, and the set type can only contain up to 64 members. the values of the two can only be selected in the member list, and the enum type can only be selected from one member, you can select multiple set types,
Enum usage: If you select one of multiple values, you can select the enum type. for example, select either gender or gender.
Set type usage: for example, you can select multiple set types, so we use the set type.