MySQL data type

Numeric type

MySQL supports all numeric types in standard SQL, including strict data types, INTEGER,SMALLINT,DECIMAL, NUMBERIC as well as approximate numeric data types FLOAT,REAL,DOUBLE PRESISION , and expands on this basis. This extension adds TINYINT,MEDIUMINT,BIGINT 3 different lengths of shaping, and adds the BIT type to hold the bit data.

type	size	Range (signed)	Range (unsigned)	Use
TINYINT	1 bytes	(-128,127)	(0,255)	Small integer value
SMALLINT	2 bytes	(-32 768,32 767)	(0,65 535)	Large integer value
Mediumint	3 bytes	(-8 388 608,8 388 607)	(0,16 777 215)	Large integer value
int or integer	4 bytes	(-2 147 483 648,2 147 483 647)	(0,4 294 967 295)	Large integer value
BIGINT	8 bytes	(-9 233 372 036 854 775 808,9 223 372 036 854 775 807)	(0,18 446 744 073 709 551 615)	Maximum integer value
FLOAT	4 bytes	( -3.402 823 466 e+38,-1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38)	0, (1.175 494 351 e-38,3.402 823 466 e+38)	Single precision Floating point value
DOUBLE	8 bytes	( -1.797 693 134 862 315 7 e+308,-2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 E +308)	0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)	Double precision Floating point value
DECIMAL	For decimal (m,d), if m>d, is m+2 otherwise d+2	Values that depend on M and D	Values that depend on M and D	Decimal value

int type

The 5 major integer types supported in MySQL are Tinyint,smallint,mediumint,int and BIGINT, and an "out of range" error message occurs if the operation is out of type range. These types are largely the same, except that the size of the values they store is not the same.

For integer types, MySQL also supports specifying the display width in parentheses following the type name, such as INT (5), which fills the width before the number when the numeric width is less than 5 bits, and the default is int (11) If the specified width is not displayed. Generally with Zerofill use, is the meaning of "0" fill, that is, the space is not enough to fill with the character "0", it should be noted that the use of a width indicator does not affect the size of the field and the range of values it can store.

In case we need to store a number that exceeds the permitted range for a field, MySQL is truncated and then stored according to the one end of the allowable range closest to it. One of the more special areas is that MySQL will automatically change to 0 before the non-compliant value is inserted into the table.

UNSIGNEDmodifier specifies that the field only holds positive values. Because you do not need to save the positive and negative symbols of the numbers, you can save a "bit" space at the time of storage. thereby increasing the range of values that this field can store. If a column is specified as Zerofill, MySQL automatically adds the unsigned property to the column.

An integer type also has a property: Auto_increment. This property can be used only for integer types when a unique identifier or attribute value needs to be generated. The auto_increment value typically starts at 1 and increases by 1 per row. When you insert null into a auto_increment column, MySQL inserts a value that is 1 larger than the current maximum value for that column. There can be up to 1 auto_increment columns in a table. For any column that wants to use auto_increment, it should be defined as not NULL and defined as primary key or defined as a unique key.

FLOAT, DOUBLE, and DECIMAL types

The three floating-point types supported by MySQL are FLOAT , DOUBLE and DECIMAL types. Float numeric types are used to represent single-precision floating-point numbers, whereas double numeric types are used to represent double-precision floating-point numbers, while fixed-point only represents decimal. Fixed-point numbers are stored as strings inside MySQL, more accurate than floating-point numbers, and are suitable for representing high-precision data such as currencies.

As with integers, these types also have additional parameters: "(m,d)" means that the value displays a total of M-digits (integer digits + decimal places), where the D bit is after the decimal point, and M and D are also known as Precision and scale, a display width indicator, and a decimal point indicator. For example, the statement FLOAT (7,3) specifies that the value displayed will not exceed 7 digits, followed by a 3-digit number after the decimal point.

值得注意的是，浮点数后面跟"（M,D）"的用法是非标准用法，如果用于数据库的迁移，最好不要 这么使用。float和double在不指定精度时，默认会按照实际的精度（由实际硬件和操作系统决定）来显示，而DECIMAL在不指定精度时，默认的 整数位为10，默认的小数位为0。

For the number of digits after the decimal point beyond the allowable range, MySQL automatically rounds it to the nearest value, and then inserts it.

The DECIMAL data type is used in calculations that require very high precision, which allows you to specify the precision and count method of a numeric value as a selection parameter. The precision here refers to the total number of valid digits saved for this value, while the Count method represents the number of digits after the decimal point. For example, the statement decimal (7,3) specifies that the stored value will not exceed 7 digits and no more than 3 digits after the decimal point.

The UNSIGNED and Zerofill modifiers can also be used by the FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type.

Summary: floating-point number if not write precision and scale, then according to the actual precision value display, if there is precision and scale, it will automatically insert the results of rounding, the system will not error; fixed-point numbers if you do not write precision and scale, follow the default decimal (10,0). And if the data exceeds the accuracy and scale values, the system will error.

BIT

For bit (bit) type, used to hold bits field value, bit (m) can be used to hold multi-bit binary number, M range from 1~64, if not write the default is 1 bits. For bit fields, direct use of the SELECT command will not see the results, and can be read in Bin () (shown in binary format) or hex () (shown in hexadecimal format) function.

When the data is inserted into the bit Type field, it is first converted to binary, and if the number of bits allows, the insert succeeds, and if the number of bits inserted is greater than the actual defined number of digits, the insertion fails.

String type

The string type refers to char, VARCHAR, Binary, VARBINARY, BLOB, text, enum, and set, which can be stored from a simple character to a large chunk of text or binary string data.

type	size	Use
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-65535 bytes	Variable length string
Tinyblob	0-255 bytes	A binary string of no more than 255 characters
Tinytext	0-255 bytes	Short text string
Blob	0-65 535 bytes	Long text data in binary form
TEXT	0-65 535 bytes	Long Text data
Mediumblob	0-16 777 215 bytes	Medium-length text data in binary form
Mediumtext	0-16 777 215 bytes	Medium-Length text data
Longblob	0-4 294 967 295 bytes	Large text data in binary form
Longtext	0-4 294 967 295 bytes	Maximum text data

CHAR and VARCHAR types

The CHAR type is used for fixed-length strings and must be defined within parentheses with a size modifier. This size modifier ranges from 0-255. A value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.

The length of the char and varchar type declarations represents the maximum number of characters that you want to save. For example, a 30-character

The maximum total length of a non-empty char is 255 "bytes"; The maximum total length of a non-null varchar is 65533 "bytes"

The maximum total length of a nullable char is 254 "bytes"; The maximum total length of a nullable varchar is 65532 "bytes"

Cause: Non-empty tags need to occupy a byte, varchar more than 255 requires 2 bytes to mark the field length, not more than 255 to mark the field length with 1 bytes

Note that the top is "byte", not "character". However, when the MYSQL5 field is defined, it is the number of "characters" that are defined. For example, varchar (10), you can only deposit 10 English letters or kanji, although one character may account for multiple bytes.

A character may occupy more than one byte, which is determined by the encoded and stored characters. For example, UTF8 (a variable-length Unicode encoding), typically a Chinese character occupies 3 bytes, and an English letter occupies one byte.

Therefore, in a UTF8 environment, it is not allowed to define VARCHAR (65535), as this is far beyond the 65,535-byte limit.

The CHAR type can use the BINARY modifier. When used for comparison operations, this modifier causes CHAR to participate in the operation in binary mode, rather than in a traditional case-sensitive manner.

A variant of the CHAR type is a VARCHAR type. It is a variable-length string type and must also have an indicator with a range of 0-255. The difference between char and Varchgar is the way the MySQL database handles this indicator: Char treats the size as a value, and when the length is insufficient, it is filled with blanks. The VARCHAR type treats it as the maximum and stores the value using only the length that the string actually needs to be stored (adding an extra byte to store the length of the string itself). So a VARCHAR type that is shorter than the indicator length is not padded with spaces, but the value longer than the indicator will still be truncated. At the time of retrieval, the char column removes trailing spaces, while varchar preserves them.

Because varchar types can dynamically change the length of stored values based on the actual content, the use of varchar type can greatly save disk space and improve storage efficiency when it is not possible to determine how many characters a field requires.

The VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier.

TEXT and BLOB types

For cases where the field length requires more than 255, MySQL provides TEXT and BLOB two types. Depending on the size of the stored data, they all have different subtypes. These large data are used to store binary data types such as text blocks or images, sound files, and so on.

The TEXT and BLOB types differ in classification and comparison. BLOB type 区分大小写 , while TEXT 不区分大小写 . The size modifier is not used for various blobs and TEXT subtypes. A value that is larger than the maximum range supported by the specified type is automatically truncated.

BLOBs are designed to prevent the loss of information due to character set problems, such as: 0xFF bytes in a picture, which is considered illegal in the ASCII character set and filtered when it is in storage.

Binary and varbinary types

Binary and varbinary are similar to char and varchar, but differ in that they contain binary strings and do not contain non-binary strings. When the binary value is saved, the length of the specified field definition is reached at the end of the value by padding "0x00" (0 bytes).

Date and Time type

MySQL has 5 different data types to choose from when working with date and time type values. They can be divided into simple dates, time types, and mixed date and time types. Depending on the accuracy required, subtypes can be used in each category, and MySQL has built-in functionality to turn a variety of input formats into a standard format.

td> time value or Duration

type	size (bytes)	range		purpose
date	3	1000-01-01/9999-12-31	YYYY-MM-DD	Date value
time	3	' -838:59:59 '/' 838:59:59 '	HH:MM:SS
year	1	1901/2155	yyyy	Year value
datetime	8	1000-01-0 1 00:00:00/9999-12-31 23:59:59	yyyy-mm-dd HH:MM:SS	Blend date and time value
timestamp	4	1970-01-01 00:00:00/2037 year time	YYYYMMDD HHMMSS	mixed date and time values, timestamp

Year, date, and time type

MySQL stores simple date values with the date and year types, and time values are stored using the type of times. These types can be described as strings or sequence of integers without delimiters. If the description is a string, the value of the DATE type should be separated by a hyphen, and the value of the time type should be separated by a colon as a delimiter.

需要注意的是，没有冒号分隔符的 TIME 类型值，将会被 MySQL 理解为持续的时间，而不是时间戳。

MySQL also maximizes the interpreter of the two numbers in the year of the date, or the two numbers entered in the SQL statement for the years type. Because values of all year types must be stored with 4 digits. MySQL attempted to convert a 2-digit year to a value of 4 digits. Converts values within the range of 00-69 to 2000-2069. Converts the value within the 70-99 range to 1970-1999. If the MySQL auto-converted value does not meet our needs, please enter a 4-digit year.

DATETIME and TIMESTAMP types

In addition to the date and time data types, MySQL also supports both DATETIME and TIMESTAMP mixed types. They can store the date and time as a single value. These two types are typically used to automatically store timestamps that contain the current date and time, and can play a role in applications that need to perform a large number of database transactions and audit trails that require a debug and review purpose.

If we have no definite assignment to a field of type TIMESTAMP, or we are assigned a null value. MySQL will automatically use the system's current date and time to populate it with the default value of Current_timestamp (System date), note that MySQL only sets the default value for the first TIMESTAMP field in the table to the system date, if there is a second TIMESTAMP type, The default setting is 0 values.

Timestamp inserts and queries are affected by the local time zone and reflect the actual date, while DATETIME only reflects the local time zone at the time of insertion, and there is bound to be an error in viewing the data for other time zones.

Composite type

MySQL also supports two composite data types, ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be treated as different data types. An ENUM type allows only one value to be obtained from a collection, whereas a set type allows any number of values to be taken from a collection.

ENUM type

The ENUM type is somewhat similar to a single option because it allows only one value to be obtained in the collection. It's easy to understand when dealing with data from each other, such as human sexuality. The ENUM Type field can take a value from the collection or use a null value, otherwise the input will cause MySQL to insert an empty string in this field. In addition, if the casing of the inserted value does not match the case of the value in the collection, MySQL automatically converts the casing of the inserted value to a value that is consistent with the case in the collection.

The ENUM type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An enum type can contain up to 65,536 elements, one of which is retained by MySQL to store the error message, which is represented by index 0 or an empty string.

MySQL considers that the value that appears in the Enum type collection is a valid input, except that any other input will fail. This means that it is easy to find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0.

You can see that the enum type ignores the case, does not return a warning when inserting a value that does not exist within the specified scope of the enum, but instead inserts the first value "M" of the Enum (' m ', ' F '), and the enum type allows only a single value to be picked from the value collection, not multiple values at a time.

SET type

The set type is similar but not the same as the ENUM type. A set type can take any number of values from a predefined collection. And the same as the enum type, any attempt to insert a non-predefined value in the Set Type field causes MySQL to insert an empty string. If you insert a record that has a valid element and an illegal element, MySQL retains the legitimate element, removing the illegal element.

A SET type can contain up to 64 elements. The value in the set element is stored as a separate "bit" sequence, which represents the element corresponding to it. A bit is a simple and efficient way to create a collection of ordered elements. And it also removes the duplicate elements, soSET类型中不可能包含两个相同的元素。

To find illegal records from the set Type field, simply look for rows that contain an empty string or binary value of 0.

A set type can be combined by selecting any or more elements from the allowed values collection, so the input value can be injected correctly into the set type as long as it is within the combined range of allowable values. Values that exceed the allowable range of values such as (' a,d,f ') will not be allowed to be injected into the set type column in the above example, whereas for (' A,d,a ') A collection containing duplicate members will be taken only once, and the result of the write is "A,d".

MySQL data type