- Introduction
- Concepts
- Architecture
- Key Features
- Horizontal Scalability
- MySQL Compatible Syntax
- Replicate from and to MySQL
- Distributed Transactions with Strong Consistency
- Cloud Native Architecture
- Minimize ETL with HTAP
- Fault Tolerance & Recovery with Raft
- Automatic Rebalancing
- Deployment and Orchestration with Ansible, Kubernetes, Docker
- JSON Support
- Spark Integration
- Read Historical Data Without Restoring from Backup
- Fast Import and Restore of Data
- Hybrid of Column and Row Storage
- SQL Plan Management
- Open Source
- Online Schema Changes
- How-to
- Get Started
- Deploy
- Hardware Recommendations
- From Binary Tarball
- Orchestrated Deployment
- Geographic Redundancy
- Data Migration with Ansible
- Configure
- Secure
- Transport Layer Security (TLS)
- Generate Self-signed Certificates
- Monitor
- Migrate
- Maintain
- Scale
- Upgrade
- Troubleshoot
- Reference
- SQL
- MySQL Compatibility
- SQL Language Structure
- Data Types
- Functions and Operators
- Function and Operator Reference
- Type Conversion in Expression Evaluation
- Operators
- 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
- Miscellaneous Functions
- Precision Math
- SQL Statements
ADD COLUMN
ADD INDEX
ADMIN
ADMIN CANCEL DDL
ADMIN CHECKSUM TABLE
ADMIN CHECK [TABLE|INDEX]
ADMIN SHOW DDL [JOBS|QUERIES]
ALTER DATABASE
ALTER TABLE
ALTER USER
ANALYZE TABLE
BEGIN
CHANGE COLUMN
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP TABLE
DROP USER
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RENAME INDEX
RENAME TABLE
REPLACE
REVOKE <privileges>
ROLLBACK
SELECT
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW [FULL] FIELDS FROM
SHOW GRANTS
SHOW INDEXES [FROM|IN]
SHOW INDEX [FROM|IN]
SHOW KEYS [FROM|IN]
SHOW PRIVILEGES
SHOW [FULL] PROCESSSLIST
SHOW SCHEMAS
SHOW STATUS
SHOW [FULL] TABLES
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
START TRANSACTION
TRACE
TRUNCATE
UPDATE
USE
- Constraints
- Generated Columns
- Character Set
- Configuration
- Security
- Transactions
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Performance
- Key Monitoring Metrics
- Alert Rules
- Best Practices
- TiSpark
- TiDB Binlog
- Tools
- Overview
- Use Cases
- Download
- Mydumper
- Syncer
- Loader
- TiDB Data Migration
- TiDB Lightning
- sync-diff-inspector
- PD Control
- PD Recover
- TiKV Control
- TiDB Control
- FAQs
- Support
- Contribute
- Releases
- All Releases
- v2.1
- v2.0
- v1.0
- Glossary
You are viewing the documentation of an older version of the TiDB database (TiDB v2.1).
Precision Math
The precision math support in TiDB is consistent with MySQL. For more information, see Precision Math in MySQL.
Numeric types
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: 1
, .2
, 3.4
, -5
, -6.78
, +9.10
.
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: 1.2E3
, 1.2E-3
, -1.2E3
, -1.2E-3
.
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.
DECIMAL data type characteristics
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 |
---|---|
0 | 0 |
1–2 | 1 |
3–4 | 2 |
5–6 | 3 |
7–9 | 4 |
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 -999
to 999
. A 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.
Expression handling
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 ERROR_FOR_DIVISION_BY_ZERO
:
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:
sql_mode Value | Result |
---|---|
'' | No warning, no error; i is set to NULL. |
strict | No warning, no error; i is set to NULL. |
ERROR_FOR_DIVISION_BY_ZERO | Warning, no error; i is set to NULL. |
strict, ERROR_FOR_DIVISION_BY_ZERO | Error; no row is inserted. |
Rounding behavior
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)