What is the size of column of int(11) in mysql in bytes?

What is the size of column of int(11) in mysql in bytes?

And Maximum value that can be stored in this columns?

Comments 10

  • An INT will always be 4 bytes no matter what length is specified.

    • TINYINT = 1 byte (8 bit)
    • SMALLINT = 2 bytes (16 bit)
    • MEDIUMINT = 3 bytes (24 bit)
    • INT = 4 bytes (32 bit)
    • BIGINT = 8 bytes (64 bit).

    The length just specifies how many characters to display when selecting data with the mysql command line client.

    … and the maximum value will be 2147483647 (Signed) or 4294967295 (Unsigned)

  • According to here, int(11) will take 4 bytes of space that is 32 bits of space with 2^(31) = 2147483648 max value and -2147483648min value. One bit is for sign.

  • INT(somenumber) will make difference only in term of display, that is to show in the number in ‘somenumber‘ digits, and not restricted only to 11. You pair it using ZEROFILL, which will prepend the zeros until it matches your length

    Note that the value stored in database is not affected, any calculation will still behave as it is.

    Remarks :

    • if the value has less digit than ‘somenumber‘, ZEROFILL will prepend zeros.

      INT(5) ZEROFILL with the stored value of 32 will show 00032
      INT(5) with the stored value of 32 will show 32
      INT with the stored value of 32 will show 32

    • if the value has more digit than ‘somenumber‘, the stored value will be shown.

      INT(3) ZEROFILL with the stored value of 250000 will show 250000
      INT(3) with the stored value of 250000 will show 250000
      INT with the stored value of 250000 will show 250000

    The similar applies to BIGINT, MEDIUMINT, SMALLINT, and TINYINT as well.

  • What is the size of column of int(11) in mysql in bytes?

    (11) – this attribute of int data type has nothing to do with size of column. It is just display width of integer data type. From 11.1.4.5. Numeric Type Attributes:

    MySQL supports an extension for optionally specifying the display
    width of integer data types in parentheses following the base keyword
    for the type. For example, INT(4) specifies an INT with a display
    width of four digits.

  • As others have said, the minumum/maximum values the column can store and how much storage it takes in bytes is only defined by the type, not the length.

    A lot of these answers are saying that the (11) part only affects the display width which isn’t exactly true, but mostly.

    A definition of int(2) with no zerofill specified will:

    • still accept a value of 100
    • still display a value of 100 when output (not 0 or 00)
    • the display width will be the width of the largest value being output from the select query.

    The only thing the (2) will do is if zerofill is also specified:

    • a value of 1 will be shown 01.
    • When displaying values, the column will always have a width of the maximum possible value the column could take which is 10 digits for an integer, instead of the miniumum width required to display the largest value that column needs to show for in that specific select query, which could be much smaller.
    • The column can still take, and show a value exceeding the length, but these values will not be prefixed with 0s.

    The best way to see all the nuances is to run:

    CREATE TABLE `mytable` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `int1` int(10) NOT NULL,
        `int2` int(3) NOT NULL,
        `zerofill1` int(10) ZEROFILL NOT NULL,
        `zerofill2` int(3) ZEROFILL NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `mytable` 
    (`int1`, `int2`, `zerofill1`, `zerofill2`) 
    VALUES
    (10000, 10000, 10000, 10000),
    (100, 100, 100, 100);
    
    select * from mytable;
    

    which will output:

    +----+-------+-------+------------+-----------+
    | id | int1  | int2  | zerofill1  | zerofill2 |
    +----+-------+-------+------------+-----------+
    |  1 | 10000 | 10000 | 0000010000 |     10000 |
    |  2 |   100 |   100 | 0000000100 |       100 |
    +----+-------+-------+------------+-----------+
    

    Notice how int1 column has a much smaller display width than zerofill2 even though the length is larger.

    This answer is tested against MySQL 5.7.12 for Linux and may or may not vary for other implementations.

  • A good explanation for this can be found here

    To summarize : The number in the bracket in int(N) is often confused by the maximum size allowed for the column, as it does in the case of varchar(N).

    But this is not the case with Integer data types- the number N in the bracket is not the maximum size for the column, but simply a parameter to tell MySQL what width to display the column at when the table’s data is being viewed via the MySQL console (when you’re using the ZEROFILL attribute).

    The number in brackets will tell MySQL how many zeros to pad incoming integers with. For example: If you’re using ZEROFILL on a column that is set to INT(5) and the number 78 is inserted, MySQL will pad that value with zeros until the number satisfies the number in brackets. i.e. 78 will become 00078 and 127 will become 00127. To sum it up: The number in brackets is used for display purposes.
    In a way, the number in brackets is kind of usless unless you’re using the ZEROFILL attribute.

    So the size for int would remain same i.e., -2147483648 to 2147483648 for signed and 0 to 4294967295 for unsigned (~ 2.15 billions and 4.2 billions, one of the reasons why developers remain unaware of the story behind the Number N in brackets, as it hardly affects the database unless it contains over 2 billions of rows), and in terms of bytes it would be 4 bytes.

    For more information on Integer Types size/range, refer MySQL Manual

  • Though this answer is unlikely to be seen, I think the following clarification is worth making:

    • the (n) behind an integer data type in MySQL is specifying the display width
    • the display width does NOT limit the length of the number returned from a query
    • the display width DOES limit the number of zeroes filled for a zero filled column so the total number matches the display width (so long as the actual number does not exceed the display width, in which case the number is shown as is)
    • the display width is also meant as a useful tool for developers to know what length the value should be padded to

    A BIT OF DETAIL
    the display width is, apparently, intended to provide some metadata about how many zeros to display in a zero filled number.
    It does NOT actually limit the length of a number returned from a query if that number goes above the display width specified.
    To know what length/width is actually allowed for an integer data type in MySQL see the list & link: (types: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT);
    So having said the above, you can expect the display width to have no affect on the results from a standard query, unless the columns are specified as ZEROFILL columns

    OR
    in the case the data is being pulled into an application & that application is collecting the display width to use for some other sort of padding.

    Primary Reference: https://blogs.oracle.com/jsmyth/entry/what_does_the_11_mean

  • In MySQL integer int(11) has size is 4 bytes which equals 32 bit.

    Signed value is : –2^(32-1) to 0 to 2^(32-1)-1
    = -2147483648 to 0 to 2147483647

    Unsigned values is : 0 to 2^32-1
    = 0 to 4294967295

  • I think max value of int(11) is 4294967295

  • 4294967295 is the answer, because int(11) shows maximum of 11 digits IMO

发表评论

电子邮件地址不会被公开。 必填项已用*标注