I was always wondering what the size of numeric columns in MySQL was. Forgive me if this is obvious to someone else. But for me the MySQL manual lacks a great deal in this field.

TL;DR: It's about the display width. You only see it when you use ZEROFILL.

Usually you see something like int(11) in CREATE TABLE statements, but you can also change it to int(4).

So what does this size mean? Can you store higher values in a int(11) than in an int(4)?

Let's see what the MySQL manual says:

INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

No word about the M. The entry about BOOL suggests that the size is not there for fun as it is a synonym for TINYINT(1) (with the specific size of 1).

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true: […]

So TINYINT(1) must be different in some way from TINYINT(4) which is assumed by default when you leave the size out1. Still, you can store for example 100 into a TINYINT(1).

Finally, let's come to the place of the manual where there is the biggest hint to what the number means:

Several of the data type descriptions use these conventions:

M indicates the maximum display width for integer types. For
floating-point and fixed-point types, M is the total number of digits
that can be stored. For string types, M is the maximum length. The
maximum allowable value of M depends on the data type.

It's about the display width. The weird thing is, though2,
that, for example, if you have a value of 5 digits in a field with a
display width of 4 digits, the display width will not cut a digits off.

If the value has less digits than the display width, nothing happens
either. So it seems like the display doesn't have any effect in real
life.

Now2 ZEROFILL comes into play. It is a neat feature that pads values that are (here it comes) less than the specified display width with zeros, so that you will always receive a value of the specified length. This is for example useful for invoice ids.

So, concluding: The size is neither bits nor bytes. It's just the display width, that is used when the field has ZEROFILL specified.

If you see any more uses in the size value, please tell me. I am curious to know.

1 See this example:
mysql> create table a ( a tinyint );
Query OK, 0 rows affected (0.29 sec)
mysql> show columns from a;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.26 sec)

mysql> alter table a change a a tinyint(1);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into a values (100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+-----+
| a |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)

2 Some code to better explain what I described so clumsily.
mysql> create table b ( b int (4));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into b values (10000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from b;
+-------+
| b |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(11);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from b;
+-------+
| b |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(11) zerofill;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from b;
+-------------+
| b |
+-------------+
| 00000010000 |
+-------------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(4) zerofill;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from b;
+-------+
| b |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(6) zerofill;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from b;
+--------+
| b |
+--------+
| 010000 |
+--------+
1 row in set (0.00 sec)

mysql integer size More articles about the size of

  1. mysql Single table size limit

    mysql The size limit of a single table .MySQL Database MyISAM Storage The engine single table size limit is no longer MySQL The database itself decides ( The limit extends to 64pb), It's from the host OS The file system above determines . stay mysql5. ...

  2. [ turn ] MySQL Summary of query table data size

    One : About mysql Table data size We know mysql Data files are usually stored in table space When mysql Use innodb When storing the engine ,mysql There are two ways to use tables to store data: shared table space and exclusive table space · Shared tablespace :I ...

  3. mysql Check the table size

    mysql Check the table size One : command  show table status like 'table_name'\G; mysql> show table status like 'x'\G; . row ...

  4. Time acquisition MySQL The size of the library

    Time acquisition MySQL The size of the library Get the size of a single database command [root@admin ~]# cat db_size.txt mysql -h 192.8.1.1 -uUSER -pPASSWORD -e' ...

  5. Two Integer Comparison of the size of the errors need to pay attention to

    Use the following example , To get to know integer Compare the size of the need to pay attention to a few . eg. Definition Integer object a and b, Comparing the two results is :a It's not equal to b Integer a = 1; Integer b = 1; if(a==b) ...

  6. block size size

    1. use tune2fs see block size size : 1 2 tune2fs -l /dev/sda1 |grep "Block size" Block size: 1024 2. use ...

  7. Integer Object size comparison problem

    One . problem Let's start with an example public class IntegerTest { public static void main(String[] args) throws Exception { In ...

  8. mysql Set up max_allowed_packet The size of the way

        show VARIABLES like '%max_allowed_packet%'; The first sentence is query  max_allowed_packet   Size , The second sentence is to reset  max_allowe ...

  9. mysql Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

    Use mysql When , be used int There are a lot of types , You need to pay attention to : 1. The range of values Type Storage Minimum Value Maximum Value   (Bytes) (Signed/Uns ...

Random recommendation

  1. Chapter 2: A random variable

    1. A random variable , Discrete random variables , Continuous random variable set up $\Omega$ Sample space for randomized trials ,$X:\Omega \rightarrow R$ Is defined in the sample space $\Omega$ Real valued functions on , said $X$ For random ...

  2. Ubuntu build svn The server

    One , Install the necessary packages . sudo apt-getinstall subversion Two , Basic SVN Server configuration         1, Create a new directory to store SVN All the files                 # ...

  3. 【QT】C++ GUI Qt4 Learning notes 1

    Find Dialog implementation platform Qt5.3.2 MinGW4.8.2 Pay attention to create with QDialog finddialog.h #ifndef FINDDIALOG_H #define FINDDIALOG_H ...

  4. python time

    Python-time Calculate program run time import time start = time.clock() time.sleep(5) end = time.clock() runtime = end ...

  5. IOC and AOP The fundamentals of

    IoC(Inversion of Control) It's the container that controls the relationship between programs , Not in traditional implementation , Directly controlled by program code . This is what we call “ Inversion of control ” The concept of . Control is transferred from the application code to the external container , The transfer of control is ...

  6. fancybox Invalid invalid Open the page directly , ajax after fancybox Invalid for updated data ,Jquery invalid Invalid

    Case study : Make a chat project , The data is all through ajax It's refreshing , And for new data binding fancybox No effect , Click to open a new page instead of pop-up , The solution is very simple   Simple analysis :ajax Loading content is in $(documen ...

  7. Don't worry about ie Next console.log Wrong report ...

    Used to be in ff perhaps chrome Will you forget to comment it out while debugging it violently ie What about the next mistake , Then you can add this code : if (typeof console == "undefined") { this ...

  8. [POJ] 1511 Invitation Cards

    Invitation Cards Time Limit: 8000MS   Memory Limit: 262144K Total Submissions: 18198   Accepted: 596 ...

  9. Tomcat Startup Report ClassNotFoundException error , solve

    Today, I put a Maven Managed web project Update after , start-up Tomcat(Eclipse in ) System error reporting . Error message  java.lang.ClassNotFoundException: , Display is spring Of C ...

  10. SQL The use view of learning

    1. brief introduction : View is a virtual table . Unlike the data contained , Views only contain queries that retrieve data dynamically when in use . a key : A view is a query , It's not a watch !