MySQL data type

1. Integral type

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

If unsigned is added to the value range, the maximum value is doubled, such as the tinyint unsigned value range (0~256).

2. Float type (float and double)

MySQL data type	Meaning
Float (m,d)	Single-precision floating-point 8-bit precision (4 bytes) m total number, D decimal place
Double (m,d)	Double-precision floating-point 16-bit precision (8 bytes) m total number, D decimal place

Set a field defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is actually the same, that is, 6 bits.

3. Fixed-point number

Floating-point types hold approximate values in the database, while the fixed-point type stores the exact values in the database.
The decimal (m,d) parameter m<65 is the total number, d<30 and d<m is the decimal place.

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 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.

Floating-point numbers are typically used to represent numeric values that contain fractional parts. When a field is defined as a floating-point type, if the precision of the inserted data exceeds the actual precision defined by the column, the insertion value is rounded to the actual defined precision value, then inserted, and the rounding process does not give an error. The float, double (real) in MySQL is used to represent floating-point numbers.

Fixed-point numbers are different from floating-point numbers, and the fixed-point number is actually stored in string form, so the fixed-point number can store data more accurately. If the accuracy of the inserted data is greater than the actual defined precision, then MySQL warns, but the data is rounded to the actual precision and inserted (if inserted in traditional mode, an error is given). In MySQL, a decimal (or numberic) is used to represent the fixed-point number.

There are errors in storing data with floating-point numbers, and in scenarios where precision is required (such as currency), fixed-point numbers should be used to hold data. For example:

4. String (Char,varchar,text)

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

Char and varchar:
(1). Char (n) If the number of characters stored is less than n, then the space is appended to it, and then the space is removed when queried. So the char type stores a string with no spaces at the end, and varchar is not limited to this.
(2). Char (n) fixed length, char (4) regardless of whether it is deposited in several characters, will occupy 4 bytes, varchar is the actual number of characters stored in + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4), A deposit of 3 characters will take up 4 bytes.
(3). Char types have a faster string retrieval speed than the varchar type.

Char is similar to a varchar type, and is used to store strings, but they save and retrieve strings in different ways. Char is a fixed-length character type , and varchar belongs to a variable-length character type . For example: for char (4) and varchar (4), the two types of definitions:
(1), "in char (4) accounted for 4 byte length, varchar (4) only occupies one byte length;
(2), ' ab ' occupies 4 bytes in char (4), and varchar (4) occupies only 3 bytes of length;
(3), ' abcd ' occupies 4 bytes in char (4), and in varchar (4) It occupies 5 bytes of length;

Why do I have one more byte length in a varchar type? this is because the varchar type uses this extra byte to hold the amount of length that the varchar type actually used. the search for char (4) and varchar (4) is not always the same, for example:

because char is fixed-length, so it processing speed than varchar faster, but its disadvantage is to waste storage space, the program needs to deal with the trailing space, such as shortcomings, so many of those with a small change in length and high query speed requirements of the data can be considered using char type to store. as the MySQL version continues to escalate, the performance of the varchar data type will continue to increase, and the varchar type is more widely used.

CHAR and VARCHAR types
The CHAR type is used for fixed-length strings and must be defined within parentheses with a size modifier. This size modifier ranges from 0-255. A value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.

The 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.

A variant of the CHAR type is a VARCHAR type. It is a variable-length string type and must also have an indicator with a range of 0-255. The difference between char and Varchgar is the way the MySQL database handles this indicator:char treats the size as a value, and a space is used to make up the case without the length. The VARCHAR type treats it as the maximum and stores the value using only the length that the string actually needs to be stored (adding an extra byte to store the length of the string itself). So a VARCHAR type that is shorter than the indicator length is not padded with spaces, but the value longer than the indicator will still be truncated.

　　Because varchar types can dynamically change the length of stored values based on the actual content, the use of varchar type can greatly save disk space and improve storage efficiency when it is not possible to determine how many characters a field requires.

5. Binary data (BLOB)

(1). BLOBs and text are stored differently, text is stored as literals, English storage is case-sensitive, and blobs are stored in binary mode, regardless of case.
(2). Blob-stored data can only be read out as a whole.
(3). Text can specify a character set, and BLOBs do not specify a character set.

You can use char and varchar data types when you save a small number of strings. When you save large text, you typically choose to use text or a blob. The main difference between the two is thatblobs can be used to hold binary data, such as photos, while text can only be used to hold character type data. text and blobs include text, Mediumtext, Longtext, and BLOBs, Mediumblob, Longblob, and three different types. The main difference between them is that the length of the stored text differs from the storage byte.

6. Date and Time type

MySQL data type	Meaning
Date	Date ' 2008-12-2 '
Time	Time ' 12:25:36 '
Datetime	Date Time ' 2008-12-2 22:06:44 '
Timestamp	Automatically store record modification times

If you define a field that is timestamp, the time data in the fields is automatically refreshed when the other fields are modified, so the field of this data type can hold the last time the record was modified.

7.ENUM type (enum type), specifying a range of values when defined.

Attribute name ENUM (' value 1',' value 2',' value 3' ... ' value n')

CREATE TABLETest4 (Sex ENUM ('male','female'));INSERT  intoTest4VALUES('male');INSERT  intoTest4VALUES('ye');--this is an error.SELECT *  fromTest4;

8.SET Type (collection)

When you create a table, you specify a range of values for the set type.

SET (' value 1',' value 2',' value 3' ..., ' value n')     

What is the difference between it and the enum? is basically the multiple-choice enum.

9. Properties of the data type

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT	Default value
PRIMARY KEY	Primary key
Auto_increment	Auto-increment for integer type
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

10. Selection of data types

(1) Integral type: Depending on the maximum value to be displayed;
(2) floating point type: to display decimals. If you want to be accurate to 10 digits after the decimal point, select Double, and you should not choose float. The decimal precision is higher, the floating-point number will appear error, if the precision is high, should select the fixed-point decimal;
(3) String type: The difference between fixed length and variable length, char type occupies a larger space, but processing speed than varchar faster, if the length change is not small, such as the identity card number, the best choice char type. For comment strings, it is best to choose varchar, for Char,mysql will not reclaim extra space, and for Varchar,mysql will reclaim extra space;
(4) Time: According to the needs of the type of display, especially timestamp, if the need to display time and time zone corresponding, you should choose Timestamp;
(5) Enum type and set type: different lengths, the enum type can be up to 65,535 members, and the set type can contain a maximum of 64 members. and enum can only be selected, and set type can be more than one choice;
(6) Text type and blob type: Text can store only character data, and blobs can store binary data. If it is plain text, it is suitable for text. If it is a picture, etc. suitable for deposit binary.

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 first number of characters, the query speed varchar faster than text.

MySQL data type