-1

I have a column in MySQL database which is defined as

IsDeleted tinyint(1) NOT NULL DEFAULT '0'

in a table called MyTable.

According to the MySQL document here:

M indicates the maximum display width for integer types. The maximum display width is 255. Display width is unrelated to the range of values a type can contain, as described in Section 11.2, “Numeric Types”.

M in the above paragraph represents the 1 in TINYINT(1).

I think this means that when I store the value 23, which contains 2 digits, inside this column.

UPDATE MyTable SET IsDeleted=23 WHERE id = 1;

I'll see 2 or 3 when I retrieve the value.

But I'm wrong. I see SELECT IsDeleted FROM MyTable WHERE id = 1; command generate 23 as the output.

Why does this happen? What's the meaning of maximum display width?

My MySQL version is 5.7.20.

Brian
  • 12,145
  • 20
  • 90
  • 153
  • "maximum display width" --- is the column width used by mysql command line client. It is effectively useless number unless you're using it in a command line and have some specific use cases. – zerkms Mar 07 '18 at 10:05
  • @zerkms If you have a documentation link for that, it would be helpful. Or, you could post your own answer. – Tim Biegeleisen Mar 07 '18 at 10:09
  • @TimBiegeleisen it's just an observation, I believe it's up to the client on how to treat it. And I cannot find anything in the `mysql` cli client documentation :shrug: – zerkms Mar 07 '18 at 10:11
  • 2
    https://dev.mysql.com/doc/refman/5.7/en/numeric-type-attributes.html – Paul Campbell Mar 07 '18 at 10:27
  • _The `(m)` is ignored except when used with `ZEROFILL`_ – Rick James Mar 12 '18 at 02:24

3 Answers3

1

The (1) in TINYINT(1) refers to the maximum display width, which is not the same as the precision of the field.

As the documentation shows, TINYINT, along with the other integer types, is an exact type, and can store a range of values from -128 to 127 (if signed), or 0 to 255 (if unsigned).

So inserting 23 into a TINYINT column is legitimate. But as this demo shows, attempting to insert a value into a TINYINT column which is outside of the range mentioned above results in an error.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

The (m) is ignored except when used with ZEROFILL

(That's all that need be said about the (1).)

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

In this answer you can find a more exhaustive explanation of the difference between different values for the parameter of TINYINT, but the idea is that it is always one byte, therefore it can either store SIGNED values going from -128 to 127 or UNSIGNED ones from 0 to 255.

Cynical
  • 9,328
  • 1
  • 15
  • 30