MySQL Forums
Forum List  »  Newbie

understanding the create table a (a int(4)) command
Posted by: Ricardo Striquer Soares
Date: September 28, 2011 09:59AM

I have some kinda big tables to tune and one thing I'm trying to is to downsize the data length of a row in a table, I have some tables which it's like this

# create table a ( i bigint(20) unsigned not null );
# show columns from a;
# insert into a values (12345);
it shows .. "bigint(20) unsigned" in the type and shows 9 in the data_lenght of a "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'a';" all right till there for the number '12345' should be stored in less them 20 bites, but if I do ..

# alter table a modify i int(4) unsigned null;
# SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'a';
It trims 7 bytes instead 4 (as I understood it should do from http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html ) and though i thought it would not get worst if I do ...

# alter table a modify i int unsigned null;
it shows .. "int(10) unsigned" in the type and keep showing 7 bytes in the data_lenght of a "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME LIKE 'a';"

I thought when you pass a parameter to the type, no matter which type it says to MySQL that he should use at most that quantity of bytes to store the number ... but it seams not work this way in numeric types ... but I search out the documentation and can't find where to explain me this ... can someone tell me why or point me some directions to understand how it works and why it works this way?

Thanks.

--
/**
 * @analist Ricardo Striquer (ricardophp yahoocombr)
 * @var Técnico em PDD
 * @see http://www.programabrasil.org
 * @see http://twitter.com/rstriquer
 */

Options: ReplyQuote


Subject
Written By
Posted
understanding the create table a (a int(4)) command
September 28, 2011 09:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.