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

Integer type:

Tinyint-8

Smallint-16

Mediumint-24

Int-32

Bigint-64

Real number type (number with decimal part)

Float-4/double-8: floating point

Decimal: precise (just the storage format, which will be converted to double in the calculation)

Floating-point types typically use less space than decimal when storing the same range of values.

String type:

Varchar: variable length string (requires 1-2 bytes to store string length)

Advantages: saving space compared with fixed length type

Cons: fragmentation problem. When the string update becomes longer, the space of a row becomes longer, but there is no more space to store in the page. MYISAM splits the row into different fragments, and InnoDB splits the page so that the row can be put into the page.

Applicable: the maximum length of a string is much larger than the average length; columns are rarely updated

Char: fixed-length string (mysql removes all trailing spaces)

Advantages: it is not easy to produce fragments

Applicable: store the MD5 value of the password; frequently changed data

Large data types:

Blob: binary

Text: character

Particularity: mysql treats blob and text values as a separate object. When the value is too large, innodb stores a pointer within the value, and the external storage area stores the actual value; mysql sorts blob and text, sorting only the first max_sort_length of each column; mysql cannot index all length strings of a column, nor can it use these indexes to sort

Enumerate type ENUM:

Mysql is very compact when storing enumerated types

Mysql internally saves the position of each value in the list as an integer and saves the lookup table of the "number-string" mapping relationship in the .frm file of the table.

Disadvantages: the list of strings is fixed, and alter table is required to add or delete strings

Advantages: the size of the table can be reduced; when the primary key is reduced, the non-primary key index of the innodb table will also become smaller

Association speed: enum column associated enum column > varchar column associated varchar column > enum column associated varchar column

Date time type:

Datetime:8 bytes; independent of time zone; holds a wide range of values, from 1001 to 9999, with a precision of seconds

Timestamp (try to use): 4 bytes; dependent on time zone; number of seconds saved since 1970.1.1; column defaults to not null

Particularity of Timestamp: if no value for the first timestamp column is specified when inserting data, mysql sets this value to the current time

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.