This is my first time using TiDB. The project I took over uses types like decimal(20,0) and decimal(30,0) as primary keys in many places. Does TiDB have special optimizations for these types? I don’t understand why this type is used as a primary key. This is very different from my understanding. In my understanding, the decimal type is only used for fields involving small number calculations. Can experts in TiDB help clarify this? Is it a mistake to use this type as the primary key in our project? Can we replace it with varchar(30)?
Isn’t decimal the main data type used for amounts? It allows for precise calculations, avoids rounding errors, can define decimal places, and has a large range. Varchar doesn’t seem to fit with it, isn’t varchar purely a string type?
I know that decimal is mainly used for fields such as monetary calculations. What I’m wondering about is the significance of using decimal as a primary key. Can it be understood that the designer might have considered using integers beyond the range of BIGINT as the primary key with decimal(30,0), and that the performance is higher compared to using string types?
TiDB does not have such a recommendation; it always suggests using bigint. This approach is definitely not due to TiDB’s recommendation; you should ask the designer.
In TiDB, using decimal(20,0) or decimal(30,0) as primary keys is a valid approach. TiDB indeed has special optimizations for the decimal type to ensure efficient storage and computation.
The decision to use decimal types as primary keys depends on the specific requirements and characteristics of the data in the project. Here are a few points to consider:
Precision and Scale: Decimal types allow precise storage and computation of numbers with specified precision and scale. If your primary key values represent numbers and require a specific level of precision and scale, using decimal types can ensure the accuracy of data representation.
Range of Values: If the primary key values have a wide range and need to cover a large number of decimal digits, decimal types can meet such requirements. They allow you to define precision and scale as needed, providing flexibility in representing various numerical values.
Sorting and Comparison: Decimal types support sorting and comparison operations, which are essential for primary keys. If your primary key values need to be sorted or compared based on their decimal representation, using decimal types is appropriate.
Regarding using varchar(30) as a replacement for decimal types in primary keys, it depends on the nature of the data and how you intend to use the primary key. Varchar types are suitable for certain scenarios, such as when primary key values represent alphanumeric codes or identifiers. However, varchar types have different sorting and comparison behaviors compared to decimal types. It is important to ensure that the chosen data type aligns with the intended use and requirements of the primary key.
To determine whether the primary key type used in the project is correct, it is crucial to consider the specific use case, data characteristics, and performance requirements. Consulting with the original project team familiar with the project design and requirements or TiDB experts may be helpful. They can provide more insights and guidance for your specific scenario.
Overall, the decision to use decimal types as primary keys in TiDB is a valid approach that offers optimizations for efficient storage and computation. However, it is essential to evaluate the project’s requirements and consider alternative data types, such as varchar, based on the specific needs of the primary key values.