15-MySQL数据类型

2021-05-20 本文已影响0人紫荆秋雪_文

一、数值类型

数值类型包括：整数类型、浮点数类型、定点数类型

1、整数类型

整数类型.png

整数类型表示的数值范围.png

2、设置字段的显示宽度

create table t1(id int(3));

整数类型的显示宽度与数据类型的取值范围无关。显示宽度只是指定最大显示的数字个数，如果在数据表中插入了大于显示宽度，但是并没有超过数据类型的数值范围，那么依然可以正确地插入数据并且能够正确的显示
插入数据

insert into t1(id) values (1), (123456);

查看数据

select * from t1;

+--------+
| id     |
+--------+
|      1 |
| 123456 |
+--------+

插入超出数据类型范围的值

mysql> insert into t1(id) values (1), (123456), (123456789987654321);
ERROR 1264 (22003): Out of range value for column 'id' at row 3

3、整数类型的显示宽度配合 zerofill 使用，zerofill表示在数字的显示位数不够时，可以用字符 0 进行填充

创建数据表时指定zerofill

create table t2(
  id1 int zerofill,
  id2 int(3) zerofill
);

插入数据

insert into t2(id1, id2) values(1, 2);

查看数据

select * from t2;

+------------+------+
| id1        | id2  |
+------------+------+
| 0000000001 |  002 |
+------------+------+

当指定数据字段为 zerofill 时，MySQL 会自动为当前字段添加 unsigned 属性
查看指定 zerofill 的字段

mysql> show create table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id1` int(10) unsigned zerofill DEFAULT NULL,
  `id2` int(3) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

设置主键自动递增类型

create table if not exists t3 ( 
  id int not null auto_increment primary key, 
  age int 
);

插入数据

insert into t3 (age) values (18), (20), (16);

小结：MySQL 自动为 auto_increment 类型的整型列设置了自动递增的整数值
不设置id字段为主键，而是将id设置为唯一索引

create table if not exists t4 ( id int not null auto_increment unique, age int );

插入数据

insert into t4 (age) values (5), (7), (9);

小结1：MySQL 同样为设置为 auto_increment 的整型列 id 设置了自动递增的整数值
小结2：其他整数类型（bool）的用法与 int 类型相同，依然会自动递增

4、浮点数类型

浮点数类型主要有两种：单精度浮点数 FLOAT 和双精度浮点数 DOUBLE

浮点数所需的存储空间.png

浮点数表示的数值范围.png

创建数据表

create table if not exists t6 (f float, d double);

插入数据

insert into t6 (f, d) values (3.14, 5.98);

再次插入数据

insert into t6 (f, d) values (3.144444444444444, 5.98999999999999999999999);

查询数据

mysql> select * from t6;
+---------+------+
| f       | d    |
+---------+------+
|    3.14 | 5.98 |
| 3.14444 | 5.99 |
+---------+------+

为 float 类型和 double 类型插入超出数据类型精度的数据时，MySQL 对插入的数据进行四舍五入
使用(M, D)方式来给浮点数设置精度
M：整数位和小数位一共会显示 M 位数字，称为精度
D：小数点后会显示的位数，称为标度
创建带有（M, D）字段的数据表

create table if not exists t7 (
  f float(5, 2), 
  d double(5, 2)
);

插入数据

insert into t7 (f, d) values (3.14, 3.14);

再次插入数据

insert into t7 (f, d) values (3.141, 3.141);

查询数据

mysql> select * from t7;
+------+------+
| f    | d    |
+------+------+
| 3.14 | 3.14 |
| 3.14 | 3.14 |
+------+------+

5、定点数类型-DECIMAL

创建数据表

create table if not exists t8 (   d1 decimal,    d2 decimal(5, 2) );

decimal默认(M, D) 为 (10, 0)

mysql> show create table t8 \G
*************************** 1. row ***************************
       Table: t8
Create Table: CREATE TABLE `t8` (
  `d1` decimal(10,0) DEFAULT NULL,
  `d2` decimal(5,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

插入数据

insert into t8 (d1, d2) values (3.14, 3.14);

查看数据

mysql> select * from t8;
+------+------+
| d1   | d2   |
+------+------+
|    3 | 3.14 |
+------+------+

再次插入数据

insert into t8 (d1, d2) values (3.14, 3.149);

查看数据

mysql> select * from t8;
+------+------+
| d1   | d2   |
+------+------+
|    3 | 3.14 |
|    3 | 3.15 |
+------+------+

二、日期和时间类型

MySQL 提供了表示日期和时间的数据类型，主要有 YEAR类型、TIME类型、DATE类型、DATETIMEL类型和TIMESTAMP类型

DATE 类型通常用来表示年月日
DATETIME 类型通常用来表示年、月、日、时、分、秒
TIME 类型通常用来表示时、分、秒

日期和时间类型占用的存储空间.png

日期和时间类型表示的范围.png

1、YEAR类型

在MySQL中，YEAR有以下几种存储格式

以4位字符串或数字格式表示 YEAR 类型，其格式是 YYYY，最小值为 1901，最大值为 2155
以2位字符串格式表示 YEAR 类型，最小值为 00，最大值为99。
- 当取值为00到69时，表示2000到2069
- 当取值为70到99，表示1970到1999
- 当插入的数据超出取值范围，则MySQL会将值自动转换为2000
创建数据表

create table if not exists t9 (y year);

插入数据（数字2020 和字符串 2020）

insert into t9 (y) values (2020), ('2020');

清空数据表中数据

delete from t9;

插入2位数数据（0，00， 88， 20）

insert into t9 (y) values (0), (00), (88), (20);

mysql> select * from t9;
+------+
| y    |
+------+
| 0000 |
| 0000 |
| 1988 |
| 2020 |
+------+

插入2位字符串（'0'，'00'， '88'， '20'）

insert into t9 (y) values ('0'), ('00'), ('88'), ('20');

mysql> select * from t9;
+------+
| y    |
+------+
| 2000 |
| 2000 |
| 1988 |
| 2020 |
+------+

2、TIME类型

在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式

可以使用带有冒号的字符串
- D：表示天，其最小值为0，最大值为34，如果使用带有D格式的字符串插入 TIME 类型的字段时，D会被转化为小时，计算格式：D*24 + HH
- D HH:MM:SS
- HH:MM:SS
- HH:MM
- D HH:MM
- D HH 或 SS格式
可以使用不带有冒号的字符串或数字
- "HHMMSS"
- HHMMSS
- 插入不合法的字符串或者数字，MySQL在存储时，会将其自动转化为00:00:00进行存储
使用 CURRENT_TIME或者NOW()，会插入当前系统的时间
注意
- 12:10表示 12:10:00，而不是00:12:10
- 1210或'1210'：表示00:12:10，而不是 12:10:00
创建数据表

create table t10 (t time);

插入数据

insert into t10 (t) values ('2 12:30:29'), ('12:30:29'), ('12:40'), ('2 12:40'), ('45');

mysql> select * from t10;
+----------+
| t        |
+----------+
| 60:30:29 |
| 12:30:29 |
| 12:40:00 |
| 60:40:00 |
| 00:00:45 |
+----------+

插入字符串数字

insert into t10 (t) values ('123520'), ('124011'), ('0');

mysql> select * from t10;
+----------+
| t        |
+----------+
| 12:35:20 |
| 12:40:11 |
| 00:00:00 |
+----------+

插入不足6位数字的字符串，先右对齐

insert into t10 (t) values ('12352');

mysql> select * from t10;
+----------+
| t        |
+----------+
| 12:35:20 |
| 12:40:11 |
| 00:00:00 |
| 01:23:52 |
+----------+

清空数据

delete from t10;

插入数据

insert into t10 (t) values (now()), (current_time);

mysql> select * from t10;
+----------+
| t        |
+----------+
| 02:45:32 |
| 02:45:32 |
+----------+

3、DATE类型

向DATE类型的字段插入数据时，同样需要满足一定的格式条件

以YYYY-MM-DD 格式或者 YYYYMMDD格式表示的字符串日期，其最小值取值为1000-01-01，最大值为9999-12-03
以YY-MM-DD 格式或者 YYMMDD 格式表示字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件
以YYYYMMDD格式表示数字日期，能够被转化为YYYY-MM-DD格式
以YYMMDD格式表示的数字日期，同样满足年份为两位数值或字符串 YEAR 类型的格式条件
使用CURRENT_DATE 或者 NOW()函数，会插入当前系统的日期
创建数据表

create table t11 (d date);

插入数据

insert into t11 (d) values ('2020-5-20'), ('20200520');

mysql> select * from t11;
+------------+
| d          |
+------------+
| 2020-05-20 |
| 2020-05-20 |
+------------+

4、DATETIME类型

向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件

以 YYYY-MM-DD HH:MM:SS 格式或者YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00, 最大值 9999-12-03 23:59:59
以YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS格式的字符串插入DATETIME类型的字段时，两位数的年费规则符号YEAR类型的规则
以YYYYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，会被转换为YYYY-MM-DD HH:MM:SS格式
以YYMMDDHHMMSS格式的数字插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则
使用函数CURRENT_TIMESTAMP() 和 NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间
创建数据表

create table t12 (dt datetime);

插入全格式字符串类型数据

insert into t12 (dt) values ('2021-05-20 11:08:08');
insert into t12 (dt) values ('20210520110808');

mysql> select * from t12;
+---------------------+
| dt                  |
+---------------------+
| 2021-05-20 11:08:08 |
| 2021-05-20 11:08:08 |
+---------------------+

插入省略式字符串类型数据

insert into t12 (dt) values ('21-05-20 11:08:08');
insert into t12 (dt) values ('210520110808');

插入全格式数字类型数据

insert into t12 (dt) values (20210520110808);

插入省略格式数字类型数据

insert into t12 (dt) values (210520110808);

mysql> select * from t12;
+---------------------+
| dt                  |
+---------------------+
| 2021-05-20 11:08:08 |
| 2021-05-20 11:08:08 |
+---------------------+

使用函数

insert into t12 (dt) values (current_timestamp);
insert into t12 (dt) values (now());

mysql> select * from t12;
+---------------------+
| dt                  |
+---------------------+
| 2021-05-20 03:17:59 |
| 2021-05-20 03:18:23 |
+---------------------+

5、TIMESTAMP 类型

与DATETIME类型相似，但是存储范围比 DATETIME 类型小

三、文本字符串类型

在MySQL中，字符串类型可以存储文本字符串数据，也可以存储一些图片、音频和视频数据，也就是二进制数据。因此在MySQL中，字符串类型可以分为文本字符串类型和二进制字符串类型。

1、文本字符串类型

文本字符串类型所占用存储空间.png

2、CHAR 与 VARCHAR 类型

CHAR 与 VARCHAR 类型都可以存储比较短的字符串
CHAR类型的字段长度是固定的，检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格
VARCHAR类型修饰的字符串是一个可变长的字符串，检索VARCHAR类型的字段数据时，会保留数据尾部的空格
创建数据表

create table t13(vc varchar(4), c char(4));

插入数据

insert into t13 (vc, c) values ('abc', 'abc');

查看数据表中字段的长度

select length(vc), length(c) from t13;

插入尾部为空格的字符串

insert into t13 (vc, c) values ('a  ', 'a  ');

mysql> select * from t13;
+------+------+
| vc   | c    |
+------+------+
| a    | a    |
+------+------+

查看数据表中字段的长度

mysql> select length(vc), length(c) from t13;
+------------+-----------+
| length(vc) | length(c) |
+------------+-----------+
|          3 |         1 |
+------------+-----------+

MySQL在检索CHAR类型的字段时，回去除尾部的空格
MySQL在检索VARCHAR类型的字段时，则不会去除尾部的空格
追加数据

mysql> select concat(vc, 'b'), concat(c, 'b') from t13;
+-----------------+----------------+
| concat(vc, 'b') | concat(c, 'b') |
+-----------------+----------------+
| a  b            | ab             |
+-----------------+----------------+

3、TEXT类型

在MySQL中，Text用来保存文件类型的字符串，共包含4种类型

tinytext
text
mediumtext
longtext

4、ENUM类型——枚举类型

创建数据表

create table t14 (e enum('A', 'B', 'C'));

插入数据

insert into t14 (e) values ('a'), ('c');

mysql> select * from t14;
+------+
| e    |
+------+
| A    |
| C    |
+------+

插入数字下标

insert into t14 (e) values ('1'), ('2');

mysql> select * from t14;
+------+
| e    |
+------+
| A    |
| B    |
+------+

5、SET类型

SET 表示一个字符串对象，可以包含 0 个或多个成员，但成员个数的上限为64. image.png

SET类型在选取成员时，可以一次选择多个成员，这点与ENUM类型不同

6、JSON类型

在MySQL5.7中支持JSON数据类型。在MySQL8.x中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效

创建数据表

create table t16 (j JSON);

插入数据

insert into t16 (j) values ('{"name":"Raven", "age":18, "address":{"city":"nmg"}}');


mysql> select * from t16;
+----------------------------------------------------------+
| j                                                        |
+----------------------------------------------------------+
| {"age": 18, "name": "Raven", "address": {"city": "nmg"}} |
+----------------------------------------------------------+

检索JSON类型的字段中数据

mysql> select j->'$.name' from t16;
+-------------+
| j->'$.name' |
+-------------+
| "Raven"     |
+-------------+

四、二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如存储图片、音频和视频等二进制数据

二进制字符串类型长度与占用空间.png