Mysql Data Type summary, mysql Data Type

Mysql Data Type summary, mysql Data Type
Data Type optimization1. Try to use the minimum data type that can normally store data (faster, less occupied disk, memory, and cpu). 2. Try to use a simple data type. (For example, the integer type is lower than the character operation cost) 3. Avoid NULL as much as possible (you can add the default value to set not null)
Data Type: aliases are supported, such as integer, bool, and numeric.1. TimeDATETIME and TIMESTAMP are precise to seconds. Use timestamp whenever possible.DatetimeIt can save a wide range of values. From January 1, 1001 to January 1, 9999, it encapsulates the date and time into an integer in the format of YYYYMMDDHHMMSS,Irrelevant to the time zone. Uses 8 bytes of storage space.TimestampStorage seconds. Only four bytes are used. The value ranges from 1970 to 2038.The date display depends on the time zone.The default value is not null. If no value is specified during insertion, the default value is the current time.
2. IntegerTinyint (8 bits), smallint (16 bits), mediumint (24 bits), int (32 bits), bigint (64 bits)An integer has an optional unsigned attribute, indicating that the 64-bit bigint can be used to specify the width of an integer (It is only used to specify the number of characters displayed by the mysql interaction tool and does not limit the valid range of values.)
3. Real NumberExact type: float and double (not suitable for currency calculation)Inaccurate type: decimal and decimal are only storage formats. In calculation, decimal is converted to double type.You can specify the precision. We recommend that you only use the data type to specify the maximum number of digits allowed before and after the decimal point. do not specify the precision.4. string typeCharWhen the storage is char type, mysql will delete all trailing spaces (for retrieval), and the char value will be filled with spaces as needed to facilitate comparison. Suitable for storing short strings or all values that are close to the same length, and for storing frequently changed data with charVarcharOnly use the necessary space, but in one case, that is, if you use row_format = fixed when creating a table, each row uses a fixed length storage. One or two additional bytes are required to record the string length (if the maximum column length is less than or equal to 255, only one byte is required, and so on:

• The maximum length of a string column is much larger than the average length.
• Few column updates, no fragmentation issues
• Using a complex character set like UTF-8, each character uses a different number of bytes for storage.

Binary and varbinary store binary strings (bytecode), fill binary with '\ 0' fill, will also remove the fill value during retrieval
5. BLOB and TEXT typesBinary storage: tinyblob, smallblob (synonym of blob), mediumblob, and longblob character storage: tinytext, smalltext (synonym of text), mediumtext, and longtext
6. Use enumeration (ENUM) instead of string typeThe storage is very compact and will be compressed to one or two bytes based on the number of values in the list. create table enum_test (e enum ('fish ', 'apple', 'dog') not null) insert into enum_test (e) values ('fish '), ('dog'), ('apple') data is inserted and stored in the table as integers rather than strings. 7. Bit Data Type BitBit (1) defines a field containing a single bit, and bit (2) stores two digits. The maximum length of a bit column is 64 bits. Mysql treats bit as a string rather than a number. It is best to avoid this type. If you want to use a bit to store a true/false value, the alternative is to create a char (0), you can save a string with a NULL value or a length of 0. SetStores a set of many true/false values, such as create table acl (perms set ('can _ read', 'can _ write', 'can _ delete') not null) insert into acl (perms) values ('can _ read, can_delete '); advantage: using the bucket effectively, you can use functions such as find_in_set (). Disadvantages: the cost of changing the column definition is too high: alter table is required, which is very expensive for large tables. In addition, you cannot search by index on the set column.
Reference: High-Performance Mysql

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.