1.MySQL数据类型一览表

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)
类型说明
SERIALBIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名
SERIAL DEFAULT VALUENOT 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])]DECFIXEDNUMERIC0≤M≤65最大可能值为 30,但不应大于M-2。
Leftover DigitsNumber of Bytes
00
11
21
32
42
53
63
74
84

3.Floating-Point(浮点近似值)

浮点类型遵循IEEE 754二进制浮点算术标准p 表示以位为单位的精度。

双精度的同义词为 DoubleDOUBLE PRECISIONREAL,如果开启了REAL_AS_FLOATSQL模式,则REAL是单精度的同义词。

类型存储(字节)精度p范围
FLOAT(p)4单精度(0≤p≤24)-3.402823466E+38 ~ -1.175494351E-3801.175494351E-38 ~ 3.402823466E+38,单精度浮点数精确到大约 7 位小数。
FLOAT(p)8双精度(25≤p≤53)-1.7976931348623157E+308 ~ -2.2250738585072014E-30802.2250738585072014E-308 ~ 1.7976931348623157E+308,双精度浮点数精确到大约 15 位小数。

4.Boolean(布尔)

类型说明
BOOLBOOLEANTINYINT(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');//false

5.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'
类型存储(字节)
YEAR1 字节
DATE3 字节
TIME3 字节 + 小数秒存储
DATETIME5 字节 + 小数秒存储
TIMESTAMP4 字节 + 小数秒存储
小数秒精度存储(字节)
00 字节
1, 21 字节
3, 42 字节
5, 63 字节

7.Character(字符型) String

CHARCHARACTER的缩写。VARCHARCHARACTER VARYING的缩写。

CHARACTER SET的同义词为CHARSET

LONGLONG VARCHAR映射到MEDIUMTEXT数据类型。这是一个兼容性功能。

M表示以字符为单位的列长度。比如M个英文字符,M个中文字符,M个表情字符等。

L表示给定字符串值的实际长度(以字节为单位)。

长度前缀存储了列所占用的字节的信息。

固定长度是指总是用空格右填充到M个字符进行存储。除非启用PAD_CHAR_TO_FULL_LENGTH SQL 模式,否则检索时将删除尾随空格。

可变长度是指按实际的字符个数进行存储。检索时将不删除尾随空格 。

最大行大小(maximum row size)为 65535 字节(所有的列加起来),TINYTEXTTEXTMEDIUMTEXTLONGTEXT不受最大行大小限制,仅对最大行大小贡献 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]0M16,777,215(224−1)个字符3字节可变
LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]0≤M≤4,294,967,295(232−1)个字符4GB4字节可变
类型存储(字节)
CHAR(M)M × w字节,0 <= M <= 255,w是字符集中最大长度字符所需的字节数。
VARCHAR(M)L + 1 字节(列不超过 255 字节),L + 2 字节(列超过 255 字节)。
TINYTEXTL+ 1 字节,其中 L< 2 8
TEXTL+ 2 字节,其中 L< 2 16
MEDIUMTEXTL+ 3 字节,其中 L< 224
LONGTEXTL+ 4 字节,其中 L< 232

8.Byte(字节型) String

BINARY的别名为CHAR BYTE

M表示以字节为单位的列长度。L表示给定字符串值的实际长度(以字节为单位)。

长度前缀存储了列所占用的字节的信息。

固定长度是指总是用零字节填充值0x00右填充到M字节进行存储,并且不删除尾随字节以进行检索。

可变长度是指列实际所占的字节进行存储,并且也不删除尾随字节以进行检索。

最大行大小(maximum row size)为 65535 字节(所有的列加起来),TINYBLOBBLOBMEDIUMBLOBLONGBLOB不受最大行大小限制,仅对最大行大小贡献 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 字节)可变
TINYBLOB0≤M≤255(28−1)字节1字节可变
BLOB[(M)]0≤M≤65,535(216−1)字节,如果M存在,MySQL 会将列创建为足够大以保存值M字节长度的最小BLOB类型。2字节可变
MEDIUMBLOB0≤M≤16,777,215(224−1)字节3字节可变
LONGBLOB0≤M≤4,294,967,295(232−1)字节4GB4字节可变
类型存储(字节)
BINARY(M)M 字节,0 <= M <= 255。
VARBINARY(M)L + 1 字节(列不超过 255 字节),L + 2 字节(列超过 255 字节)。
TINYBLOBL+ 1 字节,其中 L< 2 8
BLOBL+ 2 字节,其中 L< 2 16
MEDIUMBLOBL+ 3 字节,其中 L< 224
LONGBLOBL+ 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')的列可以具有此处显示的任何枚举值,还显示了每个值对应的索引。

枚举值对应的索引
NULLNULL
''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 和 (Mw) <= 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(可实例化)
        • Line
        • LinearRing
    • Surface(不可实例化)
      • Polygon(可实例化)
    • GeometryCollection(可实例化)
      • MultiPoint(可实例化)
      • MultiCurve(不可实例化)
        • MultiLineString (可实例化)
      • MultiSurface(不可实例化)
        • MultiPolygon(可实例化)

Geometry是基类。它是一个抽象类。

Geometry(几何)基类具有 Point(点)、Curve(线)、 Surface(面)和 GeometryCollection(几何集合)子类:

  • Point表示零维对象。
  • Curve表示一维对象,并且有子类LineString,有子子类Line和 LinearRing
  • Surface表示二维对象,并且具有子类Polygon
  • GeometryCollection具有专门的零维、一维和二维集合类,分别命名为 MultiPointMultiLineStringMultiPolygon

GeometryCurveSurfaceMultiCurve, 和 MultiSurface被定义为不可实例化的类,它们为它们的子类定义了一组通用的方法。

PointLineStringPolygonGeometryCollectionMultiPointMultiLineString 和 MultiPolygon是可实例化的类。

GEOMETRY可以存储任何类型的几何值。其它单值类型(POINT、 LINESTRINGPOLYGON)将它们的值限制为特定的几何类型。

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

(1)
huoxiaoqiang的头像huoxiaoqiang
上一篇 2022年8月28日
下一篇 2022年9月2日

相关推荐

  • 3.MySQL数据定义语句一览表

    1.用户 简述 语句 创建新角色 CREATE ROLE 角色名; 删除角色 DROP ROLE 角色名; 通过指定其授予的哪些角色处于活动状态,修改当前用户在当前会话中的有效权限。 SET ROLE 角色名; 将当前会话中的活动角色设置为当前用户默认角色。 SET ROLE DEFAULT; 定义在用户会话中默认激活…

    MySQL教程 2022年9月3日
    03900
  • 2.MySQL常用命令一览表

    1.mysql_secure_installation mysql_secure_installation 用于在生产环境中提高 MySQL Server 安装的安全性,按照提示操作。 2.mysqld mysqld 用于启动 MySQL Server。 mysqld选项 描述 mysqld –help | -? 查…

    MySQL教程 2022年9月2日
    05950
  • 4.MySQL数据操作语句一览表

    1.新增记录 简述 语句 新增记录(按列顺序) INSERT [INTO] 表名 VALUES (值1, 值2, …); 新增记录(按列名称) INSERT [INTO] 表名 (列名1, 列名2, …) VALUES (值1, 值2, …); 复制已存在表1数据到已存在表2(全部列) INSERT [IN…

    MySQL教程 2022年9月4日
    05360

发表回复

登录后才能评论