MySQL data type

numeric types in MySQL

Integer type

If you need to fill in 0, you need to Zerofill method

Example:

CREATE TABLE ' AA ' (  int(ten) not NULL auto_increment,  ' A1 ' varchar (255  ) COLLATE utf8_bin default  null,int(one) unsigned zerofill default NULL,  PRIMARY KEY (' id ')) ENGINE=innodb auto_increment=5 DEFAULT Charset=utf8 collate=utf8_bin;

tinyint[(m)] [unsigned] [zerofill] 1 byte minimum integer, data type is used to hold some range of integer numeric ranges: Signed: - -~127. Unsigned:0~255Special: No boolean value in MySQL, use tinyint (1) constructs. SmallInt 2-byte small integers are signed:32768~32768Unsigned :0~65535
Mediumint 3 bytes
Signed:-8388608 ~ 8388608
Unsigned: 0 ~ 1677215int[(M)][unsigned][zerofill] 4-byte integer, data type used to hold some range of integer numeric ranges: Signed: -2147483648~2147483647Unsigned :0~4294967295Special: M in the integer type is only for display, with no restrictions on storage range. For example:int(5), when inserting data 2 o'clock,SelectWhen the data is displayed as:00002bigint[(M)][unsigned][zerofill] 8-byte large integer, data type used to hold some range of integer numeric ranges: Signed: -9223372036854775808~9223372036854775807Unsigned :0~18446744073709551615

Floating-point type

float[(m,d)] [UNSIGNED] [Zerofill] 4-byte single-precision floating-point number (not accurate decimal value), M is the sum of numbers, D is the number of decimal places. Unsigned:-3.402823466E+38To-1.175494351E-38,                    0                    1.175494351E-38To3.402823466E+38There are symbols:0                    1.175494351E-38To3.402823466E+38The larger the number, the less accurate * * *double[(m,d)] [UNSIGNED] [Zerofill] 8-byte double-precision floating-point number (not accurate decimal value), M is the total number of digits, D is the number of decimal places. Unsigned:-1.7976931348623157E+308To-2.2250738585072014E-308                    0                    2.2250738585072014E-308To1.7976931348623157E+308There are symbols:0                    2.2250738585072014E-308To1.7976931348623157E+308The larger the number, the less accurate * * *

Fixed-point number types

        Dec ()        decimal[(M[,d])] [unsigned] [Zerofill]            the exact decimal value, M is the total number of digits (minus sign), and D is the number of decimal places. The M maximum value is 65,d maximum of 30.            Special: The reason why this type of decaimal is needed for accurate numerical calculations is that it stores the                   exact value internally as a string. 

Bit type

        bit[(m)]            bits (101001), M represents the length of bits (1-mm), default m=1 

Date Time Type

DATE YYYY-MM-DD ( +- on- on/9999- A- to) Time HH:MM:SS ('-838:59:59'/'838:59:59') Year YYYY (1901/2155) DATETIME YYYY-mm-dd HH:MM:SS ( +- on- on xx:xx:xx/9999- A- to  at: -: -Y) TIMESTAMP Current system time Note: and time zone about YYYYMMDD HHMMSS (1970- on- on xx:xx:xx/2037Years at a time)

String type

        Char(m) The char data type is used to represent a fixed-length string that can contain up to 255 characters.            where m represents the length of the string. PS: Even if the data is less than m length, the M-length varchar (m) Varchars data type is used for variable-length strings and can contain up to 255 characters.            where m represents the maximum length of a string that is allowed to be saved by the data type, as long as a string that is less than the maximum value can be saved in that data type. Note: Although varchar is more flexible to use, the char data type can be processed faster and sometimes even beyond the varchar processing speed, from the overall system performance perspective%. Therefore, users in the design of the database should be comprehensive consideration of various factors, in order to achieve the best balance text text data type used to save the large string of long, can be more than the group to 65535 (2** -−1) characters. Mediumtext a TEXT column with A maximum length of -,777,215(2** -−1) characters. Longtext a TEXT column with A maximum length of4,294,967,295or 4GB (2** +−1) characters.

Enum type

        enumenum type, an enum column can has a maximum of $,535Distinct elements. (The practical limit isLess than the.) Example: CREATE TABLE Shirts (name VARCHAR ( +), size ENUM ('X-small','Small','Medium','Large','X-large')                ); INSERT into shirts (name, size) VALUES ('Dress Shirt','Large'), ('T-shirt','Medium'),('Polo Shirt','Small');

Set type

        Setcollection type a set column can have a maximum of -distinct. Example: CREATE TABLE myset (Col SET ('a','b','C','D')); INSERT into MySet (col) VALUES ('a,d'), ('D,a'), ('A,d,a'), ('a,d,d'), ('d,a,d');

MySQL data type