mysql中临时数据类型_MySQL数据类型

整数类型

浮点类型

DECIMAL(M[,D]),NUMERIC

NUMERIC是实现为DECIMAL，因此以下有关的说明DECIMAL同样适用于 NUMERIC。

1.长度限制M是最大位数(精度)。范围是 1 到 65。

D是小数点右边的位数(小数位)。范围是 0 到 30，并且不能大于 M。

如果D省略，则默认值为0。如果 M省略，则默认值为10。

2.存储空间(byte)

例如:

DECIMAL(18,9)列在小数点的任一侧都有9位数字，因此整数部分和小数部分每个都需要4个字节。

DECIMAL(20,6)列有十四个整数位和六个小数位。整数位中的9位需要4个字节，其余5位需要3个字节。六个小数位需要3个字节。

3.舍入行为

M 若超出长度限制限制，则会报错

[Err] 1264 - Out of range value for column 'a' at row 1

D 若超出长度限制则会四舍五入例如:

SET sql_mode = 'TRADITIONAL'; 默认是开启超范围检测的，若关闭则不会报错而是存储下允许的最大值

drop table t;

create table t(a DECIMAL(6,2));

insert into t select 12345.111888;

insert into t select 1234.111888;

insert into t select 1234.119888;

mysql> insert into t select 12345.111888;

ERROR 1264 (22003): Out of range value for column 'a' at row 1

mysql> select * from t;

+---------+| a |

+---------+| 1234.11 |

| 1234.12 |

+---------+2 rows in set (0.00 sec)

FLOAT，DOUBLEFLOAT和DOUBLE类型代表近似数字数据值。

MySQL将四个字节用于单精度值，并将八个字节用于双精度值。

0到23 的将产生 4字节 的单精度浮点列。从 24到53 的精度将产生 8字节 的双精度双列。

从8.0.17开始，不推荐使用FLOAT和DOUBLE类型，在将来会移除掉它们

从8.0.17开始，UNSIGNED不适用于 FLOAT,DOUBLE,DECIMAL类型，在将来会移除掉这个支持

从MySQL 8.0.17开始，AUTO_INCREMENT不支持FLOAT和 DOUBLE列支持，并将在以后的MySQL版本中删除

日期和时间数据类型

日期和时间数据类型用于表示时间值 DATE，TIME，DATETIME，TIMESTAMP，YEAR，每个时间类型都有一个有效值范围，以及一个“ 零 ”值。

可以使用几个SQL_MODE值解除限定，解除限定操作是不推荐的NO_ZERO_IN_DATE

禁止存储零月或零日，类似 '2009-00-00' 或 '2009-01-00' 格式的日期；NO_ZERO_DATE

禁止将“零”值“虚拟日期”存储为'0000-00-00'。在一些情况下，这比使用更方便NULL的值，并且使用较少的数据和索引空间。NO_ZERO_DATE禁用了，即时设置了NO_ZERO_IN_DATE也无法限制月或日可设置为0

8.0.19开始，支持在表中插入TIMESTAMP和 DATETIME值时指定时区偏移量

INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10+05:30')TIMESTAMP和 DATETIME列定义可以为默认值和自动更新值指定一个当前时间戳,要注意explicit_defaults_for_timestamp参数对TIMESTAMP的影响

ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP从MySQL 8.0.19开始，不建议YEAR(4) 使用具有显式显示宽度的数据类型,且不支持YEAR(2)

字符串数据类型

CHAR和VARCHAR类型CHAR长度固定，长度在【0-255】之间

VARCHAR是变长，长度取决于行大小，可以指定为【0-65535】之间，受字符集影响

CHAR和VARCHAR存储比较

BINARY和VARBINARY类型

BINARY和VARBINARY与CHAR和VARCHAR相似，不同的是它们存储二进制字节字符串

BLOB和TEXT类型用于存储大对象，BLOB存储为二进制字符串(字节字符串)，TEXT存储为字符字符串

TEXT列建唯一索引的话，值‘a’与‘a ’会有唯一键冲突

对于BLOB和TEXT类型列上加索引，必须指定索引前缀长度

排序时默认使用前1024字节，可以通过 max_sort_length 设置

临时表存在BLOB和TEXT会导致直接使用磁盘而不是在内存中创建临时表，因为MEMORY存储引擎不支持这些数据类型

ENUM类型ENUM值占用1个字节，例如值 'medium'，100万行数据，存储在ENUM中需要100万字节的存储空间，而存储在varchar列中需要600万字节的存储空间

一个ENUM列最多可包含65,535个不同的元素

如果一ENUM列被声明为允许 NULL，则该NULL值为该列的有效值，默认值为 NULL。如果ENUM 声明了列NOT NULL，则其默认值是允许值列表的第一个元素。

ENUM值根据其索引号排序，索引号取决于列规范中列出的枚举成员的顺序。ENUM('b', 'a')中'b'就排在'a'之前

强烈建议不要使用数字作为枚举值

空间数据类型

JSON数据类型存储在JSON列中的任何JSON文档的大小都限于max_allowed_packet系统变量的值。

在MySQL 8.0.13之前，JSON列不能具有非NULL默认值。

在MySQL 8.0.17及更高版本中，InnoDB 存储引擎支持JSON数组上的多值索引。

MySQL使用utf8mb4字符集和utf8mb4_bin排序规则处理在JSON上下文中使用的字符串 。

因为utf8mb4_bin是二进制排序规则，所以JSON值的比较区分大小写。

区分大小写也适用于JSON null，true和 false文字，它们必须始终以小写形式编写

CREATE TABLE t1 (jdoc JSON);

mysql> SET @j = JSON_OBJECT('key', 'value');

mysql> SELECT @j;

+------------------+| @j |

+------------------+| {"key": "value"} |

+------------------+

mysql> SELECT CHARSET(@j), COLLATION(@j);

+-------------+---------------+| CHARSET(@j) | COLLATION(@j) |

+-------------+---------------+| utf8mb4 | utf8mb4_bin |

+-------------+---------------+

mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('X');

+-----------------------------------+| JSON_ARRAY('x') = JSON_ARRAY('X') |

+-----------------------------------+| 0 |

+-----------------------------------+

数据类型存储需求

数值类型

DECIMAL 和 NUMERIC

值使用二进制格式存储，该格式将9个十进制数字打包为4个字节。每个值的整数和小数部分的存储要求分别确定。九位数的每个倍数需要4个字节，剩余的任何剩余位数都需要4字节的一部分。例如:

DECIMAL(18,9)列在小数点的任一侧都有9位数字，因此整数部分和小数部分每个都需要4个字节。

DECIMAL(20,6)列有十四个整数位和六个小数位。整数位中的9位需要4个字节，其余5位需要3个字节。六个小数位需要3个字节。

日期和时间类型

TIME、DATETIME、TIMESTAMP

字符串类型

在下表中，M表示声明的列长度(对于非二进制字符串类型，以字符为单位)，对于二进制字符串类型，以字节为单位。L表示给定字符串值的实际长度(以字节为单位)。知乎不支持表格真难受

VARCHAR，， VARBINARY和 BLOB和 TEXT类型是可变长度类型。对于每种存储需求，取决于以下因素：列的实际长度值

列的最大可能长度

用于该列的字符集，因为某些字符集包含多字节字符