Data type optimization1, try to use the smallest data type that normally stores data (faster, consumes less disk, memory, and CPU) 2, use simple types as much as possible. (such as Integer Fu Cao lower cost) 3, try to avoid using null (you can set the default value of NOT NULL)
data type: Supports aliases, such as Integer,bool,numeric
1. TimeBoth datetime and timestamp are accurate to seconds. You should always use timestamp as much as possible.
datetimeCan save a wide range of values, from 1001 to 9999 the date and time are encapsulated in the format Yyyymmddhhmmss integer,
independent of time zone。 Use 8 bytes of storage space.
timestampStores the number of seconds, with only 4 bytes, with a range smaller than DateTime: can only be represented from 1970-2038.
the display of the date depends on the time zone.
The default is not NULL, and the default value is the current time if no value is specified when inserting.
2. Integral type
tinyint (8-bit), smallint (16-bit), Mediumint (24-bit), int (32-bit), bigint (64-bit)An integral type has an optional unsigned property that indicates that a positive integer calculation typically uses a 64-bit bigint to specify a width for integers (
just to show the number of characters for the interactive tool that specifies MySQL, does not limit the legal range of values）
3. Real numbers
exact type: float,double (not suitable for currency calculations)
Imprecise type: Decimal,decimal is just a storage format in which decimal is converted to a double typeYou can specify a precision that specifies the maximum number of digits allowed before and after the decimal point. Only data types are recommended, do not specify precision
4. String Type
CharWhen stored as a char type, MySQL deletes all trailing spaces (when retrieved), and the char value is populated with spaces as needed for easy comparison. Suitable for storing very short strings or all values close to the same length, frequently changing data is more suitable for char storage
varcharOnly the necessary space is used, but in one case, where row_format=fixed is used for the Jo Jian table, each row uses a fixed-length store that requires 1 or 2 extra bytes to record the length of the string (if the column has a maximum length of less than or equal to 255, only one byte, and so on) applies:
- The maximum length of a string column is much larger than the average length
- Fewer column updates, no fragmentation issues
- A complex character set such as Utf-8 is used, with each character using a different number of bytes to store
Binary and varbinary store binary strings (bytecode), fill binary with '% ' padding, and also remove padding values when retrieving
5. Blob and text typeBinary storage: Tinyblob,smallblob (synonym for blob), Mediumblob,longblob character store: tinytext,smalltext (synonym of text), Mediumtext, Longtext
6. Use enumeration (enum) instead of string typeStorage is very compact and is compressed into one or two bytes based on the number of List values CREATE table Enum_test (' Fish ', ' apple ', ' dog ') not null) INSERT into Enum_test (e) val UEs (' Fish '), (' Dog '), (' Apple ') the data that is stored in the table after the data is inserted is actually an integer rather than a string.
7. Bit data type
bitBit (1) defines a field that contains a single bit, and bit (2) stores two bits. The maximum length of the bit column is 64 bits. MySQL treats bit as a string type, not as a numeric type. It is best to avoid this type, and if you want to store a True/false value with a bit, the alternative is to create a char (0) that can hold a null value or a string of length 0.
SetSave a collection of many true/false values such as the CREATE TABLE ACL (perms set (' Can_read ', ' can_write ', ' can_delete ') not NULL) INSERT into ACL (perms) v Alues (' Can_read,can_delete '); Pros: Efficient Use of storage space, you can use functions such as find_in_set () disadvantage: Changing the definition of a column is too expensive: ALTER TABLE is required, which is very costly for large tables. and cannot be found on the Set column by index
Reference: "High performance MySQL"
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Data type summary
