MySQL - 数据类型

1. Numeric

BIT(M): M 1 to 64

TINYINT: -128 to 127

BOOL, BOOLEAN: TINYINT(1).

SMALLINT(M): -32768 to 32767

MEDIUMINT(M): -8388608 to 8388607

INT(M): -2147483648 to 2147483647

INTEGER(M): -2147483648 to 2147483647

BIGINT(M): -9223372036854775808 to 9223372036854775807

DECIMAL(M,D): M是总位数（精度），D是小数点后的位数（刻度）。DECIMAL的最大位数（M）为65.支持的最大小数（D）为30.如果省略D，则默认值为0.如果省略M，则默认值为10。

DEC(M,D)

FLOAT(M,D): 一个小的（单精度）浮点数。允许值为-3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38。M是总位数，D是小数点后面的位数。

DOUBLE(M,D): 正常大小（双精度）浮点数。允许值为-1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308.M是总位数，D是小数点后面的位数。

DOUBLE PRECISION(M,D)

 

 

2. Date and Time

DATE

DATETIME

TIMESTAMP

TIME

YEAR

 

 

3. String

CHARACTER SET latin1 COLLATE latin1_general_cs

CHAR(M): M 0 to 255

VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] : 0 to 65,535

BINARY(M)

VARBINARY(M)

TINYBLOB

TINYTEXT[CHARACTER SET charset_name] [COLLATE collation_name]

BLOB

TEXT

MEDIUMBLOB

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

LONGBLOB

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

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

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

 

 

4. Spatial Data Type

GEOMETRY

POINT

LINESTRING

POLYGON

MULTIPOINT

MUTILINESTRING

MULTIPOLYGON

GEOMETRYCOLLECTION

 

 

5. JSON

searching

JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');

JSON_SET('"x"', '$[0]', 'a');