MYSQL entry 4: MYSQL Data Types

MYSQL entry 4: MYSQL Data Type links: MYSQL Entry 1: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.html 1. Integer www.2cto.com 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 value ranges of various integer types and the number of stored bytes are as follows: the value range of the unsigned number of integer bytes is 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 decimal places. 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: the value range of negative DECIMAL bytes is unsigned; value range of 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 DOUBLE type or DEC (M, D) M: Maximum length (including DECIMAL part, but does not include the decimal point) D: Keep the length after the decimal point. For floating point and number of points, when the precision of the inserted value is higher than the actual precision, 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, www.2cto.com has a higher precision than floating point. 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 Type 1. DATETIME type indicates values that contain 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. 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. Multiple representation formats can be used for TIME-type TIME values. 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. The TIMESTAMP type uses four bytes to indicate 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. 4. string type the Data Type of the string stored in the database. 1. CHAR, VARCHAR, and VARCHAR types specify the maximum length when creating a table. The basic format is as follows: string type (M) for example, CHAR (4) that is, the index data type is CHAR type, 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 is a special string type. TEXT can only store character data, such as articles. The TEXT types include TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The allowed length of the storage space TINYTEXT 0 ~ Length of 255 bytes + 2 bytes of TEXT 0 ~ Length of 65535 bytes + 2 bytes MEDIUMTEXT 0 ~ Length of 167772150 bytes + 3 bytes LONGTEXT 0 ~ The length of the 4294967295-byte value + 4 bytes can be seen from the table, the difference between the allowed length and the storage space is different. Therefore, select the types that meet the requirements and save the most space as needed. 3. ENUM type (Enumeration type) www.2cto.com ENUM type is also called 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 directly deleted 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. Basic Form of the SET type: attribute 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, spaces at the end of these values will be directly 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. Www.2cto.com create table if not exists 'test '. 'set _ tbl' ('A' set ('A', 'B', 'C', 'D', 'E', 'F ', 'G'); insert into 'test '. 'set _ tbl' VALUES ('F'), ('a, B, C'), ('d, e, A'); INSERT INTO 'test '. 'set _ tbl' VALUES ('H'); SELECT * FROM 'set _ tbl '; 5. binary data is the data type that stores binary data in the database. The value range of the BINARY type is mb. The value range is 0 ~ The fixed-length binary string VARBINARY (M) of M can be 0 ~ The variable length binary string of M, the length of the number of bytes plus one BIT (M) M-BIT binary data, the maximum M value is 64 TINYBLOB Variable Length binary data, up to 255 bytes of BLOB binary data with Variable Length, up to (2 [16]-1) bytes of MEDIUMBLOB binary data with Variable Length, up to (2 [24]-1) bytes LONGBLOB Variable Length BINARY data, up to (2 [32]-1) bytes 1, BINARY and varbinary binary are both specified during table creation, the basic format is as follows: string type (M), which is similar to CHAR type and VARCHAR type. 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. The BIT type specifies the maximum length when creating a table. The basic form is BIT (M, '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 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.