- 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
- Common Ansible Operations
- Backup and Restore
- Use BR (recommended)
- Identify Abnormal Queries
- Scale
- Upgrade
- Troubleshoot
- Reference
- SQL
- MySQL Compatibility
- SQL Language Structure
- Attributes
- 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
- Window Functions
- Miscellaneous Functions
- Precision Math
- List of Expressions for Pushdown
- 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 INSTANCE
ALTER TABLE
ALTER USER
ANALYZE TABLE
BEGIN
CHANGE COLUMN
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE TABLE LIKE
CREATE TABLE
CREATE USER
CREATE VIEW
DEALLOCATE
DELETE
DESC
DESCRIBE
DO
DROP COLUMN
DROP DATABASE
DROP INDEX
DROP ROLE
DROP TABLE
DROP USER
DROP VIEW
EXECUTE
EXPLAIN ANALYZE
EXPLAIN
FLUSH PRIVILEGES
FLUSH STATUS
FLUSH TABLES
GRANT <privileges>
GRANT <role>
INSERT
KILL [TIDB]
LOAD DATA
LOAD STATS
MODIFY COLUMN
PREPARE
RECOVER TABLE
RENAME INDEX
RENAME TABLE
REPLACE
REVOKE <privileges>
REVOKE <role>
ROLLBACK
SELECT
SET DEFAULT ROLE
SET [NAMES|CHARACTER SET]
SET PASSWORD
SET ROLE
SET TRANSACTION
SET [GLOBAL|SESSION] <variable>
SHOW ANALYZE STATUS
SHOW CHARACTER SET
SHOW COLLATION
SHOW [FULL] COLUMNS FROM
SHOW CREATE TABLE
SHOW CREATE USER
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 REGIONS
SHOW TABLE STATUS
SHOW [GLOBAL|SESSION] VARIABLES
SHOW WARNINGS
SPLIT REGION
START TRANSACTION
TRACE
TRUNCATE
UPDATE
USE
- Constraints
- Generated Columns
- Partitioning
- Character Set
- SQL Mode
- Views
- Configuration
- Security
- Transactions
- System Databases
- Errors Codes
- Supported Client Drivers
- Garbage Collection (GC)
- Performance
- Overview
- Understanding the Query Execution Plan
- The Blocklist of Optimization Rules and Expression Pushdown
- Introduction to Statistics
- TopN and Limit Push Down
- Optimizer Hints
- Follower Read
- Check the TiDB Cluster Status Using SQL Statements
- Execution Plan Binding
- Statement Summary Table
- Tune TiKV
- Operating System Tuning
- Column Pruning
- Key Monitoring Metrics
- Alert Rules
- Best Practices
- TiSpark
- TiKV
- TiFlash
- TiDB Binlog
- Tools
- Overview
- Use Cases
- Download
- TiDB Operator
- Table Filter
- Backup & Restore (BR)
- Mydumper
- Syncer
- Loader
- Data Migration
- TiDB Lightning
- sync-diff-inspector
- PD Control
- PD Recover
- TiKV Control
- TiDB Control
- TiDB in Kubernetes
- FAQs
- Support
- Contribute
- Releases
- All Releases
- v3.1
- v3.0
- v2.1
- v2.0
- v1.0
- Glossary
You are viewing the documentation of an older version of the TiDB database (TiDB v3.1).
SQL Mode
TiDB servers operate in different SQL modes and apply these modes differently for different clients. SQL mode defines the SQL syntaxes that TiDB supports and the type of data validation check to perform, as described below:
After TiDB is started, modify SET [ SESSION | GLOBAL ] sql_mode='modes'
to set SQL mode.
Ensure that you have SUPER
privilege when setting SQL mode at GLOBAL
level, and your setting at this level only affects the connections established afterwards. Changes to SQL mode at SESSION
level only affect the current client.
Modes
are a series of different modes separated by commas (','). You can use the SELECT @@sql_mode
statement to check the current SQL mode. The default value of SQL mode: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION
.
Important sql_mode
values
ANSI
: This mode complies with standard SQL. In this mode, data is checked. If data does not comply with the defined type or length, the data type is adjusted or trimmed and awarning
is returned.STRICT_TRANS_TABLES
: Strict mode, where data is strictly checked. When any incorrect data is inserted into a table, an error is returned.TRADITIONAL
: In this mode, TiDB behaves like a "traditional" SQL database system. An error instead of a warning is returned when any incorrect value is inserted into a column. Then, theINSERT
orUPDATE
statement is immediately stopped.
SQL mode table
Name | Description |
---|---|
PIPES_AS_CONCAT | Treats "||" as a string concatenation operator (+ ) (the same as CONCAT() ), not as an OR (full support) |
ANSI_QUOTES | Treats " as an identifier. If ANSI_QUOTES is enabled, only single quotes are treated as string literals, and double quotes are treated as identifiers. Therefore, double quotes cannot be used to quote strings. (full support) |
IGNORE_SPACE | If this mode is enabled, the system ignores space. For example: "user" and "user " are the same. (full support) |
ONLY_FULL_GROUP_BY | If a non-aggregated column that is referred to in SELECT , HAVING , or ORDER BY is absent in GROUP BY , this SQL statement is invalid, because it is abnormal for a column to be absent in GROUP BY but displayed by query. (full support) |
NO_UNSIGNED_SUBTRACTION | Does not mark the result as UNSIGNED if an operand has no symbol in subtraction. (full support) |
NO_DIR_IN_CREATE | Ignores all INDEX DIRECTORY and DATA DIRECTORY directives when a table is created. This option is only useful for secondary replication servers (syntax support only) |
NO_KEY_OPTIONS | When you use the SHOW CREATE TABLE statement, MySQL-specific syntaxes such as ENGINE are not exported. Consider this option when migrating across DB types using mysqldump. (syntax support only) |
NO_FIELD_OPTIONS | When you use the SHOW CREATE TABLE statement, MySQL-specific syntaxes such as ENGINE are not exported. Consider this option when migrating across DB types using mysqldump. (syntax support only) |
NO_TABLE_OPTIONS | When you use the SHOW CREATE TABLE statement, MySQL-specific syntaxes such as ENGINE are not exported. Consider this option when migrating across DB types using mysqldump. (syntax support only) |
NO_AUTO_VALUE_ON_ZERO | If this mode is enabled, when the value passed in the AUTO_INCREMENT column is 0 or a specific value, the system directly writes this value to this column. When NULL is passed, the system automatically generates the next serial number. (full support) |
NO_BACKSLASH_ESCAPES | If this mode is enabled, the \ backslash symbol only stands for itself. (full support) |
STRICT_TRANS_TABLES | Enables the strict mode for the transaction storage engine and rolls back the entire statement after an illegal value is inserted. (full support) |
STRICT_ALL_TABLES | For transactional tables, rolls back the entire transaction statement after an illegal value is inserted. (full support) |
NO_ZERO_IN_DATE | Strict mode, where dates with a month or day part of 0 are not accepted. If you use the IGNORE option, TiDB inserts '0000-00-00' for a similar date. In non-strict mode, this date is accepted but a warning is returned. (full support) |
NO_ZERO_DATE | Does not use '0000-00-00' as a legal date in strict mode. You can still insert a zero date with the IGNORE option. In non-strict mode, this date is accepted but a warning is returned. (full support) |
ALLOW_INVALID_DATES | In this mode, the system does not check the validity of all dates. It only checks the month value ranging from 1 to 12 and the date value ranging from 1 to 31 . The mode only applies to DATE and DATATIME columns. All TIMESTAMP columns need a full validity check. (full support) |
ERROR_FOR_DIVISION_BY_ZERO | If this mode is enabled, the system returns an error when handling division by 0 in data-change operations (INSERT or UPDATE ). If this mode is not enabled, the system returns a warning and NULL is used instead. (full support) |
NO_AUTO_CREATE_USER | Prevents GRANT from automatically creating new users, except for the specified password (full support) |
HIGH_NOT_PRECEDENCE | The precedence of the NOT operator is such that expressions such as NOT a BETWEEN b AND c are parsed as NOT (a BETWEEN b AND c) . In some older versions of MySQL, this expression is parsed as (NOT a) BETWEEN b AND c . (full support) |
NO_ENGINE_SUBSTITUTION | Prevents the automatic replacement of storage engines if the required storage engine is disabled or not compiled. (syntax support only) |
PAD_CHAR_TO_FULL_LENGTH | If this mode is enabled, the system does not trim the trailing spaces for CHAR types. (syntax support only. This mode has been deprecated in MySQL 8.0.) |
REAL_AS_FLOAT | Treats REAL as the synonym of FLOAT , not the synonym of DOUBLE (full support) |
POSTGRESQL | Equivalent to PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE , NO_KEY_OPTIONS , NO_TABLE_OPTIONS , NO_FIELD_OPTIONS (syntax support only) |
MSSQL | Equivalent to PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE , NO_KEY_OPTIONS , NO_TABLE_OPTIONS , NO_FIELD_OPTIONS (syntax support only) |
DB2 | Equivalent to PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE , NO_KEY_OPTIONS , NO_TABLE_OPTIONS , NO_FIELD_OPTIONS (syntax support only) |
MAXDB | Equivalent to PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE , NO_KEY_OPTIONS , NO_TABLE_OPTIONS , NO_FIELD_OPTIONS , NO_AUTO_CREATE_USER (full support) |
MySQL323 | Equivalent to NO_FIELD_OPTIONS , HIGH_NOT_PRECEDENCE (syntax support only) |
MYSQL40 | Equivalent to NO_FIELD_OPTIONS , HIGH_NOT_PRECEDENCE (syntax support only) |
ANSI | Equivalent to REAL_AS_FLOAT , PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE (syntax support only) |
TRADITIONAL | Equivalent to STRICT_TRANS_TABLES , STRICT_ALL_TABLES , NO_ZERO_IN_DATE , NO_ZERO_DATE , ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER (syntax support only) |
ORACLE | Equivalent to PIPES_AS_CONCAT , ANSI_QUOTES , IGNORE_SPACE , NO_KEY_OPTIONS , NO_TABLE_OPTIONS , NO_FIELD_OPTIONS , NO_AUTO_CREATE_USER (syntax support only) |