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.