Detailed explanation of data types corresponding to fields in mysql

MySQL supports a large number of column types, which can be divided into: numeric types, date and time types, and string (character) types.

1. Number type

  • The default is signed. If you need to use unsigned data types, add "UNSIGNED" at the end; signed means that the number can be positive, zero, or negative; unsigned means that the number can only be non-negative .

Give an example to illustrate the difference between int(1) and int(11): mysql5.0 and above

int(1) The "1" here does not represent the storage length of the number , which means that there is no difference between int(1) and int(11) without filling zeros; as long as it does not exceed its value range [-2147483648 to + 2147483647] OK.

There is a difference between int(1) and int(11) when "fill with zeros" is checked. If it is int(11), you store the number 1, and the actual 0000000001 in the database, that is, the non-zero number will automatically add 0 before it. .

2. Date and time type

Date: Date, the format is YYYY-MM-DD, and the range is from 1000 to 9999.

Datetime: Time and date, the format is YYYY-MM-DD HH:II:SS, and the range is from 1000 to 9999.

Timestamp: The format is YYYY-MM-DD HH:II:SS, and the range is from 1970-01-01 00:00:00 to 2038-01-19 03:14:07.

Time: Time period, the format is HH:II:SS, the specified time is between a certain interval, with positive and negative. The range expressed is -838:59:59~838:59:59, which represents the time range before and after a certain date.

Year: The year, which means the range of 1901-2156.

3. String type

  • Store text, when the character length is greater than 255, it is recommended to use text;
  • The difference between varchar and char :

1) char means fixed length, fixed length, varchar means variable length, that is, variable length

2) For char, the maximum number of characters that can be stored is 255, regardless of encoding. And varchar can store up to 65532 characters. The maximum effective length of VARCHAR is determined by the maximum row size and the character set used. The overall maximum length is 65,532 bytes;

3) If the character type is gbk, each character occupies a maximum of 2 bytes (a Chinese occupies two bytes), and the maximum length cannot exceed 32766;

If the character type is utf8, each character occupies a maximum of 3 bytes (a Chinese occupies 3 bytes), and the maximum length cannot exceed 21845

4) The "5" in varchar(5) only represents the character storage length.