MySQL学习笔记：数据类型-字段选型_数据库字段选择

前言

学习SQL语句，建表的时候，离不开的就是给字段指定数据类型
那么mysql数据库中有哪些数据类型呢？
这些数据类型的应用场景又是怎样的呢？
希望本博客对你有帮助！

数据类型

我们先从官网文档里看看有哪些数据类型

整理的数据类型如下：

数据类型还是非常多，由于篇幅有限，我会介绍前三大数据类型，数值型、日期和时间类型、字符串类型哦！

数值型

整型

数值型包括以下：

适用场景

• tinyint 1个字节，8位，常用于数值较小的数，无符号0-255，有符号-128 - 127
• smallint 2个字节，16位，无符号0-65535，有符号-32768 - 32767
• mediumint 3个字节，24位
• int 4个字节，32位
• bigint 8个字节，64位

TINYINT

示例：创建t2表，增加id字段，数据类型为tinyint，默认使用signed有符号数-128~127

root@chen 02:44  mysql>create table t2(id tinyint);
Query OK, 0 rows affected (0.02 sec)

root@chen 02:44  mysql>desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

root@chen 02:44  mysql>insert into t2(id) values(100);
Query OK, 1 row affected (0.00 sec)

root@chen 02:44  mysql>insert into t2(id) values(130);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
root@chen 02:45  mysql>insert into t2(id) values(127);
Query OK, 1 row affected (0.00 sec)

root@chen 02:45  mysql>insert into t2(id) values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

示例：创建t3表，使用无符号数0~256

root@chen 02:45  mysql>create table t3(id tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)

root@chen 02:47  mysql>desc t3;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

root@chen 02:47  mysql>insert into t3(id) values(130);
Query OK, 1 row affected (0.00 sec)

root@chen 02:48  mysql>insert into t3(id) values(300);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

INT

示例：创建表t4，数据类型int，增加电话号码

root@chen 02:53  mysql>create table t4(id int);
Query OK, 0 rows affected (0.01 sec)

root@chen 02:55  mysql>insert into t4(id) values(18174458104);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

1
2
3
4
5
6

默认有符号数，电话号码值过大，int不宜存放电话号码

示例：创建表t5，数据类型varchar，存电话号码

root@chen 02:55  mysql>create table t5(id varchar(20));
Query OK, 0 rows affected (0.05 sec)

root@chen 02:58  mysql>insert into t5(id) values(18174458104);
Query OK, 1 row affected (0.00 sec)

root@chen 03:00  mysql>select * from t5;
+-------------+
| id          |
+-------------+
| 18174458104 |
+-------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

定点型decimal

特点：数值精确

适用场景

常用于审计，算账等钱财不能出一点错的场景

示例

root@chen 03:00  mysql>create table salary(id int(5),name varchar(20),salary decimal(10,2));
Query OK, 0 rows affected (0.02 sec)

root@chen 03:16  mysql>desc salary;
+--------+---------------+------+-----+---------+-------+
| Field  | Type          | Null | Key | Default | Extra |
+--------+---------------+------+-----+---------+-------+
| id     | int(5)        | YES  |     | NULL    |       |
| name   | varchar(20)   | YES  |     | NULL    |       |
| salary | decimal(10,2) | YES  |     | NULL    |       |
+--------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

decimal(10,2)，总共10位，整数占8位，小数占2位，如19923124.12

字符型

适用场景

适用于存放字符型数据

• char 定长字符串
• varchar 可变长字符串
• text 可存放文本数据
• blob 存放二进制文本，如图片、视频、音频
• enum 枚举，指定选项，固定选择
• set 集合，指定选项，内容只能由一个或多个组成

char和varchar的区别

char：character 固定长度字符串

varchar： variable character 可变长度字符串，存储会在最后多加一个空格。

存储区别

1. char（len）括号中存储写的是字符长度，最大值为255，如果在存储的时候实际存储的字符串长度小于括号中的长度，那它在存储的时候会以空格补全位数进行存储；
2. varchar（len），最大长度取值为65535，不会空格补全进行存储；

取数据区别

char在取值的时候会把存储后面的空格去掉

varchar和char类型在读取数据的时候，都会删除自动填充的空格。

存储大小区别

char类型最大可存储255个字节

varchar类型可存储65535个字节

text型

blob型

图片视频音频，一般不用blob型存储，一般只存储一个url或者是路径

enum枚举型

插入数据时，enum数据类型的字段必须从枚举选项中选择。

An is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. ENUM

root@lianxi 11:10  mysql>CREATE TABLE shirts (
    ->     name VARCHAR(40),
    ->     size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
    -> );
Query OK, 0 rows affected (0.01 sec)

root@lianxi 11:11  mysql>desc shirts;
+-------+----------------------------------------------------+------+-----+---------+-------+
| Field | Type                                               | Null | Key | Default | Extra |
+-------+----------------------------------------------------+------+-----+---------+-------+
| name  | varchar(40)                                        | YES  |     | NULL    |       |
| size  | enum('x-small','small','medium','large','x-large') | YES  |     | NULL    |       |
+-------+----------------------------------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

root@lianxi 11:11  mysql>INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
    ->   ('polo shirt','small');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

root@lianxi 11:12  mysql>select * from shirts;
+-------------+--------+
| name        | size   |
+-------------+--------+
| dress shirt | large  |
| t-shirt     | medium |
| polo shirt  | small  |
+-------------+--------+
3 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

set集合型

可以有零的字符串对象 或更多值，每个值都必须从 创建表时指定的允许值。 由多个组成的列值 集合成员由成员指定，成员之间用逗号分隔 ().

root@lianxi 11:21  mysql>CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
Query OK, 0 rows affected (0.04 sec)

root@lianxi 11:21  mysql>desc myset;
+-------+----------------------+------+-----+---------+-------+
| Field | Type                 | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| col   | set('a','b','c','d') | YES  |     | NULL    |       |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

root@lianxi 11:22  mysql>INSERT INTO myset (col) VALUES   ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

root@lianxi 11:22  mysql>select * from myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

日期和时间型

适用场景

• year 年份
• time 时分秒
• datetime 年月日时分秒
• timestamp 年月日时分秒
• date 年月日

书写格式

存储空间

常用日期时间类型–timestamp型

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

timestamp消耗4个字节，2^32，换算成秒，从1970-01-01 00：00：01开始，正好算到2038年。

使用场景

1. 员工考勤
2. 生辰八字

示例

root@lianxi 11:42  mysql>create table t1(id int,name varchar(20),birth timestamp);
Query OK, 0 rows affected (0.01 sec)

root@lianxi 11:43  mysql>desc t1;
+-------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type        | Null | Key | Default           | Extra                       |
+-------+-------------+------+-----+-------------------+-----------------------------+
| id    | int(11)     | YES  |     | NULL              |                             |
| name  | varchar(20) | YES  |     | NULL              |                             |
| birth | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)

root@lianxi 11:46  mysql>insert into t1(id,name,birth) values(1,'chenlibiao','2001-12-29 12:34:02');
Query OK, 1 row affected (0.00 sec)

root@lianxi 11:47  mysql>select * from t1;
+------+------------+---------------------+
| id   | name       | birth               |
+------+------------+---------------------+
|    1 | chenlibiao | 2001-12-29 12:34:02 |
+------+------------+---------------------+
1 row in set (0.00 sec)

root@lianxi 11:47  mysql>insert into t1(id,name,birth) values(1,'felix',now());
Query OK, 1 row affected (0.01 sec)

root@lianxi 11:47  mysql>select * from t1;
+------+------------+---------------------+
| id   | name       | birth               |
+------+------------+---------------------+
|    1 | chenlibiao | 2001-12-29 12:34:02 |
|    1 | felix      | 2023-04-19 11:47:37 |
+------+------------+---------------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

now()函数，获取当前日期时间。

结语

由于篇幅有限，很多数据类型就不详细介绍啦
有需要的读者，可以查阅官网文档哦
官方文档
https://dev.mysql.com/doc/refman/8.0/en/