MySQL data type

Blue Seal

MySQL data types can be divided into 3 categories: numeric type , date and Time type , and string (character) type

Square brackets ("[" and "]") indicate the part of the optional type modifier

M
Indicates the maximum display size. The largest legal display size is 255.
D
Applies to floating-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be less than M-2.

UNSIGNED
to mean "unsigned", that is, non-negative. is a MySQL custom type, non-standard SQL. The unsigned property is for integral type only and uses:
1.UNSIGNED can be used to constrain the range of data, such as some age this value can generally be negative, then set a UNSIGNED, so that negative numbers can not be inserted.
2. You can increase the range of values (equivalent to adding negative numbers to positive numbers). But less use, not easy to transplant.

Zerofill
Fill 0, that is, the data length is not enough to fill the front of 0, to achieve the set length, in MySQL when the field is set to this property, the column will automatically increase the unsigned property.

First, numeric type

integral type: (1 bytes is 8 bits)

When you create an integer field int (or other like tinyint) in MySQL, you can set the number of bits for that field. such as int(one),int(5), if not specified, the default length of type int is 11. The field insertion length is independent of the m set by int , and when the insertion length is greater than the set M, the field value is not truncated or saved by the actual precision of the type.
The integer field has a Zerofill property that fills 0 in front of the data with insufficient numbers to reach the set length. The M -value only has a difference when the attribute is zerofill .
If the table structure is ID1 Int (ten), Id2 Int (5), the corresponding data is the first behavior (max), the second behavior (1111111,1111111), when the Id1,id2 field is set to the Zerofill property, The display data is the first act (0000000001,00001), the second act (0001111111,1111111), and the number of bits that are not enough is padded with 0.

Data type	Size	M (default value)	Range (signed)	Range (unsigned)	Use
tinyint [(M)] [UNSIGNED] [Zerofill]	1 bytes	4	-128~127 ( -27~27-1)	0 ~ 255 (0 ~ 28-1)	Very small integer value
smallint [(M)] [UNSIGNED] [Zerofill]	2 bytes	6	-32768~32767 (-215 ~ 215-1)	0 ~ 65535 (0 ~ 216-1)	Smaller integer
Mediumint [(M)] [UNSIGNED] [Zerofill]	3 bytes	9	-8388608 ~ 8388607 (-223 ~ 223-1)	0 ~ 16777215 (0 ~ 224-1)	Medium size integer
int [(M)] [UNSIGNED] [Zerofill]	4 bytes	11	-2147483648 ~ 2147483647 (-231 ~ 231-1)	0 ~ 4294967295 (0 ~ 232-1)	Standard integer
integer [(M)] [UNSIGNED] [Zerofill]	4 bytes	11	-2147483648 ~ 2147483647 (-231 ~ 231-1)	0 ~ 4294967295 (0 ~ 232-1)	Same as int
Bigint [(M)] [UNSIGNED] [Zerofill]	8 bytes	20	-9223372036854775808 ~9223372036854775807 (-263 ~ 263-1)	0 ~ 18446744073709551615 (0 ~ 264-1)	Larger integer

floating point type:

(M,d) represents a total of M-bits, D-decimal digits, and D contained in M. Floating-point types cannot be unsigned.

For each floating-point type, you can specify a maximum display size of M and decimal place D. The value of M should take 1 to 255. The value of D can be 0 to 30, but should not be greater than M-2. M and d are optional for both float and double, but are required for decimal, and if they are omitted at option M and D, the default value is used, and if D is omitted, it is set to 0. If M is omitted, it is set to 10.

Data type	size	purpose
float  [(m,d)] [zer Ofill]	4 bytes	single-precision floating-point, 8-bit precision, parameter m only affects the display effect, does not affect the accuracy, d but different, will affect the precision; M is the total number of decimal digits, and D is the number of digits after the decimal point
double [(m,d)] [Zerofill]	8 bytes	double-precision floating-point, 16-bit precision, parameter M only affects the display effect, does not affect the accuracy, D but different, will affect the accuracy
Real [(m,d)] [Zerofill]	8 bytes	Same double
decimal [(M[,d])] [Zerofill]	4 byte	decimal (m,d) fixed-point type floating point types are approximate values in the database. The fixed-point type holds the exact value in the database. The parameter m is the maximum number (precision) of a fixed-point type number with a range of 0~65,d digits to the right of the decimal point and a range of 0~30, but not more than M. The calculation of the fixed-point number can be accurate to 65 digits. The DECIMAL data type is used in calculations that require very high precision, which allows you to specify the precision and count method of a numeric value as a selection parameter . The precision here refers to the total number of valid digits saved for this value, while the Count method represents the number of digits after the decimal point. For example, the statement decimal (7,3) specifies that the stored value will not exceed 7 digits, and no more than 3 digits after the decimal point
Numeric [(m,d)]  [Zerofill]	4 bytes	with decimal

The decimal type differs from float and decimal, where decimal is actually stored as a string. The maximum possible range of DECIMAL values is the same as a double, but the valid range of values is determined by the values of M and D. If M is changed and the D is fixed, the range of values will be larger with M. This is illustrated in the first three lines of table 2-7. if M is fixed and D is changed, the range of values will be smaller (but with increased accuracy) as D becomes larger. The following three lines of table 2-7 illustrate this point.

The range of values for a given decimal type depends on the version of the MySQL data type. For previous versions of MySQL3.23, each value of the decimal (M, D) column occupies m bytes, while the symbol (if necessary) and the decimal point are included in M bytes. Therefore, a column of type decimal (5, 2) has a value ranging from 9.99 to 9 9. 9 9, because they override all possible 5-character values. As with MySQL3.23, decimal values are handled according to the ANSI specification, and the ANSI specification specifies that decimal (m, d) must be able to represent any value of M-digits and D-decimal digits.
For example, DECIMAL (5, 2) must be able to represent all values from 999.99 to 999.99. Also, symbols and decimal points must be stored, so the decimal value has been 2 bytes since MySQL3.23. For decimal (5, 2), the "longest" value (-9 9 9). 9 9) requires 7 bytes. At one end of the range of positive values, a plus sign is not required, so the MySQL data type uses it to extend the range of values beyond what is required by ANSI specifications. The maximum value for decimal (5, 2) is 9 9 9 9. 9 9, because there are 7 bytes available.
in short, in MySQL3.23 and later versions, the value range of decimal (M, d) equals the range of decimal (M + 2, D) in earlier versions . In all versions of the MySQL data type, if D of a decimal column is 0, the decimal point is not stored. The result of this is to expand the range of values for the column, since the bytes used to store the decimal point are now used to hold other numbers.

Second, character type

for variable-length character types, the amount of storage that the value occupies is different, depending on the length of the value actually stored in the column, which is expressed in L .

Data type	Size (range)	Use
Char M [BINARY]	m bytes,0 <= m <= 255 (l is fixed = 255, not enough space )	fixed-length string; CHAR type can use the BINARY modifier. When used for comparison operations, this modifier causes CHAR to participate in the operation in binary mode, rather than in a traditional case-sensitive manner. Char values are sorted and compared in a case-insensitive manner based on the default character set, unless a binary keyword is given
varchar (M) [BINARY]	L + 1 bytes, where L <= m and 0 <= m <= 65535 (MySQL5.0 is the largest 255 before)	Variable length string; VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier
Tinyblob,tinytext	L + 1 bytes, of which L < 28-1 (255)	Tinyblob: A binary string of no more than 255 characters;tinytext: Short text string
Blob,text	L + 2 bytes, of which L < 216-1 (65535)	blob: Long text data in binary form, which is case-sensitive when classifying and comparing BLOB types; Text: Long text data, text is not case-sensitive when classifying and comparing
Mediumblob,mediumtext	L + 3 bytes, where L < 224-1	Mediumblob: medium-length text data in binary form;mediumtext: medium-length text data,
Longblob,longtext	L + 4 bytes, where L < 232-1	Longblob: large text data in binary form ; long text: Great Textual data
Enum (' value1 ', ' value2 ',...)	1 or 2 bytes, depending on the number of enumeration values (up to 65,535 values )
Set (' value1 ', ' value2 ',...)	1,2,3,4, or 8 bytes, depending on the number of Set members (up to 64 members)

Iii. Type of date

MySQL comes with 5 different data types to choose from. They can be divided into simple dates, time types, and mixed date and time types. Depending on the accuracy required, subtypes can be used in each category, and MySQL has built-in functionality to turn a variety of input formats into a standard format.

type	size	range	format	Purpose
Year [(2|4)]	1 bytes	1901/2155	YYYY	years value
DATE	3 bytes	' 1000-01-01 '--' 9999-12-31 '	yyyy-mm-dd	Date value
Time	3 bytes	' -838:59:59 ' to ' 838:59:59 '	HH:MM:SS	&n BSP; time value or Duration
DATETIME	8 byte	' 1000-01-01 00:00:00 '- -' 9999-12-31 23:59:59 '	yyyy-mm-dd HH:MM:SS	Mixed date and time values
Times Tamp [(M)]	8 byte	1970-01-01 00:00:00/2037 year time	YYYYMMDD HHMMSS	Blend date and time values, timestamp

(1) Year

There are three ways to assign a value to the year type.

The first is to insert a 4-bit string or a 4-bit number directly.

The second is to insert a 2-bit string, in which case inserting ' 00 ' ~ ' 69 ' would be equivalent to inserting 2000~2069, or inserting ' 70 ' ~ ' 99 ', which would be equivalent to insert 1970~1999. In the second case, if it is ' 0 ', it is the same as inserting ' 00 ', which represents 2000.

The third is to insert a 2-digit number, which differs from the second (inserting a two-bit string) except that if you insert a number 0, it is 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.

(2) DATE

MySQL is a value that displays the date type in the YYYY-MM-DD format, which can be persisted when data is inserted. In addition, MySQL also supports some of the non-strict syntax format, the delimiter "-" can be used "@", "." And so many rich to replace. You can also use the "YY-MM-DD" format when inserting data, and the rules for converting YY to the corresponding year are similar to the years type. If we want to insert the time of the current system, we can insert current_date or now ().
Allows you to assign a value to a date column using a string or number.

(3) Time

The time type is expressed as "when: minutes: Seconds", although the hour range is generally 0~23, but in order to represent some special time intervals, MySQL expands the hour range of the hours and supports negative values. The standard format for assigning a value to the time type is "HH:MM:SS", but not necessarily in this format. If the "D HH:MM:SS" format is inserted, it is similar to insert "(D*24+HH): Mm:ss". For example, inserting "2 23:50:50" is equivalent to inserting "71:50:50". If you insert a "hh:mm" or "SS" format, the effect is assigned a zero value for other values that are not represented by a bit. For example, inserting "30" is equivalent to inserting "00:00:30", or inserting "11:25:00" if "11:25" is inserted.
You can also insert ' d HH ' and ' d hh:mm ', which can be inferred from the above example. In MySQL, for the ' HHMMSS ' format, the system can be automatically converted to a standard format. If we want to insert the time of the current system, we can insert current_time or now ().
The time type allows a string or number to be assigned a value of 3 bytes, which is most appropriate if it is just a data store.
Note that the time type value without the colon delimiter will be understood by MySQL as the duration, not the timestamp.

(4) DATETIME

typically used to automatically store timestamps that contain the current date and time, and can play a good role in applications that require a large number of database transactions and audit trails that require a debug and review purpose.

(5) TIMESTAMP

typically used to automatically store timestamps that contain the current date and time, and can play a good role in applications that require a large number of database transactions and audit trails that require a debug and review purpose.
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.
MySQL Displays the timestamp value in Yyyymmddhhmmss, Yymmddhhmmss, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or omitted), 12, 8, or 6, However, you are allowed to assign a value to the timestamp column using a string or a number. A timestamp column is useful for recording the date and time of an insert or update operation, because if you do not assign it yourself, it is automatically set to the date and time of the most recent operation. You can set it to the current date and time by assigning it a null value

MySQL data type