gogoWebsite

TiDB data type

Updated to 17 hours ago

Data Type Overview

TiDB supports space deletion type(SPATIAL)All MySQL data types except numeric type, string type, time and date type, and JSON type.

The data type definition is generally T(M[, D]),in:

  • T Indicates the specific type.
  • MIn the integer type, the maximum display length is represented; the precision is represented in floating point or fixed point number; the maximum length is represented in the character type. The maximum value of M depends on the specific type.
  • DDenotes the length of the decimal places of floating point numbers and fixed point numbers.
  • fspIn the time and date types TIME、 DATETIMEas well as TIMESTAMP The accuracy of seconds is represented, and its value range is 0 to 6. Value is 0
    Indicates that there is no decimal part. If omitted, the default accuracy is 0.

Default value for data type

In a data type descriptionDEFAULT value The paragraph describes the default value of a column. This default value must be a constant, not a function or an expression. However, for time types, exceptions can be usedNOW 、CURRENT_TIMESTAMP 、LOCALTIME 、LOCALTIMESTAMP Equal function asDATETIME orTIMESTAMP The default value of .

BLOB 、TEXT as well asJSON The default value cannot be set.

If there is no column definition DEFAULT settings. TiDB is determined according to the following rules:

  • If this type can be used NULL As a value, this column will add implicit default value settings when defining DEFAULT NULL。
  • If this type is unavailable NULL As a value, this column will not add implicit default value settings when defined.

For a setting NOT NULL But there is no explicit setting DEFAULT column, when INSERT 、 REPLACE When no value of the column is involved, TiDB is based on the SQL_MODE Perform different behaviors:

  • If this isstrict sql mode , Statements in transactions will cause transactions to fail and roll back, and statements in non-transactions will directly report errors.
  • If this is not strict sql mode , TiDB assigns this column value as the implicit default value of the column data type.

At this time, the implicit default value is set according to the following rules:

  • For numeric types, their default value is 0. When there isAUTO_INCREMENTWhen parameters are used, the default value will be assigned the correct value according to the increment.
  • For datetime types other than timestamps, the default value will be the "zero value" of that type. The default value of the timestamp type will be the current time.
  • For string types other than enumerations, the default value will be an empty string. For enum types, the default value is the first value in the enum.

Numerical type

TiDB supports all numerical types of MySQL, which can be divided into:

  • Integer type (exact value)
  • Floating point type (approximate value)
  • Fixed point type (exact value)

Integer Type

TiDB supports all MySQL integer types, including INTEGER/INT、TINYINT、SMALLINT、MEDIUMINTas well as BIGINT, full information referenceThis articledocument.
Field description:

Syntax elements illustrate
M Type display width, optional
UNSIGNED Unsigned number, if this flag is not added, it is a signed number
ZEROFILL Complement zero mark. If there is this mark, TiDB will automatically add UNSIGNED mark to the type, but there is no operation to fill zero.

Type definition

BITtype

Bit value type. M represents the length of the bit, with a value ranging from 1 to 64, and its default value is 1.

BIT[(M)]
BOOLEANtype

Boolean type, aliasBOOL,andTINYINT(1)equivalence. The zero value is consideredFalse, non-zero value is consideredTrue. Inside TiDB,TrueStore as1，FalseStore as0。

BOOLEAN
TINYINTtype

TINYINTtype. The range of signed numbers is[-128, 127]. The range of unsigned numbers is[0, 255] 。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
SMALLINTtype

SMALLINTtype. The range of signed numbers is[-32768, 32767]. The range of unsigned numbers is[0, 65535]。

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
MEDIUMINTtype

MEDIUMINTtype. The range of signed numbers is[-8388608, 8388607]. The range of unsigned numbers is[0, 16777215]。

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
INTEGERtype

INTEGERType, aliasINT. The range of signed numbers is[-2147483648, 2147483647]. The range of unsigned numbers is[0, 4294967295]。

INT[(M)] [UNSIGNED] [ZEROFILL]

or:

INTEGER[(M)] [UNSIGNED] [ZEROFILL]
BIGINTtype

BIGINTtype. The range of signed numbers is[-9223372036854775808, 9223372036854775807]. The range of unsigned numbers is[0, 18446744073709551615]。

BIGINT[(M)] [UNSIGNED] [ZEROFILL]
Storage space and value range

The requirements for storage space of each type and the maximum/minimum values ​​are shown in the following table:

type Storage space Minimum value (signed/unsigned) Maximum value (signed/unsigned)
TINYINT 1 -128 / 0 127 / 255
SMALLINT 2 -32768 / 0 32767 / 65535
MEDIUMINT 3 -8388608 / 0 8388607 / 16777215
INT 4 -2147483648 / 0 2147483647 / 4294967295
BIGINT 8 -9223372036854775808 / 0 9223372036854775807 / 18446744073709551615

Floating point type

TiDB supports all MySQL floating point types, including FLOAT, DOUBLE, for complete information referenceThis articleblog.
Field description:

Syntax elements illustrate
M Total decimal places
D Number of digits after decimal point
UNSIGNED Unsigned number, if this flag is not added, it is a signed number
ZEROFILL Complement zero mark. If there is this mark, TiDB will automatically add UNSIGNED mark to the type.

Type definition

FLOATtype

Single precision floating point number. The allowed values ​​range from -2^128 to +2^128, i.e. -3.402823466E+38 to -1.175494351E-38, 0 and 1.175494351E-38 to 3.402823466E+38. These are theoretical limitations based on the IEEE standard. The actual range may be slightly smaller depending on the hardware or operating system.

FLOAT( p )Among the types,pRepresentation accuracy (in digits). Use only this value to determine whether the data type of the result column isFLOATorDOUBLE. ifpFrom 0 to 24, the data type becomes without M or D valuesFLOAT. ifpFrom 25 to 53, the data type becomes without M or D valuesDOUBLE. Result column range and single precision described earlier in this sectionFLOATOr double precisionDOUBLEThe data type is the same.

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
FLOAT(p) [UNSIGNED] [ZEROFILL]

Capital warning:
As in MySQL, the FLOAT data type stores approximations. For precise values ​​such as currency, it is recommended to use the DECIMAL type.

DOUBLEtype

Double precision floating point number, aliasDOUBLE PRECISION. The allowed values ​​range are: -2^1024 ~ +2^1024, i.e. -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are theoretical limitations based on the IEEE standard. The actual range may be slightly smaller depending on the hardware or operating system.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE PRECISION [(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

Capital warning:
As in MySQL, the DOUBLE data type stores approximations. For precise values ​​such as currency, it is recommended to use the DECIMAL type.

Storage space

The requirements for storage space for each type are shown in the following table:

type Storage space
FLOAT 4
FLOAT§ If 0 <= p <= 24 is 4 bytes, if 25 <= p <= 53 is 8 bytes
DOUBLE 8

Fixed-point type

TiDB supports all fixed-point types of MySQL, including DECIMAL and NUMERIC. For complete information, please refer to this blog.
Field description:

Syntax elements illustrate
M Total decimal places
D Number of digits after decimal point
UNSIGNED Unsigned number, if this flag is not added, it is a signed number
ZEROFILL Complement zero mark. If this mark is present, TiDB will automatically add UNSIGNED mark to the type.

Type definition

DECIMALtype

Fixed point number, aliasNUMERIC. M is the total number of decimal places (precision), and D is the number of digits following the decimal point (scaling). The decimal points and -(negative) symbols are not included in M. If D is 0, the value has no decimal point or fractional part. If D is omitted, the default is 0. If M is omitted, the default is 10.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]

Date and time type

TiDB supports all date and time types in MySQL, including DATE, DATETIME, TIMESTAMP, TIME and YEAR, for complete information referenceThis articledocument.

Type definition

DATEtype

DATEThe format of the type isYYYY-MM-DD, the supported scope is1000-01-01arrive 9999-12-31。

DATE
TIMEtype

TIMEThe format of the type isHH:MM:SS[.fraction], the supported scope is-838:59:59.000000arrive 838:59:59.000000. TIME can be used not only to indicate the time of day, but also to refer to the time interval between two events.fspThe parameters represent the second accuracy, with a value range of: 0 ~ 6, and the default value is 0.

TIME[(fsp)]

Note in capitalization:

Note the abbreviation of TIME. For example, ‘11:12’ means ‘11:12:00’ instead of ‘00:11:12’. However, ’1112’ means ‘00:11:12’. These differences depend on the existence of the : character.

DATETIMEtype

DATETIMEType is a combination of date and time, in the formatYYYY-MM-DD HH:MM:SS[.fraction]. The supported scope is1000-01-01 00:00:00.000000arrive9999-12-31 23:59:59.000000。fspThe parameters represent the accuracy of seconds, with a value range of 0~6, and the default value is 0.

DATETIME[(fsp)]
TIMESTAMPtype

TIMESTAMPThe type contains date and time, and the supported range is1970-01-01 00:00:01.000000arrive2038-01-19 03:14:07.999999。fspThe parameters represent the accuracy of seconds, with a value range of 0~6, and the default value is 0. existTIMESTAMPIn , zeros are not allowed to appear in the month or date section, with the only exception being the zero value itself ‘0000-00-00 00:00:00’.

TIMESTAMP[(fsp)]
YEARtype

YEARThe format of the type is ‘YYYY’, and the supported values ​​range are 1901 to 2155, or zero value 0000.

YEAR[(4)]

String type

TiDB supports all MySQL string types, includingCHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUMas well asSET, full information referenceThis articledocument.

Type definition

CHARtype

Fixed-length string.CHARThe length of the column is fixed to the length declared when the table is created. The length can be any value from 0 to 255. When saving CHAR values, fill in spaces to the right of them to the specified length.

[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
VARCHARtype

Variable length string. M represents the maximum column length, ranging from 0 to 65535. The maximum actual length of VARCHAR is determined by the size of the longest line and the character set used.

[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]
TEXTtype

Text string. M represents the maximum column length, ranging from 0 to 65535. The maximum actual length of a TEXT is determined by the size of the longest line and the character set used.

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]
TINYTEXTtype

Similar toTEXT, the difference is that the maximum column length is 255.

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
MEDIUMTEXTtype

Similar toTEXT, the difference is that the maximum column length is 16,777,215.

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
LONGTEXTtype

Similar toTEXT, the difference is that the maximum column length is 4,294,967,295.

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]
BINARYtype

Similar toCHAR, the difference isBINARYIt stores binary strings.

BINARY(M)
VARBINARYtype

Similar toVARCHAR, the difference isVARBINARYIt stores binary strings.

VARBINARY(M)
TINYBLOBtype

Similar toBLOB, the difference is that the maximum column length is 255.

TINYBLOB
BLOBtype

Large binary file.MIndicates the maximum column length, ranging from 0 to 65535.

BLOB[(M)]
MEDIUMBLOBtype

Similar toBLOB, the difference is that the maximum column length is 16,777,215.

MEDIUMBLOB
LONGBLOBtype

Similar toBLOB, the difference is that the maximum column length is 4,294,967,295.

LONGBLOB
ENUMtype

An enum type is a string that can only have a string object with one value. Its value must be selected from a fixed set, which is defined when creating a table. The syntax is:

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

For example:

ENUM('apple', 'orange', 'pear')

Values ​​of enum types are stored in TiDB using numeric values. Each value is converted into a number in the defined order. For example, in the above example, each string value will be mapped to a number:

value number
NULL NULL
" 0
‘apple’ 1
‘orange’ 2
‘pear’ 3

More information referenceMySQL enumeration documentation。

SETtype

A collection type is a string containing zero or more values, where each value must be selected from a fixed set. This fixed set is defined when creating a table. The syntax is:

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

For example:

SET('1', '2') NOT NULL

In the above example, the valid values ​​of this column can be:

''
'1'
'2'
'1,2'

The value of the collection type is converted into an Int64 value inside TiDB. Whether each element exists is represented by a 0/1 value of a binary bit, such as this exampleSET('a','b','c','d'), each element is mapped as a number, and the binary representation of each number will only have one of 1:

member Decimal representation Binary representation
‘a’ 1 0001
‘b’ 2 0010
‘c’ 4 0100
‘d’ 8 1000

In this way, for elements with the value (‘a’, ‘c’), their binary representation is 0101.

More information referenceMySQL Collection Documentation。

JSONtype

The JSON type can store semi-structured data like JSON. Compared to directly storing JSON as a string, its advantage is that:

  1. Use Binary format for serialization to speed up query and parse of JSON's internal fields;
  2. With the extra steps to verify JSON legality, only legitimate JSON documents can be put into this field;

The JSON field itself cannot create an index. Instead, you can create an index on a subfield in a JSON document. For example:

CREATE TABLE city (
    id INT PRIMARY KEY,
    detail JSON,
    population INT AS (JSON_EXTRACT(detail, '$.population'))
);
INSERT INTO city (id,detail) VALUES (1, '{"name": "Beijing", "population": 100}');
SELECT id FROM city WHERE population >= 100;