Mysql8.0 数据类型介绍

原创已于 2024-02-28 21:48:33 修改 · 2.8k 阅读 ·

大模型引用 1 次

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

xharvard

关注

标签

#mysql #数据库

分类数据库

于 2024-02-28 21:47:22 首次发布

Mysql 专栏收录该内容

18 篇文章

订阅专栏

本文详细介绍了MySQL中各种数据类型，包括整数、浮点数、位、日期时间、字符串（固定和可变长度）、空间、枚举与集合以及JSON类型的特点、使用方法和示例。

1，数值类型

1.1 整数类型

TINYINT：微整数，1字节

SMALLINT：小整数，2字节

MEDIUMINT：中等整数，3字节

INT：整数，4字节

BIGINT：大整数，8字节

如果添加UNSIGNED，表示无符号整数。

1.2 浮点数和定点数类型

FLOAT：4字节

DOUBLE：8字节

DECIMAL：M+2字节 DECIMAL(M,D)定义，默认为DECIMAL(10, 0)

DECIMAL以字符串的形式存放，对精度要求比较高（如货币，科学数据等）的时候，使用该类型比较好。

1.3 位类型

BIT(M)：M的范围1-64，不写默认为1。

MySQL 8.0版本中默认以“0X”开头的十六进制形式显示，可以通过BIN()函数显示为二进制格式。

mysql> create table tb_bit(
    -> num1 BIT,
    -> num2 BIT(5)
    -> );
Query OK, 0 rows affected (2.21 sec)

mysql> DESC tb_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| num1  | bit(1) | YES  |     | NULL    |       |
| num2  | bit(5) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into tb_bit values(1, 1);
Query OK, 1 row affected (0.20 sec)

mysql> select * from tb_bit;
+------------+------------+
| num1       | num2       |
+------------+------------+
| 0x01       | 0x01       |
+------------+------------+
1 row in set (0.00 sec)

看到，默认查出来的是16进制。

mysql> insert into tb_bit values(0, 17);
Query OK, 1 row affected (0.18 sec)

mysql> select * from tb_bit;
+------------+------------+
| num1       | num2       |
+------------+------------+
| 0x01       | 0x01       |
| 0x00       | 0x11       |
+------------+------------+
2 rows in set (0.00 sec)

其中0x11表示17。

如果要展示二进制的形式，使用BIN函数。

mysql> select BIN(num1), BIN(num2) from tb_bit;
+-----------+-----------+
| BIN(num1) | BIN(num2) |
+-----------+-----------+
| 1         | 1         |
| 0         | 10001     |
+-----------+-----------+
2 rows in set (0.00 sec)

2，日期类型

YEAR：年份，1字节。格式为YYYY，取值范围1901-2155

DATE：日期，3字节。格式为YYYY-MM-DD，取值范围1000-01-01至9999-12-31

TIME：时间，3字节。格式为HH:MM:SS，取值范围-838:59:59至838:59:59

DATETIME：日期时间，8字节。格式为YYYY-MM-DD HH:MM:SS，取值范围1000-01-01 00:00:00 至 9999-12-31 23:59:59

TIMESTAMP：时间戳，4字节。格式为YYYY-MM-DD HH:MM:SS，取值范围1970-01-01 00:00：01 UTC 至 2038-01-19 03:14:07 UTC

mysql> create table tb_date(
    -> d1 DATE,
    -> d2 TIME,
    -> d3 DATETIME,
    -> d4 TIMESTAMP
    -> );
Query OK, 0 rows affected (2.98 sec)

mysql> DESC tb_date;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| d1    | date      | YES  |     | NULL    |       |
| d2    | time      | YES  |     | NULL    |       |
| d3    | datetime  | YES  |     | NULL    |       |
| d4    | timestamp | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into tb_date values('2023-2-28','21:21:12','2023-2-28 21:21:12', '2023-2-28 21:21:12');
Query OK, 1 row affected (0.18 sec)

mysql> select * from tb_date;
+------------+----------+---------------------+---------------------+
| d1         | d2       | d3                  | d4                  |
+------------+----------+---------------------+---------------------+
| 2023-02-28 | 21:21:12 | 2023-02-28 21:21:12 | 2023-02-28 21:21:12 |
+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

插入日期，时间的分隔符不限于"-"，也可以是“%”，“#”等。甚至可以不要符号，类似20230208这样。

mysql> insert into tb_date values('2023%2%28','21:21:12','2023@2@28 21:21:12', '2023#2#28 21:21:12');
Query OK, 1 row affected, 3 warnings (0.35 sec)

mysql> select * from tb_date;
+------------+----------+---------------------+---------------------+
| d1         | d2       | d3                  | d4                  |
+------------+----------+---------------------+---------------------+
| 2023-02-28 | 21:21:12 | 2023-02-28 21:21:12 | 2023-02-28 21:21:12 |
| 2023-02-28 | 21:21:12 | 2023-02-28 21:21:12 | 2023-02-28 21:21:12 |
+------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

DATETIME与TIMESTAMP的区别：

TIMESTAMP的取值范围小，并且TIMESTAMP类型的日期时间在存储时会将当前时区的日期时间值转换为时间标准时间值，检索时再转换回当前时区的日期时间值。

而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

3，字符串类型

3.1 文本字符串类型

CHAR(M)：固定长度，M省略默认为1。

VARCHAR(M)：可变长度，M不能省略。

TINYTEXT：小文本

TEXT：文本

MEDIUMTEXT：中等文本

LONGTEXT：大文本

ENUM：枚举，从预定义的字符串列表中选择一个成员。

SET：集合，从预定义的字符串列表中选择任意个成员。

3.2 二进制字符串类型

BINARY(M)：固定长度，M省略默认为1。

VARBINARY(M)：可变长度，M不能省略。

TINYBLOB：小BLOB

BLOB：BLOB

MEDIUMBLOB：中等BLOB

LONGBLOB：大BLOB

BLOB类型与TEXT类型的区别如下

（1）BLOB类型存储的是二进制字符串，TEXT类型存储的是文本字符串。BLOB类型还可以存储图片和声音等二进制数据。
（2）BLOB类型没有字符集，并且排序和比较基于列值字节的数值，TEXT类型有一个字符集，并且根据字符集对值进行排序和比较。

3.3 ENUM和SET类型

无论是数值类型、日期类型、普通的文本类型，可取值的范围都非常大，但是有时候我们指定在固定的几个值范围内选择一个或多个，那么就需要使用ENUM枚举类型和SET集合类型了。比如性别只有“男”或“女”；上下班交通方式可以有“地铁”“公交”“出租车”“自行车”“步行”等。枚举和集合类型字段声明的语法格式如下。

字段名 ENUM('值1', '值2', ... '值N')

字段名 SET('值1', '值2', ... '值N')

mysql> create table tb_enum_set(
    -> gender ENUM('男','女'),
    -> transport SET('地铁','公交','汽车')
    -> );
Query OK, 0 rows affected (2.30 sec)

mysql> desc tb_enum_set;
+-----------+---------------------------+------+-----+---------+-------+
| Field     | Type                      | Null | Key | Default | Extra |
+-----------+---------------------------+------+-----+---------+-------+
| gender    | enum('男','女')           | YES  |     | NULL    |       |
| transport | set('地铁','公交','汽车') | YES  |     | NULL    |       |
+-----------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into tb_enum_set values('男','公交,汽车');
Query OK, 1 row affected (0.21 sec)

mysql> insert into tb_enum_set values('男','公交,火车');
ERROR 1265 (01000): Data truncated for column 'transport' at row 1
mysql> select * from tb_enum_set;
+--------+-----------+
| gender | transport |
+--------+-----------+
| 男     | 公交,汽车 |
+--------+-----------+
1 row in set (0.00 sec)

insert into tb_enum_set values('男','公交,火车');

可以看到，insert的时候，值必须是创建表的时候预定义的成员，输入其他的会报错。

4，空间类型

MySQL空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如一个十字路口等。MySQL中使用Geometry（几何）来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。

简单了解下，后续专门写一篇介绍。

5，JSON类型

在MySQL 5.7之前，如果需要在数据库中存储JSON数据只能使用VARCHAR或TEXT字符串类型。从MySQL 5.7.8之后开始支持JSON数据类型，和原来JSON格式的字符串相比，JSON类型有以下的优点。

• 自动验证。错误的JSON格式会报错。
• 存储格式优化。数据保存为二进制格式，文件存储很紧凑，读取速度快。
• 可以通过键名或数组索引查询和修改对应的值，不用把整个字符串都读出来。

mysql> create table tb_json(
    -> j JSON);
Query OK, 0 rows affected (1.06 sec)

mysql> desc tb_json;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| j     | json | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into tb_json values('{"k1":"v1", "k2":"v2"}');
Query OK, 1 row affected (0.18 sec)

mysql> select * from tb_json;
+--------------------------+
| j                        |
+--------------------------+
| {"k1": "v1", "k2": "v2"} |
+--------------------------+
1 row in set (0.00 sec)

在MySQL中字符串、日期类型通常使用单引号，JSON类型的数据也用单引号，那么JSON串中的字符串就需要使用双引号。

mysql> insert into tb_json values('["abcd", 123, null, true, false]');
Query OK, 1 row affected (0.35 sec)

mysql> select * from tb_json;
+----------------------------------+
| j                                |
+----------------------------------+
| {"k1": "v1", "k2": "v2"}         |
| ["abcd", 123, null, true, false] |
+----------------------------------+
2 rows in set (0.00 sec)