Inconsistent NUMERIC_PRECISION Field Precision in TiDB System Table information_schema.columns with Identical Table Creation Statements

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 相同建表语句tidb系统表information_schema.columns中NUMERIC_PRECISION字段精度不一致问题

| username: ljh_wxy

[TiDB Usage Environment] Production and Testing
[TiDB Version] V5.4.1
[Reproduction Path] Create the same table in MySQL and TiDB

create table testtype 
(
   test_int int(2),
   test_smallint smallint(1),
   test_mediumint mediumint(2),
   test_bigint bigint(3)
);

MySQL Version: 5.6.19
[Encountered Problem: Phenomenon and Impact]
The NUMERIC_PRECISION values in the information_schema.columns system table are inconsistent for the same table creation statement.
TiDB:

image
image1339×523 31.4 KB

MySQL:
image
image1328×444 28.8 KB

Is this difference designed due to some special functionality in TiDB?
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

2
| username: Kongdom | Original post link

Explanation for TiDB:

NUMERIC_PRECISION: For numeric columns, it represents the numeric precision.

Explanation for MySQL:

For columns of type int, NUMERIC_PRECISION always shows 10, while the length inside the COLUMN_TYPE column’s int(xxx) may vary. If we define a table structure and create a column of type int(3), the column will still occupy 4 bytes of storage and can store up to 10 digits. However, when this column is displayed by default, it will be shown with a width of 3 characters.

3
| username: ljh_wxy | Original post link

In TiDB, the NUMERIC_PRECISION value does not match the actual precision that can be stored. For example, test_bigint bigint(3) has a precision of 3, but it can actually store data exceeding the precision of 3.

4
| username: zhanggame1 | Original post link

I’ve researched this value and found it to be useless. For int type, there’s no difference between writing 1 and writing 100. Int, bigint, etc., all have fixed precision.

5
| username: redgame | Original post link

It shouldn’t have much impact.

6
| username: zhanggame1 | Original post link

image
image703×240 47.1 KB

7
| username: wangccsy | Original post link

Precision issues have always existed, right?

8
| username: Kongdom | Original post link

The explanation suggests that it can store more than 3 decimal places, but it only displays 3 decimal places. I haven’t tested it myself, you can give it a try.

9
| username: lemonade010 | Original post link

Got it, I’ll test it myself when I have time.

10
| username: onlyacat | Original post link

This only affects display, not storage, as I recall.
You can check the MySQL official website.

image
image1380×398 91.7 KB

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-attributes.html

11
| username: Kongdom | Original post link

:thinking: As expected, we still need to refer to the official documentation.

12
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.