Unsigned in the primary key increases my chances?

Asked

Viewed 207 times

1

I was setting up a bank for a personal project and I came across creating a table to keep a few values, this would use a TINYINT as a primary key, since I assume I won’t go more than 100 records, but if I do, I want to have a ceiling greater than 127.
(the above example is hypothetical, serving only to explain the question. I am already using INT(2)).

So I’d like to know, considering the key values start at 0, use the UNSIGNED in the primary key allows me to register more values? Since the negative values are used in the positive ones?

(Sure I can register using negative keys and it would be useful to keep without the UNSIGNED in that case, but it’s not something I’ll use).

Edit after title change: When I changed the title ("is it really necessary?") "lost" part of the question, another part would be: It is common/necessary to always use?

mysql
  • 3

    Reviewing 4 years later, this is an example of why Accept should not define ordering of responses, precisely because it is a common answer wrong stay above the correct ones, as happened here - after all, the Accept is given by those who are just in doubt. And worse, with positive votes. For those who did not understand, e.g. from -10 to 10 have the same amount of possibilities as 0 to 20. The unsigned does not change it. It facilitates self-numbering, but nothing prevents make Insert using the negatives using the whole range.

3 answers

8

If you use the UNSIGNED can reach the ID 255. If you do not use only up to 127 (you can use negatives up to -128).

So does not increase your chances, you’ll still have 255, only then you can use all the positive ones. There is no compaction, changes the representation, but not the data or size.

In general people only use one database only. And when you use others, either you leave it bad for everyone, or you end up making adaptations to each one.

I prefer not to use because it can give impedance to the language. If you think you will use more than 127, use SMALLINT. Spending an extra byte on 200 records will usually not make the slightest difference on any device that uses Mysql and you dismiss the worry instead of doing micro-optimization and keep working hard in margin.

Be careful because there are tools that put the UNSIGNED on its own account.

Documentation.

  • Oh yes, I had seen it, but I think I expressed it a little poorly in the question without the previous title, is it really necessary? by removing the negative value I may lose something?

  • 4

    It’s necessary, otherwise it goes to 127, unless you use negatives, which is weird for ID, whether or not you need all this is another story.

  • @bigown For real and practical cases, it increases yes, because only positive numbers are used. About the third paragraph, better not even comment.

  • 2

    I learned mathematics otherwise, in which I learned a range ranging from -128 to 127, gives 256 possibilities, the range from 0 to 255 gives 256 possibilities. 256 equals 256, so I can’t see a raise.

3


Increases yes!

Furthermore, I consider it important to examine whether this level of compaction is really necessary for the column.

You are working with limited devices, with little disk space, where does this represent a significant difference? Eventually a very specific type may not be supported by another database by plastering its application.

  • Good answer, had changed the title ended up changing a little what you expected from the question (I’ll edit again), but that’s what I wanted to understand. Um, can you tell me if it’s common to use the UNSIGNED or most leave without to maintain this support issue?

  • 1

    I don’t think it’s common! I also suggest using a standard type and large enough for any entity of your application, facilitating the maintenance and evolution of it.

  • 3

    It doesn’t really increase the possibilities. What makes it easier is self-numbering, that’s all (in a way, the author has already put the answer in the question itself if he is going to see).

2

Characteristics of a primary key:

  1. Its value should always be unique. If you use a composite primary key, the combination of fields should be unique
  2. The primary key column cannot contain null values, so its field must be declared as NOT NULL

With this in mind, the use of INT for primary keys because this has very low reading time compared to other types (character, floating point...)

For database size reasons (when you work in limited storage spaces) you can save a few bytes using modifiers: TINYINT, SMALLINT, etc... Just as necessary, one can make use of the BIGINT for tables with large amounts of data.

It is also good practice to use the modifier AUTO_INCREMENT, which guarantees to each new record the auto-increment of the key.

Finally, answering your question, the modifier UNSIGNED guarantees only positive values for the key, which is usually composed of integers. However, as a rule, a key is defined by a single element, regardless of its numerical value.

  • 4

    Don’t take this the wrong way, but it was really necessary all these paragraphs before answering the question?

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.