MySql:: if field is primary key + auto increment should he also defined as unsigned or mysql doing that by default?

Question

Hi,

MySql:: if field is primary key + auto increment should he also defined as unsigned or mysql doing that by default?

Edit:

1. I asking because integer unsigned save 2* space vs integer.
2. I asking about INNODB. Thanks

Answer 1

MySQL doesn't define it to be unsigned by default. The value won't wrap round into negative values, if that's what you are worried about. Inserts will start to fail when you have 'burst' the column type. A good idea would be to use a column type that is 'enormous' compared to the dataset at hand and then you'll never have to worry about it.

More here about auto-increment... http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

Answer 2

MySQL does not make auto increment fields unsigned by default, and you can keep it signed if you like.

The consequence of auto increment is that MySQL keeps a counter for this table which is increased whenever you insert a row without specifying a value for the identity column, and the current value is used as a default for that column. However, if you explicitly specify a value for the identity column on insert, the auto increment status will remain untouched. If the column is of a signed type, this means that you can insert negative values as the ID. In most cases however, you want to let MySQL handle auto increment values completely.

Of course, due to the primary key constraint, the insert will still fail if you try to insert identity values that are in use already, because a primary key also implies a unique constraint.