MySQL中的数据类型

MySQL中的数据类型

1、数字类型

1.1 BIT[(M)]

M表示宽度，也是实际长度(会填充0)，同时也是在存储上占用的空间(BIT(1)占1位)，取值范围在1～64之间，省略的时候默认为1。默认情况下，值为二进制字符串，用0bval或者b’val’或者B’val’，但0Bval是错误的。

虽然此类型很省空间，但是会出现一些意想不到的问题，因为mysql的类型检查不严格，可能会出现字符和整型混乱的情况。

CREATE TABLE `bit_demo` (
  `column_1` bit(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `temp_bit_demo` (
  `column_1` bit(1) DEFAULT NULL,
  KEY `idx_column_1` (`column_1`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

上图中字段有索引和没有索引的情况下，针对字符串的处理是不一样的。

1.2 TINYINT[(M)] [UNSIGEND] [ZEROFILL]

整数型，无符号的取值范围是0～255(2^8-1)，有符号范围是-128～127。

CREATE TABLE `number_demo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `column_1` tinyint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

CREATE TABLE `temp_number_demo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `column_1` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3;

表示布尔值时，0表示false，非0表示true，通常只用0，1表示false和true。

1.3 SMALLINT[(M)] [UNSIGEND] [ZEROFILL]

整数型，无符号的取值范围是0～65535(2^16-1)，有符号范围是 -32768～32767。

1.4 MEDIUMINT[(M)] [UNSIGEND] [ZEROFILL]

整数型，无符号的取值范围是0～16777215(2^24-1)，有符号范围是 -8388608～8388607。

1.5 INT[(M)] [UNSIGEND] [ZEROFILL]

整数型，无符号的取值范围是 0～4294967295(2^32-1)，有符号范围是 -2147483648～2147483647。

1.6 INTEGER[(M)] [UNSIGEND] [ZEROFILL]

同INT类型。

1.7 BIGINT[(M)] [UNSIGEND] [ZEROFILL]

整数型，无符号取值范围是0～18446744073709551615(2^64-1)，有符号范围是 -9223372036854775808～9223372036854775807。

注意事项：

两个大整数进行加、减、乘运算时，有可能导致溢出，从而得到异常结果9223372036854775807。

1.8 DECIMAL[(M[, D])] [UNSIGEND] [ZEROFILL]

可以精确的表示小数，M是总位数，D是小数位数(小数点后的位数)，小数点和-符号(负数)不计入M中。M的最大值为65，D的最大值为30，如果M省略默认为10，D省略默认为0。

注意事项：

​ 指定小数位，超出的部分会被自动忽略；

​ 从8.0.17版本开始，不建议使用UNSIGEND DECIMAL，未来版本会删除。

1.9 FLOAT[(M, D)] [UNSIGEND] [ZEROFILL]

单精度浮点数，取值范围是-3.402823466E+38～-1.175494351E-38、 0和1.175494351E-38～3.402823466E+38，实际范围可能小于此，具体取决于系统和硬件。

M是总位数，D是小数点后的位数。如果省略M和D，则值将存储到硬件允许的限制内。单精度浮点数大约精确到小数点后7位。

使用此类型可能有意想不到的问题，因此不建议使用。

1.10 DOUBLE[(M)] [UNSIGEND] [ZEROFILL]

双精度浮点数， 取值范围是-1.7976931348623157E+308～-2.2250738585072014E-308、 0和 2.2250738585072014E-308～1.7976931348623157E+308，实际范围可能小于此，具体取决于系统和硬件。

M是总位数，D是小数点后的位数。如果省略M和D，则值将存储到硬件允许的限制内。双精度浮点数精确到大约15位小数。

MySQL Server 8.0.17 deprecated the display width for the TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT data types when the ZEROFILL modifier is not used, and MySQL Server 8.0.19 has removed the display width for those data types from results of SHOW CREATE TABLE, SHOW CREATE FUNCTION, and queries on INFORMATION_SCHEMA.COLUMNS,INFORMATION_SCHEMA.ROUTINES, and INFORMATION_SCHEMA.PARAMETERS (except for the display width for signed TINYINT(1)). This patch adjusts Connector/J to those recent changes of MySQL Server and, as a result, DatabaseMetaData, ParameterMetaData, and ResultSetMetaData now report identical results for all the above-mentioned integer types and also for the FLOAT and DOUBLE data types. (Bug #30477722)（从8.0.17开始，TINYINT, SMALLINT, MEDIUMINT, INT和 BIGINT类型显示长度不再使用）

2、日期和时间类型

2.1 DATE

日期，范围是1000-01-01～9999-12-31，mysql以yyyy-MM-DD显示值，支持字符串或者数字的值。

2.2 DATETIME[(fsp)]

日期和时间的组合，范围是1000-01-01 00:00:00.000000～9999-12-31 23:59:59.499999，mysql以YYYY-MM-DD hh:mm:ss[.fraction]显示值，支持字符串或者数字的值，提供自动初始化和更新到当前日期和时间。fsp在0～6的范围内的来指定小数秒精度，0表示没有小数部分，如果省略，则默认精度为0。

2.3 TIMESTAMP[(fsp)]

时间戳，范围是'1970-01-01 00:00:01.000000'UTC～'2038-01-19 03:14:07.499999'UTC，提供自动初始化和更新到当前日期和时间，检索时会转换成当前时区时间。fsp在0～6的范围内的来指定小数秒精度，0表示没有小数部分，如果省略，则默认精度为0。

2.4 TIME[(fsp)]

时间，范围是-838:59:59.000000～838:59:59.000000，mysql以hh:mm:ss [fraction]显示值，支持字符串或者数字的值。fsp在0～6的范围内的来指定小数秒精度，0表示没有小数部分，如果省略，则默认精度为0。

2.5 YEAR[(4)]

4位数字的年份，mysql以y y y y显示值，支持字符串或者数字的值。

3、字符串类型

3.1 CHAR

固定长度字符串，长度范围是0～255。存储时会在末尾填充空格补齐(即按设置的长度存储)，检索时会删除尾随空格(PAD_CHAR_TO_FULL_LENGTH模式下除外)，非严格模式下，超长会截断保存并警告，严格模式下会抛出"Data too long"的error，mysql默认情况下开启的是STRICT_TRANS_TABLES模式。

3.2 VARCHAR

可变长度字符串，长度范围是0～65536，在存储时不会被填充(即按实际长度存储，最终存储的时候还有其他处理)，检索和存储时都会保留右侧空格。

注意事项：

​ 严格模式下，插入超长的字符串时，如果超长的部分是空格，会忽略空格插入，否则error；

​ 如果没超长的情况下，值会保留右侧空格；

3.3 BINARY

固定字节字符串，长度是0～65535，与varchar不同的是，binary存储的是字节字符串。

3.4 VARBINARY

可变字节字符串，长度是0～65535，与binary类似，存储的是字节字符串。

ps:0x00和 space 的比较不同， 0x00排序在 space 之前

3.5 BLOB

二进制字符串。

数据类型	最大长度
tinyblob	255(2^8-1)
blob	65536(2^16-1)
mediumblob	2^24-1
longblob	2^32-1

ps：排序是按照字节逐位对比的 ；

使用临时表处理的查询结果中的 实例BLOB 列会导致服务器使用磁盘上的表而不是内存中的表，因为存储引擎不支持这些数据类型，使用磁盘会导致性能损失；

3.6 TEXT

字符串类型。

数据类型	最大长度
tinytext	255(2^8-1)
text	65535(2^16-1)
mediumtext	2^24-1
longtext	2^32-1

PS:

​ 1、对于BLOB和 TEXT列上的索引，必须指定索引前缀长度；

​ 2、BLOB和TEXT列不能有默认值；

​ 3、每个BLOB和TEXT值在内部由单独分配的对象表示。这与所有其他数据类型相反，在打开表时，为每列分配一次存储空间；

3.7 ENUM

字符串对象，其值是在创建表时在列规范中显式枚举的，最多可以有65535个值。

Enum根据索引号进行排序。

3.8 SET

字符串对象，可以有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择。

4、空间类型

• GEOMETRY 是所有空间集合的父类
  • POINT(m, n)，坐标点，支持经纬度；
  • LINESTRING(m1 n1, m2 n2, …)， 由多个点连接而成的线，也可以首位相连组成多边形；
  • POLYGON((m1 n1, m2 n2…), (m21 n21, m22 n22, …))，由多个线组成的多边形(集合)；
  • MULTIPOINT(m1 n1),(m2 n2)…，也可以表示成MULTIPOINT(m1 n1, m2 n2, …)，点的集合；
  • MUILTLINESTRING((m1 n1, m2, n2…), (m21 n21, m22 n22…))，线的集合；
  • MULTIPOLOYGON(((m1 n1, m2 n2…)), ((m21 n21, m22 n22…), (m31 n31, m32 n32…)))，多边形的集合；
  • GEOMETRYCOLLECTION(POINT(m1 n1), LINESTRING(m2 n2, m3 n3), POLYGON((m4 n4, m5 n5…)))，空间类数据集合；

CREATE TABLE `gis_demo` (
  `id` int NOT NULL AUTO_INCREMENT,
  `column_1` point DEFAULT NULL,
  `column_2` linestring DEFAULT NULL,
  `column_3` polygon DEFAULT NULL,
  `column_4` multipoint DEFAULT NULL,
  `column_5` multilinestring DEFAULT NULL,
  `column_6` multipolygon DEFAULT NULL,
  `column_7` geomcollection DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO gis_demo(column_1, column_2, column_3, column_4, column_5, column_6, column_7) VALUES (point(10, 10), ST_GeomFromText('linestring(10 10, 20 10, 20 20, 10 20, 10 10)'), ST_GeomFromText('polygon((10 10, 20 10, 20 20, 10 20, 10 10), (0 0, 30 0, 30 30, 0 30, 0 0))'),
  ST_GeomFromText('multipoint(10 20, 20 30)'), ST_GeomFromText('multilinestring((10 10, 20 10, 20 20, 10 20, 10 10), (5 5, 10 10, 15 15, 20 20))'), ST_GeomFromText('multipolygon(((10 10, 20 10, 20 20, 10 20, 10 10), (0 0, 30 0, 30 30, 0 30, 0 0)), ((40 40, 60 60, 60 80, 40 40)))'),
 ST_GeomFromText('geometrycollection(point(2 2), linestring(4 4, 5 5, 6 6))'));

SELECT id, st_astext(column_1), st_astext(column_2), st_astext(column_3), st_astext(column_4), st_astext(column_5), st_astext(column_6), st_astext(column_7) from gis_demo;

5、json类型

会自动校验存入的值是否为标准的json格式，相对于文本格式存储json数据优化存储格式，可以直接通过键或数组索引查找子对象或嵌套值。