SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    inserting records exceeding integer data type length given

    Hi,

    actually when iam creating table i have given column length 11 & auto increment.

    ---------------------------------------------------------
    CREATE TABLE IF NOT EXISTS `merchants` (
    `merchant_id` int(2) NOT NULL auto_increment,
    `merchant_name` varchar(255) NOT NULL,
    PRIMARY KEY (`merchant_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Merchants Master Table' AUTO_INCREMENT=1 ;
    ---------------------------------------------------------
    Then i wriiten one program to insert records in to it, having loop 1 to 100000 records. its inserting......

    There is no restriction in the integer range..

    iam in full confusion, though iam having 3 years experience in php. i thought till now if we give 11 length, records will insert till 11 digits.

    please give reply asap.

    thanks in advance.

    Regards,
    Sunil.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    The length you can provide for an int when defining a column has nothing to do with the range of characters it can store.

    Like da manual says:

    Quote Originally Posted by Da Manual
    The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three characters are displayed using more than three characters.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    then wt the meaning of giving length, when defing table.
    pls send.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Click the link to the manual in my previous post to find out
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi

    i read the manual, sorry to say i didn't get exact answer what i need.
    my question is "how many number of digits allowed for length 3 given to integer column".

    Regards,
    Sunil.

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    An INT always takes values in the range from -2147483648 to 2147483647, regardless of the length you set.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    so what length 3 for?
    kindly explain.

    Sunil.

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nagasunil View Post
    Hi,

    so what length 3 for?
    kindly explain.

    Sunil.
    Alright ...

    If you create an INT(1) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(2) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(3) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(4) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(5) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(6) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(7) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(8) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(9) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(10) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.
    If you create an INT(11) the minimum value for that field is -2147483648 and the maximum value for that field is 2147483647.

    I really don't know how to make it any more clear than that ...
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    From the page Scallio linked to:
    MySQL supports and 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 with of 4 digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
    So, the number has nothing to do with the range of values the column can contain. It is extra info that may be used by the application that extracts the data. May, or may not.

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thank you. i got the answer.
    i think , i irritated you. sorry for it.

    Regards,
    Sunil.

  11. #11
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by nagasunil View Post
    Hi,

    Thank you. i got the answer.
    i think , i irritated you. sorry for it.

    Regards,
    Sunil.
    No worries, glad you got it
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  12. #12
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    one more doubt, if it increases more than integer range, what should we need to do.
    suppose i given even BIGINT unsigned.

    i mean if crossed 18446744073709551615.

    Thanks in Advance,
    Sunil.

  13. #13
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by nagasunil View Post
    Hi,

    one more doubt, if it increases more than integer range, what should we need to do.
    suppose i given even BIGINT unsigned.

    i mean if crossed 18446744073709551615.

    Thanks in Advance,
    Sunil.
    I think your server would explode before you reach that quantity of data

    Why do you think that enormous number wouldn't be enough?

  14. #14
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    some typical people will ask those questions in the interviews, first of all they dont know & try to get from us.
    instead of showing odd face, we can give exact know.
    thanks.

  15. #15
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So test it and see what happens then.

  16. #16
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    The number of digits you specify for an int only makes a difference if you also specify a fill character as then if the length to be displayed is shorter than the length you specify it will pad it out using the fill character

    eg int(4) fill('*') with a value of 100 will return *100 from any database call.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    stephen, could you provide a link to the FILL option in the manual, please?

    it sounds like you're thinking of ZEROFILL, actually
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    stephen, an update please?

    where did you find this mysterious FILL('*') function?

    i think you must've just imagined it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    53
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    so if length is 5, then like below right.

    eg int(5) fill('*') with a value of 100 will return **100 from any database call.

    if you dont mind, how will we display with that stars using the application.

    Thanks In Advance,
    Sunil.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't think that FILL('*') exists, i think stephen was simply confused

    ZEROFILL does exist, however, and how it works is explained in the manual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,871
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Yes, I was misremembering the command because I have never found a need to use it.

    I meant ZEROFILL.

    I think I was getting it mixed up with Cobol which has a similar option that is actually useful.
    Last edited by felgall; Oct 8, 2010 at 13:32.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •