MySQL data type

Data types for MySQL

1. Integral type (xxxint)
   

2. Floating-point type (float and double)

3. Fixed-point number (decimal)

4. String (Char,varchar,xxxtext)

5. Binary data (XXXBLOB)

6. Date Time Type

7. Properties of the data type

1. Integral type (xxxint)  

Data type	Meaning
tinyint (m)	1 byte representation ( -128~127)
smallint (m)	2 byte representation ( -32768~32767)
Mediumint (M)	3 byte representation ( -8388608~8388607)
Int (m)	4 byte representation ( -2147483648~2147483647)
BigInt (M)	8-byte representation (18 +-9.22*10 of the X-square)

Note: M indicates the maximum display width. Does not represent the number of data bits, regardless of the data itself. if m=2, if the data is stored in 9, then display 09

2. Floating-point type (float and double)

Data type	Meaning
Float (m,d)	Single-precision floating point, 8-bit precision (4 bytes), M is the total number of decimal digits, and D is the number of digits after the decimal point
Double (m,d)	Double-precision floating-point, 16-bit precision (8 bytes)

Note: M indicates the maximum display width. Only affects the display effect without affecting the accuracy. D affects precision and D is reserved for several decimals.

3. Fixed-point number (decimal)

Decimal (m,d) fixed-point type floating-point types hold approximate values in the database, while fixed-point types hold exact values in the database. The parameter m is the maximum number (precision) of the fixed-point type number, the range is 0-65,d the number of digits to the right of the decimal point, the range is 0-30, but must not exceed M, the calculation of the fixed point number can be accurate to 65 bits.

Note: In MySQL, you use the floating-point type and the fixed-point number type to represent decimals. In MySQL, fixed-point numbers are stored as strings, so their accuracy is higher than floating-point numbers, and the floating-point number errors occur, which is a persistent flaw in floating-point numbers. If you want the accuracy of the data to be high, or to select the fixed-point number decimal is more secure.

4. String (Char,varchar,xxxtext)  

Data type	Meaning
CHAR (n)	Fixed-length string with a maximum of 255 characters
VARCHAR (n)	Fixed-length string with a maximum of 65,535 characters
Tinytext	Variable-length string, up to 255 characters
Text	Variable-length string, up to 65,535 characters
Mediumtext	Variable-length string, up to 2 of 24 square-1 characters
Longtext	Variable-length string, up to 2 of 32 square-1 characters

5. Binary data (XXXBLOB)

Xxxblob and Xxxtext are corresponding, but stored in different ways, Xxxtext is stored in the text, if the storage of English words are case-sensitive, and Xxxblob is stored in binary way, not case-sensitive. The data stored by Xxxblob can only be read out in its entirety. Xxxtext can specify a character set, Xxxblob does not specify a character set. Xxxblob is generally used to store big data, slices, objects, etc.

6. Date and Time type  

data type
date	date ' 2016-10-31 '
time	Time ' 17:05:30 '
datetime
timestamp	not fixed

Note: Timestamp is very special, if you define a field of type timestamp, the time of this field will be refreshed automatically when other fields are modified. So a field of this data type can hold the last time the record was modified, rather than the actual time it was stored.

Note: The string in the database is represented by a single quote '.

7. Properties of the data type  

Key words	Meaning
Null	Data columns can contain null values
Notnull	Data columns are not allowed to contain null values
Defaultxxx	Default value, if no value is specified when inserting the record, the default value will be taken
PRIMARY KEY	Primary key
Auto_increment	Increment, if no value is specified when inserting a record, add 1 to the value of the previous record and apply only to the integer type.
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

MySQL data type