What are the int types in MySQL?

Selection of int type

Integer field types include tinyint, smallint, mediumint, int, bigint Five types, the occupied space size and storage range are as shown in the figure below:

The smaller the storage bytes, the smaller the occupied space. Therefore, based on the principle of minimizing storage, we should try our best to choose the appropriate integer type. Here are several common cases and selection suggestions.

• Choose the appropriate type according to the storage range. For example, unsigned tinyint is used for human age (range 0~255, human life span will not exceed 255 years); turtles must be smallint, but If it is the age of the sun, it must be an int.

• If the stored data is a non-negative value, it is recommended to use the UNSIGNED flag to expand the storage range of positive numbers.

• Use TINYINT or SMALLINT for short data, such as: human age, city code.

• Use TINYINT for fields that store status variables, such as: whether to delete, 0 means not deleted, 1 means deleted.

• Primary key column, no negative numbers, it is recommended to use INT UNSIGNED or BIGINT UNSIGNED; it is estimated that the field number value will exceed 4.2 billion, use the BIGINT type.

The following is an example of a table creation statement:

CREATE TABLE  `tb_int` (
  `increment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `stu_age` tinyint unsigned NOT NULL COMMENT '学生年龄',
  `is_deleted` tinyint unsigned DEFAULT '0' COMMENT '0:未删除 1:删除',
  `col1` bigint NOT NULL COMMENT 'bigint字段',
  PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='int测试表';

The above is the detailed content of What are the int types in MySQL?. For more information, please follow other related articles on the PHP Chinese website!