What is the difference between Mysql tinyint(1) and tinyint(4)

What is tinyint(M)?

First let’s understand the difference between the string type varchar(M) and the numerical type tinyint(M) in mysql ?
String column type: varchar(M) For example, M is the maximum character length that can be stored in the field, That is to say, the field length. Depending on the settings, you are likely to receive an error when you insert data that exceeds the field length, and even if you do not receive an error, the data you insert will be automatically truncated to fit the predefined length of the field. Therefore, varchar(20) and varchar(40) are different, which truly reflects the length of data that can be stored in the field.
Numeric column type: Its length modifier indicates the maximum display width and has nothing to do with the physical storage of the field. In other words, the numerical range that tinyint(1) and tinyint(4) can store are both -128...127 (or for unsigned values ​​0...255). They are the same data type, of course they are still There is a slight difference, which will be explained below.
For the tinyint data type, it only occupies 1 byte:

- unsigned (unsigned), the range is 0 to 255, The default length is 3.
- Signed, range is -128 to 127, default length is 4.

Range algorithm: tinyint occupies 1 byte, a byte has 8 bits, that is, 1*8=8, and the number of numbers that can be represented is 8 times 2 Square (2^8 = 256 numbers).

Difference: If zerofill is used, when the actual length does not reach the specified display length, it will be padded with 0 in front. (The abbreviation of zerofill is to fill in zeros)

Test

First create a test table and use zerofill for the tinyint type.

CREATE TABLE `pre_demo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `unsigned_t` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
  `signed_t` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `t1` tinyint(1) unsigned zerofill NOT NULL DEFAULT '0',
  `t2` tinyint(2) unsigned zerofill NOT NULL DEFAULT '00',
  `t3` tinyint(3) unsigned zerofill NOT NULL DEFAULT '000',
  `t4` tinyint(4) unsigned zerofill NOT NULL DEFAULT '0000',
  `t5` tinyint(5) unsigned zerofill NOT NULL DEFAULT '00000',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Then, insert the test data.

NSERT INTO pre_demo VALUES(NULL,8,8,8,8,8,8,8);
INSERT INTO pre_demo VALUES(NULL,123,123,123,123,123,123,123);

Finally, query the data in the data table.

mysql> SELECT * FROM pre_demo;
+----+------------+----------+-----+-----+-----+------+-------+
| id | unsigned_t | signed_t | t1  | t2  | t3  | t4   | t5    |
+----+------------+----------+-----+-----+-----+------+-------+
|  1 |        008 |     0008 |   8 |  08 | 008 | 0008 | 00008 |
|  2 |        123 |     0123 | 123 | 123 | 123 | 0123 | 00123 |
+----+------------+----------+-----+-----+-----+------+-------+
2 rows in set (0.00 sec)

rrree

The above is the detailed content of What is the difference between Mysql tinyint(1) and tinyint(4). For more information, please follow other related articles on the PHP Chinese website!