Common Mysql Data Types

Table 1: Numeric column type

The names and value ranges of each numeric type are shown in table 2.

Type description	Value Range
Tinyint [(m)]	Signed value:-128 to 127 (-27 to 27-1) Unsigned value: 0 to 255 (0 to 28-1)
Smallint [(m)]	Signed value:-32768 to 32767 (-215 to 215-1) Unsigned value: 0 to 65535 (0 to 21 6-1)
Mediumint [(m)]	Signed value:-8388608 to 8388607 (-22 3 to 22 3-1) Unsigned value: 0 to 16777215 (0 to 22 4-1)
Int [(m)]	Signed value:-2147683648 to 2147683647 (-231 to 231-1) Unsigned value: 0 to 42array4array672array5 (0 to 232-1)
Bigint [(m)]	Signed value:-array223372042554775808 to array223373010954775807 (-263 to 263-1) Unsigned value: 0 to 1844673167370array551615 (0 to 264? 1)
Float [(m, d)]	Minimum non-zero value: ± 1. 1754array4351e-38
Double [(m, d)]	Minimum non-zero value: ± 2. 225074255072014e-308
Decimal (M, d)	Variable; its value range depends on m and D

Table 2: value range of the value column type

The storage required for various types of values is shown in table 3.

Type description	Storage Requirements
Tinyint [(m)]	1 byte
Smallint [(m)]	2 bytes
Mediumint [(m)]	3 bytes
Int [(m)]	4 bytes
Bigint [(m)]	8 bytes
Float [(m, d)]	4 bytes
Double [(m, d)]	8 bytes
Decimal (M, d)	M bytes (MySQL <3.23), m + 2 bytes (mysql> 3.23)

Table 3: Data column storage requirements

MySQL provides five types of integers: tinyint, smallint, mediumint, Int, and bigint. Int is the abbreviation of integer. These types are different in the value range that can be expressed. An integer column can be defined as unsigned to disable negative values. This enables the value range of the column to be greater than 0. Different types of storage requirements are also different. The storage required for a type with a large value range is large.

MySQL provides three floating point types: float, double, and decimal. Different from integer types, the floating point type cannot be unsigned, and its value range is also different from that of integer types. The difference is that these types have the maximum value and the minimum non-zero value. The minimum value provides a metric of the corresponding type of precision, which is very important for recording scientific data (of course, there are also the maximum and minimum values of negative values ).

When you select a value type, you must consider the range of the value to be represented. You only need to select the minimum type that can cover the range of the value. Selecting a large type will result in a waste of space, making the table unnecessary to increase, and it is not as effective as selecting a small type. For integer values, if the data value range is small, such as the age of a person or the number of siblings, tinyint is the most suitable. Mediumint can represent millions of values and can be used for more types of values, but the storage cost is high. Bigint has the largest value range among all integer types, and the required storage space is twice that of the int type of the integer that represents the maximum range. Therefore, it is only used when it is actually needed. For floating point values, double occupies twice the space of float. Unless high precision or a large range of values are particularly required, the data should be represented by a float model that only uses half of the storage cost.

When defining integer columns, you can specify the optional display size M. In this case, m should be an integer ranging from 1 to 255. It indicates the number of characters used to display the column's median. For example, mediumint (4) specifies a mediumint column with a display width of 4 characters. If an integer column with no specific width is defined, it is automatically assigned a default width. The default value is the length of the "longest" value for each type. If the printable expression of a specific value requires more than m characters, the full value is displayed. The value is not truncated to fit M characters.

For each floating point type, you can specify the maximum display size m and the number of decimal places D. The m value should be 1 to 255. The value of D can be 0 to 3 0, but should not be greater than m-2 (if you are familiar with ODBC terminology, m and D correspond to the "precision" and "number of decimal places" of ODBC concepts "). Both m and D are optional for float and double, but are required for decimal. If M and D are omitted, the default value is used.

2.2String column type 

MySQL provides several string types for storing character data, which are as follows:

Type name	Description
Char	Fixed Length string
Varchar	Variable Length string
Tinyblob	Very small blob (large binary object)
Blob	Small blob
Mediumblob	Moderate blob
Longblob	Large blob
Tinytext	Very small text string
Text	Small text string
Mediumtext	Medium text string
Longtext	Large text string
Enum	Enumeration. A column can be assigned to an enumeration member.
Set	Set; columns can be assigned to multiple set members.

Table 4: String column types

The following table lists the types of string value columns defined by MySQL, as well as the maximum size and storage requirements for each type. For variable-length column types, the values of each row occupy different storage volumes, depending on the length of the values actually stored in the column. The length is represented by L in the table.

Type description	Maximum Size	Storage Requirements
Char (m)	MB bytes	MB bytes
Varchar (m)	MB bytes	L + 1 byte
Tinyblob, tinytext	28-1 bytes	L + 1 byte
Blob, text	216-1 bytes	L + 2 bytes
Mediumblob, mediumtext	224-1 bytes	L + 3 bytes
Longblob, longtext	232-1 bytes	L + 4 bytes
Enum ("value1", "value2 ",...)	65535 members	1 or 2 bytes
Set ("value1", "value2 ",...)	64 members	1, 2, 3, 4, or 8 bytes

Table 5: maximum size and storage requirements of the string column type

L The additional bytes are the number of bytes required to store the value length. MySQL processes variable-length values by storing the content and length of values. These additional bytes are unsigned integers. Note that the maximum length of a variable length type, the number of additional bytes required for this type, and the relationship between unsigned integers occupying the same number of bytes. For example, the mediumblob value may be 224-1 bytes long and three bytes are required to record its results. The maximum unsigned value of the Three-byte integer mediumint is 224-1. This is not accidental.

2.3Date and Time column type 

MySQL provides several time value column types: date, datetime, time, timestamp, and year. The following table lists the types that MySQL provides to define the storage date and time values, and provides valid value ranges for each type.

Type name	Description
Date	Date value in YYYY-MM-DD format
Time	Time Value in "HH: mm: SS" Format
Datetime	YYYY-MM-DD hh: mm: SS format
Timestamp	Timestamp value in the format of "yyyymmddhhmmss"
Year	Year Value in "YYYY" Format

Table 6: Date and Time column types

Type name	Value Range	Storage Requirements
Date	From 1000-01-01 to ArrayArrayArrayArray-12-31"	3 bytes
Time	"-838: 5 array: 5array" to "838: 5 array: 5array"	3 bytes
Datetime	"1000-01-01 00:00:00" to "ArrayArrayArrayArray-12-31 23: 5 array: 5array"	8 bytes
Timestamp	1array700101000000 to a certain time point in 2037	4 bytes
Year	1array01 to 2155	1 byte

Table 7: date column type value range and storage requirements

The following is an example:

Create Table student ( Name varchar (20) not null, Chinese tinyint (3 ), Maths tinyint (3 ), English tinyint (3 ), Birthday date )

In this example, create a student table with the name field and character type column. null is not allowed ). There are three integer columns: Chinese, maths, and English. There is also a birthday date type column.