MySQL entry (iii) -- MySQL Data Type

MySQL entry (iii) -- MySQL Data Type

MySQL data types include integer, floating point, point, date, and time, string, and binary data. Different Data Types determine the data storage format, valid range, and corresponding restrictions.

1. Integer type

The following table lists the integer types supported by MySQL.

MySQL supports specifying the display width after the data type name. The basic format is as follows:

Data Type (display width)

The display width refers to the maximum length of data that can be displayed. If no width is specified, the default display width of the integer type is the same as the display width of the maximum value of the number of symbols. For example, if INT type is 11, BIGINT type is 20.
But in fact, even if the width of the incoming and outgoing data is greater than the configured display width, it can be displayed normally and completely as long as it is smaller than or equal to the default width.

2. floating point type and fixed point type

The floating point type and the fixed point type are used to represent decimals. Including the following types

M of the fixed point type indicates the precision, which is the total length of the data. The decimal point does not occupy the position. D is the scale, which refers to the length after the decimal point. When the precision of the inserted data is higher than the actual precision, the system will automatically perform rounding. FLOAT and DOUBLE of the floating point type do not return an error when rounding, but DECIMAL of the specific point type has a warning.
If no precision is specified, the DECIMAL type defaults to 10 digits and 0 DECIMAL places, that is, the default integer.
Because the number of fixed points is stored as strings in MySQL, the precision is higher than that of floating point numbers, and there will be errors in floating point numbers. When the data precision is high, it is safer to select DECIMAL.

3. Date and Time types

When the inserted data value exceeds the value range of this type, the system reports an error and inserts the corresponding zero value into the database.

3.1 Year type

When you insert data to the Year type, you can use numbers or strings. Values smaller than four digits are automatically converted, such as 32, '78 ', 3, '2' is converted to 2032,1978, 2003, and 2002 respectively.
Note that both '0' and '00' are converted to 2000, while 0 is converted to 0000.

3.2 Time type

The Time data value assignment method is as follows:
A string in 'd HH: MM: ss' format, or a string in 'hhmms' format or a value in HHMMSS format. D indicates the number of days, and the value range is 0 ~ 34. D is automatically converted to HH during saving. If you enter '2 11:32:21 ', it is converted to '59: 32: 21'. Of course, you do not have to strictly follow the above format, the system will automatically switch. You can try the specific rules on your own. For example, '2 20', '2 ', '30', and '123' are converted to '68: 00: 00', '68: 20: 00', '00: 00: 30' and '34: 54: 54 '. That is, it basically matches the distribution from left to right.
When the value of HH and SS is greater than 60, that is, this value is invalid. The system reports an error and converts it to 00:00:00. When the input data is valid but exceeds the Time value range, it is cut to the nearest endpoint in the range. For example, '2014: 00: 00' is converted to '2014: 59: 59 '.
Applicable to the current system time when CURRENT_TIME and NOW () are used.

3.3 DATE type

A string in 'yyyy-MM-DD 'or 'yyyymmdd' format, a string in 'yy-MM-DD' or a string in 'yyyymmdd' format, a number in YYYYMMDD format, or a number in YYMMDD format can be applied.
You can also use CURRENT_DATE or NOW () to enter the current system date.

3.4 DATETIME type

The DATATIME type is equivalent to a combination of the DATE and TIME types.
When the data type of a field is DATETIME, you can use NOW () to enter the current system date and time.

3.5 TIMESTAMP type

Similar to the DATETIME type:
-The display format is the same.
-The replication method is the same.
-Use NOW () to change the current system time
Different from the DATETIME type:
-The value range is smaller than that of the DATETIME type.
-Use CURRENT_TIMESTAMP to enter the current system date and time.
-If NULL is input or no input is input, the system automatically enters the current date and time of the system.
-This type can be converted based on the time zone of different regions.

4. string type

String types include CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET.

4.1 CHAR and VARCHAR types

Both of them specify the maximum length when creating a table. The basic form is as follows:

String type (M)

Note that the length of the CHAR type is fixed, always the specified M, and its value can be 0 ~ Any value of 255.
The length of the VARCHAR type is variable, and the M value specified during creation is the maximum length, which can be 0 ~ Any value between 65535. That is, how much space is allocated within the maximum M range. The actual occupied space is the actual length of the string plus 1, and this 1 is the end sign of the string. Therefore, the space at the end of the input string will be saved, but the former will not.

When the length of the input and output strings is greater than the maximum value, the system will prevent insertion and report an error.
Although VARCHAR occupies less space and is more flexible than CHAR, the processing speed is not as high as CHAR type. Therefore, it is best to select CHAR type for string types with low length variation and high query speed requirements.

4.2 TEXT type

The TEXT type can only store character data.

4.3 ENUM type

The ENUM type is also called the enumeration type. When creating a table, its value range is defined in the form of a list. The basic form is as follows:

Attribute name ENUM ('value 1', 'value 2', ·, 'value n ')

Here, the attribute name refers to the name of the field, and 'value n' indicates the nth value in the list. spaces at the end of these values will be directly deleted by the system.
Values of the ENUM type can only take one element in the list. A maximum of 65535 values are allowed in the Value List. Each value in the list has a sequential number, which is stored in MySQL rather than in the list.

4.4 SET type

Generally, if you can select only one value in the list, select the ENUM type. If you want to select a combination of multiple values in the list, select the SET type.
Similarly, when creating a table, its value range is defined in the form of a list. The basic form is as follows:

Property name SET ('value 1', 'value 2', ·, 'value n ')

The specific type is the same as that of ENUM.
When multiple elements are obtained, different elements are separated by commas. The SET type value can only be a SET consisting of up to 64 elements. The same as ENUM, which stores numbers rather than specific values.
When a record is inserted, the element sequence in the SET field is irrelevant. The system automatically displays the sequence in the defined order.
For example, if the value type of SET is ('A', 'B', 'C', 'D', 'E'), the inserted value is ('B ') and ('C, B, D'), the result is (B) and (B, C, D ).

5. Binary type

5.1 BINARY and VARBINARY types

It is similar to the relationship and usage of CHAR and VARCHAR types.
For CHAR, "\ 0" is used to complete the space with less than the maximum length.

5.2 BIT type

The BIT type also specifies the maximum length when creating a table. The basic form is

BIT (M)

If the field type is BIT (4), the stored data is from 0 ~ 15.
When querying BIT data, you must 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 store binary data and slices with a large amount of data. Including TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
BLOB is mainly used to store images, PDF files, and other binary files. Generally, these files can be stored in the file system and then stored in the database. This method is simpler than simply storing them in the database, of course, the access speed will be slower.

6. Select a data type

When creating a table in MySQL, you must consider which data type is the most suitable for the field. For the basic precautions, refer to the above summary.
Below are some common problems

6.1 data types that can be stored in the path

6.2 BOOL type

6.3 storage in JPG and MP3 formats