Unsigned in the primary key increases my chances?

Question

Unsigned in the primary key increases my chances?

Navigation

#1 by (5 votes)
#2 by (4 votes)
#3 by (0 votes)

4

I was setting up a bank for a personal project and I came across creating a table to save a few values, this would use a TINYINT as the primary key, since I assume that I will not exceed 100 records, but if I happen 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 would like to know, considering that key values start at 0 , does using UNSIGNED in the primary key allow me to register more values? Since negative values are being used on positives?

(It's okay to use negative keys, and it would be useful to keep it with UNSIGNED in that case, but it's not something I'll use).

When I changed the title ("Is it really necessary?") "I lost" part of the question, another part would be: Is it common / need to always use?

>

mysql

asked by anonymous 21.02.2017 / 17:27

3 answers

5

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

So does not increase your possibilities, you will continue to have 255, only there you can use all positive. There is no compression at all, it changes the representation, but not the die or size.

In general people only use a database only. And when you use others, or the person makes it bad for everyone, or ends up making adaptations for each one.

I prefer not to use because it can give impedance with the language. If you think you will use more than 127, use SMALLINT . Spending an extra byte in 200 records usually will not make the slightest difference to any device that uses MySQL, and you take the worry aside instead of doing micro-optimization and continue to work hard at the margin.

Be careful because there are tools that puts UNSIGNED on its own.

Documentation .

21.02.2017 / 17:32

0

Characteristics of a primary key:

Your value should always be unique. If you use a composite primary key, the combination of fields must be unique

The primary key column can not contain null values, so its field should be declared as NOT NULL

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

For bank size issues (when working in limited storage spaces) you can save a few bytes using modifiers: TINYINT , SMALLINT , etc ... As well as necessary, you can make use of the BIGINT for tables with large amounts of data.

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

Finally, answering your question, the UNSIGNED modifier only guarantees 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 numeric value.

21.02.2017 / 17:45

What is the difference between a package and a module in Python? Jquery ignoring houses on the right

score 4 · Accepted Answer

Increase yes!

In addition, I consider it important to analyze whether this level of compaction is really necessary for the column.

Are you working with limited devices, with little disk space, where does this make a significant difference? Eventually a very specific type may not be supported by another database, plastering your application.