【MySQL】常见数据类型总结

已于 2022-09-25 11:45:15 修改
阅读量513 收藏 2
点赞数 3
分类专栏： MySQL 文章标签： mysql 数据库 database
于 2022-05-29 12:54:15 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_51064412/article/details/125001234
版权

文章目录

一. 数值类型

1. 概览

类型字节取值范围
tinyint1带符号：[-128, 127]
不带符号：[0, 255]
smallint2带符号：[-32768, 32767]
不带符号：[0, 65535]
mediumint3带符号：[-8388608, 8388607]
不带符号：[0, 16777215]
int4带符号：[-2147483648, 2147483647]
不带符号：[0, 4294967295]
bigint8带符号：[-9223372036854775808, 9223372036854775807]
不带符号：[0, 18446744073709551615]

2. tinyint类型

  • 类型大小：1字节
  • 带符号时的数据范围：[-128, 127]
  • 不带符号时的数据范围：[0, 255]

示例：
有符号的tinyint类型字段：

mysql> create table if not exists t1(
    -> num tinyint);
Query OK, 0 rows affected (0.02 sec)

// 127在规定的范围内，插入成功
mysql> insert into t1 values(127);
Query OK, 1 row affected (0.01 sec)

// 128大于规定的范围，插入失败
mysql> insert into t1 values(128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

// 插入结果查询
mysql> select * from t1;
+------+
| num  |
+------+
|  127 |
+------+
1 row in set (0.00 sec)

无符号的tinyint类型字段：

mysql> create table if not exists t2(
    -> num tinyint unsigned);
Query OK, 0 rows affected (0.03 sec)

// 255在范围内，插入成功
mysql> insert into t2 values (255);
Query OK, 1 row affected (0.00 sec)

// 256大于规定的数据范围，插入失败
mysql> insert into t2 values (256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

// 插入结果查询
mysql> select * from t2;
+------+
| num  |
+------+
|  255 |
+------+
1 row in set (0.01 sec)

说明：

  • 在MySQL中，数值类型都可以指定是有符号的还是无符号的，默认是有符号的。可以在数值类型后面加上unsigned来说明某个字段是无符号的。
  • 不要因为范围的原因而使用unsigned。比如对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。
  • MySQL对于数据的存储，本身具有很严格的约束。说准确点，MySQL数据类型本身就是一种约束。
    在这里插入图片描述

3. bit类型

基本语法：
bit(M)：位字段类型。M表示所能存储值的最大二进制位数，范围从1到64。如果M被忽略，默认为1。

示例：
在这里插入图片描述

原因在于：bit类型的字段在显示时，是按照ASCII码对应的值来显示的，而ASCLL码为10对应的是一个控制字符，自然无法显示。下面我们插入65这个数字，看看这次能否显示出来：

mysql> select * from t1;
+------+------+
| id   | a    |
+------+------+
|   10 | 
    |
+------+------+
1 row in set (0.00 sec)

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

mysql> select * from t1;
+------+------+
| id   | a    |
+------+------+
|   10 | 
    |
|   65 | A    |
+------+------+
2 rows in set (0.00 sec)

可以看到65数字插入a字段后，显示出来的是字符’A’，确实说明了bit类型的字段在显示时，是按照ASCII码对应的值来显示的。

作为一种数据类型，bit也有对应的数据范围约束，即我们插入的数据其二进制长度不能超过M：

mysql> create table if not exists t2(
    -> gender bit(1) comment '0:男, 1:女');
Query OK, 0 rows affected (0.02 sec)

// 0的二进制：0 --- 插入成功
mysql> insert into t2 values (0);
Query OK, 1 row affected (0.02 sec)

// 2的二进制：10 --- 插入失败
mysql> insert into t2 values (2);
ERROR 1406 (22001): Data too long for column 'gender' at row 1

bit使用场景：实际中，如果我们有这样的字段，其值只存放0或1，这时可以把它定义为bit(1)类型。缺点是数据无法被显示出来，但是这样可以节省空间；如果这张表结构简单且只是交给机器识别的话，我们可以定义bit类型的字段。

4. float类型

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

注意：MySQL在保存浮点数时会进行四舍五入。

示例：
float(4, 2)表示的数据范围是[-99.99 ~ 99.99]，下面我们来看看该类型的约束情况：

mysql> create table if not exists t1(
    -> salary float(4, 2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values (99.99);// 在规定范围之内，插入成功
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (99.993);// 四舍后插入成功
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (99.995);// 五入后插入失败
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

mysql> insert into t1 values (100.00);// 本身超过数据类型规定的范围，插入失败
ERROR 1264 (22003): Out of range value for column 'salary' at row 1

mysql> select * from t1;// 最终只有前两个插入成功
+--------+
| salary |
+--------+
|  99.99 |
|  99.99 |
+--------+
2 rows in set (0.00 sec)

对浮点数而言，无符号类型的浮点数还是经常用到的，比如：工资、得分等。如果定义的类型是float(4, 2) unsigned，其能存储的数据范围是[0 ~ 99.99]。

mysql> create table if not exists t2(
    -> score float(4, 2) unsigned);
Query OK, 0 rows affected (0.02 sec)

// 在范围内，插入成功
mysql> insert into t2 values (28.4);
Query OK, 1 row affected (0.00 sec)

// 负数不在规定范围内，插入失败
mysql> insert into t2 values (-23.3);// 不允许插入负数
ERROR 1264 (22003): Out of range value for column 'score' at row 1

// 查询插入结果
mysql> select * from t2;
+-------+
| score |
+-------+
| 28.40 |
+-------+
1 row in set (0.01 sec)

5. decimal类型

语法：
decimal[(m, d)] [unsigned]：m指定长度，d表示小数点的位数。不指定时默认decimal(10, 0)。

用法：
用法上decimal与float别无二致。

  • decimal(5,2) 表示的范围是 -999.99 ~ 999.99
  • ecimal(5,2) unsigned 表示的范围 0 ~ 999.99

float 和 decimal的区别

float和decimal的区别在于二者表示的有效数字位数不同：

  • float能表示的有效数字位数是7位。
  • decimal能表示的有效数字位数是38位。

在这里插入图片描述

使用场景：如果希望小数的精度高，推荐使用decimal。

二. 字符串类型

1. char类型

语法：
char(len): 固定长度字符串，len表示可以存储的字符长度，最大值可为255。

说明：
1、定义char类型字段时，最大长度不能超过255，否则会报错：

mysql> create table if not exists t1(
    -> name char(256));
ERROR 1074 (42000): Column length too big for column 'name' (max = 255); use BLOB or TEXT instead

2、固定长度的字符串，其长度本身也是一种约束：

mysql> create table if not exists t1(
    -> name char(2));// name字段限定最多存储两个字符
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values ('a');// 只有一个字符，插入成功
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('ab');// 两个字符，插入成功
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc');// 三个字符，插入失败，超过了name字段限定的长度
ERROR 1406 (22001): Data too long for column 'name' at row 1

mysql> select * from t1;
+------+
| name |
+------+
| a    |
| ab   |
+------+
2 rows in set (0.00 sec)

3、MySQL中的字符指的是：一个字母、一个符号或一个汉字，这些字符不一定非得是1个字节，也可能是2字节或3字节，具体多大与字符集的种类有关。即MySQL限定“字符”这个概念不是凭借字节大小，这有一个非常大的好处，就是不需要让用户再关心复杂的编码的细节了，区别C/C++中的char类型，在哪里一个字符的大小是固定的一个字节。

mysql> create table if not exists t2(
    -> name char(2));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t2 values ('中');// 一个字符，插入成功
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values ('中国');// 两个字符，插入成功
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values ('中国人');// 三个字符，插入失败
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> select * from t2;
+--------+
| name   |
+--------+
| 中     |
| 中国   |
+--------+
2 rows in set (0.00 sec)

mysql>

4、在MySQL中，char(len)类型也叫作固定长度字符串，这里的“固定长度”指的是直接分配给你长度为len字符所需的空间，实际你用了多少字符那是你的事，具体一个字符占多少字节那是字符集种类决定的。

2. varchar类型

varchar(len)：可变长度字符串，len表示最大允许存储的字符长度，规定最大允许存储65535个字节大小的字符，具体最大字符个数由字符集种类决定。

varchar能存储的最大字符个数

  • varchar能存储数据的字节大小范围是[0, 65535]，其中需要用到1 - 3个字节的空间来记录当前存储的字符个数，所以说最大有效字节空间是65532。

1、当我们表的字符集编码是utf8时，varchar(n)中参数n的最大值是65533/3=21844（因为utf8中，一个字符占用3个字节，需要2个字节来记录所存储的字符个数）：
在这里插入图片描述

2、如果字符集编码是gbk的话，varchar(n)的参数n最大是65533/2=32766（因为gbk中，一个字符占用2字节，另外需要2个字节来记录所存储字符个数）：
在这里插入图片描述

char和varchar比较

  • 相同点：二者最大存储的字符长度不能超过定义时给定的值len。
  • 不同点：
    • char(len)按len的大小以及字符集种类来决定该字段需要多少内存空间。
    • varchar(len)按实际存储的字符个数以及字符集种类决定该字段需要多少内存空间。

示例：
在这里插入图片描述

如何选择定长或变长字符串？

  • 如果字段确定好了固定的长度，就使用定长（char），比如：身份证号，手机号等。
  • 如果字段的长度有变化，就使用变长(varchar)，比如：名字，地址等。但是你要保证最长的能存的进去。
  • 定长字符串的磁盘空间比较浪费，但是读取效率高。因为它直接读取len字符长度大小的内存空间就行。
  • 变长的磁盘空间比较节省，但是效率低。因为读取之前要事先确定目前存储了多少有效字符。
  • 定长的意义是，直接开辟好对应的内存空间。
  • 变长的意义是，在不超过自定义长度len的情况下，实际用多少字符，就开辟多少空间。

三. 时间日期类型

常用的日期类型有如下三种：

  • date：格式 ‘yyyy-mm-dd’ 。仅表示日期，占用三字节。
  • datetime：格式 ‘yyyy-mm-dd HH:ii:ss’ 。表示日期时间，占用八字节。
  • timestamp：表示时间戳，该字段的数据不需要显示地插入，MySQL会自动补上当前的日期时间且表中该行记录的其他字段内容发送改变时，这个时间戳字段的数值也会相应更新。

举例：

// 1、创建一张表示生日信息的表
mysql> create table if not exists birthday(
    -> t1 date,
    -> t2 datetime,
    -> t3 timestamp);
Query OK, 0 rows affected (0.03 sec)

// 2、向表中插入一条数据
mysql> insert into birthday (t1, t2) values ('2001-07-29', '2001-07-29 00:00:00');
Query OK, 1 row affected (0.00 sec)

// 3、插入结果查询
mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2001-07-29 | 2001-07-29 00:00:00 | 2022-05-28 13:05:11 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

// 4、过一段时间后更改数据信息，发现时间戳字段的数据自动更新了
mysql> update birthday set t1='1999-07-29';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from birthday;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 1999-07-29 | 2001-07-29 00:00:00 | 2022-05-28 13:08:45 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

四. enum和set

枚举：
enum：枚举，“单选”类型。该设定提供了若干个选项的值，最终一个单元格中实际只存储了其中一个值。出于效率考虑，这些值实际存储的是“数字”，因为这些选项中每个选项的值依次对应如下数字：1,2,3,…最多65535个，所以我们在添加枚举值时，也可以用数字编号去代替具体的选项值。

枚举语法：enum(‘选项1’, ‘选项2’, ‘选项3’,…);

集合：
set：集合，“多选”类型。该设定提供了若干个选项的值，最终一个单元格中可存储其中任意多个值。出于效率考虑，这些值实际存储的仍是“数字”；最后按顺序每个选项值依次对应到如下数字：1,2,4,8,16,32，… 最多64个。

集合语法：set(‘选项值1’, ‘选项值2’, ‘选项值3’, …);

PS：在向set类型的字段插入数据时，把需要的选项统一写到一个共同的单引号中用逗号隔开，且逗号后面不能有空格。

举例：

  • 如果你给出了enum的范围，那在插入时只能多选一，且插入的字段必须在enum中出现。
  • 如果你限定了set的范围，那么可以多选多，插入的字段必须在set中出现。

PS：如果不满足上述要求，MySQL会直接终止你的SQL语句，这也是一种约束的体现。

mysql> create table if not exists Hero(
    -> name varchar(4),
    -> gender enum('男', '女'),
    -> property set('战士', '法师', '坦克', '刺客', '射手', '辅助'));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into Hero values ('赵云', '男', '战士,刺客');
Query OK, 1 row affected (0.01 sec)

mysql> insert into Hero values ('钟馗', '男', '辅助,法师');
Query OK, 1 row affected (0.00 sec)

mysql> select * from Hero;
+--------+--------+---------------+
| name   | gender | property      |
+--------+--------+---------------+
| 赵云    | 男     | 战士,刺客      |
| 钟馗    | 男     | 法师,辅助      |
+--------+--------+---------------+
2 rows in set (0.00 sec)

关于插入数据的一些说明

1、在向enum类型的字段中插入数据的时候，我们可以采用数字的方案：1,2,3,4…，这些数字可以看做是每一个选项的下标，下标对应的的内容就是enum中一个一个枚举出来的元素。
在这里插入图片描述

2、使用数字的方案向set类型的字段中插入数据，不同于enum这时下标代表的是一个指数，具体2^n才是代表元素的数值，这个下标从0开始。
在这里插入图片描述

总结：不推荐在插入枚举、集合数据的时候采用数字的方式代替选项值，因为这样会导致SQL语句的可读性变差，且维护起来很困难。

补充：查询包含某个集合元素记录的方法

1、广泛查询
这里需要介绍一下查询相关的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 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('c', 'a,b,c');
+---------------------------+
| find_in_set('c', 'a,b,c') |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)

查询Hero表中，属性包含“辅助”的英雄：
在这里插入图片描述

2、严格查询：select * from 表名 where 集合字段='需要查询的选项';

在这里插入图片描述

帝国无伤
关注
  • 3
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql中字段大小n a_MySQL中字段类型与合理的选择字段类型；int(11)最大长度是多少？，varchar最大长度是多少...
weixin_42138545的博客
02-18 226
字段类型数值MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分(UNSIGNED)或者用零填补(ZEROFILL)。 MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展(如 INT(6),6即是其宽度指示器,该宽...
【MySQL篇】第四篇——数据类型
m0_58367586的博客
11-23 646
数据类型是指系统中所允许的数据类型。MySQL数据类型定义了列中可以存储什么数据以及该数据怎么样存储的规则。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。
MySQL数据库的数据类型以及取值范围详解
03-04 1万+
主要包括以下五大类： 整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT 浮点数类型：FLOAT、DOUBLE、DECIMAL 字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB 日期类型：Dat...
mysql 各类型取值范围
sxf_123456的博客
11-27 910
Mysql中数据类型整理 数据类型 名称 字节数 有符号取值范围 有符号指数取值范围 无符号取值范围 tinyint 微小整数 1 （-128,127） (-2^7,2^7-1) （0,255） smallint 小整数 2 （-32768,32767） (-2^15,2^15-1) （0,6553...
MySQL常见面试题总结.docx
03-24
MySQL是世界上最流行的关系型数据库管理系统之一，其性能和效率在很大程度上取决于如何有效地利用索引。索引是一种特殊的数据结构，它允许数据库快速找到数据行，就像书籍的目录可以帮助我们快速定位到想要阅读的...
MySQL常见面试题总结
最新发布
11-01
MySQL是一种广泛使用的开源关系型数据库管理系统，其设计和操作基于关系模型理论，使得数据之间的联系清晰易懂。SQL，全称Structured Query Language，是用于管理关系数据库的标准语言，包括创建数据库对象、操作...
MySQL常见面试题总结.pdf
06-08
MySQL常见面试题总结主要涉及索引这一核心概念。索引是一种数据库系统为了加速查询而创建的数据结构，它指向数据并支持高效查找算法。创建索引的主要优点在于提高系统的查询性能，减少扫描的数据行数，避免排序和...
python3实现往mysql中插入datetime类型的数据
09-08
然而，初学者可能会遇到问题，比如在尝试将datetime对象直接作为字符串插入时，可能会因为数据类型不匹配而失败。例如，直接使用`%(dt)`或者`'%s' % dt`作为SQL语句的占位符，由于dt是datetime对象，而不是字符串，...
MYSQL ERROR 1292 错误解决办法 -- 数据类型不匹配
热门推荐
哈维敖尔的博客
06-29 1万+
1292 错误
mysql insert into select插入表中的数据与select的数据不一样
叫我泽西哥好吗？（手动滑稽 ‾◡◝）
10-09 5431
同一个动态拼接sql的写法： 使用变量拼接sql的受影响的行为6881（错误）, 不使用变量动态拼接sql的受影响的行为6916（正确）。 最后发现是用变量拼接sql中变量中有单引号影响了结果。 用双引号包住sql，问题消失。错误： set @monthTime=DATE_FORMAT(now(),'%Y%m'); set @clientTime_1=date_format(date_sub
数据库中使用float型和decimal字段的注意事项
chenzhe6622的博客
03-29 1347
1. float 默认只保存6位（除去小数点），如果超过6位，则四舍五入，所以float存储的数据是不精确的，只是近似值； 2. decimal，如果输入的数据超过了定义的最大值，那么则溢出，数据库里面存储的是定义的最大值，例如，decimal(5,2)输入123123.45,实际存储为99...
SQL Server Len() 函数
Lu鹿夫人的博客
07-05 3823
LEN() 函数用于输入字符串的字符数。LEN() 函数返回输入字符串的字符数，不包括尾随空格。以下是LEN() 函数的语法： 在此语法中，input_string 可以是文字字符串，字符串表达式或字符或二进制数据的列。如果input_string 是 VARCHAR(max)，NVARCHAR(max) 或 VARBINARY(max)数据类型，则LEN() 函数返回数据类型为 BIGINT 的值；否则返回为 INT 类型。...
mysql len函数的用法_SQL LEN() 函数
weixin_42237487的博客
01-27 1万+
SQL 中的 LEN() 函数返回文本字段中值的长度SELECT LEN( column_name ) FROM table_name;LEN() 返回的是数据库服务器编码下的字符串长度，如果数据库服务器的编码是 UTF-8，那么 '中国' 将返回 6不过 MySQL 中没有 LEN() 函数，取而代之的是 LENGTH() 函数，但用法都一样SELECT LENGTH( column_name ...
彻底了解mysql int类型
heizistudio的专栏
04-16 7070
mysql> create table t1(id int(40) unsigned zerofill); Query OK, 0 rows affected (0.01 sec) mysql> insert into t set id= 4294967295; Query OK, 1 row affected (0.00 sec) mysql> commit; Quer
Mysql中的int类型默认为有符号类型的
wocjj的专栏
04-07 6470
Mysql中的int类型默认为有符号类型的 2012-02-14 神圣的工作在每小我的日常事务里，胡想的前程在于一点一滴做起。access 多表查询有反复的字段，并且要前台绑定，怎么办？access用 列表+空格+别号可不成！ 美国哈佛大黉舍长普西曾经深切地指出，一小我是否具有创新才能，是“一流人才和三流人才之间的分水岭。”     ————中国、朗加明著《创新的
MySQL 62 道面试题及答案.docx
07-07
在这篇文章中，我将对MySQL数据库中的一些常见问题进行总结和回答，以帮助读者更好地理解和使用MySQL。 首先，我将从锁方面开始介绍。MySQL中有几种不同的锁类型，分别是表级锁、行级锁和页面锁。表级锁的优点是...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值