Enter more than length.

joe2

Hi,

if you enter a string of higher length in varchar field size. it truncates the last extra string.
for e.g
name varchar(5)
enter "Joeseph"
Mysql will insert "Joese".
But in the case of Inetger. if u enter the higher length value , mysql puts a new value in that ineger field.
for e.g.
age int(2)
enter "123"
it will write a new value in the age field.

What is that new value. or What is concept behind integer insert.

Thanks

drawmack

Well if you're using a signed integer (which int is) then you should be getting a negative number from this as it's treating the bit you're setting as a sign bit.

joe2

Thanks

Sxooter

Please note that in MySQL this doesn't work as you might expect. It doesn't affect the storage of ints, only the display.

it's a half-assed feature I'd avoid personally, since it doesn't behave the way you'd expect, and no other database has a "precision" for ints or floats or anything like it.

drawmack

Originally posted by Sxooter
Please note that in MySQL this doesn't work as you might expect. It doesn't affect the storage of ints, only the display.

it's a half-assed feature I'd avoid personally, since it doesn't behave the way you'd expect, and no other database has a "precision" for ints or floats or anything like it.

Here, here for the ints you've already got tinyint, int, mediumint and bigint that should be enough versatility.

However, on floats a lot of dbs do provide this so you can tell it how many decimal places to store because often with floats you want to store things like money. Though most databases have a different type for a float with limited percision though I can't remember what it's usually called off the top of my head cause I've been using mysql too long.

Sxooter

A couple of points. PostgreSQL 8.0 beta 1 will accept an argument for precision of a float, but appears to simply ignore it. I'm certain most other dbms engines either error out on it or ignore it as well.

Second point, if you're storing money in a float you're making a mistake. Currency should always but always be stored in an exact type (int, numeric, decimal) and if those exact types aren't guaranteed to really be exact then the database is broken and should not be used (i.e. mysql's strange numeric behavious, and tendency to silently overflow them without an error makes it ineligable for use as an accounting system database.) It's the sole reason MySQL doesn't get to really claim the to be fully ACID, because you can feed it the number 123498732409872340987 into an int4 and it just chops it off to 2³² - 1 or so.

So I'm gonna guess drawmack meant numeric when he said he couldn't remember the type with adjustable precision for things like money.

By the way, automatically chopping the varchar going into a varchar(5) is also another reason MySQL can't claim to be ACID compliant, as the proper behaviour is to throw an error that the string was too long and let the user decide if chopping it off is what they want to do or not. Silently truncating data is not a good thing for a database to do automatically.

Note that if it's the behaviour you want, then most dbms engines support triggers, which can be used to do just that. But the base behaviour must be to throw an error to be correct.