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

In this issue, the editor will bring you about the mysql8 data types. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

The integer type has an optional unsigned attribute, which means that negative values are not allowed, which doubles the upper limit of positive numbers (1 more). Signed and unsigned types use the same storage space and have the same performance, so you can choose the appropriate type according to the actual situation.

Mysql can specify a width for an integer type, such as INT (11), which makes no sense for most applications: it does not limit the legal range, but simply specifies the number of characters used by some of mysql's interactive tools (such as the mysql command line client). For storage and computing, INT (1) and INT (20) are the same. Real number type

Note that the range that FLOAT and DOUBLE can represent is not completely continuous floating-point numbers. Due to the limitation of precision, some very small decimals cannot be expressed. Floating-point related content can refer to my another article, the magical binary (binary) floating-point number. A maximum of 65 digits are allowed for the DECIMAL type, which is expressed in the format of DECIMAL (MMagne D). For example, DECIMAL (5J2) means to hold up to five numbers, two of which are decimals, in the range of-999.99 to 999.99. If the decimal D is 0, the DECIMAL value does not contain a decimal point or decimal part. The FLOAT and DOUBLE types can only be approximated using standard floating-point operations, and the DECIMAL type is required if precise operations, such as amount calculations, are required. Because cpu does not support the direct calculation of DECIMAL, the mysql server itself implements the high-precision calculation of DECIMAL in versions above mysql5.0. Relatively speaking, cpu directly supports native floating-point calculations, so floating-point calculations are significantly faster. When it comes to the amount of money calculation, when the amount of data is relatively large, you can consider using BIGINT instead of DECIMAL. For example, the amount unit is accurate to the point, and 9999 can be used to represent it, and the sub-unit is stored in the BIGINT field, which avoids both the inaccuracy of floating point calculation and the high cost of accurate DECIMAL calculation. The string type CHARCHAR type is of fixed length and space is allocated according to the defined length. When storing the Char value, mysql removes all trailing spaces.

CHAR is ideal for storing very short strings, or all values are close to the same length; for example, CHAR is ideal for storing the value of MD5 for passwords, because it is a fixed length. CHAR is also better than VARCHAR for frequently changing data, because fixed-length CHAR types are not prone to fragmentation. For very short columns, CHAR also has an advantage over VARCHAR in storage space. For example, using CHAR (1) to store only Y and N values requires only 1 byte if you use a single-byte character set, but VARCHAR (1) requires 2 bytes because you need an extra byte of record length.

VARCHARVARCHAR is used to store variable-length strings, which is more space-efficient than fixed-length types. VARCHAR needs one or two extra bytes to record the length of the string: if the maximum length of the column is less than or equal to 255bytes, only 1 byte is used to represent the length, and if the column length is greater than 255bytes, you need to use 2 bytes to represent the length. Assuming the latin character set, a column of VARCHAR (10) requires 11 bytes of storage, and a column of VARCHAR (1000) requires 1002 bytes of storage, because 2 bytes of length information needs to be stored. Which situations are more appropriate to use the VARCHAR type?

The column length of a string is much larger than the average length.

There are few updates to the column, fragmentation is not a problem.

Using a complex character set like UTF-8, each character is stored with a different number of bytes

BINARY and VARBINARYBINARY and VARBINARY store binary strings. Binary strings are very similar to regular strings, but binary strings store bytecodes instead of characters.

Padding is also different: mysql fills BINARY with\ 0 (zero bytes) instead of spaces, and the padding value is not removed when retrieving.

When you need to store binary data, you can use BINARY or VARBINARY,mysql to compare binary strings, one byte at a time, and compare based on the value of that byte. So binary comparisons are much simpler than strings, so they are faster.

BLOB, TEXTBLOB and TEXT are string data types designed to store big data, using binary and character storage respectively. Binary types: TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB character types: TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT differs from other types in that mysql treats each BLOB and text value as a separate object. The storage engine usually does special processing in storage. When the BLOB and text values are too large, InnoDB uses a special "external" storage area for storage, where each value needs 1 to 4 bytes to store a pointer in the row, and then stores the actual value in the external storage area. The only difference between the BLOB and TEXT families is that BLOB stores binary data without collations and character sets, while TEXT stores characters with collations and character sets. Mysql sorts BLOB and TEXT columns differently from other data types: it sorts only the first max_sort_length bytes of each column, not the entire string. If you only need to sort a small portion of the preceding characters, you can reduce the value of max_sort_length, or use ORDER BY SUBSTRING (column,length). Mysql cannot index strings of the full length of BLOB and TEXT columns, nor can they be used to eliminate sorting. The date and time type DATETIME uses 8 bytes of storage, regardless of the time zone. TIMESTAMP uses 4 bytes of storage space, and the time displayed depends on the time zone, saving the number of seconds since midnight GMT on January 1, 1970, which is the same as the timestamp of unix. TIMESTAMP can only represent the period from 1970 to 2038. If the value of TIMESTAMP is not specified when inserting and updating data, mysql updates the current system time of TIMESTAMP by default.

Digit type type range BIT1 to 64 bit

BIT (1) defines a data containing 1 bit, BIT (2) contains 2 bits, and so on, the maximum length is 64 bits. Mysql treats BIT as a string type, not a numeric type. When retrieving the value of BIT, the result is the converted character of the ASCII code corresponding to the binary representation, while the binary number is used when retrieving in the numeric context scenario. For example, if you store a column with the value bread00111001' (decimal value 57) to BIT (8) and retrieve it, the result is 9 (the ASCII code of 9 is 57), and if you add or subtract the field, the result 57 is returned.

The JSON type mysql8 supports direct storage of json format strings, corresponding to the json data type.

The json data column automatically validates the data format of json and will report an error if the format is incorrect.

Optimize the storage format. The json data type converts a string in json format into an internal format, allowing you to quickly read the elements in it.

When summarizing mysql's design of table data types, the following simple principles help to design good data types:

Smaller ones are better.

Choose the smallest type that you think will not go beyond the scope. Smaller data types are usually faster because they take up less disk, memory, and cpu cache, and require fewer cpu cycles to process.

Just keep it simple.

Simple data type operations are usually faster, such as shaping operations faster than strings, because strings have character sets and proofreading rules (collations) are more complex than shaping.

Try to avoid null

If the field can not store null values, try to set the field to not null. Because columns that can be null make indexing, index statistics, and value comparisons more complex, columns that are null use more storage space and require special handling in mysql.

These are the mysql8 data types shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.