[SOLVED] MySQL FAQ?

[Trium918]

When should unsigned be used in a query and why?

 

Example:

create table table_name( id int unsigned not NULL,
PRIMARY KEY(id) );

[Illusion]

When you are sure that u r going to enter positive values only in that column u can go far UNSIGNED.

[gizmola]

Although technically even Data definition language (DDL) is a "query" that's only issued when the table is created.  In selects and updates you would never use unsigned, as the concept of data typing of columns is not usually relevant once the table has been created.

 

MySQL is similar to SQL Server in that it has byte length oriented numeric datatypes.  The types match the number of bytes used to store the value, so in your example, the int type is a 4 bytes (32 bit value).  Simple binary will reveal to you the largest value that can be stored with 32 bits. 

 

By default if you you don't specify at create time, that you want an UNSIGNED int, you will get a signed int, where one of the 32 bits is reserved to indicate whether the value is + or -.  Effectively what this does is cut the maximum value range in half.

 

I your example, you are creating the primary key of a table.  More often than not, people will also define that column to be Auto Increment.  With auto_increment you would never have a value that is minus, so it is a waste of storage to not used UNSIGNED as you lose 1/2 the potential maximum rows.  This means that you would run out of keys at 2,147,483,647 rows rather than 4,294,967,295.

 

Very few databases require that number of rows, so this is largely academic for most people, although the same principle works for smaller data types like the smallint (64k) or tinyint(256) values.  One of the most important decisions you make in mysql design is correct data typing.  The smaller and more efficient the size of an individual row, the more efficient the database will be, all other things being equal.

 

For example, if I have a lookup table that is going to store types of fruit, it's worth asking the question of what is the largest number of fruit I expect to ever have in that table.  In all probability, I will not be able to think of more than 256 different types of fruit, so an UNSIGNED TINYINT is a good type for the primary key of that table.

 

As I relate that table to other tables in my database (think for example of a recipe database where I might indicate fruit and a quantity) that I may have thousands of rows of recipes that involve fruit, however in each one it only costs me 1 byte of storage to indicate the fruit as a foreign key.