"Go" MySQL Beginner learning four: MySQL data type

Reprint Address: http://www.2cto.com/database/201212/175536.html

Integral type  www.2cto.com            integer types are the most basic data types in a database. Both integer and smallint types are supported in standard SQL. In addition to supporting both types, the MySQL database extends support for tinyint, mediumint, and bigint.           range of integer types, bytes stored as follows:          integer         number of bytes   The range of unsigned numbers   signed value range          tinyint     1       0~255 &nbsp ;                 -128~127         smallint    2       0~65535                 -32768~12767      &NBS P  mediumint   3       0~16777215              -8388608~8388607& nbsp        int         4       0~4294967295            -2147483648~2147483647         integer     4       0~4294967295            -2147483648~2147483647      &NB Sp    bigint      8       0~18446744073709551615  -9223372036954775808~ 9223372036854775807           two, floating-point type and fixed-point type           MySQL uses a floating-point type and a fixed-point number type to represent decimals. Floating-point types include single-precision floating-point numbers (float type) and double-precision floating-point numbers (double type). The fixed-point number type is decimal.          float, double, decimal range, bytes stored as follows:          Decimal type   & nbsp Bytes   Negative values range             unsigned value range          float     &N Bsp  4       -3.402823466e+38~        0 and 1.175494351e-38~        &N Bsp         -1.175494351E-38         3.402823466e+38          DOUBLE       8       1.7976931348623157E+308~ 0 and 2.2250738585072014e~                  -2.2250738585072014E-308 1.7976931 348623157e+308         decimal (m,d) m+2     Double type                 Same double type           or Dec (m,d)          M: Maximum length (including fractional parts, but Excluding decimal points)          D: Retain length after decimal point           for floating-point and fixed-point numbers, when the insertion value is more accurate than the actual defined precision, Rounding is done automatically. The purpose is to make the accuracy of the value meet the requirements. A floating-point number is rounded without an alarm and a warning appears.           The floating-point and fixed-point numbers have their default precision without specifying precision. The float type and double type Save the actual precision by default. This accuracy is related to the accuracy of the operating system and hardware. The default integer bit for the decimal type is 10, and the decimal bit is 0, which is the default integer.  www.2cto.com            in MySQL, fixed-point precision is higher than floating-point number. Moreover, the floating-point number will be error. If you want the accuracy of the data to be high, you should select the fixed-point number.   Date and Time type  1, datetime type          datetime types represent values that contain both date and time information. MySQL retrieves and displays the datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The range of support is ' 1000-01-01 00:00:00 ' ~ ' 9999-12-31 23:59:59 '.  2, Date type       The    date type represents a value that has only a date value and no time value. MySQL retrieves and displays the date value in the ' YYYY-MM-DD ' format. The scope of support is ' 1000-01-01 ' ~ ' 9999-12-31 '.  3, Time type          time values can be used in a variety of presentation formats.           ' D HH:MM:SS.fraction ' format strings. You can also use any of the following "non-strict" syntax ' HH:MM:SS.fraction ', ' HH:MM:SS ', ' hh:mm ', ' d HH:MM:SS ', ' d ' hh:mm ', ' d HH ' or ' SS '. Here d is the day, you can take the value of 0~34. Please note that MySQL does not save fractions.           ' HHMMSS ' in the format of a string without spacers, is assumed to be a meaningful time. For example, ' 101112 ' is understood as ' 10:11:12 ', but ' 109712 ' is illegal (it has a meaningless minute part) and it will become ' 00:00:00 '. The value of          HHMMSS format is assumed to be a meaningful time. For example, 101112 is understood as ' 10:11:12 '. The following format can also be understood: SS, MMSS, HHMMSS, Hhmmss.fraction. Please note that MySQL does not save fractions.  4, Year type          year type is a single-byte type used to represent years. MySQL retrieves and displays the year value in yyyy format. The range is 1901~2155.           You can specify year values in various formats.           Four-bit string with a range of ' 1901 ' ~ ' 2155 '.           four-digit range of 1901~2155.           Two-bit string with a range of ' 00 ' ~ ' 99 '. The values of ' 00 ' ~ ' 69 ' and ' 70 ' ~ ' 99 ' ranges are converted to the year values of the 2000~2069 and 1970~1999 ranges, respectively. &nbsp         Two-bit integer range of 1~99. The values of the 1~69 and 70~99 ranges are converted to the year values of the 2001~2069 and 1970~1999 ranges, respectively. Note that the two-bit integer range differs slightly from the two-bit string range, because 0 cannot be specified directly as a number and interpreted as 2000. It must be specified as a string ' 0 ' or ' 00 ' or it is interpreted as 0000.  5, timestamp type          timestamp type uses 4 bytes to represent the date and time. The timestamp type ranges from 1970-01-001 08:00:01 to 2038-01-19 11:14:07. MySQL also displays values of type timestamp in the form of ' Yyyy-mm-dd HH:MM:SS '. As you can see from its form, the timestamp type is the same format as the datetime type. Assigning a value to a field of type timestamp is basically the same as the datetime type. Note that the timestamp type has a smaller range than the range of datetime types. Therefore, when entering a value, ensure that the range is valid in the timestamp type.            Four, String type           The string type is the data type that stores the string in the database.  1, char types, and varchar types          CHAR types and varchar types Specify the maximum length when creating a table with the following basic form:          String type (M)           For example, char (4) means that the data type is a char type with a maximum length of 4.          The length of the  char type is fixed and is specified when the table is created. Its length can be any value of 0~255.           For example, char (100) is the specified char type with a length of 100.          varcharThe length of the type is variable and the maximum length is specified when it is created. When defined, its maximum value can take any value between 0~65535. After specifying the maximum value of the varchar type, the length can be between 0 and the maximum length. For example, the maximum length of VARCHAR (100) is 100. However, not every record has to occupy 100 locations. Instead, it is within this maximum range, how much is allocated. The actual space occupied by the varchar type is the actual length of the string plus 1. In this way, the space of the system can be saved effectively.  2, Text type          text type is a special type of string. Text can only hold character data, such as articles. The text type contains Tinytext, text, Mediumtext, and Longtext.           types         allowed lengths       storage          tinytext    0~255 bytes         value length + 2 bytes          text   &NB Sp    0~65535 bytes       value length + 2 bytes          mediumtext  0~167772150 bytes & nbsp; Length of Value + 3 bytes          longtext    0~4294967295 byte value length + 4 bytes           As can be seen from the table, the difference between the various text types is that the allowed lengths and storage spaces are different. So in these kinds of text, according to the requirements of the choice can meet the needs of the most space-saving type can be.   3, enum types (enum types)  www.2cto.com           enum types are also known as enum types. When you create a table, the range of values for the enum type isThe form of the list is specified.           attribute name ENUM (' value 1 ', ' Value 2 ',...., ' value n ');          where the ' attribute name ' parameter specifies the field name; ' Value n ' The parameter represents the nth value in the list, and the spaces at the end of those values are deleted directly by the system.          enum A value of type only one element in the list. It can have up to 65,535 values in its list of values. Each value in the list has a sequential number, which is the number that is stored in MySQL, not the value in the list.           If the enum type is prefixed with a NOT NULL property, the default value is the 1th element of the list of values. If the not NULL property is not added, the enum type allows NULL to be inserted, and NULL is the default value.          create TABLE IF not EXISTS ' test '. ' Enum_tbl ' (          ' a ' enum ( ' Male ', ' female '),          ' B ' ENUM (' true ', ' false ') not null         ); nbsp        insert into ' test ', ' enum_tbl '           values (' Male ', ' true '), ( NULL, ' false '), (null, NULL), (; )        select * from ' enum_tbl ';  4, set type   &NBSP ;       Basic forms as follows:          Property name Set (' Value 1 ', ' Value 2 ', ' Value 3 ' ... ' Value n ');      &NBSP   where the ' attribute name ' parameter specifies the field name, and the nth value in the ' Value n ' argument list, the spaces at the end of these values will be deleted directly by the system. Its basic form is the same as the enum type.          set The value of a type can take a combination of one element or multiple elements in a list. When multiple elements are taken, different elements are separated by commas. The value of a set type can be a combination of up to 64 elements. Each value in the list has a sequential number. This number is stored in MySQL instead of the value in the list.           when inserting records, the order of elements in the Set field does not matter. Once stored in the MySQL database, the database system is automatically displayed in the order in which it was defined.  www.2cto.com           create TABLE IF not EXISTS ' test '. ' Set_tbl ' (    &NBSP ;     ' 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 ';         & nbsp; the binary type           binary type is the data type that stores the binary data in the database.           binary type   value range          binary (m)     bytes to m, allowed length 0~m The fixed-length binary string          varbinary (m) allow variable-length binary string lengths of 0~m, number of bytes for the length of the value plus one          bit (m)   & nbsp   m-bit binary data, m maximum 64         TINYBLOB     variable Long binary data, up to 255 bytes       &NBS P  blob         variable Long binary data, up to (2[16]-1) bytes          MEDIUMBLOB   variable Long binary number Up to (2[24]-1) bytes          LONGBLOB     variable-length binary data, up to (2[32]-1) bytes  1, Binary and varbinary types          binary and varbinary types all specify the maximum length when creating a table with the following basic form:          String type (M)           This is similar to char type and varchar type.           For example, binary (10) means that the data type is a binary type with a maximum length of 10. The length of the          binary type is fixed and is specified when the table is created. The space that is less than the maximum length is complete by "\". For example, binary (50) is the length of the specified binary type of 50.         The length of the  varbinary type is variable, specifying the maximum length when the table is created. After specifying the maximum value of the varbinary type, the base length can be between 0 and the maximum length. For example, the maximum byte length for VARBINARY (50) is 50. However, not every record has a byte length of 50. In thisMaximum range, how many allocations are used. The actual space occupied by the VARBINARY type is the actual length plus one. In this way, the space of the system can be saved effectively.  2, BIT type          bit type is also the maximum length specified when creating a table, and its basic form is as follows:         bit (M)           where ' M ' specifies the maximum byte length for the binary number of M,m is 64. For example, bit (4) is the data type bit type, and the length is 4. If the field is of 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, which exceeds the maximum length. Therefore, a number greater than or equal to 16 cannot be inserted into a field of type bit (4). When querying bit type data, use Bin (field name +0) to convert the value to binary display.  3, blob type          BLOB type is a special binary type. BLOBs can be used to hold large amounts of data in binary data, slices, and so on. Blob types include Tinyblob, blobs, Mediumblob, and Longblob. The biggest difference between these kinds of BLOB types is the maximum length that can be saved. The length of the Longblob is the largest and the length of the Tinyblob is minimal.

The

         BLOB type is similar to the text type. The difference is that blob types are used to store binary data, and BLOB type data is compared and sorted by binary encoding. The text type is compared and sorted in textual mode.