Data Types of MySQL Learning

Data Types of MySQL Learning

I. Data Types

1. value types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, and DECIMAL
2. Time type -- DATE, DATETIME, TIMESTAMP
3. string type-CHAR, VARCHAR, TEXT
4. Special types-ENUM and SET
String case sensitive -- VARBINARY

Data Type	Type	Expression range	Storage Requirements
TINYINT [(M)] [UNSIGNED] [ZEROFILL]	Integer	-128 to 127 or 0 to 255	1 byte
SMALLINT [(M)] [UNSIGNED] [ZEROFILL]		-32768 to 32767 or 0 to 65535	2 bytes
MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]		-8388608 to 8388607 or 0 to 16777215	3 bytes
INT [(M)] [UNSIGNED] [ZEROFILL]		-2147483648 to 2147483647 or 0 to 4294967295	4 bytes
BIGINT [(M)] [UNSIGNED] [ZEROFILL]		-9223372036854775808 to 9223372036854775807 or 0 to 18446744073709551615	8 bytes
DECIMAL [(M [, D])] [UNSIGNED] [ZEROFILL]	Numerical hybrid type	The maximum integer (M) is 65, and the maximum number of decimal places is 30.	Variable Length
DATE	Date type	YYYY-MM-DD accurate to days	3 bytes
DATETIME		YYYY-MM-DD HH: MM: SS (range from 1001 to 9999)	8 bytes
TIMESTAMP		YYYY-MM-DD HH: MM: SS (range from 1970 to 2037)	4 bytes
ENUM	Enumeration type	1 To 65535 pieces of metadata	1 or 2 bytes
SET	Set Type	1 to 64 pieces of metadata	1, 2, 3, 4, or 8 bytes
CHAR (M)	String type	0 (we recommend that you replace CHAR (1) with VARCHAR (M) if the length is exceeded)	M * N Bytes, where N is encoded by the character set and whether it is a Chinese character or a letter or number.
VARCHAR (M)		0 characters
TEXT		64 K bytes

All variable-length strings use the VARCHAR type, similar to the State and finite-type fields. They also use strings that can clearly represent the actual meaning, instead of digits such as INT.

A fixed-length string uses the CHAR type. All single characters use the CHAR type instead of the VARCHAR type.

When the number of characters may exceed 20000, you can use the TEXT type to store character-class data. All TEXT fields must be split from the original table and separate with the original table primary key to form another table for storage;

DATETIME or TIMESTAMP can be used for fields that need to be accurate to the time (year, month, day, hour, minute, and second). However, pay attention to their respective formats and whether the TIMESTAMP feature is required, use the TIMESTAMP type instead of DATETIME to reduce the occupation of data storage space.

You only need to use the DATE type for all fields accurate to the day, instead of using the TIMESTAMP or DATETIME auto-incrementing sequence. Only INT or BIGINT fields can be used, it must be clearly identified as an UNSIGNED type (Keyword: UNSIGNED). Unless a negative number exists, the BIGINT type is used only when the value of this field exceeds 4.2 billion, and the auto-increment field must be part of the primary key or primary key.

This article permanently updates the link address: