Type of the CPF or CNPJ field in the VARCHAR or INT database?

Question

What type of CPF or CNPJ field in the database VARCHAR or INT?

I’ve seen some posts suggesting using INT to optimize performance in the event of JOINand filters.

If you have 0 the left is only complete according to the size of the stored field (CPF or CNPJ) to perform the validation.

So what would be the best approach?

Answer 1

Optimization should only be done if it is really very necessary. I doubt that this optimization is worth in any scenario.

The basic rule for selecting the type when they can be confused is what you will use them for. Numbers represent quantities. CPF or CNPJ are quantities? No, they are identifiers that can even change, that can one day have letters (happened with RG). The correct semantics for this data is the varchar after all he is descriptive.

The question of zero on the left alone is already good reason to use varchar. So you can skulk around to solve a problem caused by the wrong guy. I always say this for everything in computing and even in life, the person causes a problem and then causes new ones trying to solve something that they should have done right from the start (This happens more than almost everyone realizes, especially with experienced people who can no longer see their mistakes as the arrogance that develops, even speaking the opposite)

You should always choose to varchar on any given data until you find a reason to choose another type. A general ID, for example, has a good reason to be a int, It needs to be incremented, you do accounts with it. The same goes for salary, price, quantity. But it is not valid for zip code, phone, even a property number in the address when this field is separated. An expiration has reason to be of another type, you need to sort the information by date order, and as text this would not work correctly. These are just a few examples.

Some will even say that it is necessary to use the varchar because he has scores beyond numbers. But this is not a good argument since the punctuation should not even be recorded, at least not in most situations. This should be solved in the presentation only, the score is not part of the data (usually).

In some cases a numeric type cannot even be used since the number of digits used to identify something is greater than the numeric type holds.

Some will say that neither varchar should be used. As the size is fixed, a simple char solves the problem. But there are controversies about this. Depending on the DB system one type may actually be more advantageous than another but not enough to justify, in most cases a choice will be made because of the performance. Semantics should be the primary option.

Unfortunately there is a lot of wrong information on the internet about the subject (or any subject :) ).

Answer 2

I’ve always advocated using VARCHAR, because they are a string that coincidentally occurs to be a sequence of digits. Unlike a INT where the purpose of being a number is to allow you to perform mathematical operations, something that makes no sense at all in a CPF or CNPJ.

The same case occurs with RG, where each state has a different format, and use as INT would remove the zeros on the left in addition to bring problems concerning the check digit.

Answer 3

Use VARCHAR you will have problems if the user uses . (point) or/and - to separate the digits. If you do some checking and remove these or other characters, then you can use INT. But I recommend using VARCHAR. Use INT for records you know will only have digits/numbers.

Answer 4

If we look at the size of the INT(UNSIGNED) ranging from 0 to 4294967295, we have 10 supported positions.
As the CPF has 11 numbers without formatting, we would have 1 number more in this string, forcing the DB, at the time of insertion, to make a CPF with initial value say of "500.000.000-60" or if it is "4294967296" turn 4294967295.

The rule applies to the CNPJ as well, in the case of the CNPJ with 14 numbers without formatting. The ideal would be to use a BIGINT from 0 to 18446744073709551615. For speed issues in queries, an index can be added in this field and other rules ranging from each DBA.

In the case of the 0 left in Mysql for example we can define the field as ZEROFILL.

On formatting it is not necessary to allow the application to reach the DB with a CPF or CNPJ formatted with "." , "-" or "/", this rule should be made in a view.

Answer 5

According to Victor Stafusa’s answer, it will help you make the best decision to see what types of operations will be carried out in the field in question. In the case of CPF and CNPJ, these do not undergo arithmetic operations (usually) and are treated as alphanumeric data types (char, varchar) since their validation algorithms, which by definition consider them fixed-sized character strings. Another point in favor of using alphanumeric types such as varchar is that it is easier to use searches using the LIKE operator, as opposed to fields of numerical types. Data fields of "identifier codes" (such as CPF and CNPJ) alphanumerics can also be considered more the proof of the future, because there is some possibility (maybe remote, but there is) of switching to using letters in these, since each letter greatly increases the number of different combinations and at the same time requires fewer characters to memorize.

But like everything else in the world of computing, everything has its price and depending on its performance requirements and database size, it can be much more performative to store Cpfs and Cnpjs as numerical types, since they take considerably less bytes (especially in relation to varchar with Unicode), resulting in physically smaller table indices, in addition to faster search algorithms. In fact, I have seen great performance improvement in Oracle databases when having Cpfs stored as number.

Answer 6

Cara I advise you to use a string even, because you may have problems with 0 the left pq they can be disregarded.