MySQL Data Type detailed _mysql

1. Integral type

MySQL data type	Meaning (Signed)
tinyint (m)	1 byte range ( -128~127)
smallint (m)	2 byte range ( -32768~32767)
Mediumint (M)	3 byte range ( -8388608~8388607)
Int (m)	4 byte range ( -2147483648~2147483647)
BigInt (M)	8 byte Range (18 +-9.22*10 of the second party)

The value range, if added unsigned, doubles the maximum value, such as tinyint unsigned (0~256).
The M in Int (m) represents the width of the display in the Select query result set, does not affect the actual range of values, does not affect the width of the display, and does not know what is the use of this m.

2. Floating-point type (float and double)

MySQL data type	Meaning
Float (m,d)	Single-precision floating-point 8-bit precision (4 bytes) m total number, D decimal places
Double (m,d)	Double-precision floating-point 16-bit precision (8 bytes) m total number, D decimal places

Set a field defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is also the actual, that is 6 bits.

3, fixed-point number

A floating-point type holds an approximate value in the database, while a fixed-point type holds an exact value in the database.
The decimal (m,d) parameter m<65 is the total number, d<30 and d<m are decimal places.

4, String (Char,varchar,_text)

MySQL data type	Meaning
CHAR (n)	Fixed length, up to 255 characters
VARCHAR (n)	Fixed length, up to 65,535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65,535 characters
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

Char and varchar:
1.char (n) If the number of characters is less than N, then fill in the space after the query, then remove the space. Therefore, no spaces are allowed at the end of the string stored by the char type, and varchar is not limited to this.
2.char (n) fixed length, char (4) Whether it is stored in several characters, will occupy 4 bytes, varchar is the actual number of characters stored + 1 bytes (n<=255) or 2 bytes (n>255), so varchar (4), Storing 3 characters will take up 4 bytes.
String retrieval of type 3.char is faster than varchar type.

varchar and text:
1.varchar can specify that N,text cannot be specified, the internal storage varchar is the actual number of characters to be stored + 1 bytes (n<=255) or 2 bytes (n>255), and text is the actual number of characters + 2 bytes.
The 2.text type cannot have a default value.
3.varchar You can create an index directly, text to create an index to specify how many characters before. varchar query faster than text, the index of text does not seem to work when all indexes are created.

5. Binary data (_BLOB)

1._blob and _text are stored in different ways, _text are stored as text, the English store is case-sensitive, and _blob is stored in binary mode, regardless of case.
The data stored in the 2._BLOB can only be read as a whole.
3._text can specify the character set, _blo not specify the character set.

6. Date Time Type

MySQL data type	Meaning
Date	Date ' 2008-12-2 '
Time	Time ' 12:25:36 '
Datetime	Date Time ' 2008-12-2 22:06:44 '
Timestamp	Automatic Store record modification time

If you define a field of timestamp, the time data in the fields is automatically refreshed with other fields, so this data type of field can hold the last time the record was modified.

Properties of data types

mysql keyword	Meaning
Null	Data columns can contain null values
Not NULL	Data columns are not allowed to contain null values
DEFAULT	Default value
PRIMARY KEY	Primary key
Auto_increment	Auto increment, applicable to integer type
UNSIGNED	No sign
CHARACTER SET Name	Specify a character Set

The table lists the various numeric types and their allowable range and memory footprint.

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)	Single precision Floating-point values
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+3 08)	0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308)	Double precision Floating-point values
DECIMAL	to decimal (m,d), if m>d, for m+2 otherwise for d+2	Values that depend on M and D	Values that depend on M and D	Decimal value

INT type

The 5 main integer types supported in MySQL are Tinyint,smallint,mediumint,int and BIGINT. These types are to a large extent the same, and only the values they store are not the same size.

MySQL extends the SQL standard in the form of an optional display width indicator, which can be lengthened to a specified length when retrieving a value from the database. For example, specifying a field with a type of INT (6) ensures that values that contain fewer than 6 digits are automatically padded with spaces when retrieved from the database. It should be noted that using 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 is out of the allowable range for a field, MySQL stores it as soon as the allowable range is closest to its end. Another special place is that MySQL will automatically be modified to 0 before the specified value is inserted into the table.

The UNSIGNED modifier stipulates that the field holds only positive values. Because you do not need to save the positive and negative symbols of a number, you can save a "bit" space at the time of storage. This increases the range of values that the field can store.

The Zerofill modifier stipulates that 0 (not spaces) can be used to really complement the output value. Use this modifier to prevent the MySQL database from storing negative values.

FLOAT, DOUBLE, and DECIMAL types

Three floating-point types supported by MySQL are float, DOUBLE, and DECIMAL types. The float value type is used to represent single-precision floating-point values, and the double numeric type is used to represent double-precision floating-point values.

Like integers, these types also have additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT (7,3) stipulates that the value displayed does not exceed 7 digits, followed by a 3-digit number.

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

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

Ignoring the precision and count method modifiers for the DECIMAL data type will cause the MySQL database to set the field precision of all identifiers to this data type to 10, and the calculation method to 0.

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

String type

MySQL provides 8 basic string types that can be stored in ranges from simple one character to huge text block or binary string data.

Type	Size	Use
CHAR	0-255 bytes	Fixed-length strings
VARCHAR	0-255 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
Logngblob	0-4 294 967 295 bytes	Maximum 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 in parentheses with a size modifier. The range of this size modifier is from 0-255. A value that is larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space.

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

A variant of the CHAR type is the 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 Musql database handles this indicator: Char treats this size as a value, and is filled with a space if it is not of sufficient length. The VARCHAR type treats it as the maximum value and stores the value using only the length that is actually required by the storage string (an extra byte is added to store the length of the string itself). Therefore, the VARCHAR type shorter than the indicator length will not be filled with spaces, but the value longer than the indicator will still be truncated.

Because the VARCHAR type can dynamically change the length of the stored value depending on the actual content, using the VARCHAR type when unsure of how many characters the field requires can greatly conserve disk space and increase storage efficiency.

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

TEXT and BLOB types

MySQL provides TEXT and BLOB two types in cases where the field length requires more than 255. 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 blocks of text or images, sound files, and so on.

TEXT and BLOB types differ in classification and comparison. BLOB types are case-sensitive, and TEXT is case-insensitive. The size modifier is not used for various blobs and TEXT subtypes. The maximum range of values supported by the specified type is truncated automatically.

Date and Time type

When working with values for date and time types, MySQL has 5 different data types to choose from. They can be divided into simple dates, time types, and mixed date and time types. Depending on the required precision, subtypes can be used in each category, and MySQL has built-in functionality to transform the diverse input formats into a standard format.

Type	Size bytes	Range	Format	Use
DATE	3	1000-01-01/9999-12-31	Yyyy-mm-dd	Date value
Time	3	' -838:59:59 '/' 838:59:59 '	HH:MM:SS	Time Value or duration
Year	1	1901/2155	YYYY	Year value
Datetime	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	Mixed date and time values
TIMESTAMP	8	1970-01-01 00:00:00/2037 A certain time of year	YYYYMMDD HHMMSS	Mixed date and time value, timestamp

DATE, time, and TEAR types

MySQL stores a simple date value with the date and TEAR types, and uses the time type to store the value of the Times. These types can be described as a string or a sequence of integers with no delimiters. If described as 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 separator.

It should be noted that the time type value without the colon delimiter will be interpreted by MySQL as a duration, not a timestamp.

MySQL also simultaneous the maximum number of two digits in the year of the date, or the two digits entered in the SQL statement for the TEAR type. Because values of all TEAR types must be stored in 4 digits. MySQL attempts to convert a 2-digit year to a 4-digit value. Converts a value in the range of 00-69 to a range of 2000-2069. Converts the value in the 70-99 range to within 1970-1979. If the MySQL conversion value does not meet our needs, please enter a 4-digit year.

Dateyime and TIMESTAMP types

In addition to date and time data types, MySQL also supports both the Dateyime and TIMESTAMP hybrid 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 good role in applications that need to perform a large number of database transactions and need to establish an audit trail for debugging and review purposes.

If we do not explicitly assign values to fields of type TIMESTAMP, or are assigned null values. MySQL will automatically populate it with the current date and time of the system.

Composite type

MySQL also supports two types of 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 obtained 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 is easy to understand when dealing with data that is torn apart, such as the sex of human beings. The ENUM Type field can get a value from the collection or use a null value, except that the input will cause MySQL to insert an empty string in this field. In addition, if the capitalization of the inserted value does not match the capitalization of the value in the collection, MySQL automatically converts the capitalization of the inserted value to a value that is case-consistent in the collection.

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

MySQL believes that the value that appears in the collection of ENUM types is a valid input, except that any other input will fail. This means that you can easily find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0.

SET type

The SET type is similar to, but not identical to, the ENUM type. A set type can obtain any number of values from a predefined collection. And as with the ENUM type, any attempt to insert a value that is not predefined in the SET type field will cause MySQL to insert an empty string. If you insert a record that has a legitimate element and an illegal element, MySQL retains the legitimate elements and removes the illegal elements.

A SET type can contain up to 64 elements. The values in the SET element are stored as a separate "bit" sequence, which represents the element corresponding to it. Bit is a simple and efficient way to create an ordered set of elements. And it also removes duplicate elements, so it is impossible to contain two identical elements in a SET type.

You want to find illegal records from a SET type field just look for rows that contain an empty string or a binary value of 0.