MySQL "Third" data type

First, the integral type

Each of the integers has both unsigned (unsigned) and signed (signed) types.

MySQL data type	Meaning
tinyint (m)	1-byte representation: Signed ( -128~127); unsigned (0~255)
smallint (m)	2-byte representation: Signed ( -32768~32767); unsigned (0~65535)
Mediumint (M)	3-byte representation: Signed ( -8388608~8388607); unsigned (0~16777215)
Int (m)	4-byte representation: Signed ( -2147483648~2147483647); unsigned (0~4294967295)
BigInt (M)	8-byte representation: Signed ( -2^63 ( -9223372036854775808) ~ 2^63-1 (9223372036854775807)); unsigned (0~18446744073709551615)

second, floating-point type

MySQL 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)

Third, fixed-point number

Decimal (m,d) fixed-point type: floating-point types hold approximate values in the database, while fixed-point types store exact values in the database. The parameter m is the maximum number (precision) of the fixed-point type number, and the range 0~65,d is the number of digits to the right of the decimal, and the range is 0~30. But not more than M.

The exact 65-digit number can be calculated for the fixed-point number.

Four, string

MySQL 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

Char and varchar:

• You can limit the maximum number of characters stored by specifying N, char (20) and varchar (20) will only store up to 20 characters, and more characters will be truncated. n must be less than the maximum number of characters allowed for this type
• After the char type specifies n, if the number of characters stored is less than n, the following space will be padded, and the trailing space will be removed at query time, so the char type stores a string with no spaces at the end, and varchar is not bound by this restriction
• The mechanism for internal storage is different. Char is a fixed length, and char (4) consumes 4 bytes Whether it is 1 characters, 2 characters, or 4 characters (in English). varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4) in one character will occupy 2 bytes, 2 characters occupy 3 bytes, 4 characters occupy 5 bytes
• String retrieval speed of char type is faster than varchar type

varchar and text:

• are variable-length and can store up to 65,535 characters
• VarChar can specify that the N,text cannot be specified, the internal storage varchar is the actual number of characters stored + 1 bytes (n<=255) or 2 bytes (n>255), the text is the actual number of characters + 2 bytes
• The text type cannot have a default value
• VarChar can create an index directly, text creates an index to specify the number of characters before the query speed varchar faster than the text

Five or two binary data

Xxxblob and Xxxtext are corresponding, but they are stored in different ways. Xxxtext are stored as text, and are case-sensitive if stored in English, whereas Xxxblob are stored in binary mode and are not case-sensitive.

The data stored by Xxxblob can only be read out in its entirety.

Xxxtext can specify a character set, Xxxblob cannot specify a character set

Vi. Date and Time type

MySQL data type	Meaning
Date	Dates, such as ' 2016-12-12 '
Time	Time, such as ' 22:22:22 '
Datetime	Date + time, such as ' 2016-12-12 22:22:22 '
Timestamp	Not fixed

Timestamp is special, if you define a field of type timestamp, the time of this field is automatically refreshed when other fields are modified. So the field of this data type can hold the last time the record was modified, not the actual time of storage.

Vii. Properties of data types

When you define a table, you can specify some properties for each field.

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT xxx	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 integer types
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

MySQL "Third" data type