Inserting records exceeding integer data type length given

Databases
1

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

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.

3

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.

4

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.

5

An INT always takes values in the range from -2147483648 to 2147483647, regardless of the length you set.

6

I think your server would explode before you reach that quantity of data :smiley:

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

7

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.

8

No worries, glad you got it :slight_smile:

9

Hi,

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

Regards,
Sunil.

10

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.

11

Click the link to the manual in my previous post to find out :slight_smile:

12

then wt the meaning of giving length, when defing table.
pls send.

13

stephen, an update please?

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

i think you must’ve just imagined it :stuck_out_tongue:

14

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:

15

stephen, could you provide a link to the FILL option in the manual, please?

it sounds like you’re thinking of ZEROFILL, actually

16

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.

17

So test it and see what happens then.

18

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

19

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 …

20

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.