Mysql Data Types

Value Type

MySQL numeric data types can be roughly divided into two categories: one is an integer, and the other is a floating point or decimal. Many different subtypes are available for each of these classes. Each subtype supports data of different sizes. MySQL allows us to specify whether the values in the value field are positive or negative or fill with zero.

The table lists various numeric types, their permitted ranges, and occupied memory space.

Type	Size	Range (Signed)	Range (unsigned)	Purpose
Tinyint	1 byte	(-128,127)	(0,255)	Small integer
Smallint	2 bytes	(-32 767)	(535)	Large integer
Mediumint	3 bytes	(-8 388 388 607)	(777 215)	Large integer
Int or integer	4 bytes	(-2 147 483 147 483, 2 647)	(294 967 295)	Large integer
Bigint	8 bytes	(-9 233 372 036 854 775 223 372 854 775 807)	(446 744 709 073 551 615)	Maximum integer
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)	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 7 e + 315)	Double Precision Floating point value
Decimal	For decimal (M, d), if M> D is m + 2, otherwise d + 2	Values dependent on m and D	Values dependent on m and D	Small value

 

Int type

The five major integer types supported in MySQL are tinyint, smallint, mediumint, Int, and bigint. These types are largely the same, and only the sizes of the stored values are different.

MySQL
The SQL standard is expanded in the form of an optional display width indicator, so that when a value is retrieved from the database, the value can be extended to the specified length. For example, specify the type of a field
INT (6), you can ensure that the number is less than 6
Will be automatically filled with spaces when retrieved from the database. It should be noted that using a width indicator does not affect the field size 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 will truncate the end that is closest to the permitted range before storing it. Another special note is that MySQL will automatically change the value to 0 before the table is inserted with an unspecified value.

The unsigned modifier specifies that the field only saves positive values. Because you do not need to save the positive and negative symbols of numbers, you can reserve a space of about a bit during the storage period. This increases the range of values that this field can store.

The zerofill modifier specifies that 0 (not a space) can be used to truly complement the output value. This modifier can be used to prevent negative values from being stored in the MySQL database.

Float, double, and decimal types

MySQL supports float, double, and decimal floating point types. Float values are used to represent single-precision floating point values, while double values are 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 float () statement specifies that the displayed value is no more than 7 digits, and the decimal point is followed by three digits.

MySQL will automatically round the number of digits after the decimal point to the value closest to it and then insert it.

Decimal
The data type is used in calculations with very high precision requirements. This type allows you to specify the precision and counting method of the value as the selection parameter. The precision here refers to the total number of valid numbers saved for this value, and the count method indicates
The number of digits after the decimal point. For example, the decimal () statement specifies that the stored value cannot exceed 7 digits, and the decimal point cannot exceed 3 digits.

Ignoring the decimal data type precision and counting method modifier will make MySQL database set the precision of all fields marked as this data type to 10, and the calculation method to 0.

The unsigned and zerofill modifiers can also be used by float, double, and decimal data types. And the effect is the same as that of the int data type.

String type

MySQL provides eight basic string types that can store data ranging from a simple character to a huge text block or binary string.

Type	Size	Purpose
Char	0-255 bytes	Fixed Length string
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 format
Text	0-65 535 bytes	Long text data
Mediumblob	0-16 777 215 bytes	Moderate-length text data in binary format
Mediumtext	0-16 777 215 bytes	Moderate-length text data
Logngblob	0-4 294 967 295 bytes	Extremely large text data in binary form
Longtext	0-4 294 967 295 bytes	Large text data

Char and varchar types

The char type is used for fixed-length strings and must be defined by a size modifier in parentheses. The size modifier ranges from 0 to 255. Values greater than the specified length are truncated, while values smaller than the specified length are filled with spaces.

The char type can use the binary modifier. When used for comparison operations, this modifier enables Char to be involved in operations in binary mode, rather than the traditional case-sensitive mode.

A variant of the char type is varchar.
Type. It is a variable-length string type and must also contain an indicator ranging from 0 to 255. The difference between char and varchgar is that musql
How the database processes this indicator: Char regards this size as the value size, and uses spaces to fill it up without being long enough. Varchar
The Type treats it as the maximum value and only uses the length required to store the string (an additional byte is added to store the length of the string itself) to store the value. Therefore, varchar shorter than the indicator Length
The type is not filled by spaces, but the value longer than the indicator is still truncated.

Because the varchar type can dynamically change the length of the storage value according to the actual content, using the varchar type can greatly save disk space and improve storage efficiency when you cannot determine the number of characters required by the field.

The varchar type is identical to the char type when the binary modifier is used.

Text and blob types

If the length of a field exceeds 255, MySQL provides two types: Text and blob. They all have different subtypes based on the size of the stored data. These large data types are used to store binary data types such as text blocks or images and audio files.

The text and blob types differ in classification and comparison. Blob type is case sensitive, while text is case insensitive. The size modifier is not used for various blob and text subtypes. Values that are larger than the maximum range supported by the specified type are automatically truncated.

 

Date and Time Type

When processing values of the date and time types, MySQL has five different data types available. They can be divided into simple date, time type, and mixed date, time type. According to the required precision, subtypes can be used in each shard type, and MySQL has a built-in function to convert diverse input formats into a standard format.

Type	Size (Bytes)	Range	Format	Purpose
Date	3	April 1000-01-01/9999-12-31	YYYY-MM-DD	Date Value
Time	3	'-838: 59: 59'/'2014: 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	2037 00:00:00/	Yyyymmdd hhmmss	Mixed Date and Time Value, timestamp

Date, time, and tear types

MySQL uses date and tear
Type stores simple date values and uses time to store time values. These types can be described as strings or integer sequences without delimiters. If the description is string, date
Type values should be separated by font size, while time values should be separated by colons.

It should be noted that the time type value without the colon separator will be understood by MySQL as the duration rather than the timestamp.

MySQL
The two numeric values in the year of the date, or the two numbers entered for the tear type in the SQL statement, are also translated to the maximum extent. Because the value of all tear types must use 4
Digital Storage. MySQL tries to convert the year of two numbers to the value of four numbers. Convert the value in the range of 00-69 to 2000-2069. Set
The value in the range of 70-99 is converted to 1970-1979. If the Automatically converted MySQL value does not meet our needs, enter four numbers to indicate the year.

Dateyime and timestamp types

In addition to the date and time data types, MySQL also supports
Dateyime and timestamp
These two types are mixed. They can store dates and times as a single value. These two types are usually used to automatically store timestamps containing the current date and time, and can execute a large number of database transactions and needs
An audit trail application for debugging and review purposes should be establishedProgram.

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

 

Composite Type

MySQL also supports two types of composite data, Enum and set, which extend the SQL specification. Although these types are technically string types, they can be considered as different data types. An Enum type can obtain only one value from a set, while a set type can obtain any number of values from a set.

 

Enum type

Enum
Type because only one value can be obtained in the Set, which is similar to a single option. It is easy to understand when processing mutually exclusive data, such as human gender. Enum
Type fields can get a value from the set or use null values. Other input causes MySQL
Insert an empty string into this field. In addition, if the case of the inserted value does not match the case of the set value, MySQL will automatically convert the case of the inserted value to a value consistent with the upper and lower case of the set.

The enum type can be stored as numbers in the system and indexed from 1. An Enum type can contain a maximum of 65536 elements, one of which is retained by MySQL and used to store error information. This error value is represented by index 0 or an empty string.

MySQL considers the value in the enum type set as a valid input, and any other input will fail. This means that the location of the error record can be easily found by searching for rows that contain null strings or corresponding numeric indexes of 0.

Set Type

Set Type and enum
Similar but different types. The set type can obtain any number of values from a predefined set. And the same as the enum type is any attempt
Inserting a non-predefined value in a type field causes MySQL to insert an empty string. If you insert a record with valid and invalid elements, MySQL
Valid elements are retained to Remove invalid elements.

A set type can contain up to 64 elements. The value in the Set element is stored as a separated "bit" sequence, which indicates the elements corresponding to it. "Bit" is a simple and effective way to create an ordered element set. Besides repeated elements, the set type cannot contain two identical elements.

To find an invalid record from the set type field, you only need to find the row that contains a null string or a binary value of 0.