Shulou(Shulou.com)06/01 Report--

Data type introduction the data table consists of multiple columns of fields, each field specifies a different data type, and after specifying the data type, it also determines the data content to be inserted into the field; different data types also determine the way MySQL uses to store them, and which operation symbols to choose when using them. Numeric data types: TINYINT, SMALINT, MEDIUMINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL Date / time type: YEAR, TIME, DATE, DATETIME, TIMESTAMP; string type: CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET. 2. Brief introduction of numerical types numerical types are mainly used to store numbers. Different numerical types provide different ranges of values. The larger the range of values that can be stored, the greater the storage space required. The numerical types are divided into: ① integer type ② floating point type ③ fixed point type. 1. The integer types are as follows:

Example:

Mysql > create table T1 (- > m tinyint,-> n smallint,-> x mediumint,-> yint,-> z bigint unsigned->)

The details for viewing the table are as follows (its length was not specified when the table was created, but each column has its own default length):

2. Floating-point and fixed-point MySQL use floating-point and fixed-point numbers to represent decimals. There are two types of floating-point numbers: single-precision floating-point numbers (FLOAT) and double-precision floating-point numbers (DOUBLE). Fixed-point numbers only have DECIMAL. Both floating-point numbers and fixed-point numbers can be represented by (M) N, where M is the precision, indicating the total number of digits, and N is the scale, indicating the number of decimal places, such as: 3.145, expressed by M _ Band N, that is, 4 ~ 4 ~ 3 ~ 3. Decimal is actually stored in the form of a string, and it is better to use the DECIMAL type when the precision is high (such as currency, scientific data, etc.). The advantage of floating-point numbers over fixed-point numbers is that floating-point numbers can represent a larger range of data when the length is fixed, and its disadvantage is that it will cause accuracy problems.

Optimization recommendations:

It is recommended to use TINYINT instead of ENUM, BITENUM, SET; to avoid using integer display width, that is, do not use INT (10) similar method to specify the field display width, directly with INT;DECIMAL is the most suitable for saving accuracy requirements, and for calculating data, such as price. But when using the DECIMAL type, pay attention to the length setting

It is recommended that you use integers to operate and store real numbers by multiplying them by the corresponding multiples.

Integers are usually the best data type because they are fast and can use AUTO_INCREMENT. Example 1:mysql > create table T2 (- > x float (5insert into 2),-> y double (5mysql 2),-> z decimal (5Magne2)->); mysql > insert into T2 values (123.45123.45123.45); Query OK, 1 row affected (0.00 sec) mysql > insert into T2 values (123.456123.456123.456); Query OK, 1 row affected, 1 warning (0.01sec) mysql > show warnings +-+ | Level | Code | Message | +- -+ | Note | 1265 | Data truncated for column 'z' at row 1 | +-- + 1 row in set (0.00 sec)

The data inserted in the above example is actually displayed as follows:

From the actual inserted data, it is not difficult to find that if you insert data that does not meet the column requirements, it will eventually be rounded.

It should be noted that in the above numeric types, it allows only one more place after the decimal point, not one more digit before the decimal point, such as inserting 1234.5 or 1234.35 will report an error.

Example 2:mysql > create table T3 (- > x float (10L2),-> y double (10L2),-> z decimal (10L2)->); mysql > insert into T3 values (12345678.123); Query OK, 1 row affected, 1 warning (0.00 sec)

The final data inserted into the table is as follows:

In the above table, x is listed as the float numerical type, and the values of the other two columns are inserted based on rounding, but for the x column of the float numerical type, there is some discrepancy between the inserted data and the actual input data, and the floating range will be larger as the number of decimal places increases.

3. Date and time type

The date and time types that represent time values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR.

Each time type has a range of valid values and a "zero" value, which is used when specifying values that cannot be represented by illegal MySQL.

The TIMESTAMP type has a proprietary automatic update feature.

Optimization recommendations:

The minimum time granularity that MySQL can store is seconds. It is recommended that you use the DATE data type to save the date. The default date format in MySQL is that yyyy-mm-dd; uses MySQL's built-in types DATE, TIME and DATETIME to store time instead of strings; when the data format is TIMESTAMP and DATETIME, you can use CURRENT_TIMESTAMP as the default (after MySQL5.6), and MySQL will automatically return the exact time when the record was inserted; TIMESTAMP is a UTC timestamp and is related to the time zone; the storage format of DATETIME is an integer of YYYYMMDD HH:MM:SS, regardless of the time zone Unless there are special needs, it is recommended to use TIMESTAMP, which is more space-efficient than DATETIME. 1) YEAR format 1: YEAR in 4-bit string format, range from '1901' to' 2155; format 2: YEAR in 4-digit format, range from 1901 to 2155; format 3: YEAR in 2-bit string format, range from '00' to' 99', where '00' ~' 69' is converted to 2000 ~ 2069 and '70' ~' 99' is converted to 1970 ~ 1999 Format 4: YEAR in 2-digit format, ranging from 1 to 99, in which 1-69 is converted to 2001-2069 and 70-99 is converted to 1970-1999.

Example:

Mysql > create table T4 (y year); mysql > insert into T4 values ('2000'), (2000), (94'), (94)

The inserted data is as follows:

Mysql > delete from T4; mysql > insert into T4 values ('0'), (0), (00'), (00)

The newly inserted data is as follows:

Conclusion: when the inserted year is illegal, it will be expressed as 0000.

2) the format of the TIMETIME type is HH:MM:SS, HH for hours, MM for minutes, SS for seconds format 1: TIME in 'HHMMSS' format, for example,' 101112'is understood as 10:11:12, but if an illegal time is inserted, such as' 109712', it is stored as 00:00:00 format 2: TIME in'D HH:MM:SS' string format, where D represents the day You can take a value between 0 and 34, and D will be converted into hours when it is inserted into the database, for example,'2 10 2x24+10 10'is represented in the database as 58:10:00, that is, when it is inserted into the database, D is converted into hours.

Example:

Mysql > create table T5 (d time); mysql > insert into T5 values ('12 time 12'), (121212), (3), (3), (3), (14), ('08 mysql 08')

The inserted data is as follows:

The above inserted data, basically can be corresponding to the SQL statement to see its rule, the only need to explain, should be "82:02:00", the corresponding insertion value is "3 10:2", the most time to write into the table is 3 days (3 X 24) + 10 hours, zero 2 minutes, that is, 82 hours and two minutes.

3) the format of DATEDATE type is YYYY-MM-DD, where YYYY represents the year, MM represents the month, and DD represents the day. The format 1 is' 1000-01-01'~ '9999-12-3'. Format 2 YMMI DD' or 'YYMMDD', where YY represents the annual value of two digits, ranging from' 00' to '99', where' 00' ~ '69' is converted to 2000 ~ 2069 and' 70' ~ 99' is converted to 1970 ~ 1999 Date in 3:YY-MM-DD or YYMMDD format, where the YY range is 00-99, where 00-69 is converted to 2000-2069 and 70-99 is converted to 1970-1999.

Example:

Mysql > create table T6 (d date); mysql > insert into T6 values ('1999-09-09'), (990906), (19990909)

The result of viewing the insert is as follows:

In fact, for the numerical type of date, there are no strict requirements for its format. Values such as 2019-12-12 can be inserted successfully, 2000! A value like 10:10 can also be inserted successfully, as follows:

4) the format of DATETIMEDATETIME type is YYYY-MM-DD HH:MM:SS, where YYYY represents year, MM represents month, DD represents day, HH represents hour, MM represents minute, SS represents second; format 1:'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS', string format, value range is' 1000-01-01 00 YYYY 00' ~ '9999-12-31 23 YYYY 59' Format 2:'YY-MM-DD HH:MM:SS' or 'YYMMDDHHMMSS', string format, where YY range is' 00' ~ '99', where' 00' ~ '69' is converted to 2000 ~ 2069,' 70' ~ '99' is converted to 1970 ~ 1999; format 3:YYYYMMDDHHMMSS or YYMMDDHHMMSS, numeric format, value range is the same as above.

Example:

Mysql > insert into T7 values ('1996-09-19 12-12-24-12-56')

The data inserted into the table is as follows:

5) the format of TIMESTAMPTIMESTAMP type is YYYY-MM-DD HH:MM:SS, and the display width is fixed at 19 characters; the difference between TIMESTAMP and DATETIME is that the value range of TIMESTAMP is smaller than that of DATETIME; the value range of TIMESTAMP is 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC, where UTC is the world standard time, the current time zone will be converted during storage, and the retrieval time will be converted back to the current time zone. String data types string types are used to store string data, as well as binary data such as pictures and sounds; MySQL supports two string types: text strings and binary strings.

Optimization recommendations:

The length of the string varies greatly with the VARCHAR; string short, and all values are close to one length. Binary strings are stored with CHAR;BINARY and VARBINARY, independent of the character set; the BLOB series stores binary strings, independent of the character set; TEXT is a larger VARCHAR;BLOB and TEXT cannot have default values. 1. Char and varcharCHAR (M) are fixed-length strings that specify the length of the string column when they are defined, and fill in the spaces on the right to achieve the specified length when saving. M represents the length of the column, with a range of 0,255 characters. For example, CHAR (4) defines a fixed-length string column that contains up to 4 characters. When the CHAR value is retrieved, the trailing space will be deleted. VARCHAR (M) is a variable length string, and M represents the maximum column length. The range of values is 0mm 65535. The maximum actual length of VARCHAR is determined by the size of the longest line and the character set used, while the actual space occupied is the actual length of the string plus one (a string Terminator).

Example:

Mysql > create table T9 (c char (4), vc varchar (4)); Query OK, 0 rows affected (0.01sec) mysql > insert into T9 values ('ab','ab')

Call the length function to see the length of the inserted data:

Call the compact function to view its actual data:

It can be verified that if it is a column of type char, the trailing space will be deleted, and if it is a column of type varchar, the space will not be deleted, but a space will occupy one bit.

It should be noted that if the column is of type char, if the length of the defined value is 4, then even if the length of the inserted value is only 2, it will still occupy a space of 4 lengths, while varchar will not, because the former belongs to the numerical type of immutable length, while the latter is variable.

2. The maximum length of TEXTTINYTEXT is 255characters; the maximum length of TEXT is 65536 characters; the maximum length of MEDIUMTEXT is 16777215 characters; the maximum length of LONGTEXT is 4294967295 characters. 3 、 ENUM

In the basic data types, there are nothing more than numbers and characters, but some things are more difficult to accurately represent in numbers and characters. For example, there are seven days in a week, which are Sunday, Monday, Tuesday, Wednesday, Thursday, Friday and Saturday. If we use integers 0, 1, 2, 3, 4, 5, 6 to represent these seven days, what about the remaining integers? And such a setting can easily make the data error, that is, the value is out of range. Can we create a data type, and the value range of the data is these seven days? Hence the ENUM type (Enumeration, enumeration), which allows the user to define a data type and list the range of values for that data type. ENUM is a string object whose value is a list of values enumerated (that is, enumerated) in the column specification when the table is created. The syntax format is: field name ENUM ('value 1', 'value 2',. The field name refers to the field to be defined, and the value n refers to the nth value in the enumerated list. Fields of type ENUM can only be taken from the specified enumerated list, and can only be taken one at a time. If you create a member with a space, the trailing space is automatically deleted. The ENUM value is represented internally as an integer, and each enumerated value has an index value: the member values allowed by the list value are numbered starting with 1, and MySQL stores the index number. Enumerations can have up to 65535 elements.

Example 1:

Mysql > create table T10 (- > enm enum ('first','second','third')->); mysql > insert into T10 values (' first'), ('third'), (' second')

The data inserted in the normal view is as follows:

Look up the enumeration values corresponding to each value by using the following methods, as follows:

Example 2:

Mysql > create table T11 (- > soc int,-> level enum ('excellent','good','bad')->); mysql > insert into T11 values (70 excellent','good','bad'), (90 insert into 1), (75 level' at row 1), (50 level' at row 3); mysql > insert into T11 values (70 force best1), (90 values 1), (75 level' at row 1)), (50 level' at row 4)

View the values in the final table (only the values inserted by the first statement):

4. SETSET is a string object, which can have zero or more values. A SET column can have up to 64 members. The value is a list of values specified when the table is created. Syntax: SET ('value 1' values, 'value 2'. 'value n'); like the ENUM type, SET values are represented internally as integers, and each value in the list has an index number; unlike the ENUM type, fields of type ENUM can only select one value from the defined column values to insert, while columns of type SET can select a union of multiple characters from the defined column values If there are duplicates in the column values inserted into the SET field, MySQL automatically deletes the duplicate values, the order in which the values of the SET fields are inserted is not important, and the MySQL will be displayed in the defined order when it is stored in the database.

Example:

Mysql > create table T12 (s set); mysql > insert into T12 values ('a'), ('brecediccphia'), (' arecedicalbpena'); mysql > insert into T12 values ('dLINGMAG at row'); ERROR 1265 (01000): Data truncated for column's'at row 1

Look at the order in the table and find that the values have been deduplicated and arranged in order, as follows:

5. The BITBIT data type is used to save the bit field value, that is, to save the data in binary form. For example, if the data 13 is saved, the binary value of 13 is actually saved, that is, 1101 bit is the bit field type, and the M in BIT (M) represents the number of bits of each value, with a range of 1mm 64. If M is omitted, the default is 1. If the length of the value assigned to the BIT (M) column is less than M bit, fill it with 0 on the left side that is worth it. If a BIT type with at least 4 digits is required to be defined as BIT (4), data greater than 1111 cannot be inserted.

Example:

Mysql > create table T13 (b bit (4)); mysql > insert into T13 values (2), (9), (15); mysql > insert into T13 values (2), (9), (18); ERROR 1406 (22001): Data too long for column 'b'at row 3

Look at the data that was finally inserted in the table (values of the binary type need to be viewed with the following statement, and you can see that only the first sql statement was successfully inserted):

6. The BINARY and VARBINARYBINARY and VARBINARY types are similar to CHAR and VARCHAR, except that they contain binary byte strings; the length of the BINARY type is fixed, and if the length is less than the maximum length, the right side of the BINARY type will be filled with'\ 0' to make up the specified length; the length of the VARBINARY type is variable, and its length can be between 0 and the maximum after the specified length.

Example:

Mysql > create table T14 (- > b binary (3),-> vb varbinary (30)->); mysql > insert into T14 values (5L5)

View the length of the inserted data:

You can also view the comparison with the following statement:

7, BLOBBLOB is used to store a variable number of binary strings, divided into TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB four types; BLOB stores binary strings, while TEXT stores text strings; BLOB has no character set, and sorts and compares values based on column value bytes; TEXT has a character set and sorts and compares values according to the character set.

-this is the end of this article. Thank you for reading-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.