Software ：MySQL8.0,Navicat Premium 12
MySQL Supports all standards SQL Numerical data type .
These types include strict numeric data types (INTEGER,SMALLINT,DECIMAL and NUMERIC), And approximate numerical data types (FLOAT,REAL and DOUBLE
keyword INT yes INTEGER A synonym for , keyword DEC yes DECIMAL A synonym for .
BIT Data type save bit field value , And support MyISAM,MEMORY,InnoDB and BDB surface .
As SQL Extension of standards ,MySQL Integer types are also supported TINYINT,MEDIUMINT and BIGINT. The following table shows the storage and range of each integer type required .
Date and time type
The date and time types that represent time values are DATETIME,DATE,TIMESTAMP,TIME and YEAR.
Each time type has a valid value range and a " Fatal Frame " value , When the designation is illegal MySQL Cannot represent the value of " Fatal Frame " value .
TIMESTAMP Types have a proprietary automatic update feature , It will be described later .
String type refers to CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM and SET. This section describes how these types work and how they are used in queries .
be careful ：char(n) and varchar(n) In brackets n Represents the number of characters , Does not represent the number of bytes , such as CHAR(30) You can store it 30 Characters .
CHAR and VARCHAR Similar type , But they are saved and retrieved in different ways . Their maximum length and whether trailing spaces are retained are also different . No case conversion during storage or retrieval .
BINARY and VARBINARY be similar to CHAR and
VARCHAR, The difference is that they contain binary strings instead of non binary strings . in other words , They contain byte strings instead of character strings . That means they don't have a character set , And sort and compare numeric values based on column value bytes .
BLOB Is a binary large object , It can hold a variable amount of data . yes 4 species BLOB type ：TINYBLOB,BLOB,MEDIUMBLOB and
LONGBLOB. The difference is that they can hold different storage ranges .
yes 4 species TEXT type ：TINYTEXT,TEXT,MEDIUMTEXT and LONGTEXT. Corresponding to this 4 species BLOB
type , The maximum length that can be stored is different , It can be selected according to the actual situation .
there “ length “ Is the total number of digits that will be displayed ,“ decimal point ” Is the number of decimal places that will be displayed
If it is set in the figure above Integer bit =15-10=5 position Decimal place =10 position
In fact, when we choose to use int When it comes to the type of , Whether it is int(3) still int(11), What it stores in the database is 4 The length of one byte .
INT(M) ZEROFILL, add ZEROFILL after M To make a difference , such as INT(3)
ZEROFILL, What you inserted into the database is 10, The actual insertion is 010, That is to say, a new one was added in the front 0. If INT(3) and INT(10) No ZEROFILL, There is no difference between them ．Ｍ Not to limit INT The range in which values are stored in a column ．int(M) The maximum and minimum values of and UNSIGNED of . in use int(3) If you type 10, It will give you memory by default 010, Which means this 3 Represents the default length , When you don't have enough 3 Bit time , It won't help you , When you exceed 3 Bit time , There will be no impact .
float(7,3): The total allowed width of the field is 7, Where the integer part is 4 position （ The numerical range can be 0-9999）, The decimal part is 3 position （ The numerical range can be 0-999）
Above If you want to be right tinyint/smallint/mediumint/int/bigint Select length , To fill in the unsigned and zeros below start to fight √.