What is the value range of TINYINT in mysql

Project scenario:

Recently during the development process, I saw that there was a thinint field in the table design, but I became curious about its range

Questions Description:

When we fill in a value exceeding 128, this field will report the following error

##Cause: com.mysql.jdbc. MysqlDataTruncation:Data truncation:Out of range value for column 'priority' at row 1;

Cause analysis:

From -2^7 (-128) to 2^7 - 1 (127) integer data. Storage size is 1 byte (not set to UNSIGNED unsigned type).

So when building a table, the real effect is actually only tinyint(3). Even if you build tinyint(100), it will still have 3 digits at most.

Here I’ll give you some popular science on why tinyint has such limitations

The value range of the Tinyint data type in MySQL is signed -128 to 127. 0 to 255 It is the value range within the unsigned range. You can check the official "MySQL 5.1 Reference Manual"

Tinyint occupies 1 byte of storage space, that is, 8 bits (bit). So where does the value range of Tinyint come from? Let’s look at the unsigned case first. The unsigned minimum value is that all 8 bits are 0, which is 0 when converted to decimal. Therefore, the minimum unsigned Tinyint value is 0. The unsigned maximum value is that all 8 bits are 1, 11111111, which is converted to decimal. It's 255. This is easy to understand.
How does the value range of signed Tinyint come from? In computers, the highest bit is used to represent symbols. 0 represents positive, 1 represents negative, and the rest represent numerical values. Then the signed 8bit
minimum value:
　1　1　1　1　1　1　1　1 = -127 (representing a negative value)
Maximum value:
　0　1　1　1　1　1　1　1 = 127 ( Indicates a positive value)

#Why is the minimum signed value -127, not -128? This is the key point of this article. In computers, negative values ​​are expressed using the complement code Why is the minimum value of signed TINYINT -128? Although "-0" is also "0", according to the positive, negative, and complement system, the complement of "-0" is different from "0", so there is a situation where two complements represent a numerical value. In order to correspond one's complement to numbers one by one, it is artificially stipulated that "0" should always be represented by "0". At the same time, in order to make full use of resources, the complement code that should originally represent "-0" is specified as representing -128.

Additional knowledge point expansionA brief introduction to the main differences between int, bigint, smallint and tinyint in mysql

Recently When using the MySQL database, I encounter a variety of number types, mainly int, bigint, smallint and tinyint. What is more confusing is the difference between int and smallint. I will write it down in my blog:
Use the precise numeric data type of integer data.
bigintInteger data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807). Storage size is 8 bytes.

##int

Integer data from -2^31 (-2,147,483,648) to 2^31 – 1 (2,147,483,647) (all number). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) to 2^15 – 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 to 255. Storage size is 1 byte.
Supplement:
The bigint data type is supported where integer values ​​are supported. However, bigint is used in some special cases, when the integer value exceeds the range supported by the int data type, bigint can be used. In SQL Server, the int data type is the primary integer data type. In the data type precedence table, bigint is between smallmoney and int.
The function returns bigint only when the parameter expression is of bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.
int(M) In the integer data type, M represents the maximum display width. In int(M), the value of M has nothing to do with how much storage space int(M) occupies. It has nothing to do with the number of digits. int(3), int(4), and int(8) all occupy 4 btyes of storage space on the disk.

Solution:

There are three main solutions

1. Modify the data table fields (change to int or other types)

2. Calibrate the front-end input box If the value exceeds the value, a pop-up prompt will be displayed (unfriendly)
3. The backend value is verified, and if it does not match, an exception prompt will be thrown (the third method I used)

Assert.isTrue(!(rule.getPriority()!= null && rule.getPriority()>100),"支持最大优先级数为100!");

The above is the detailed content of What is the value range of TINYINT in mysql. For more information, please follow other related articles on the PHP Chinese website!