MySQL数据库的数据类型和基于MySQL数据类型的综合实例项目

原创

已于 2022-08-06 09:53:38 修改 · 1.2k 阅读

29 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #MySQL数据库 #MySQL数据库的应用 #大数据

于 2022-08-06 06:00:00 首次发布

本文详细介绍了MySQL的各种数据类型，包括整数、浮点数、定点数、日期时间、字符串、文本和二进制类型，并给出了选择数据类型的指导。通过实际案例展示了如何在cmd和Navicat环境中创建数据库、数据表并插入不同类型的数据，帮助读者深入理解MySQL数据类型的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL数据库的数据类型和基于MySQL数据类型的综合实例项目

1、MySQL具有的数据类型

1.1、MySQL整数类型

数值型数据类型主要用来存储数字。MySQL的整数类型如下所示
TINYINT、SMALLINT、MEDIUMINT、INT(INTEGER）、 BIGINT。

类型名称	说明	存储需求
TINYINT	很小的整数	1字节
SMALLINT	小的整数	2字节
MEDIUMINT	中等大小的整数	3字节
INT	普通大小的整数	4字节
BIGINT	大整数	8字节

创建表tmp1，其中字段x、y、z、m、n数据类型依次为TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，SQL语句如下：

mysql> CREATE TABLE tmp1 ( x TINYINT,  y SMALLINT,  z MEDIUMINT,  m INT,  n BIGINT );
Query OK, 0 rows affected (0.01 sec)

mysql> DESC tmp1;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| x     | tinyint   | YES  |     | NULL    |       |
| y     | smallint  | YES  |     | NULL    |       |
| z     | mediumint | YES  |     | NULL    |       |
| m     | int       | YES  |     | NULL    |       |
| n     | bigint    | YES  |     | NULL    |       |
+-------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

1.2、MySQL浮点类型和定点数

MySQL中使用浮点数和定点数来表示小数。
浮点类型：FLOAT、DOUBLE。
定点类型：DECIMAL。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4字节
DOUBLE	双精度浮点数	8字节
DECIMAL(M,D)	定点数	M+2字节

DECIMAL的存储空间不是固定的，而是由M和D决定的。

创建表tmp2，其中字段x、y、z数据类型依次为FLOAT(5,1)、DOUBLE(5,1)和DECIMAL(5,1)，向表中插入数据5.12、5.15和5.123，SQL语句如下：

mysql> CREATE TABLE tmp2 ( x FLOAT(5,1),  y DOUBLE(5,1),  z DECIMAL(5,1) );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

向tmp2表中插入数据：

mysql> INSERT INTO tmp2 VALUES(5.12, 5.15, 5.123);
Query OK, 1 row affected, 1 warning (0.01 sec)

查看警告信息

mysql> SHOW WARNINGS;
+-------+------+----------------------------------------+
| Level | Code | Message                                |
+-------+------+----------------------------------------+
| Note  | 1265 | Data truncated for column 'z' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)

可以看到定点数被阶段了，发出警告
查看如下：

mysql> SELECT * FROM tmp2;
+------+------+------+
| x    | y    | z    |
+------+------+------+
|  5.1 |  5.2 |  5.1 |
+------+------+------+
1 row in set (0.00 sec)

1.3、MySQL日期与时间类型

MySQL中表示日期和时间的数据类型有

YEAR、TIME 、DATE、DATETIME和TIMESTAMP。

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1字节
TIME	HH:MM:SS	-838:59:59~838:59:59	3字节
DATE	YYYY-MM-DD	1000-01-01~9999-12-3	3字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:1 UTC~2038-01-19 03:14:07 UTC	4字节

1、YEAR

存储格式：
存储方式(YEAR) 范围注意点
4位字符串或数字 ‘1901’~’2155’ 输入‘2022’或2022，插入到数据库的值都为2022

2位字符串	‘00’~’99’	 ‘00’~‘69’对应2000~2069；‘70’~‘99’对应1970~1999；
2位数字	1~99	1~69对应2001~2069；70~99对应1970~1999；0对应0000

创建数据表tmp3，定义数据类型为YEAR的字段y，向表中插入值2010，’2010’，’2166’，SQL语句如下：
首先创建表tmp3：

mysql> CREATE TABLE tmp3(  y YEAR );
Query OK, 0 rows affected (0.01 sec)

向表中插入数据：

mysql> INSERT INTO tmp3 values(2010),('2010');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

再次向表中插入数据：

mysql> INSERT INTO tmp3 values ('2166');
ERROR 1264 (22003): Out of range value for column 'y' at row 1

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1264 | Out of range value for column 'y' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)SELECT * FROM tmp3;

2166超过了最大取值：2155，所以会有Out of range value for column ‘y’ at row 1的错误。

查看数据表tmp3中的数据表

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
+------+
2 rows in set (0.00 sec)

可以看到2010和‘2010’都转换成了2010。

向tmp3表中y字段插入2位字符串表示的YEAR值，分别为’0’、’00’、’77’和’10’，SQL语句如下：

向表中插入数据：

mysql> INSERT INTO tmp3 values('0'),('00'),('77'),('10');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

查看数据表tmp3中的数据表：

mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
| 2000 |
| 2000 |
| 1977 |
| 2010 |
+------+
6 rows in set (0.00 sec)

可以看到‘0’，‘00’转换成了2000，‘77’转换成了1977，‘10’转换成了2010。

向tmp3表中y字段插入2位数字表示表示的YEAR值，分别为0、75和13，SQL语句如下：
向表中插入数据：

mysql> INSERT INTO tmp3 values(0),(75),(13);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看数据表tmp3中的数据表：

SELECT * FROM tmp3;
mysql> SELECT * FROM tmp3;
+------+
| y    |
+------+
| 2010 |
| 2010 |
| 2000 |
| 2000 |
| 1977 |
| 2010 |
| 0000 |
| 1975 |
| 2013 |
+------+
9 rows in set (0.00 sec)

可以看到0转换成了0000，75转换成了1975，13转换成了2013。

2、TIME

存储格式：
存储方式(TIME) 范围注意点
‘HH:MM:SS -838:59:59~838:59:59 ‘HHMMSS’类型的字符串被理解为：‘HH:MM:SS’；’D HH:MM:SS’的D为0~34之间，为小时保存—》‘D*24+H’
创建数据表tmp4，定义数据类型为TIME的字段t，向表中插入值’10:05:05’，’23:23’，’2 10:10’，’3 02’，’10’，SQL语句如下：
首先创建表tmp4，

mysql> CREATE TABLE tmp4( t TIME );
Query OK, 0 rows affected (0.01 sec)

向表中插入数据：

INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10');
mysql> INSERT INTO tmp4 values('10:05:05 '), ('23:23'), ('2 10:10'), ('3 02'),('10')