Yuhan's Blog
文章 标签
Yuhan's Blog

目录

MySQL 8.0 数据类型

yuhan
   约 3747 字   预计阅读 8 分钟 
目录 
datetime -- timestamp 只到 2038年，而它到 9999 年，他俩都能存秒后的小数
bit(1) default b'0' -- 比特值 bit(M)，默认值为b'0'
tinyint -- 一个字节八位，到127
char(36)  -- char 类型可取值 0-255，存储时不够则右填充空格
varchar(255)  -- 可指定 0-65535
text -- 已经很足了，可以存 2^16=65536 + 2 字节
json  -- 存储大小至少跟 LONGBLOB 和 LONGTEXT 一样大，可局部更新
Type Storage (Bytes) Minimum Value Signed Minimum Value Unsigned Maximum Value Signed Maximum Value 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 -263 0 263-1 264-1

DECIMAL 小数和 NUMERIC 数字类型存储精确的数字数据值。这些类型用于需要保持精确精度的情况，例如货币数据。

FLOAT和DOUBLE类型表示近似的数值数据值。MySQL为单精度值使用4个字节，为双精度值使用8个字节。

对于FLOAT, SQL标准允许在圆括号中的关键字FLOAT之后指定精度。但是FLOAT(p)中的精度值只用于确定存储大小。从0到23的精度将产生一个4字节的单精度浮点列。精度从24到53将产生一个8字节的双精度双列。

从MySQL 8.0.17开始，不支持非标准的FLOAT(M,D)和DOUBLE(M,D)语法，在未来的MySQL版本中将不再支持它。

为了获得最大的可移植性，需要存储近似数值数据值的代码应该使用浮点数或双精度数，而不指定精度或位数。

BIT数据类型用于存储位值。 BIT(M)类型可以存储M位的值。 M的范围是1到64。

要指定位值，可以使用b'value'符号。值是使用0和1编写的二进制值。例如，b'111’和b'10000000’分别代表7和128。

如果将一个值赋给长度小于M位的BIT(M)列，则该值在左侧用零填充。例如，将b'101’赋值给BIT(6)列实际上与将b'000101’赋值是一样的。

一个DATETIME或TIMESTAMP值可以包含一个以微秒(6位数字)精度为单位的尾随小数秒部分。特别是，插入到DATETIME或TIMESTAMP列中的值的任何小数部分都将被存储，而不是丢弃。包含的小数部分,这些值的格式是"YYYY-MM-DD hh: mm: ss (.fraction)", DATETIME值的范围是"1000-01-01 00:00:00.000000" "9999-12-31 23:59:59.999999"，TIMESTAMP值的范围是"1970-01-01 00:00:01.000000" "2038-01-19 03:14:07.999999"。

MySQL将时间戳值从当前时区转换为UTC进行存储，然后从UTC转换回当前时区进行检索。

包括 CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.

CHAR和VARCHAR类型相似，但在存储和检索方式上有所不同。

CHAR和VARCHAR类型的声明长度表示希望存储的最大字符数。

CHAR列的长度固定为创建表时声明的长度。长度可以是0到255之间的任何值。当存储CHAR值时，用空格将它们右填充到指定的长度。检索CHAR值时，除非启用了PAD CHAR到全长度的SQL模式，否则将删除尾随空格。

VARCHAR列中的值是可变长度的字符串。长度可以指定为0到65,535之间的值。VARCHAR的有效最大长度取决于最大行大小(所有列共享的65,535字节)和使用的字符集。

与CHAR不同，VARCHAR值存储为1字节或2字节长度的前缀加上数据。

二进制和VARBINARY类型与CHAR和VARCHAR类似，不同之处在于它们存储的是二进制字符串而不是非二进制字符串。

Example: For a BINARY(3) column, 'a ' becomes 'a \0' when inserted. 'a\0' becomes 'a\0\0' when inserted. Both inserted values remain unchanged for retrievals.

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold.

The four TEXT types are TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to the four BLOB types and have the same maximum lengths and storage requirements.

BLOB值被视为二进制字符串(字节字符串)。它们具有二进制字符集和排序，比较和排序基于列值中字节的数值。TEXT值被视为非二进制字符串(字符串)。它们有一个非二进制的字符集，并且根据字符集的排序规则对值进行排序和比较。

In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.

Data Type Storage Required
CHAR(M) The compact family of InnoDB row formats optimize storage for variable-length character sets. See COMPACT Row Format Storage Characteristics. Otherwise, M × w bytes, <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set.
BINARY(M) M bytes, 0 <= M <= 255
VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 − 255 bytes, L + 2 bytes if values may require more than 255 bytes
TINYBLOB, TINYTEXT L + 1 bytes, where L < 2^8
BLOB, TEXT L + 2 bytes, where L < 2^16
MEDIUMBLOB, MEDIUMTEXT L + 3 bytes, where L < 2^24
LONGBLOB, LONGTEXT L + 4 bytes, where L < 2^32
ENUM('value','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

ENUM是一个字符串对象，其值是从表创建时列规范中显式枚举的允许值列表中选择的。

枚举值必须是带引号的字符串文字。

For example, a column specified as ENUM('Mercury', 'Venus', 'Earth') can have any of the values shown here. The index of each value is also shown.

Value Index
NULL NULL
'' 0
'Mercury' 1
'Venus' 2
'Earth' 3

An ENUM column can have a maximum of 65,535 distinct elements.

SET是一个可以有零个或多个值的字符串对象，每个值都必须从创建表时指定的允许值列表中选择。由多个SET成员组成的SET列值由逗号(，)分隔的成员指定。其结果是，SET成员值本身不应该包含逗号。

For example, a column specified as SET('one', 'two') NOT NULL can have any of these values:’’ ‘one’ ’two’ ‘one,two’

A SET column can have a maximum of 64 distinct members.

开放地理空间联盟(OGC)是一个由250多家公司、机构和大学组成的国际联盟，它们参与开发可用于管理空间数据的各种应用程序的公共概念解决方案。

MySQL空间扩展支持地理特征的生成、存储和分析。

空间数据类型，暂不涉及，不看。

MySQL支持RFC 7159定义的原生JSON数据类型，它支持对JSON (JavaScript对象表示法)文档中的数据的有效访问。与在string列中存储JSON格式的字符串相比，JSON数据类型提供了这些优势：

  • 存储在JSON列中的JSON文档的自动验证。无效文档产生错误。
  • 优化的存储格式。存储在JSON列中的JSON文档被转换为允许快速读取文档元素的内部格式。当服务器稍后必须读取以这种二进制格式存储的JSON值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够通过键或数组索引直接查找子对象或嵌套值。

MySQL 8.0还支持RFC 7396中定义的JSON Merge Patch格式。

存储JSON文档所需的空间与存储LONGBLOB或LONGTEXT所需的空间大致相同。

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

除了JSON数据类型之外，还可以使用一组SQL函数来支持对JSON值的操作，比如创建、操作和搜索。

还可以使用一组空间函数对GeoJSON值进行操作。

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

MySQL NDB集群8.0支持JSON列和MySQL JSON函数，包括在从JSON列生成的列上创建一个索引，作为无法索引JSON列的解决方案。每个NDB表最多支持3个JSON列。

在MySQL 8.0中，优化器可以执行JSON列的局部就地更新，而不是删除旧文档并将整个新文档写入该列。

The UPDATE statement uses any of the three functions JSON_SET(), JSON_REPLACE(), or JSON_REMOVE() to update the column. A direct assignment of the column value (for example, UPDATE mytable SET jcol = '{"a": 10, "b": 25}') cannot be performed as a partial update.

The value being replaced must be at least as large as the replacement value. In other words, the new value cannot be any larger than the old one.

JSON数组包含一列值，这些值由逗号分隔，并包含在[和]字符中：[“abc”, 10, null, true, false]

JSON对象包含一组键-值对，以逗号分隔，并包含在{和}字符中：{“k1”: “value”, “k2”: 10}

JSON对象中的键必须是字符串。

JSON数组元素和JSON对象键值中允许嵌套。

JSON values can be assigned to user-defined variables.

在MySQL中，JSON值被写成字符串。

MySQL 8.0.3(及以后版本)支持两种合并算法，由函数JSON MERGE PRESERVE()和JSON MERGE PATCH()实现。它们处理重复键的方式不同:JSON MERGE PRESERVE()保留重复键的值，而JSON MERGE PATCH()则丢弃除最后一个值之外的所有值。

A JSON path expression selects a value within a JSON document.

Path语法使用一个前导的$字符来表示正在考虑的JSON文档，后面可选地跟随一些选择器。

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

  • 句点后跟键名，用给定的键表示对象中的成员。
  • [N]附加到选择数组的路径上，将数组中位置N处的值命名为[N]。数组位置是从零开始的整数。
  • [M to N]指定数组值的子集或范围，以位置M的值开始，以位置N的值结束。
  • last作为最右边数组元素的索引的同义词。还支持数组元素的相对寻址。如果path没有选择一个数组值，path[last]的计算结果与path相同。
  • 路径可以包含或是简单的通配符：
    • .[*] evaluates to the values of all members in a JSON object.
    • [*] evaluates to the values of all elements in a JSON array.
    • prefix**suffix evaluates to all paths that begin with the named prefix and end with the named suffix.
    • A path that does not exist in the document (evaluates to nonexistent data) evaluates to NULL.

示例：

  • $[1].a[1] evaluates to 6.
  • $[2][0] evaluates to 99.
  • $[1].b evaluates to 10.
  • $."a fish" evaluates to shark.

有些函数采用现有的JSON文档，以某种方式修改它，然后返回修改后的结果文档。

  • JSON_SET() replaces values for paths that exist and adds values for paths that do not exist.
  • JSON_INSERT() adds new values but does not replace existing values.
  • JSON_REPLACE() replaces existing values and ignores new values.
  • JSON_REMOVE() takes a JSON document and one or more paths that specify values to be removed from the document. The return value is the original document minus the values selected by paths that exist within the document.
pathExpression:
    scope[(pathLeg)*]

pathLeg:
    member | arrayLocation | doubleAsterisk

member:
    period ( keyName | asterisk )

arrayLocation:
    leftBracket ( nonNegativeInteger | asterisk ) rightBracket

keyName:
    ESIdentifier | doubleQuotedString

doubleAsterisk:
    '**'

period:
    '.'

asterisk:
    '*'

leftBracket:
    '['

rightBracket:
    ']'

如前所述，在MySQL中，路径的范围总是操作的文档，表示为$。

JSON values can be compared using the =, <, <=, >, >=, <>, !=, and <=> operators.

The following comparison operators and functions are not yet supported with JSON values:

数据类型规范可以具有显式或隐式默认值。

A DEFAULT value clause in a data type specification explicitly indicates a default value for a column.

Examples:

CREATE TABLE t1 (
  i     INT DEFAULT -1,
  c     VARCHAR(10) DEFAULT '',
  price DOUBLE(16,2) DEFAULT 0.00
);

这里有很多存储范围的资料，看官网。

Chapter 11 Data Types

11.7 Data Type Storage Requirements

更新于 2020-03-22
 mysql
返回 | 主页