【MySQL】5. 数据类型

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 【MySQL】5. 数据类型

数据类型

1. 数据类型分类

在这里插入图片描述

2. 数值类型

在这里插入图片描述

2.1 tinyint类型

数值越界测试:

mysql> use tt;
Database changed
mysql> create table t1(
    -> num tinyint
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(-128);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(127);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(22);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(-23);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(-255);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values(-256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from t1;
+------+
| num  |
+------+
| -128 |
|  127 |
|    0 |
|   22 |
|  -23 |
+------+
5 rows in set (0.00 sec)

说明:
在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
可以通过UNSIGNED来说明某个字段是无符号的,以下是无符号案例

mysql> create table t2(
    -> num tinyint unsigned
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(255);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(-2);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+------+
| num  |
+------+
|    0 |
|  255 |
|  100 |
+------+
3 rows in set (0.00 sec)

其他类型自己推导(和tinyint类型类似,只不过就是范围的问题罢了)
在这里插入图片描述

2.2 bit类型

基本语法:

bit[(M)] : 位字段类型。M表示每个值的位数,范围从164。如果M被忽略,默认为1

举例:

mysql> create table t3(
    -> id int,
    -> online bit(1)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t3 values(124,0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(123,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(123,2);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> insert into t3 values(123,-1);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> select * from t3;
+------+--------+
| id   | online |
+------+--------+
|  124 |        |
|  123 |       |
+------+--------+
2 rows in set (0.00 sec)

mysql> select id,hex(online) from t3;
+------+-------------+
| id   | hex(online) |
+------+-------------+
|  124 | 0           |
|  123 | 1           |
+------+-------------+
2 rows in set (0.00 sec)

这里我们发现单纯的select 无法显示online当中的数据(因为online中的数据是按位存储,无法直接显示)
可以通过hex方法(十六进制)的方式显示

mysql> create table t4(
    -> id int,
    -> bite bit(4)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4 values(1,65);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(2,97);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;
+------+------+
| id   | bite |
+------+------+
|    1 | A    |
|    2 | a    |
+------+------+
2 rows in set (0.00 sec)

==bit字段在显示时,是按照ASCII码对应的值显示。==

2.3 小数类型

2.3.1 float

语法:

float[(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节

案例:
小数:float(4,2)表示的范围是-99.99 ~ 99.99

mysql> create table t5(
    -> id int,
    -> salary float(4,2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t5 values(1,99.99);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t5 values(2,-99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(2,-100.00);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t5 values(2,100.00);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select * from t5;
+------+--------+
| id   | salary |
+------+--------+
|    1 |  99.99 |
|    2 | -99.99 |
+------+--------+
2 rows in set (0.00 sec)

MySQL在保存值时会进行四舍五入

mysql> insert into t5 values(3,78.668);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(3,99.994);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(4,-99.994);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t5 values(4,-99.998);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t5 values(4,99.998);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> select * from t5;
+------+--------+
| id   | salary |
+------+--------+
|    1 |  99.99 |
|    2 | -99.99 |
|    3 |  78.67 |
|    3 |  99.99 |
|    4 | -99.99 |
+------+--------+
5 rows in set (0.00 sec)

案例:
如果定义的是float(4,2) unsigned 这时,因为把它指定为无符号的数,范围是 0 ~ 99.99

mysql> create table t6(
    -> id int,
    -> salary float(4,2) unsigned
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t6 values(1,0);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t6 values(1,99.99);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t6 values(1,-0.0001);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 values(1,0.0001);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t6;
+------+--------+
| id   | salary |
+------+--------+
|    1 |   0.00 |
|    1 |  99.99 |
|    1 |   0.00 |
+------+--------+
3 rows in set (0.00 sec)

2.3.2 decimal

语法:

decimal(m, d) [unsigned] : 定点数m指定长度,d表示小数点的位数

decimal(5,2) 表示的范围是 -999.99 ~ 999.99
decimal(5,2) unsigned 表示的范围 0 ~ 999.99
decimal和float很像,但是有区别:
float和decimal表示的精度不一样

mysql> desc t7;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | int(11)       | YES  |     | NULL    |       |
| salary  | float(16,8)   | YES  |     | NULL    |       |
| salary2 | decimal(16,8) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+

mysql> insert into t7 values(1,199992.54586,199992.54586);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t7 values(2,1900042.54586,1900042.54586);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t7;
+------+------------------+------------------+
| id   | salary           | salary2          |
+------+------------------+------------------+
|    1 |  199992.54687500 |  199992.54586000 |
|    2 | 1900042.50000000 | 1900042.54586000 |
+------+------------------+------------------+
2 rows in set (0.01 sec)

说明:float表示的精度大约是7位。
decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略,默认为0.如果m被省略,默认是10。
我们可以发现decimal的精度更准确
float不管是整数部分还是小数都可能会发生数据精度丢失的情况
如果我们的应用场景需要对数据精度很高的要求推荐使用decimal类型

同样的decimal类型也会进行四舍五入操作

mysql> create table t8(
    -> id int,
    -> salary decimal(4,2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t8 values(1,99.999);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t8 values(1,99.991);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into t8 values(1,29.999);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t8;
+------+--------+
| id   | salary |
+------+--------+
|    1 |  99.99 |
|    1 |  30.00 |
+------+--------+
2 rows in set (0.00 sec)

3. 字符串类型

3.1 char

语法:
char(L): 固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255
案例(char):

mysql> create table t9(
    -> id int,
    -> name char(2)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t9 values(1,'ab');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9 values(2,'中国');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t9 values(2,'中国人');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t9 values(1,'abc');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t9;
+------+--------+
| id   | name   |
+------+--------+
|    1 | ab     |
|    2 | 中国   |
+------+--------+
2 rows in set (0.00 sec)

说明:
char(2) 表示可以存放两个字符,可以是字母或汉字,但是不能超过2个, 最多只能是255

mysql> create table t10(
    -> id int,
    -> name char(256)
    -> );
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

这里需要提一句的是:在MySQL当中汉字也只算是一个字符,虽然在utf-8当中一个汉字占三个字节的大小

3.2 varchar

语法:

varchar(L): 可变长度字符串,L表示字符长度,最大长度65535个字节

案例:

mysql> create table t10(
    -> id int,
    -> name varchar(6)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t10 values(1,'hello');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t10 values(1,'你好鸭!!!!');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t10 values(1,'你好鸭!!!');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t10;
+------+--------------------+
| id   | name               |
+------+--------------------+
|    1 | hello              |
|    1 | 你好鸭!!!         |
+------+--------------------+
2 rows in set (0.00 sec)

说明:
关于varchar(len),len到底是多大,这个len值,和表的编码密切相关:
varchar长度可以指定为0到65535之间的值,但是有1 - 3 个字节用于记录数据大小,所以说有效字节数是65532。
当我们的表的编码是utf8时,varchar(n)的参数n最大值是65532/3=21844[因为utf中,一个字符占用3个字节],如果编码是gbk,varchar(n)的参数n最大是65532/2=32766(因为gbk中,一个字符占用2字节)。

mysql> create table tt11(name varchar(21845))charset=utf8; --验证了utf8确实是不能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> create table t11(name varchar(21844))charset=utf8;
Query OK, 0 rows affected (0.01 sec)

3.3 char和varchar比较

在这里插入图片描述
如何选择定长或变长字符串?
如果数据确定长度都一样,就使用定长(char),比如:身份证,手机号,md5
如果数据长度有变化,就使用变长(varchar), 比如:名字,地址,但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费,但是效率高。
变长的磁盘空间比较节省,但是效率低。

定长的意义是,直接开辟好对应的空间
变长的意义是,在不超过自定义范围的情况下,用多少,开辟多少。

4. 日期和时间类型

常用的日期有如下三个:
date :日期 'yyyy-mm-dd' ,占用三字节
datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ,占用八字节
timestamp :时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致,占用四字节
案例:

mysql> create table t12(
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t12(t1,t2) values('1997-7-5','2008-8-8 12:1:1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t12;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1997-07-05 | 2008-08-08 12:01:01 | 2024-03-16 19:32:24 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> update t12 set t1='2000-1-1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t12;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2000-01-01 | 2008-08-08 12:01:01 | 2024-03-16 19:33:05 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

5. enum和set

语法:enum:枚举,“单选”类型;

enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值,最终一个单元格中,实际只存储了其中一个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,3,....最多65535个;当我们添加枚举值时,也可以添加对应的数字编号。
set:集合,“多选”类型;

set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值,最终一个单元格中,设计可存储了其中任意多个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,4,8,16,32,....最多64个。
说明:不建议在添加枚举值,集合值的时候采用数字的方式,因为不利于阅读。
案例:
有一个调查表votes,需要调查人的喜好, 比如(登山,游泳,篮球,武术)中去选择(可以多选),(男,女)[单选]

mysql> create table votes(
    -> username varchar(30),
    -> hobby set('登山','游泳','篮球','武术'),
    -> gender enum('男','女')
    -> );
Query OK, 0 rows affected (0.01 sec)

在这里插入图片描述
这里enum的存储方式就是按下标的方式存储

mysql> insert into votes values('关羽','6','3');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into votes values('关羽','6','-1');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

在这里插入图片描述

# 查找出性别为女性的人
mysql> select * from votes where gender = 2;
+----------+----------------------+--------+
| username | hobby                | gender |
+----------+----------------------+--------+
| 刘备     | 篮球                 ||
| 曹操     | 游泳,篮球,武术       ||
| 孙权     | 登山,游泳,武术       ||
+----------+----------------------+--------+
3 rows in set (0.00 sec)

mysql> select * from votes where gender = '女';
+----------+----------------------+--------+
| username | hobby                | gender |
+----------+----------------------+--------+
| 刘备     | 篮球                 ||
| 曹操     | 游泳,篮球,武术       ||
| 孙权     | 登山,游泳,武术       ||
+----------+----------------------+--------+
3 rows in set (0.00 sec)
# 查找出爱好中包含篮球的人
mysql> select * from votes where hobby = '篮球';
+----------+--------+--------+
| username | hobby  | gender |
+----------+--------+--------+
| 刘备     | 篮球   ||
+----------+--------+--------+
1 row in set (0.00 sec)

我们发现这样的SQL语句并不能准确的找出所有爱好中包含篮球的人,那应该如何操作呢?
集合查询使用find in set函数:
find_in_set(sub,str_list) :如果 sub 在 str_list 中,则返回下标;如果不在,返回0;
str_list 用逗号分隔的字符串。

mysql> select find_in_set('a', 'a,b,c');
+---------------------------+
| find_in_set('a', 'a,b,c') |
+---------------------------+
| 1                         |
+---------------------------+
mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
| 0                         |
+---------------------------+
# 查询爱好篮球的人:
mysql> select * from votes where find_in_set('篮球',hobby);
+----------+-----------------------------+--------+
| username | hobby                       | gender |
+----------+-----------------------------+--------+
| 刘备     | 篮球                        ||
| 曹操     | 游泳,篮球,武术              ||
| 孙权     | 登山,游泳,篮球,武术         ||
| 关羽     | 游泳,篮球                   ||
| 关羽     | 游泳,篮球                   |        |
+----------+-----------------------------+--------+
5 rows in set (0.00 sec)
# 查找同时爱好篮球和武术的人 --- 不能在一个find语句中同时查找两个属性
mysql> select * from votes where find_in_set('篮球',hobby) and find_in_set('武术',hobby);
+----------+-----------------------------+--------+
| username | hobby                       | gender |
+----------+-----------------------------+--------+
| 曹操     | 游泳,篮球,武术              ||
| 孙权     | 登山,游泳,篮球,武术         ||
+----------+-----------------------------+--------+
2 rows in set (0.00 sec)
# 同时拥有所有爱好的就可以用上限来查找
mysql> select * from votes where hobby = 15;
+----------+-----------------------------+--------+
| username | hobby                       | gender |
+----------+-----------------------------+--------+
| 孙权     | 登山,游泳,篮球,武术         ||
+----------+-----------------------------+--------+
1 row in set (0.01 sec)
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
关系型数据库 MySQL 数据库
MySQL数据库基础(数据库操作,常用数据类型,表的操作)
MySQL数据库基础(数据库操作,常用数据类型,表的操作)
33 5
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据类型详解及实例应用
MySQL数据类型详解及实例应用
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库的数据类型、语法和高级查询
MySQL数据库的数据类型、语法和高级查询
68 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL支持多种数据类型
MySQL支持多种数据类型
97 3
|
1月前
|
存储 关系型数据库 MySQL
什么是mysql的数据类型?
什么是mysql的数据类型?
46 2
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据类型
MySQL数据类型
50 2
|
6月前
|
SQL 存储 关系型数据库
RDS for MySQL的SQL分类与数据类型
小明需在MySQL中管理商品信息,使用SQL完成业务操作。SQL分为DQL(查询)、DML(增删改)、DDL(定义)、DCL(权限控制)和TCL(事务)五大类。DDL用于创建、修改和删除数据库结构,DML处理数据,DCL控制权限,TCL管理事务,DQL则用于查询数据。MySQL有多种数据类型,如数值型(整数、小数)、日期型和字符串型等,选择合适的数据类型是高效开发的关键。
66 0
|
3月前
|
关系型数据库 MySQL Linux
数据类型和运算符(MySQL服务器的安装,MySQL客户端,数据类型,运算符,MySQL的语法规范)
无论是对于初学者还是有经验的开发者,了解MySQL的安装、客户端使用、数据类型、运算符和语法规范都是至关重要的。这不仅有助于高效地管理和查询数据,而且对于设计和实现数据库解决方案来说是基础工作。通过深入学习和实践这些知识,您可以更好地发挥MySQL数据库的强大功能。
32 2
|
3月前
|
存储 关系型数据库 MySQL
MySQL 中的 BLOB 数据类型深入解析
【8月更文挑战第31天】
413 0
|
3月前
|
存储 关系型数据库 MySQL