MYSQL entry 4: MYSQL data type _ MySQL

MYSQL getting started 4: MYSQL data type bitsCN.com

MYSQL entry 4: MYSQL data types

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

I. integer

Integer is the most basic data type in the database. Standard SQL supports INTEGER and SMALLINT INTEGER types. In addition to the two types, MySQL databases also support TINYINT, MEDIUMINT, and BIGINT extensions.

The values of various integer types and the number of stored bytes are as follows:

Value range of the unsigned number of integer bytes

TINYINT 1 0 ~ 255-128 ~ 127

SMALLINT 2 0 ~ 65535-32768 ~ 12767

MEDIUMINT 3 0 ~ 16777215-8388608 ~ 8388607

INT 4 0 ~ 4294967295-2147483648 ~ 2147483647

INTEGER 4 0 ~ 4294967295-2147483648 ~ 2147483647

BIGINT 8 0 ~ 18446744073709551615-9223372036954775808 ~ 9223372036854775807

II. Floating point type and fixed point type

MySQL uses the floating point type and fixed point type to represent decimals. Floating-point numbers include single-precision floating-point numbers (FLOAT type) and DOUBLE-precision floating-point numbers (DOUBLE type ). The point type is DECIMAL.

The value range of FLOAT, DOUBLE, and DECIMAL types and the number of stored bytes are as follows:

Number of decimal bytes negative value range unsigned value range

FLOAT 4-3.402823466E + 38 ~ 0 and 1.175494351E-38 ~

-1.175494351E-38 3.402823466E + 38

DOUBLE 8 1.7976931348623157E + 308 ~ 0 and 2.225074255072014e ~

-2.225074255072014e-308 1.7976931348623157E + 308

DECIMAL (M, D) M + 2 DOUBLE type same as DOUBLE type

Or DEC (M, D)

M: Maximum length (including decimal places, but not decimal places)

D: Keep length after decimal point

For floating-point numbers and fixed points, when the precision of the inserted value is higher than the actual precision defined, the system will automatically perform rounding. The purpose is to make the accuracy of this value meet the requirements. If the floating point is rounded to the nearest integer, no alarm is triggered.

If no precision is specified, floating point numbers and fixed points have their default precision. FLOAT and DOUBLE types save the actual precision by default. This accuracy is related to the accuracy of the operating system and hardware. The DECIMAL type defaults to 10 digits and 0 DECIMAL places, that is, the default value is an integer.

In MySQL, the precision of fixed points is higher than that of floating points. Besides, floating point numbers may have errors. If you want to have high data precision requirements, you should select a specific number of points.

III. date and time types

1. DATETIME type

DATETIME indicates the value that contains both date and time information. MySQL retrieves and displays DATETIME values in 'yyyy-MM-DD HH: MM: SS' format. The supported range is '2017-01-01 00:00:00 '~ '2017-12-31 23:59:59 '.

2. DATE type

The DATE type indicates that only the DATE value does not have the time value. MySQL retrieves and displays DATE values in 'yyyy-MM-DD 'format. Supported range: '2017-01-01 '~ '2017-12-31 '.

3. TIME type

The TIME value can be expressed in multiple formats.

String in the format of 'd HH: MM: SS. fraction. You can also use any of the following non-strict syntax 'hh: MM: SS. fraction', 'hh: MM: SS', 'hh: mm', 'd HH: MM: SS', and 'd HH: MM ', d hh', or 'SS '. Here, D indicates the day, which can be 0 ~ Value of 34. Note that MySQL does not save the score.

A string in the 'hhmms' format that has no separators is assumed to be a meaningful time. For example, '123' is interpreted as '10: 11: 12', but '123' is invalid (it has a meaningless minute part ), it will be changed to '00: 00: 00 '.

The value in HHMMSS format is assumed to be a meaningful time. For example, 101112 is interpreted as '10: 11: 12 '. The following formats are also understandable: SS, MMSS, HHMMSS, and HHMMSS. fraction. Note that MySQL does not save the score.

4. YEAR type

YEAR is a single-byte type, used to represent the YEAR. MySQL retrieves and displays the YEAR value in YYYY format. The value range is 1901 ~ 2155.

YEAR values in various formats can be specified.

Four-character string in the range of '20140901' '~ '123 '.

Four digits in the range of 1901 ~ 2155.

Two strings in the range of '00 '~ '99 '. '00 '~ '69 'and '70 '~ Values in the '99' range are converted to 2000 ~ 2069 and 1970 ~ The YEAR value in the range of 1999.

Two integers in the range of 1 ~ 99. 1 ~ 69 and 70 ~ Values in the 99 range are converted to 2001 ~ 2069 and 1970 ~ The YEAR value in the range of 1999. Note that the range of the two integers is slightly different from that of the Two Strings, because zero cannot be directly specified as a number and interpreted as 2000. It must be specified as a string '0' or '00' or interpreted as 0000.

5. TIMESTAMP type

The TIMESTAMP type uses four bytes to represent the date and time. The range of the TIMESTAMP type is from 1970-01-001 08:00:01 to 2038-01-19 11:14:07. MySQL also displays a value of the TIMESTAMP type in the form of 'yyyy-MM-DD HH: MM: SS. The format shows that the TIMESTAMP type is the same as that of the DATETIME type. The expression for assigning values to fields of the TIMESTAMP type is basically the same as that of the DATETIME type. Note that the range of the TIMESTAMP type is relatively small, and the range of the DATETIME type is not large. Therefore, the input value must be within the valid range of the TIMESTAMP type.

IV. string type

The string type is the data type that stores strings in the database.

1. CHAR and VARCHAR types

Both the CHAR and VARCHAR types specify the maximum length when creating a table. The basic format is as follows:

String type (M)

For example, CHAR (4) indicates that the exponential data type is CHAR, and its maximum length is 4.

The length of the CHAR type is fixed and is specified during table creation. Its length can be 0 ~ Any value of 255.

For example, CHAR (100) indicates that the length of the specified CHAR type is 100.

The length of the VARCHAR type is variable and the maximum length is specified during creation. The maximum value can be 0 ~ Any value between 65535. After the maximum value of the VARCHAR type is specified, the length can be between 0 and the maximum length. For example, the maximum length of VARCHAR (100) is 100. However, not every record occupies 100 locations. But how much is allocated in the maximum range. The actual space occupied by the VARCHAR type is the actual length of the string plus 1. In this way, the system space can be effectively saved.

2. TEXT type

TEXT is a special string type. TEXT can only store character data, such as articles. The TEXT types include TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT.

Allowed length of storage space

TINYTEXT 0 ~ Length of 255 bytes + 2 bytes

TEXT 0 ~ Length of 65535 bytes + 2 bytes

MEDIUMTEXT 0 ~ Length of 167772150 bytes + 3 bytes

LONGTEXT 0 ~ Length of the 4294967295-byte value + 4 bytes

From the table, we can see that the difference between various TEXT types is that the allowed length and storage space are different. Therefore, select the types that meet the requirements and save the most space as needed.

3. ENUM type (enumeration type)

The ENUM type is also called the enumeration type. When creating a table, the value range of the ENUM type is specified as a list.

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

The 'Property name' parameter specifies the field name. The 'value n' parameter indicates the nth value in the list. spaces at the end of these values will be deleted directly by the system.

Values of the ENUm type can only be one element in the list. The value list contains up to 65535 values. Each value in the list has a sequential number, which is stored in MySQL, rather than the value in the list.

If the ENUm type is added with the not null attribute, the default value is 1st elements in the value list. If the not null attribute is NOT added, NULL can be inserted for the ENUm type, and NULL is the default value.

Create table if not exists 'test'. 'enum _ tbl '(

'A' ENUM ('male', 'female '),

'B' ENUM ('true', 'false') NOT NULL

);

Insert into 'test'. 'enum _ tbl'

VALUES ('male', 'true'), (NULL, 'false'), (NULL, NULL), (20, 20 );

SELECT * FROM 'enum _ tbl ';

4. SET type

The basic format is as follows:

Property Name set ('value 1', 'value 2', 'value 3'... 'value n ');

The 'Property name' parameter specifies the field name. The nth value in the 'value n' parameter list will be deleted by the system. The basic format is the same as that of the ENUM type.

A SET value can be a combination of one or more elements in the list. Multiple elements are separated by commas. A SET value can be a combination of up to 64 elements. Each value in the list has a sequential number. This number is stored in MySQL, not the value in the list.

When a record is inserted, the element sequence in the SET field is irrelevant. After the database is saved to the MySQL database, the database system will automatically display it in the defined order.

Create table if not exists 'test'. 'set _ tbl '(

'A' SET ('A', 'B', 'C', 'D', 'e', 'e', 'F ')

);

Insert into 'test'. 'set _ TBL' VALUES ('F'), ('a, B, c'), ('d, e, ');

Insert into 'test'. 'set _ TBL' VALUES ('h ');

SELECT * FROM 'set _ tbl ';

V. binary type

Binary data is the data type that stores binary data in the database.

Value range of binary type

The number of bytes in BINARY (M) is M, and the allowed length is 0 ~ A fixed-length binary string of M

VARBINARY (M) can be 0 ~ A variable-length binary string of M. The number of bytes is the length of the value plus one.

BIT (M) M-BIT binary data, the maximum M value is 64

TINYBLOB variable-length binary data, up to 255 bytes

BLOB variable length binary data, up to (2 [16]-1) bytes

MEDIUMBLOB variable-length binary data, up to (2 [24]-1) bytes

LONGBLOB variable-length binary data, up to (2 [32]-1) bytes

1. BINARY and VARBINARY types

Both the BINARY and VARBINARY types specify the maximum length when creating a table. The basic format is as follows:

String type (M)

This is similar to CHAR and VARCHAR.

For example, BINARY (10) indicates that the index data type is BINARY and its maximum length is 10.

The length of the BINARY type is fixed and is specified when the table is created. The space with the maximum length is supplemented by "/0. For example, BINARY (50) indicates that the length of the BINARY type is 50.

The VARBINARY type length is variable, and the maximum length is specified during table creation. After the maximum value of the VARBINARY type is specified, the base length can be between 0 and the maximum length. For example, the maximum length of VARBINARY (50) is 50. However, not every record has a length of 50 bytes. In the maximum range, how much is allocated. The actual space occupied by the VARBINARY type is the actual length plus one. In this way, the system space can be effectively saved.

2. BIT type

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

BIT (M)

Here, 'M' specifies the maximum length of the number of bytes of the binary is M, and the maximum value of M is 64. For example, BIT (4) is the data type BIT, with a length of 4. if the field type BIT (4), the stored data is from 0 to 15. Because, after becoming binary, the value of 15 is 1111, and its length is 4. If the inserted value is 16, the binary number is 10000 and the length is 5, exceeding the maximum length. Therefore, a value greater than or equal to 16 cannot be inserted into a BIT (4) field. When querying BIT data, you must use BIN (field name + 0) to convert the value to binary display.

3. BLOB type

BLOB is a special binary type. BLOB can be used to store binary data and slices with a large amount of data. BLOB types include TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. The biggest difference between these BLOB types is that the maximum length that can be stored is different. LONGBLOB has the largest length and TINYBLOB has the smallest length.

BLOB and TEXT are similar. The difference is that BLOB is used to store binary data. BLOB data is compared and sorted based on its binary encoding. TEXT is compared and sorted by TEXT mode.

BitsCN.com