- About TiDB
- Quick Start
- Software and Hardware Requirements
- Environment Configuration Checklist
- Topology Patterns
- Install and Start
- Verify Cluster Status
- Benchmarks Methods
- Backup and Restore
- Read Historical Data
- Configure Time Zone
- Daily Checklist
- Manage TiCDC Cluster and Replication Tasks
- Maintain TiFlash
- Maintain TiDB Using TiUP
- Maintain TiDB Using Ansible
- Modify Configuration Online
- Monitor and Alert
- TiDB Troubleshooting Map
- Identify Slow Queries
- SQL Diagnostics
- Identify Expensive Queries
- Statement Summary Tables
- Troubleshoot Hotspot Issues
- Troubleshoot Increased Read and Write Latency
- Troubleshoot Cluster Setup
- Troubleshoot High Disk I/O Usage
- Troubleshoot Lock Conflicts
- Troubleshoot TiCDC
- Troubleshoot TiFlash
- Troubleshoot Write Conflicts in Optimistic Transactions
- Performance Tuning
- System Tuning
- Software Tuning
- SQL Tuning
- Understanding the Query Execution Plan
- SQL Optimization Process
- Logic Optimization
- Physical Optimization
- Prepare Execution Plan Cache
- Control Execution Plans
- Multiple Data Centers in One City Deployment
- Three Data Centers in Two Cities Deployment
- Best Practices
- Use Placement Rules
- Use Load Base Split
- Use Store Limit
- TiDB Ecosystem Tools
- Use Cases
- Backup & Restore (BR)
- TiDB Binlog
- TiDB Lightning
- Cluster Architecture
- Key Monitoring Metrics
- SQL Language Structure and Syntax
- SQL Statements
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
CREATE [GLOBAL|SESSION] BINDING
CREATE TABLE LIKE
DROP [GLOBAL|SESSION] BINDING
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW [GLOBAL|SESSION] BINDINGS
SHOW CHARACTER SET
SHOW [FULL] COLUMNS FROM
SHOW CREATE SEQUENCE
SHOW CREATE TABLE
SHOW CREATE USER
SHOW DRAINER STATUS
SHOW [FULL] FIELDS FROM
SHOW INDEX [FROM|IN]
SHOW INDEXES [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW MASTER STATUS
SHOW [FULL] PROCESSSLIST
SHOW PUMP STATUS
SHOW TABLE NEXT_ROW_ID
SHOW TABLE REGIONS
SHOW TABLE STATUS
SHOW [FULL] TABLES
SHOW [GLOBAL|SESSION] VARIABLES
- Data Types
- Functions and Operators
- Type Conversion in Expression Evaluation
- Control Flow Functions
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Bit Functions and Operators
- Cast Functions and Operators
- Encryption and Compression Functions
- Information Functions
- JSON Functions
- Aggregate (GROUP BY) Functions
- Window Functions
- Miscellaneous Functions
- Precision Math
- Set Operations
- List of Expressions for Pushdown
- Generated Columns
- SQL Mode
- Garbage Collection (GC)
- Character Set and Collation
- System Tables
- TiDB Dashboard
- Overview Page
- Cluster Info Page
- Key Visualizer Page
- SQL Statements Analysis
- Slow Queries Page
- Cluster Diagnostics
- Search Logs Page
- Profile Instances Page
- TiDB Dashboard
- Command Line Flags
- Configuration File Parameters
- System Variables
- Storage Engines
- Errors Codes
- TiCDC Overview
- TiCDC Open Protocol
- Table Filter
- Schedule Replicas by Topology Labels
- Release Notes
- All Releases
- TiDB Roadmap
The precision math support in TiDB is consistent with MySQL. For more information, see Precision Math in MySQL.
The scope of precision math for exact-value operations includes the exact-value data types (integer and DECIMAL types) and exact-value numeric literals. Approximate-value data types and numeric literals are handled as floating-point numbers.
Exact-value numeric literals have an integer part or fractional part, or both. They may be signed. Examples:
Approximate-value numeric literals are represented in scientific notation (power-of-10) with a mantissa and exponent. Either or both parts may be signed. Examples:
Two numbers that look similar might be treated differently. For example,
2.34 is an exact-value (fixed-point) number, whereas
2.34E0 is an approximate-value (floating-point) number.
The DECIMAL data type is a fixed-point type and the calculations are exact. The FLOAT and DOUBLE data types are floating-point types and calculations are approximate.
This section discusses the following topics of the characteristics of the DECIMAL data type (and its synonyms):
- Maximum number of digits
- Storage format
- Storage requirements
The declaration syntax for a DECIMAL column is
DECIMAL(M,D). The ranges of values for the arguments are as follows:
- M is the maximum number of digits (the precision). 1<= M <= 65.
- D is the number of digits to the right of the decimal point (the scale). 1 <= D <= 30 and D must be no larger than M.
The maximum value of 65 for M means that calculations on DECIMAL values are accurate up to 65 digits. This limit of 65 digits of precision also applies to exact-value numeric literals.
Values for DECIMAL columns are stored using a binary format that packs 9 decimal digits into 4 bytes. The storage requirements for the integer and fractional parts of each value are determined separately. Each multiple of 9 digits requires 4 bytes, and any remaining digits left over require some fraction of 4 bytes. The storage required for remaining digits is given by the following table.
|Leftover Digits||Number of Bytes|
For example, a
DECIMAL(18,9) column has 9 digits on each side of the decimal point, so the integer part and the fractional part each require 4 bytes. A
DECIMAL(20,6) column has 14 integer digits and 6 fractional digits. The integer digits require 4 bytes for 9 of the digits and 3 bytes for the remaining 5 digits. The 6 fractional digits require 3 bytes.
DECIMAL columns do not store a leading
+ character or
- character or leading
0 digits. If you insert
+0003.1 into a
DECIMAL(5,1) column, it is stored as
3.1. For negative numbers, a literal
- character is not stored.
DECIMAL columns do not permit values larger than the range implied by the column definition. For example, a
DECIMAL(3,0) column supports a range of
DECIMAL(M,D) column permits at most
M - D digits to the left of the decimal point.
For more information about the internal format of the DECIMAL values, see
mydecimal.go in TiDB souce code.
For expressions with precision math, TiDB uses the exact-value numbers as given whenever possible. For example, numbers in comparisons are used exactly as given without a change in value. In strict SQL mode, if you add an exact data type into a column, a number is inserted with its exact value if it is within the column range. When retrieved, the value is the same as what is inserted. If strict SQL mode is not enabled, truncation for INSERT is permitted in TiDB.
How to handle a numeric expression depends on the values of the expression:
- If the expression contains any approximate values, the result is approximate. TiDB evaluates the expression using floating-point arithmetic.
- If the expression contains no approximate values are present, which means only exact values are contained, and if any exact value contains a fractional part, the expression is evaluated using DECIMAL exact arithmetic and has a precision of 65 digits.
- Otherwise, the expression contains only integer values. The expression is exact. TiDB evaluates the expression using integer arithmetic and has a precision the same as BIGINT (64 bits).
If a numeric expression contains strings, the strings are converted to double-precision floating-point values and the result of the expression is approximate.
Inserts into numeric columns are affected by the SQL mode. The following discussions mention strict mode and
ERROR_FOR_DIVISION_BY_ZERO. To turn on all the restrictions, you can simply use the
TRADITIONAL mode, which includes both strict mode values and
SET sql_mode = 'TRADITIONAL`;
If a number is inserted into an exact type column (DECIMAL or integer), it is inserted with its exact value if it is within the column range. For this number:
- If the value has too many digits in the fractional part, rounding occurs and a warning is generated.
- If the value has too many digits in the integer part, it is too large and is handled as follows:
- If strict mode is not enabled, the value is truncated to the nearest legal value and a warning is generated.
- If strict mode is enabled, an overflow error occurs.
To insert strings into numeric columns, TiDB handles the conversion from string to number as follows if the string has nonnumeric contents:
- In strict mode, a string (including an empty string) that does not begin with a number cannot be used as a number. An error, or a warning occurs.
- A string that begins with a number can be converted, but the trailing nonnumeric portion is truncated. In strict mode, if the truncated portion contains anything other than spaces, an error, or a warning occurs.
By default, the result of the division by 0 is NULL and no warning. By setting the SQL mode appropriately, division by 0 can be restricted. If you enable the
ERROR_FOR_DIVISION_BY_ZERO SQL mode, TiDB handles division by 0 differently:
- In strict mode, inserts and updates are prohibited, and an error occurs.
- If it's not in the strict mode, a warning occurs.
In the following SQL statement:
INSERT INTO t SET i = 1/0;
The following results are returned in different SQL modes:
|''||No warning, no error; i is set to NULL.|
|strict||No warning, no error; i is set to NULL.|
|Warning, no error; i is set to NULL.|
|strict, ||Error; no row is inserted.|
The result of the
ROUND() function depends on whether its argument is exact or approximate:
For exact-value numbers, the
ROUND()function uses the “round half up” rule.
For approximate-value numbers, the results in TiDB differs from that in MySQL:
TiDB > SELECT ROUND(2.5), ROUND(25E-1); +------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 3 | +------------+--------------+ 1 row in set (0.00 sec)
For inserts into a DECIMAL or integer column, the rounding uses round half away from zero.
TiDB > CREATE TABLE t (d DECIMAL(10,0)); Query OK, 0 rows affected (0.01 sec) TiDB > INSERT INTO t VALUES(2.5),(2.5E0); Query OK, 2 rows affected, 2 warnings (0.00 sec) TiDB > SELECT d FROM t; +------+ | d | +------+ | 3 | | 3 | +------+ 2 rows in set (0.00 sec)