Can the primary index be CHAR in MySQL?

Question

My primary indexes are unique reference numbers, such as 002345 and 000023.

If I format them as integers, I will lose my zero. They must be 6 digits.

Can I use CHAR? I do not need any automatic increments.

Answer 1

Yes, you can use the CHAR column as the primary key in MySQL.

However, you should be aware that your reference numbers are integers and apply leading 0 at the application level.

You may also consider using a surrogate key that is not derived from a reference number or any other application data. The main advantage is that your records will not lose their “descriptor” if the order reference number ever changes. You will also get additional integer index performance as a positive side effect.

Answer 2

I just tested local MySQL - it works great. But for some performance gains, I would go for a solid primary index.

Answer 3

You can use order numbers as a primary key. But one day, your boss will call you and say that the order number sent for your most important customer was printed as 002346 not 002345 and that they cannot receive payments until this error has been fixed in your system .. .

You should also consider the integer primary key as it will save space and connections will be faster.