MySQL data precision confusion

Question

I have two tables with same column.

First table:

DESC orders;
Field           Type       Null Key      Default    Extra
order_id        int(8)     unsigned      zerofill   NO  PRI (null)auto_increment
customer_id     int(10)    unsigned      YES        MUL (null)  

Second table:

DESC items;
Field            Type      Null Key      Default    Extra
item_id          int(11)   unsigned      NO PRI (null)  auto_increment
order_id         int(10)   unsigned      NO PRI (null)  

Based on tables above order_id have different data precision.

But upon checking on information_schema database the precision is identical

SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.NUMERIC_PRECISION, t.ENGINE from information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t on t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_SCHEMA = 'database' AND c.COLUMN_NAME = 'order_id' and c.TABLE_NAME in ('orders','items');

The result was.

TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME DATA_TYPE   NUMERIC_PRECISION   ENGINE
database         items      order_id    int         10                  MyISAM
database         orders     order_id    int         10                  MyISAM

Which of them is accurate? The one displayed in DESC table of the result from information schema? Thank you.

Answer 1

NUMBER_PRECISION is useless except for one case: ZEROFILL. Otherwise, it is an indication of how many columns of output are needed to display the largest number.

INT UNSIGNED needs 10 digits. INT SIGNED needs 11 columns -- a sign (if negative) and 10 digits.

Ignore the column there. Ignore it when you see int(11).

And don't bother declaring a column to be INT(100) or INT(2); it is still a 32-bit (4-byte) signed binary number.