Getting Started with MySQL (iii)--MYSQL data type

MySQL data types include integer type, floating-point type, fixed-point number type, date and time type, string type, and binary data type. Different data types determine the data storage format, the valid range, and the corresponding restrictions.

1, Integer type

MySQL supports integer types as shown in the following table

MySQL supports the name of the data type after specifying the display width of the type, the basic form is as follows:

Data type (display width)

Display width refers to the maximum length of data that can be displayed. When you do not specify a width, the default display width of an integer type is the same as the display width of the largest value of the signed number. If the int type is 11,bigint type 20.
In fact, even if the width of the data in and out is greater than the display width of the setting, it can be displayed normally as long as it is less than or equal to the default width.

2. Floating-point type and fixed-point number type

The floating-point type and the fixed-point number type are used to represent decimals. Include the following types

Where the M-finger precision of the fixed-point number type is the total length of the data. The decimal point does not occupy the position, D is the scale, refers to the length after the decimal point. When the precision of inserting data is higher than the actual defined precision, the system is automatically rounded. Floating-point types float and double do not get an error when rounding, but a fixed-point number type decimal will have a warning.
When precision is not specified, the decimal type defaults to the integer digit of 10 and the decimal bit to 0, which is the default integer.
Because the fixed-point number in MySQL is stored as a string, its precision is higher than the floating-point numbers, and the floating-point numbers will appear error, in the case of high data accuracy, the choice of decimal is more secure.

3. Date and Time type

When the inserted data value exceeds the value range of the type, the system will error and insert the corresponding 0 value into the database.

3.1 Year Type

When inserting data to year type, you can use numbers directly or strings, for those below 4 bits will be converted automatically, such as input 32, ' 78 ', 3, ' 2 ' will be converted to 2032,1978,2003 and 2002, respectively.
Note that both ' 0 ' and ' 00 ' are converted to 2000, and 0 will be converted to 0000.

3.2 Time Type

The data assignment for the time type is as follows
' d HH:MM:SS ' format string, or the ' HHMMSS ' format of the string or the value of HHMMSS format, where D represents the number of days, the value range is 0~34, save will automatically convert D to HH, such as input ' 2 11:32:21 ' will be converted to ' 59:32:21 ', of course input does not have to strictly abide by the above format, the system will be automatically converted, the specific rules can be tried. such as ' 2 20 ', ' 2 20:20 ', ' 30 ' and ' 345454 ' will be converted to ' 68:00:00 ', ' 68:20:00 ', ' 00:00:30 ' and ' 34:54:54 '. That is, the basic compliance is assigned from left to right.
When the assignment of HH and SS is greater than 60 o'clock, that is, the value is invalid, the system will error and convert it to 00:00:00. When the input data is valid but outside the time value range, it is cropped to the closest endpoint in the range, such as ' 880:00:00 ', which translates to ' 838:59:59 '.
Current_time and now () can be used to access the current system time.

3.3 Date Type

can be used in ' yyyy-mm-dd ' or ' YYYYMMDD ' format strings, ' Yy-mm-dd ' or ' YYMMDD ' formatted numeric representations of strings, YYYYMMDD, or YYMMDD formats.
You can also use Current_date or now () to enter the current system date.

3.4 DateTime Type

The Datatime type corresponds to a combination of the date type and the time type.
When the data type of a field is DateTime, use Now () to enter the current system date and time.

3.5 Timestamp Type

The same point as the datetime type:
-Display format is the same
-Same Copy method
-Change the current time of the system with now ()
Different points from DateTime types:
-Smaller range than datetime type
-Use Current_timestamp to enter the current date and time of the system
-The system automatically enters the system's current date and time when null or no input is entered
-This type can convert time according to the time zone of different region

4. String type

String types include char, VARCHAR, BLOB, TEXT, enum, and set.

4.1 Char and varchar types

Both are specified with the maximum length when the table is created, the basic form is as follows

String Type (M)

Note that the length of a char type is fixed, always the specified m, and its value can be any value of 0~255.
The length of the varchar type is variable, and the specified m at the time of creation is only the maximum length, and can take any value between 0~65535. That is, how much allocation is used within the maximum m range, the actual space occupied is the actual length of the string plus 1, and this 1 is the end marker for the string. Therefore, the last space in the input string is saved, and the former does not.

When the string length is greater than the maximum value inserted, the system blocks insertion and error.
Although varchar occupies less space and is more flexible than char, but processing speed is less than the char type, it is best to choose the char type for string types that have little change in length and require higher query speed.

4.2 Text Type

The text type can only hold character data, specifically

4.3 Enum types

The enum type is also known as an enumeration type. When a table is created, its range of values is set in the form of a list. The basic form is as follows

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

Where the property name refers to the name of the field, ' Value n ' represents the nth value in the list, and the spaces at the end of those values are deleted directly by the system.
The value of an enum type can take only one element from the list. You can have up to 65,535 values in a list of values. Each value in the list has a sequential number, which is stored in MySQL instead of the specific value in the list.

4.4 Set Type

In general, if you can pick only one value from the list, select the enum type, and if you need to pick a combination of multiple values in the list, you need to select the set type.
Similarly, when you create a table, its range of values is set up in the form of a list. The basic form is as follows

Property name SET (' value 1 ', ' Value 2 ', ..., ' Value n ')

The exact same enum type.
When multiple elements are taken, different elements are separated by commas, and the value of the set type can be a collection of up to 64 elements. With enum, the number is stored instead of the specific value.
When inserting records, the order of elements in the Set field does not matter, and the system is automatically displayed in the order in which they are defined.
If the value class table for the set type is (' A ', ' B ', ' C ', ' D ', ' E '), the insertion value is (' B ') and (' C,b,d '), the result is displayed as (b) and (B,C,D).

5. Binary type

5.1 Binary and varbinary types

Similar to the relationships and usage methods of char and varchar types.
For Char, the space with the maximum length is complete with "\".

5.2 Bit type

The bit type also specifies the maximum length when the table is created, in the basic form

BIT (M)

If the field is of type bit (4), the stored data is from 0~15.
When querying bit type data, use Bin (field name +0) to convert the value to binary display.

5.3 Blob Type

This is a special binary type that can be used to hold large data volumes of binary data, slices, etc. Includes Tinyblob, blobs, Mediumblob, and Longblob.
Blob types are primarily used to store binary files such as pictures, PDF documents, and so on. Typically, these files can be stored in the file system and then stored in the database in a way that is simpler than storing directly in the database and, of course, the speed of access is slower.

6. Select the data type

When you create a table in MySQL, you need to consider which data type is most appropriate for the field you are choosing. Basic precautions can be referred to the above summary.
Here are some frequently asked questions

6.1 Data types that can store paths

6.2 BOOL Type

Storage in 6.3 jpg and MP3 formats

Getting Started with MySQL (iii)--MYSQL data type