MySQL data type

MySQL data type

In MySQL, there are three main types: text, numbers, and date/time types.

Text Type:

Data Type	Description
CHAR (size)	Holds a fixed-length string (which can contain letters, numbers, and special characters). Specifies the length of the string in parentheses. A maximum of 255 characters.
VARCHAR (size)	Saves variable-length strings (which can contain letters, numbers, and special characters). Specifies the maximum length of the string in parentheses. A maximum of 255 characters. Note: If the value is longer than 255, it is converted to the TEXT type.
Tinytext	Holds a string with a maximum length of 255 characters.
TEXT	Holds a string with a maximum length of 65,535 characters.
Blob	For BLOBs (Binary Large OBjects). Store up to 65,535 bytes of data.
Mediumtext	Holds a string with a maximum length of 16,777,215 characters.
Mediumblob	For BLOBs (Binary Large OBjects). Store up to 16,777,215 bytes of data.
Longtext	Holds a string with a maximum length of 4,294,967,295 characters.
Longblob	For BLOBs (Binary Large OBjects). Store up to 4,294,967,295 bytes of data.
ENUM (x,y,z,etc.)	Allows you to enter a list of possible values. The maximum of 65,535 values can be listed in the ENUM list. If the inserted value does not exist in the list, a null value is inserted. Note: These values are stored in the order that you entered them. Possible values can be entered in this format: ENUM (' X ', ' Y ', ' Z ')
SET	Similar to ENUM, set can contain up to 64 list items, but set can store more than one value.

Number type: -128 to 127 conventional. 0 to 255 unsigned *. Specify the maximum number of digits in parentheses.

data type	description
TINYINT (size)
SMALLINT (size)	-32768 to 32767 general. 0 to 65535 unsigned *. Specify the maximum number of digits in parentheses.
mediumint (size)	-8388608 to 8388607 normal. 0 to 16777215 unsigned *. Specify the maximum number of digits in parentheses.
INT (size)	-2147483648 to 2147483647 general. 0 to 4294967295 unsigned *. Specify the maximum number of digits in parentheses.
BIGINT (size)	-9223372036854775808 to 9223372036854775807 general. 0 to 18446744073709551615 unsigned *. Specify the maximum number of digits in parentheses.
float (size,d)	A small number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter.
double (size,d)	A large number with a floating decimal point. Specify the maximum number of digits in parentheses. Specifies the maximum number of digits to the right of the decimal point in the D parameter.
Decimal (size,d)	is a DOUBLE type stored as a string, allowing a fixed decimal point.

* These integer types have an additional option of UNSIGNED. Generally, integers can be negative or positive. If you add the UNSIGNED property, the range will start at 0 instead of a negative number.

Date Type:

Data Type	Description
DATE ()	Date. Format: YYYY-MM-DD Note: The scope of support is from ' 1000-01-01 ' to ' 9999-12-31 '
DATETIME ()	* Combination of date and time. Format: Yyyy-mm-dd HH:MM:SS Note: The range of support is from ' 1000-01-01-00:00:00 ' to ' 9999-12-31 23:59:59 '
TIMESTAMP ()	* Time stamp. The TIMESTAMP value is stored using the description of the Unix era (' 1970-01-01 00:00:00 ' UTC) to date. Format: Yyyy-mm-dd HH:MM:SS Note: The supported range is from ' 1970-01-01 00:00:01 ' UTC to ' 2038-01-09 03:14:07 ' UTC
Time ()	Time. Format: HH:MM:SS Comment: The supported range is from ' -838:59:59 ' to ' 838:59:59 '
Year ()	Year of 2-bit or 4-bit format. Note: The 4-bit format allows values from 1901 to 2155. The 2-bit format allows values from 70 to 69, representing 1970 to 2069.

* Even if DATETIME and TIMESTAMP return the same format, they work in a different way. In an INSERT or UPDATE query, TIMESTAMP automatically sets itself to the current date and time. TIMESTAMP also accepts different formats, such as YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD.

MySQL data type