MySQL Data type introduction

A few simple principles for selecting a data type

1. The smaller the better, the more simple the better

In general, you should try to use the smallest and simplest data types that can store your data correctly. Because they consume less disk, memory, and CPU cache, they require less CPU cycles to process. At the same time, however, it is important to be careful not to underestimate the range of values that need to be stored, or to cause problems later in the decision.

2. Avoid null as much as possible

Null is the default property of a column, but generally it is best to specify NOT NULL unless you really need to store null. Because columns that contain nulls make indexes, index statistics, and value comparisons more complex, and when nullable columns are indexed, each index record requires an extra byte, and in the MyISAM table it can even cause a fixed-size index to become a variable-size index.

Changing a nullable column to NOT NULL has a small performance boost, so you don't have to deliberately modify it unless you need to, but if you plan to index on a column, you should try to avoid a nullable column.

InnoDB stores null values using separate bits (bit), so for sparse data (many values are null, only a few rows of columns have non-null values), this is a good space efficiency, but this does not apply to MyISAM.

3. Storage of IP

IP is not a string, but a 32-bit unsigned integer, with the decimal point to divide the address into four segments of the presentation method just to make people read easily, so should be used to store IP addresses with unsigned integers, MySQL provides Inet_aton () and Inet_ntoa () function between the two representations of the conversion

Mysql> Show CREATE TABLE test;+-------+------------------------------------------------------------------------ -----------------------------------------+| Table | Create Table |+-------+ --------------------------------------------------------------------------------------------------------------- --+| Test | CREATE TABLE ' Test ' (' a ' int (ten) unsigned default NULL) engine=innodb default Charset=utf8 collate=utf8_bin |+-------+-- --------------------------------------------------------------------------------------------------------------- +1 row in Set (0.00 sec) mysql> INSERT into test values (Inet_aton (' 192.168.1.200 ')), (Inet_aton (' 200.100.30.241 ')); Query OK, 2 rows affected (0.01 sec) mysql> select * from test;+------------+| A |+------------+| 3232235976 | | 3362004721 |+------------+ mysql> Select Inet_ntoa (a) from test;+----------------+| Inet_ntoa (a) |+----------------+| 192.168.1.200 | | 200.100.30.241 |+----------------+

Two specific data types introduced

1. Number types

(1) Integer type

Basic use: Integer type (display width)

Note: The display width has no effect on the size of the number, but only when the Zerofill is set, it is 0 when it is displayed. It is recommended not to set the display width and Zerofill

(2) Floating-point number type

Basic usage: Data type (M,D)

Note 1:m refers to the accuracy, the total number of digits, D is the indicator degree, the number length after the decimal point, such as decimal (5,2) can store 5 numbers and two decimals, that is, the storage range is 999.99 to 999.99.

Note 2: Divided into three categories, decimal (fixed-point number), float (single-precision floating-point type), double (double-precision floating-point type), generally use DECIMAL to store accurate data, such as wages, but unless necessary, it is best not to use floating-point type, Consider using bigint instead of decimal, and multiply the number of units of money you want to store by multiplying the digits of the decimal.

(3) Out of range and overflow handling

When MySQL stores a value that is outside the specified range, the result depends on the SQL mode setting.

• If SQL mode is strict mode, MySQL will error, data insertion failure
• In the case of unrestricted mode, MySQL converts the value to the maximum or minimum value allowed in the range to be stored, such as when the data type is TINYINT or TINYINT UNSIGNED, the stored value 256 is converted to 127 or 255.

2. Time Type

(1) Date

Range:1000-01-01 to 9999-12-31

(2) the DateTime (FSP) FSP represents the number of digits after the decimal point

Range:1000-01-01 00:00:00. 000000 to 9999-12-31 23:59:59. 999999

Eg:  CREATE TABLE T1 (t time (3), DT DATETIME (6));

(3) Timestamp(FSP) FSP represents the number of digits after the decimal point

scope: 1970-01-01 00:00:01. 000000   UTC to   2038-01-19 03:14:07. 999999   UTC

Features: time zone conversion, conversion of the current time zone during storage, and conversion back to the current time zone when retrieving

When defined, the default Current_timestamp and on Update Current_timestamp will set the current time to the defaults and update the time to the current time when any of the values in the row are subsequently updated. When defined, only the default is added and no on update current_timestamp sets the current time to the defaults, and the time value is no longer updated when the row content is updated later. Default has two options, one is the default Current_timestamp, and the other is to specify a specific time. CREATE TABLE T1 (ts TIMESTAMP default current_timestamp on UPDATE current_timestamp,dt DATETIME default Current_timestamp on UPDATE Current_timestamp); CREATE TABLE T1 (ts TIMESTAMP default CURRENT_TIMESTAMP,DT DATETIME default Current_timestamp); CREATE TABLE T1 (ts TIMESTAMP default 0,DT DATETIME default 0);

< Span class= "FONTSTYLE0" > (4) time

scope: -838:59:59. 000000   to  838:59:59. 000000  &NBSP;
/span>

< Span class= "Fontstyle2" > (5) year

Range: Year (4):   1901 to 2155  

          year: 1 to 99   1-69 will be converted to 2001 to 2069, 70-99 will be converted to 1970 to 1999   MySQL5.7.5 has been removed after  

< Span class= "FONTSTYLE0" > 3. Character type

< Span class= "Fontstyle2" > (1) char and varchar

• their storage, data recovery, The maximum length and whether the trailing spaces are retained are different.
• char (n) and varchar (m) n /M represents a character, such as Char (30) that can store 30 characters, and how many bytes are used in the character set
• char is a fixed-length character, char (n), The range of n is 0-255, and when its value is stored, the right side of the value is filled with spaces of the specified length. When the value is retrieved, unless pad_char_to_full_length sql mode is specified, the trailing space is removed
  
• varchar is stored without padding, and if there is a space at the end, it is persisted as is when storing and retrieving data, consistent with standard SQL
• VARCHAR is a variable-length character, varchar (m), the range of M is 0-65535, the maximum effective length of its value is the maximum number of 65535 bytes, and if the value is not greater than 255 bytes, the column consumes 1 bytes to store the length of the value (in bytes). If the value is greater than 255bytes, the column occupies 2 bytes to store the value length.
• For char and varchar, if you do not use strict SQL mode, the value is cut off and a warning is generated when you insert a value that exceeds the bounds. If it is set to strict mode, once the non-whitespace character of the inserted value is cut off, it will be blocked and an error can be induced.
• VARCHAR is more space-saving than a fixed-length type because it uses only the necessary space, but because the rows are longer, it may make the rows longer than they were when the update occurs, which leads to extra work, and if there is no more space stored in the current page, InnoDB also needs to split the page so that rows can be placed in the page , it is necessary to determine, depending on usage, that when the maximum length of a string column is much larger than the average length, the column is updated very rarely, or a character set such as UTF8 is used, and a varchar can be used in cases where each character is stored using a different number of bytes.

(2) binary and varbinary

• The maximum allowable length is similar, except that this length is bit length, not character length.
• 0x00 0x00 < spaces.
  
• for varbinary, the end of the insertion value is not populated and data is not intercepted when the data is being checked. At the time of comparison, all bytes are explicit and identical to binary.
• If the data must be fully consistent with the data being stored at the time of the check-in, one of the varbinary or blobs would be better. With binary words its fill function will cause a certain change, as follows:

mysql> CREATE TABLE T (c BINARY (3)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT into t SET C = ' a '; Query OK, 1 row affected (0.01 sec) mysql> SELECT HEX (c), C = ' a ', c = ' a\0\0 ' from t;+--------+---------+-------------+ | HEX (c) | c = ' a ' | c = ' a\0\0 ' |+--------+---------+-------------+| 610000 | 0       | 1           |+--------+---------+-------------+1 row in Set (0.09 sec)

(3) Blob and text

• BLOBs are divided into tinyblob, blobs, Mediumblob and Longblob, text divided into tinytext, text, Mediumtext and longtext
  
• You must prefix all of the BLOBs and text columns when you index them
• Cannot have default value on BLOB and text column
• When sorting, only the data of the first max_sort_length bits of the column values is compared, andthe default value of Max_sort_length is 1024, which can be changed online.
• The intermediate results generated when the text or blob is checked are stored in a temporary table on the hard disk, not in the in-memory temporary table, because the in-memory temporary table is used by the memories engine, which does not support the text and BLOB data types, so unless necessary, the text and BLOB columns are generally not checked. For example, to avoid using SELECT *
• The length of the data that can be stored depends on the available memory and the size of the buffer, basically can be saved, when it is too large, InnoDB will allocate a specialized external zone for storage, at this time, each value only needs 1-4 bytes to store a pointer in the row, and then store the actual value in the external storage area

(4) Enum and set

• An enum is an enumeration type that can store some non-repeating strings as a predetermined collection. When you store an enumeration, MySQL compresses the number of list values into one or two bytes, internally saves each value in the list as an integer, and holds the lookup table for the number-string mapping relationship in the table's. frm file. Therefore, to avoid using numbers as Enum enumeration constants, it is easy to cause confusion.

  CREATE TABLE Test (' Fish ', ' apple ', ' dog '), insert into test values (' Fish '), (' Dogs '), (' Apple ');mysql> s Elect e+0 from test;+-----+| e+0 |+-----+|   1 | |   3 | |   2 |+-----+

• Enum is sorted by an internally stored integer rather than a defined string

  Mysql> Select e from test;+-------+| E     |+-------+| Fish  | | dog   | | Apple |+-------+

• The disadvantage of enum is that the list of strings is fixed, and you must use ALTER TABLE when you need to add or remove strings
• Enum can only be selected, but set can be multiple, if the value of enum is ' a,b,c ', optional is either a, or B, or C, if it is set, it can be "A, B", "A,b,c".

MySQL Data type introduction