1.Integer(整数精确值)
INT 的同义词为 INTEGER 。
| 类型 | 存储(字节) | 范围 |
TINYINT[UNSIGNED] | 1 | 有符号 -128(-27) ~ 127(27-1)，无符号 0 ~ 255(28-1)。 |
SMALLINT[UNSIGNED] | 2 | 有符号 -32,768(-215) ~ 32,767(215-1)，无符号 0 ~ 65535(216-1)。 |
MEDIUMINT[UNSIGNED] | 3 | 有符号 -8,388,608(-223) ~ 8,388,607(223-1)，无符号 0 ~ 16777215(224-1)。 |
INT[UNSIGNED] | 4 | 有符号 -2,147,483,648(-231) ~ 2,147,483,647(231-1)，无符号 0 ~ 4294967295(232-1)。 |
BIGINT[UNSIGNED] | 8 | 有符号 -9,223,372,036,854,775,808(-263) ~ 9,223,372,036,854,775,807(263-1)，无符号 0 ~ 18,446,744,073,709,551,615(264-1)。 |
| 类型 | 说明 |
SERIAL | BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名 |
SERIAL DEFAULT VALUE | NOT NULL AUTO_INCREMENT UNIQUE的别名，前缀务必添加五种整数类型之一，比如INT SERIAL DEFAULT VALUE，注意：VALUE无需为具体的值，就是VALUE。 |
2.Fixed-Point(定点精确值)
M表示可以存储的总位数（精度）。小数点 和 -符号（对于负数）不计入 M。如果M被省略，默认值为 10。
D表示小数点后的位数（小数位数）。如果 D为 0，则值没有小数点或小数部分。如果D省略，则默认为 0。
DECIMAL(5,2) 表示的范围为-999.99到 999.99。
| 类型 | 同义词 | 精度M | 小数位数D |
DECIMAL[(M[,D])] | DEC、FIXED、NUMERIC | 0≤M≤65 | 最大可能值为 30，但不应大于M-2。 |
| Leftover Digits | Number of Bytes |
| 0 | 0 |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 4 |
| 8 | 4 |
3.Floating-Point(浮点近似值)
浮点类型遵循IEEE 754二进制浮点算术标准。p 表示以位为单位的精度。
双精度的同义词为 Double、DOUBLE PRECISION 和 REAL，如果开启了REAL_AS_FLOATSQL模式，则REAL是单精度的同义词。
| 类型 | 存储(字节) | 精度p | 范围 |
FLOAT(p) | 4 | 单精度(0≤p≤24) | -3.402823466E+38 ~ -1.175494351E-38、0、1.175494351E-38 ~ 3.402823466E+38，单精度浮点数精确到大约 7 位小数。 |
FLOAT(p) | 8 | 双精度(25≤p≤53) | -1.7976931348623157E+308 ~ -2.2250738585072014E-308、0、2.2250738585072014E-308 ~ 1.7976931348623157E+308，双精度浮点数精确到大约 15 位小数。 |
4.Boolean(布尔)
| 类型 | 说明 |
BOOL或BOOLEAN | TINYINT(1) 的同义词。 |
//0 值被认为是false，非 0 值被认为是true。
mysql> SELECT IF(0, 'true', 'false');//false
mysql> SELECT IF(1, 'true', 'false');//true
mysql> SELECT IF(2, 'true', 'false');//true
//但是，值TRUE和 FALSE只是 1 和 0 的别名。
mysql> SELECT IF(0 = FALSE, 'true', 'false');//true
mysql> SELECT IF(1 = TRUE, 'true', 'false');//true
mysql> SELECT IF(2 = TRUE, 'true', 'false');//false
mysql> SELECT IF(2 = FALSE, 'true', 'false');//false5.Bit(位)
| 类型 | 存储(字节) | 说明 |
BIT[(M)] | 大约(M+7)/8字节 | BIT[(M)]语法，M表示二进制值的位数，从 1 到 64。如果 M省略，则默认为 1。b'value'语法，value是使用 0 和 1 写入的二进制值。例如 ，赋值给BIT(3)的b'111' 代表十进制数字7，赋值给BIT(8)的 b'10000000'代表十进制数字128。如果将长度小于 M位的值分配给BIT(M)，则该值在左侧用0填充。例如，b'101'赋值给BIT(6)，实际上为b'000101'。 |
6.Date and Time(日期和时间)
每种时间类型都有一个有效值范围，以及一个“零”值，当您指定 MySQL 超出范围 或 无法表示的无效值（TIME除外）时，可以使用该“零”值。
fsp(fractional seconds precision)适用于 TIME、 DATETIME和 TIMESTAMP 类型，表示小数秒精度(小数点后的表示秒数的小数部分)。值必须在 0 到 6 的范围内，值 0 表示没有小数部分。如果省略，则默认精度为 0。
TIME 小时部分可能很大(hhh:mm:ss[.fraction])，因为该TIME类型不仅可以用于表示一天中的某个时间（必须小于 24 小时），还可以用于表示经过的时间或两个事件之间的时间间隔（可能远大于 24小时，甚至负数）。
默认情况下，位于TIME 范围之外但可以表示的有效值将被剪裁到范围的最近端点。例如， '-850:00:00' 和 '850:00:00'被转换为 '-838:59:59' 和 '838:59:59'。无法表示的无效TIME 值转换为'00:00:00'。请注意，因为'00:00:00'它本身是一个有效值 TIME，所以无法根据存储在表中的'00:00:00'值来判断是原始值指定的还是无效值。
TIMESTAMP存储时值从当前时区转换为 UTC，然后检索时从 UTC 转换回当前时区。
| 类型 | 格式 | 范围 | “零”值 |
YEAR | 'YYYY' | '1901‘ 到 '2155' | '0000' |
DATE | 'YYYY-MM-DD' | '1000-01-01' 到 '9999-12-31' | '0000-00-00' |
TIME[(fsp)] | 'hh:mm:ss[.fraction]' 或 'hhh:mm:ss[.fraction]' | '-838:59:59.000000' 到 '838:59:59.000000' | '00:00:00' |
DATETIME[(fsp)] | 'YYYY-MM-DD hh:mm:ss[.fraction]' | '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.499999' | '0000-00-00 00:00:00' |
TIMESTAMP[(fsp)] | 'YYYY-MM-DD hh:mm:ss[.fraction]' UTC | '1970-01-01 00:00:01.000000' UTC 到 '2038-01-19 03:14:07.499999' UTC | '0000-00-00 00:00:00' |
| 类型 | 存储(字节) |
YEAR | 1 字节 |
DATE | 3 字节 |
TIME | 3 字节 + 小数秒存储 |
DATETIME | 5 字节 + 小数秒存储 |
TIMESTAMP | 4 字节 + 小数秒存储 |
| 小数秒精度 | 存储(字节) |
| 0 | 0 字节 |
| 1, 2 | 1 字节 |
| 3, 4 | 2 字节 |
| 5, 6 | 3 字节 |
7.Character(字符型) String
CHAR是CHARACTER的缩写。VARCHAR是CHARACTER VARYING的缩写。
CHARACTER SET的同义词为CHARSET。
LONG 和 LONG VARCHAR映射到MEDIUMTEXT数据类型。这是一个兼容性功能。
M表示以字符为单位的列长度。比如M个英文字符，M个中文字符，M个表情字符等。
L表示给定字符串值的实际长度（以字节为单位）。
长度前缀存储了列所占用的字节的信息。
固定长度是指总是用空格右填充到M个字符进行存储。除非启用PAD_CHAR_TO_FULL_LENGTH SQL 模式，否则检索时将删除尾随空格。
可变长度是指按实际的字符个数进行存储。检索时将不删除尾随空格 。
最大行大小(maximum row size)为 65535 字节(所有的列加起来)，TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT不受最大行大小限制，仅对最大行大小贡献 9 到 12 个字节，因为它们的贡献内容与行的其余部分分开存储。
排序和比较基于字符集的排序规则。
| 类型 | 字符M | 长度前缀 | 长度 |
CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] | 0≤M≤255(28−1)个字符,如果M省略,则默认为1。 | ______ | 固定 |
VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] | 0≤M≤65,535(216−1)个字符,M不可以省略。 | 1 字节(列不超过 255 字节) 或 2 字节(列超过 255 字节) | 可变 |
TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] | 0≤M≤255(28−1)个字符 | 1字节 | 可变 |
TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] | 0≤M≤65,535(216−1)个字符，如果M存在，MySQL 会将列创建为足够大以保存值M个字符长度的最小TEXT类型。 | 2字节 | 可变 |
MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] | 0≤M≤16,777,215(224−1)个字符 | 3字节 | 可变 |
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] | 0≤M≤4,294,967,295(232−1)个字符 或 4GB。 | 4字节 | 可变 |
| 类型 | 存储(字节) |
CHAR(M) | M × w字节，0 <= M <= 255，w是字符集中最大长度字符所需的字节数。 |
VARCHAR(M) | L + 1 字节(列不超过 255 字节)，L + 2 字节(列超过 255 字节)。 |
TINYTEXT | L+ 1 字节，其中 L< 2 8 |
TEXT | L+ 2 字节，其中 L< 2 16 |
MEDIUMTEXT | L+ 3 字节，其中 L< 224 |
LONGTEXT | L+ 4 字节，其中 L< 232 |
8.Byte(字节型) String
BINARY的别名为CHAR BYTE。
M表示以字节为单位的列长度。L表示给定字符串值的实际长度（以字节为单位）。
长度前缀存储了列所占用的字节的信息。
固定长度是指总是用零字节填充值0x00右填充到M字节进行存储，并且不删除尾随字节以进行检索。
可变长度是指列实际所占的字节进行存储，并且也不删除尾随字节以进行检索。
最大行大小(maximum row size)为 65535 字节(所有的列加起来)，TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB不受最大行大小限制，仅对最大行大小贡献 9 到 12 个字节，因为它们的贡献内容与行的其余部分分开存储。
比较和排序基于列值中字节的数值。
| 类型 | 字节M | 长度前缀 | 长度 |
BINARY[(M)] | 0≤M≤255(28−1)字节,如果M省略,则默认为1。 | ——— | 固定 |
VARBINARY(M) | 0≤M≤65,535(216−1)字节,M不可以省略。 | 1 字节(列不超过 255 字节) 或 2 字节(列超过 255 字节) | 可变 |
TINYBLOB | 0≤M≤255(28−1)字节 | 1字节 | 可变 |
BLOB[( | 0≤M≤65,535(216−1)字节，如果M存在，MySQL 会将列创建为足够大以保存值M字节长度的最小BLOB类型。 | 2字节 | 可变 |
MEDIUMBLOB | 0≤M≤16,777,215(224−1)字节 | 3字节 | 可变 |
LONGBLOB | 0≤M≤4,294,967,295(232−1)字节 或 4GB。 | 4字节 | 可变 |
| 类型 | 存储(字节) |
BINARY(M) | M 字节，0 <= M <= 255。 |
VARBINARY(M) | L + 1 字节(列不超过 255 字节)，L + 2 字节(列超过 255 字节)。 |
TINYBLOB | L+ 1 字节，其中 L< 2 8 |
BLOB | L+ 2 字节，其中 L< 2 16 |
MEDIUMBLOB | L+ 3 字节，其中 L< 224 |
LONGBLOB | L+ 4 字节，其中 L< 232 |
9.Enum(枚举)
| 类型 | 存储(字节) | 说明 |
ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] | 1 或 2 字节 | 一个枚举是一个字符串对象，它只能有一个值，从表创建时列规范中明确枚举的允许值列表 'value1', 'value2', ..., NULL 或特殊的 '' 错误值中选择。ENUM创建表时，会自动从表定义中的成员值中删除尾随空格。检索时，存储在 ENUM 列中的值使用列定义中使用的字母大小写显示。ENUM列最多可以有 65,535 个不同的元素。单个 ENUM元素的最大支持长度为M<= 255 和 (Mxw) <= 1020，其中M是元素自变量长度，w是字符集中最大长度字符所需的字节数。 |
//示例
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name | size |
+---------+--------+
| t-shirt | medium |
+---------+--------+9.1枚举的索引
ENUM值在内部表示为整数。
列规范中列出的元素分配有索引号，从 1 开始。
空字符串错误值对应的索引值为 0 。
NULL对应的索引值为NULL。
例如，指定为ENUM('Mercury', 'Venus', 'Earth')的列可以具有此处显示的任何枚举值，还显示了每个值对应的索引。
| 枚举值 | 对应的索引 |
NULL | NULL |
'' | 0 |
'Mercury' | 1 |
'Venus' | 2 |
'Earth' | 3 |
10.Set(集合)
| 类型 | 存储(字节) | 说明 |
SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] | 1、2、3、4 或 8 字节 | 一个集合是一个字符串对象，可以有零个或多个值，从表创建时允许的值列表'value1', 'value2', ...中选择 。SET值在内部表示为整数。SET创建表时， 会自动从表定义中的成员值中删除尾随空格。检索时，存储在 SET列中的值使用列定义中使用的字母大小写显示。SET列最多可以有 64 个不同的成员。单个SET元素的最大支持长度为 M<= 255 和 (Mx w) <= 1020，其中M是元素自变量长度，w是字符集中最大长度字符所需的字节数。 |
与Enum的区别是,Set插入时可以插入多个值，但重复的会被筛选掉。
//示例
mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
mysql> INSERT INTO myset (col) VALUES
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
mysql> SELECT col FROM myset;
+------+
| col |
+------+
| a,d |
| a,d |
| a,d |
| a,d |
| a,d |
+------+11.Spatial(空间)
11.1层次结构
Geometry（不可实例化）Point（可实例化）Curve（不可实例化）LineString（可实例化）LineLinearRing
Surface（不可实例化）Polygon（可实例化）
GeometryCollection（可实例化）MultiPoint（可实例化）MultiCurve（不可实例化）MultiLineString（可实例化）
MultiSurface（不可实例化）MultiPolygon（可实例化）
Geometry是基类。它是一个抽象类。
Geometry(几何)基类具有 Point（点）、Curve（线）、 Surface（面）和 GeometryCollection（几何集合）子类：
Point表示零维对象。Curve表示一维对象，并且有子类LineString，有子子类Line和LinearRing。Surface表示二维对象，并且具有子类Polygon。GeometryCollection具有专门的零维、一维和二维集合类，分别命名为MultiPoint、MultiLineString和MultiPolygon。
Geometry, Curve, Surface, MultiCurve, 和 MultiSurface被定义为不可实例化的类，它们为它们的子类定义了一组通用的方法。
Point, LineString, Polygon, GeometryCollection, MultiPoint, MultiLineString 和 MultiPolygon是可实例化的类。
GEOMETRY可以存储任何类型的几何值。其它单值类型（POINT、 LINESTRING和POLYGON）将它们的值限制为特定的几何类型。
GEOMETRYCOLLECTION可以存储任何类型的对象的集合。其它集合类型（MULTIPOINT、 MULTILINESTRING和 MULTIPOLYGON）将集合成员限制为具有特定几何类型的成员。
11.2Well-Known Text(WKT)数据格式
//示例
CREATE TABLE geom (g GEOMETRY);
//POINT
POINT(15 20)
//LINESTRING
LINESTRING(0 0, 10 10, 20 25, 50 60)
//POLYGON
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))
//GEOMETRYCOLLECTION
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))
//MULTIPOINT
MULTIPOINT(0 0, 20 20, 60 60)
//MULTILINESTRING
MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
//MULTIPOLYGON
MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))12.JSON
JSON 数组包含由逗号分隔并包含在[ 和 ]字符中的值列表。
["abc", 10, null, true, false]JSON 对象包含由逗号分隔并包含在{和 }字符中的一组键值对。JSON 对象中的键必须是字符串。
{"k1": "value", "k2": 10}JSON 数组 和 JSON 对象 允许嵌套。
[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}//示例
mysql> CREATE TABLE t1 (c1 JSON);
mysql> INSERT INTO t1 VALUES
> ('{"x": 17, "y": "red"}'),
> ('{"x": 17, "y": "red", "z": [3, 5, 7]}');
mysql> SELECT c1 FROM t1;
+---------------------------------------+
| c1 |
+---------------------------------------+
| {"x": 17, "y": "red"} |
| {"x": 17, "y": "red", "z": [3, 5, 7]} |
+---------------------------------------+原创文章，作者：huoxiaoqiang，如若转载，请注明出处：https://www.huoxiaoqiang.com/sql/mysql/8536.html
微信扫一扫 
支付宝扫一扫