What kind of data type to use for numbering in the Harmonized System?

Question

The harmonized system uses a numbering that shows the hierarchy between different items. Similar to bookkeeping ledger numbers. Here is an example:

01  LIVE ANIMALS
0103  swine, live
010310  Purebred breeding animals

For each level there are two digits available, and this can go down in my case to up to eight digits in total (so four levels). I would like to catalog the harmonized system codes and their descriptions, but wonder what a good data type is to use in MySQL. These numbers are unique so I would like to make them my Primary Keys as well. Given the hierarchy, I should be able to do some SELECTs that allow me to find out the child records. What I am thinking of is using some sort of INT type, and then doing things like this:

SELECT * FROM accounts WHERE CAST(id AS TEXT) LIKE '01%'

Which should allow me to find the child accounts that fall under the '01' category. The problem here would be to work with the leading zeros. If I use zerofill, then 0103 will become 00000103, so that is not ideal. But if I do not use zerofill then this will be 103. Again not ideal.

Using a text data field instead would solve this, but this feels quite wrong as these are clearly numbers. Alternatively I could make several columns - each for the number group - but this again feels like a weird solution. Any advice is appreciated.

Answer 1

"The harmonized system uses a numbering" - they are not numbers, they are codes. They just happen to use digits instead of Latin letters for the code values.

As you have to retain leading zeros anything numeric will not suit. You have to go with textual types. Suppose the codes were

A01        LIVE ANIMALS
A01B03     swine, live
A01B03C10  Purebred breeding animals

I suspect you would have no concerns about using text? Just think of them this way, but with the A, B and C removed. Or think of your alphabet as consisting of 36 characters, a to z and 0 to 9.