primary key should always be unsigned?

since a primary开发者_高级运维 key (identifier) wont be under 0, i guess it should always be unsigned?

TL/DR: Yes, but it almost doesn't matter.

Auto-increment always increases, so it will never use negative values. You might as well make it unsigned, and you get twice the range of values.

On the other hand, if your table uses 2³¹ values, it will probably also use 2³² values in a short time, so having twice the range of values isn't a big difference. You will have to upgrade to BIGINT anyway.

MySQL supports an optional SERIAL data type (presumably for compatibility with PostgreSQL, since SERIAL is not standard ANSI SQL). This data type is just shorthand that creates a BIGINT UNSIGNED.

Go ahead try it:

CREATE TABLE test.foo (foo_id SERIAL PRIMARY KEY);

SHOW CREATE TABLE test.foo;

CREATE TABLE `test`.`foo` (
  `foo_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`foo_id`),
  UNIQUE KEY `foo_id` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

You get the same number of distinct values whether you declare an integer signed or unsigned: 2³² for an INT and 2⁶⁴ for a BIGINT. If the number is unsigned, you get values from 0 to that max value minus one. If the number is signed, you get values from -max/2 to max/2-1. Either way, you get the same absolute number of distinct values.

But since AUTO_INCREMENT starts at zero by default and increments in the positive direction, it's more convenient to utilize the positive values than the negative values.

But it hardly matters that you get 2X as many positive values. Any table that would exceed the maximum signed integer value 2³¹-1 is likely to continue to grow, so you should just use a BIGINT for these tables.

You're really, really, really unlikely to allocate more than 2⁶³-1 primary key values, even if you delete all your rows and re-load them many times a day.

Why exactly are you presuming that a primary key won't be under 0? That is not a given. I think you are confusing it with an identity column.

In any case it should make no appreciable difference either way, map the data type to the type of data you expect in the column regardless of whether it is a primary key or not.

since a primary key (identifier) wont be under 0

I assume from that statement that your primary key also auto-increments.

If it does, then is is very sensible to ensure your column is unsigned, for the reason described in the MySQL manual:

An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers “wrap” over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.

If you are inserting negative numbers into an auto-incrementing column, then you are probably solving a problem the wrong way.

NO - a primary key wont always be unsigned for example:

 create table user_status
 (
  status_id tinyint not null primary key,
  name varchar(64) not null,
  msg varchar(255) default null
 )engine=innodb;

 insert into user_status values 
    (-99,'banned', 'Account banned'), 
    (-2,'closed', 'Account closed'),
    (-1,'unverified', 'Account not verified'),
    (0,'suspended','Account suspended'),
    (1,'active', null);     

if this was an orders table however i'd use order_id int unsigned