MySQL数据类型

最新推荐文章于 2024-07-15 19:44:21 发布

Xpccccc

最新推荐文章于 2024-07-15 19:44:21 发布

阅读量1k

点赞数 18

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_44121078/article/details/140213798

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

文章目录

MySQL数据类型

MySQL数据类型

1、数据类型分类

1.1、数值类型

数据类型大小说明
TINYINT 1 字节范围：-128 到 127 / 0 到 255，小范围整数
SMALLINT 2 字节范围：-32,768 到 32,767 / 0 到 65,535，中等范围整数
MEDIUMINT 3 字节范围：-2^23 到 2^23-1 / 0 到 2^24-1，大范围整数
INT / INTEGER 4 字节范围：-2^31 到 2^31-1 / 0 到 2^32-1，常用整数
BIGINT 8 字节范围：-2^63 到 2^63-1 / 0 到 2^64-1，超大范围整数
FLOAT(m, d) 4 字节浮点数，单精度。m是总位数，d是小数点后的位数。范围：-3.402823466E+38 到 -1.175494351E-38, 0, 1.175494351E-38 到 3.402823466E+38
DOUBLE(m, d) 8 字节浮点数，双精度。m是总位数，d是小数点后的位数。范围：-1.7976931348623157E+308 到 -2.2250738585072014E-308, 0, 2.2250738585072014E-308 到 1.7976931348623157E+308
DECIMAL(m, d) 可变精确小数点数。m是总位数，d是小数点后的位数。范围依赖于m和d
BIT(m) 1-64 位存储位字段。m是位字段的长度，如果M被忽略，默认为1

数据类型	大小	说明
TINYINT	1 字节	范围：-128 到 127 / 0 到 255，小范围整数
SMALLINT	2 字节	范围：-32,768 到 32,767 / 0 到 65,535，中等范围整数
MEDIUMINT	3 字节	范围：-2^23 到 2^23-1 / 0 到 2^24-1，大范围整数
INT / INTEGER	4 字节	范围：-2^31 到 2^31-1 / 0 到 2^32-1，常用整数
BIGINT	8 字节	范围：-2^63 到 2^63-1 / 0 到 2^64-1，超大范围整数
FLOAT(m, d)	4 字节	浮点数，单精度。`m`是总位数，`d`是小数点后的位数。范围：-3.402823466E+38 到 -1.175494351E-38, 0, 1.175494351E-38 到 3.402823466E+38
DOUBLE(m, d)	8 字节	浮点数，双精度。`m`是总位数，`d`是小数点后的位数。范围：-1.7976931348623157E+308 到 -2.2250738585072014E-308, 0, 2.2250738585072014E-308 到 1.7976931348623157E+308
DECIMAL(m, d)	可变	精确小数点数。`m`是总位数，`d`是小数点后的位数。范围依赖于`m`和`d`
BIT(m)	1-64 位	存储位字段。`m`是位字段的长度，如果M被忽略，默认为1

1.2、日期和时间类型

数据类型大小说明
DATE 3 字节格式：YYYY-MM-DD，日期值
TIME 3 字节格式：HH:MM:SS，时间值或持续时间
DATETIME 8 字节格式：YYYY-MM-DD HH:MM:SS，日期和时间值
TIMESTAMP 4 字节格式：YYYY-MM-DD HH:MM:SS，UNIX 时间戳值
YEAR 1 字节格式：YYYY，年份值

数据类型	大小	说明
DATE	3 字节	格式：YYYY-MM-DD，日期值
TIME	3 字节	格式：HH:MM:SS，时间值或持续时间
DATETIME	8 字节	格式：YYYY-MM-DD HH:MM:SS，日期和时间值
TIMESTAMP	4 字节	格式：YYYY-MM-DD HH:MM:SS，UNIX 时间戳值
YEAR	1 字节	格式：YYYY，年份值

1.3、字符串类型

数据类型大小说明
CHAR(n) 0-255 字节定长字符串。n是字符串的长度
VARCHAR(n) 0-65535 字节变长字符串。n是字符串的最大长度
TINYTEXT 0-255 字节小型文本字段
TEXT 0-65535 字节大型文本字段
MEDIUMTEXT 0-16,777,215 字节中型文本字段
LONGTEXT 0-4,294,967,295 字节超大型文本字段
BINARY(n) 0-255 字节定长二进制字符串。n是字符串的长度
VARBINARY(n) 0-65535 字节变长二进制字符串。n是字符串的最大长度
TINYBLOB 0-255 字节小二进制对象
BLOB 0-65,535 字节二进制大对象
MEDIUMBLOB 0-16,777,215 字节中型二进制对象
LONGBLOB 0-4,294,967,295 字节超大型二进制对象
ENUM(val1, val2, …) 1 或 2 字节枚举类型。值从定义的一组值中选取，最多 65,535 个值
SET(val1, val2, …) 1-8 字节集合类型。可以存储一个预定义集合中的多个值，最多 64 个成员

数据类型	大小	说明
CHAR(n)	0-255 字节	定长字符串。`n`是字符串的长度
VARCHAR(n)	0-65535 字节	变长字符串。`n`是字符串的最大长度
TINYTEXT	0-255 字节	小型文本字段
TEXT	0-65535 字节	大型文本字段
MEDIUMTEXT	0-16,777,215 字节	中型文本字段
LONGTEXT	0-4,294,967,295 字节	超大型文本字段
BINARY(n)	0-255 字节	定长二进制字符串。`n`是字符串的长度
VARBINARY(n)	0-65535 字节	变长二进制字符串。`n`是字符串的最大长度
TINYBLOB	0-255 字节	小二进制对象
BLOB	0-65,535 字节	二进制大对象
MEDIUMBLOB	0-16,777,215 字节	中型二进制对象
LONGBLOB	0-4,294,967,295 字节	超大型二进制对象
ENUM(val1, val2, …)	1 或 2 字节	枚举类型。值从定义的一组值中选取，最多 65,535 个值
SET(val1, val2, …)	1-8 字节	集合类型。可以存储一个预定义集合中的多个值，最多 64 个成员

1.4、JSON 类型

数据类型大小说明
JSON 可变存储 JSON (JavaScript Object Notation) 文本

数据类型	大小	说明
JSON	可变	存储 JSON (JavaScript Object Notation) 文本

1.5、空间数据类型

数据类型大小说明
GEOMETRY 可变存储任意几何值
POINT 25 字节存储点
LINESTRING 可变存储线串
POLYGON 可变存储多边形
MULTIPOINT 可变存储多个点
MULTILINESTRING 可变存储多个线串
MULTIPOLYGON 可变存储多个多边形
GEOMETRYCOLLECTION 可变存储几何集合

数据类型	大小	说明
GEOMETRY	可变	存储任意几何值
POINT	25 字节	存储点
LINESTRING	可变	存储线串
POLYGON	可变	存储多边形
MULTIPOINT	可变	存储多个点
MULTILINESTRING	可变	存储多个线串
MULTIPOLYGON	可变	存储多个多边形
GEOMETRYCOLLECTION	可变	存储几何集合

2、数值类型

2.1、tinyint类型

范围测试：
mysql> create table t1(num tinyint);
Query OK, 0 rows affected (0.25 sec)

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

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

mysql> 
可以看到，在t1表中插入1是成功的，插入128失败，因为tinyint的有符号数值范围是-128～127。

说明：

在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的。
指定为无符号就需要在类型后加上 unsigned 。

无符号案例：
mysql> create table t2(num tinyint unsigned);
Query OK, 0 rows affected (0.03 sec)

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

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

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

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

mysql> 
可以看到，在t2表中插入1、128、255是成功的，插入256、-1失败，因为tinyint的无符号数值范围是0～255。

其他整数类型自行按照上述分类的范围自行验证。

注意：尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

2.2、bit类型

语法：

字段名 bit[(M)] -- 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1

案例：

mysql> create table t3(id int,c bit(8));
Query OK, 0 rows affected (0.03 sec)

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

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

mysql> insert into t3 values(3,256);
ERROR 1406 (22001): Data too long for column 'c' at row 1
mysql> insert into t3 values(3,255);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+------+
| id   | c    |
+------+------+
|    1 |     |
|    2 | 
   |
|    3 | ?     |
+------+------+
3 rows in set (0.00 sec)

mysql>

可以看到，有些c的值不显示！

bit使用的注意事项：

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

mysql> insert into t3 values(4,65);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t3;
+------+------+
| id   | c    |
+------+------+
|    1 |     |
|    2 | 
    |
|    3 | ?     |
|    4 | A    |
+------+------+
4 rows in set (0.00 sec)

mysql>

如果想让bit字段显示为ASCII，可以使用ORD。

mysql> select id,ord(c) from t3;
+------+--------+
| id   | ord(c) |
+------+--------+
|    1 |      2 |
|    2 |     10 |
|    3 |    255 |
|    4 |     65 |
+------+--------+
4 rows in set (0.00 sec)

mysql>

如果我们有这样的值，只存放0或1，这时可以定义bit(1)。这样可以节省空间。

2.3、小数类型

2.3.1、float类型

语法：

字段名 float[(m, d)] [unsigned] -- M指定显示长度，d指定小数位数，占用空间4个字节
-- float后面不带(m, d)则默认，具体默认为多少，不同的操作系统可能不同

案例：

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

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

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

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

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

mysql> insert into t4 values(2,99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t4 values(3,-99.991);
Query OK, 1 row affected (0.01 sec)

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

mysql>

可以看到，字段salary float(4,2)可插入范围是在-99.99 ~ 99.99的，当小数长度大于指定长度时候，会进行四舍五入，四舍五入后可能会越界。

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

案例：

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

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

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

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

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

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

mysql>

这里可以看到，小数部分没写会自动补对于少的位数的0。

2.3.2、decimal类型

语法：
字段名 decimal[(m, d)] [unsigned] -- 定点数m指定长度，d表示小数点的位数
-- decimal后面不带(m, d)则默认，具体默认为多少，不同的操作系统可能不同
案例：

decimal(5,2) 表示的范围是 -999.99 ~ 999.99。这个这float差不多，但是精度更高。

decimal(5,2) usnsigned 表示的范围是 -999.99 ~ 999.99
mysql> create table t6 (id int,salary float(10,8),salary2 decimal(10,8));
Query OK, 0 rows affected (0.03 sec)

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

mysql> select * from t6;
+------+-------------+-------------+
| id   | salary      | salary2     |
+------+-------------+-------------+
|    1 | 11.12345695 | 11.12345678 |
+------+-------------+-------------+
1 row in set (0.00 sec)

mysql> 
说明：

float表示的精度大约是7位

decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0。如果m被省略，默认是10。

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

3、字符类型

3.1、char类型

语法：

char(L) -- 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

案例：

mysql> create table t7(id int,name char(2));
Query OK, 0 rows affected (0.03 sec)

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

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

mysql> select * from t7;
+------+--------+
| id   | name   |
+------+--------+
|    1 | aa     |
|    1 | 云大   |
+------+--------+
2 rows in set (0.00 sec)

mysql>

可以看到，汉字和字符是一样的，char(2)表示只能存两个字符或者汉字。

另外char(L)中的L，最大为255。

如

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

3.2、varchar类型

语法：

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

案例：

mysql> create table t8(id int,name varchar(5));
Query OK, 0 rows affected (0.03 sec)

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

mysql> insert into t8 values(1,'abcdef');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into t8 values(2,'哈哈你好啊');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+------+-----------------+
| id   | name            |
+------+-----------------+
|    1 | abcde           |
|    2 | 哈哈你好啊      |
+------+-----------------+
2 rows in set (0.00 sec)

mysql>

说明：

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

案例：默认是utf8

mysql> create table t9(name varchar(21845));
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 t9(name varchar(21844));
Query OK, 0 rows affected (0.03 sec)

mysql>

指定为gbk：

mysql> create table t10(name varchar(32767)) character set=gbk;
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 t10(name varchar(32766)) character set=gbk;
Query OK, 0 rows affected (0.04 sec)

mysql>

3.3、char类型和varchar类型的比较

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

如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5

如果数据长度有变化，就使用变长(varchar)，比如：名字，地址，但是你要保证最长的能存的进去。

定长的磁盘空间比较浪费，但是效率高。

变长的磁盘空间比较节省，但是效率低。

定长的意义是，直接开辟好对应的空间。

变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4、日期和时间类型

常用的几个：

date：日期。格式：YYYY-MM-DD，日期值，占用3字节。

datetime：日期时间。格式：YYYY-MM-DD HH:MM:SS，日期和时间值，占用8字节。

timestamp：时间戳，从1970年开始的。格式：YYYY-MM-DD HH:MM:SS，UNIX 时间戳值，占用4字节。

案例：

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

mysql> insert into t11(t1,t2) values('2024-7-5','2024-7-5 16:42:30');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t11;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2024-07-05 | 2024-07-05 16:42:30 | 2024-07-05 16:43:37 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

我们可以看到，时间戳是自动生成的。

并且我们更新数据，被更新数据的那行的时间戳会自动改为当前时间。

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

mysql> select * from t11;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2024-07-04 | 2024-07-05 16:42:30 | 2024-07-05 16:46:13 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

5、enum类型和set类型

enum：枚举，“单选”类型；

语法：
enum('选项1','选项2','选项3',...)
该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1，2，3，… 最多65535个；当我们添加枚举值时，也可以添加对应的数字编号（不建议，可视化不行）。

set：集合，“多选”类型；

语法：
set('选项值1','选项值2','选项值3', ...)
该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，… 最多64个，如果多选，那么整体数字就是每个选项的数字的和（即用对于比特位表示的）。

说明：不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读。

案例：
mysql> create table t12(
   -> name varchar(10),
   -> hobby enum('唱','跳','rap','篮球'),
   -> ^C
mysql> create table t12(
   -> name varchar(10),
   -> gender enum('男','女'),
   -> hobby set('唱','跳','rap','篮球'));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t12 values('xp','男','唱');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t12 values('xx','男','唱,跳');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t12 values('pp','未知','唱,跳');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t12 values('pp','女','唱,跳,rap');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t12 values('ppp',0,'唱,rap');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into t12 values('ppp',1,'唱,rap');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t12 values('xxx',2,1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t12 values('xxpp',2,7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t12;
+------+--------+-------------+
| name | gender | hobby       |
+------+--------+-------------+
| xp   | 男     | 唱          |
| xx   | 男     | 唱,跳       |
| pp   | 女     | 唱,跳,rap   |
| ppp  | 男     | 唱,rap      |
| xxx  | 女     | 唱          |
| xxpp | 女     | 唱,跳,rap   |
+------+--------+-------------+
6 rows in set (0.00 sec)

mysql> 
使用查询语句：
mysql> select * from t12 where hobby='唱';
+------+--------+-------+
| name | gender | hobby |
+------+--------+-------+
| xp   | 男     | 唱    |
| xxx  | 女     | 唱    |
+------+--------+-------+
2 rows in set (0.00 sec)

mysql> 
可以看到只能查询到精确为只’唱’的人，查询不到所有会’唱’的人。

集合查询使用find_ in_ set函数：

语法：
find_in_set(sub,str_list) -- 如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串
案例：
mysql> select * from t12 where find_in_set('唱',hobby);
+------+--------+-------------+
| name | gender | hobby       |
+------+--------+-------------+
| xp   | 男     | 唱          |
| xx   | 男     | 唱,跳       |
| pp   | 女     | 唱,跳,rap   |
| ppp  | 男     | 唱,rap      |
| xxx  | 女     | 唱          |
| xxpp | 女     | 唱,跳,rap   |
+------+--------+-------------+
6 rows in set (0.01 sec)

mysql> 
可以看到查询到了所有会’唱’的人。