What does "unsigned" mean in MySQL and when should I use it?
Asked
Active
Viewed 2.3e+01k times
2 Answers
594
MySQL says:
All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.
When do I use it ?
Ask yourself this question: Will this field ever contain a negative value?
If the answer is no, then you want an UNSIGNED data type.
A common mistake is to use a primary key that is an auto-increment INT starting at zero, yet the type is SIGNED, in that case you’ll never touch any of the negative numbers and you are reducing the range of possible id's to half.
-
9values representing the 'sizes' of things, like the quantity of a particular item on an order, or the distance between two locations, will typically be unsigned – SingleNegationElimination Oct 09 '10 at 04:28
-
41Great answer, seems strange that mysql wouldn't default integers to unsigned when they are auto incrementing identities? – wired00 Oct 30 '14 at 03:19
-
4If you want more clear answer then look this https://stackoverflow.com/a/11515613/6335029 – Naveen DA Oct 25 '17 at 16:58
-
2@wired00 Perhaps part of the reason is that `UNSIGNED` is a non-standard MySQL feature. – minexew Oct 30 '20 at 10:30
-
yeah, imagine you have a negative ID – Jovylle Sep 28 '21 at 02:02
0
What does "unsigned" mean in MySQL:
In schema building (database design), the unsigned integer is an integer that can only hold a positive (non-negative) value.
In the context of mySQL, the unsigned method used to specify that column should only hold a positive value.
when should I use it?
It's good practice in schema building to assign the columns that hold foreign key (as id) to unsigned method to make sure that column only holds positive value and prevent erros and unexpected behaviors in queries that use that column.
jhon26
- 313
- 2
- 11