![](https://i-blog.csdnimg.cn/blog_migrate/96486699aa4ae46238f7e9d778255caf.png)
目录
【注】本章代码测试建议在 MySQL5.7中进行。
1. MySQL中的数据类型
类型 | 类型举例 |
---|---|
整数类型 | TINYINT, SMALLINT, MEDIUMINT, INT(或INTEGER), BIGINT |
浮点类型 | FLOAT, DOUBLE |
定点数类型 | DECIMAL |
位类型 | BIT |
日期时间类型 | YEAR, TIME, DATE, DATETIME, TIMESTAMP |
文本字符串类型 | CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT |
枚举类型 | ENUM |
集合类型 | SET |
二进制字符串类型 | BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB |
JSON类型 | JSON对象,JSON数组 |
空间数据类型 | 单值:GEOMETRY, POINT, LINESTRING, POLYGON; 集合:MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION |
常见数据类型的属性如下:
MySQL关键字 | 含义 |
---|---|
NULL | 数据列可包含NULL值 |
NOT NULL | 数据列不允许包含NULL值 |
DEFAULT | 默认值 |
PRIMARY KEY | 主键 |
AUTO_INCREMENT | 自动递增,适用于整数类型 |
UNSIGNED | 无符号 |
CHARACTER SET name | 指定一个字符集 |
2. 整数类型
2.1 类型介绍
整数类型 | 字节 | 有符号数范围 | 无符号数范围 |
---|---|---|---|
TINYINT | 1 | -128~127 | 0~255 |
SMALLINT | 2 | -32768~32767 | 0~65535 |
MEDIUMINT | 3 | -8388608~8388607 | 0~16777215 |
INT、INTEGER | 4 | -2147483648~2147483647 | 0~4294967295 |
BIGINT | 8 | -9223372036854775808~9223372036854775807 | 0~18446744073709551616 |
2.2 例子说明
举个例子:
创建一张表,5 个字段分别是五种不同的整数类型。
CREATE TABLE test_int1(
f1 TINYINT,
f2 SMALLINT,
f3 MEDIUMINT,
f4 INT,
f5 BIGINT
);
查询结果:
- 不能超过表示范围
如果我往字段 f1
中插入数据 128
,这就超过了 TINYINT 有符号的最大表示范围 127 。MySQL就会报错:
INSERT INTO test_int1(f1)
VALUES(128);
输出:
错误代码: 1264
Out of range value for column 'f1' at row 1
- MySQL5.7中的差异
在5.7中查看字段属性,
DESC test_int1;
输出:
可以看到在 type
这一栏比 8.0 的数据类型后面多了一个括号。里面的数字表示的是每一个整数数据类型的数据宽度,如,TINYINT 的范围是 -128~127 ,带上负号一共占用 4 位宽度,因此 TINYINT 小括号里的数字就是 4
,其他以此类推。
由此衍生出一个属性,那就是 “显示宽度” 。
2.3 可选属性
- 显示宽度
在创建表声明字段的数据类型时,可以在数据类型后面添加小括号来声明该数据类型的显示宽度,一般要搭配关键字 ZEROFILL 来使用。例如:
CREATE TABLE test_int2(
f1 INT,
f2 INT(5),
f3 INT(5) ZEROFILL
);
查询结果:
往该表中插入数据,并查询:
INSERT INTO test_int2(f1, f2, f3)
VALUES
(123, 123, 123),
(123456, 123456, 123456);
SELECT *
FROM test_int2;
查询结果:
【说明】
① 显示宽度并不会限制数据类型原来的表示范围。具体来说,当插入的数据大于显示宽度且在表示范围内时,数据正常显示;当插入的数据小于显示宽度且该字段被关键字 ZEROFILL 修饰时 (如 f3
),其不满足显示宽度的部分就会被 0 填充。如果插入数据小于显示宽度,但该字段没有被关键字 ZEROFILL 修饰时 (如字段 f2
) ,则不会被 0 填充,所以声明的显示宽度也没有意义。故字段的显示宽度一般需要与关键字 ZEROFILL 搭配使用。
② 从MySQL8.0.17开始,整数数据类型就不推荐使用显示宽度属性了。
③ 添加了关键字 ZEROFILL 的字段,MySQL默认就将其声明为无符号 unsigned
类型的字段。
- 无符号UNSIGNED
在创建表格时可以使用关键字 UNSIGNED 来修饰该字段的数据类型,表示无符号,即该字段全是非负数的。
举个例子,
CREATE TABLE test_int3(
f1 INT UNSIGNED
);
2.4 使用场景
- TINYINT:一般用于枚举数据,比如系统设定取值范围很小且固定的场景。
- SMALLINT:可以用于较小范围的统计数据,比如统计工厂的固定资产库存数量等。
- MEDIUMINT:用于较大整数的计算,比如车站每日的客流量等。
- INT:取值范围足够大,一般情况下不用考虑超限问题,用得最多。比如商品编号。
- BIGINT:只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。
2.5 如何选择
在评估用哪种整数类型的时候,你需要考虑存储空间和可靠性的平衡问题:一方面,用占用字节数少的整数类型可以节省存储空间;另一方面,要是为了节省存储空间,使用的整数类型取值范围太小,一旦遇到超出取值范围的情况,就可能引起系统错误,影响可靠性。
举个例子,商品编号采用的数据类型是 INT 。原因就在于,客户门店中流通的商品种类较多,而且,每
天都有旧商品下架,新商品上架,这样不断迭代,日积月累。
如果使用 SMALLINT 类型,虽然占用字节数比 INT 类型的整数少,但是却不能保证数据不会超出范围 65535。相反,使用 INT,就能确保有足够大的取值范围,不用担心数据超出范围影响可靠性的问题。
你要注意的是,在实际工作中,系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因
此,建议首先确保数据不会超过取值范围,在这个前提之下,再去考虑如何节省存储空间。
3. 浮点类型
MySQL表示小数的数据类型有两种,分别是浮点类型和定点类型。由于浮点类型精度不够高,容易造成精度丢失,因此在实际开发场景中常使用定点数类型来确保精度。
整数可以通过隐式转换赋给浮点数。
3.1 类型介绍
MySQL中浮点类型有三种:FLOAT、DOUBLE 和 REAL 。其中 REAL 其实就是 FLOAT 。
浮点类型 | 字节 |
---|---|
FLOAT | 4 |
DOUBLE | 8 |
【注意】
- 浮点类型声明为无符号 UNSIGNED 之后,其表示范围并不会扩大一倍。其底层就是有符号数值范围大于零的部分。即,浮点类型声明为无符号 UNSIGNED 之后,其表示范围缩小为有符号的一半。
3.2 数据精度说明
MySQL中的浮点数支持非标准语法 (即其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么使用) 。
FLOAT(M, D)
# 或
DOUBLE(M, D)
其中,M
称为精度,表示整数位+小数位;D
称为标度,表示小数位。
举个例子:
FLOAT(5, 2)
表示整数位有 3 位,小数位有 2 位,加起来一共 5 位,表示范围是 -999.99 ~ 999.99 。
【说明】
- 使用 (M ,D) 语法,整数部分超出 (M - D) ,就会报错;小数部分超出 D ,MySQL就会四舍五入。
- 如果不使用 (M ,D) 语法,就会由操作系统来决定精度和标度。
- 浮点类型,也可以加UNSIGNED ,但是不会改变数据范围,例如:FLOAT(3,2) UNSIGNED仍然只能表示 0 ~ 9.99 的范围。
3.3 精度误差说明
浮点数是不准确的,所以我们要避免使用 =
来判断两个浮点数是否相等。
同时,在一些对精确度要求较高的项目中,千万不要使用浮点数,不然会导致结果错误,甚至是造成不可挽回的损失。那么,MySQL 有没有精准的数据类型呢?当然有,这就是定点数类型: DECIMAL 。
4. 定点数类型
4.1 类型介绍
定点类型 | 字节数 | 含义 |
---|---|---|
DECIMAL(M, D) / DEC / NUMERIC | M + 2 | 有效范围由M和D决定 |
使用 DECIMAL(M,D) 的方式表示高精度小数。其中,M 被称为精度,D 被称为标度。0 <= M <= 65,0 <= D <= 30,D < M。
例如,定义 DECIMAL(5, 2) 的类型,表示该列取值范是 -999.99 ~ 999.99 。
4.2 说明
- DECIMAL(M, D) 的最大取值范围与 DOUBLE 类型一样,但是有效的数据范围是由 M 和 D 决定的。DECIMAL 的存储空间并不是固定的,由精度值 M 决定,总共占用的存储空间为 M + 2 个字节。也就是说,在一些对精度要求不高的场景下,比起占用同样字节长度的定点数,浮点数表达的数值范围可以更大一些。
- 定点数在 MySQL 内部是以字符串的形式进行存储,这就决定了它一定是精准的。
- 当 DECIMAL 类型不指定精度和标度时,其默认为 DECIMAL(10, 0) ,即默认是没有小数位的。当数据的精度超出了定点数类型的精度范围时,则 MySQL 同样会进行四舍五入处理。
举个例子:
创建一张表,字段为 2 个定点类型,一个没指定精度和标度,另一个则指明了:
CREATE TABLE test_decimal1(
f1 DECIMAL,
f2 DECIMAL(5, 2)
);
查询结果:
红框中的小括号里的精度和标度在 MySQL 8.0 中同样也会显示,因为 DECIMAL 是需要精确的。
然后往表 test_decimal1
中插入数据并查询:
INSERT INTO test_decimal1(f1)
VALUES(123), (123.45);
SELECT *
FROM test_decimal1;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/d7fe07d8afda1bf7e4a0e41860d71494.png)
可以看到,由于默认情况下 DECIMAL 为 (10, 0) 没有小数位,所以对有小数位的数据进行四舍五入操作。
接下来往字段 f2
中插入数据:
INSERT INTO test_decimal1(f2)
VALUES(999.99), (67.567);
SELECT *
FROM test_decimal1;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/ee02ef4ed8d4c93b571c8a6a5c3488b0.png)
可以看到数据 67.567
由于小数位超出标度因此被四舍五入了。
再插入数据:
INSERT INTO test_decimal1(f2)
VALUES(1267.567);
输出:
错误代码: 1264
Out of range value for column 'f2' at row 1
可以看到,整数位数超过 (M - D) 时就会报错。
同理,因小数位四舍五入操作导致整数位超过 (M - D) 时也会报错:
INSERT INTO test_decimal1(f2)
VALUES(999.995);
输出:
错误代码: 1264
Out of range value for column 'f2' at row 1
4.3 定点类型与浮点类型精度的对比
这里用一个简单的例子来体现两种数据类型精度的差别:计算三个小数之和:0.47、0.44、0.19 。正确的答案应该是 1.10 。接下来我们分别查看浮点类型和定点类型的计算精度表现。
- 浮点类型
# 建表
CREATE TABLE test_double(
f1 DOUBLE
);
# 插入数据
INSERT INTO test_double
VALUES(0.47), (0.44), (0.19);
# 计算总和
SELECT SUM(f1)
FROM test_double;
输出:
SELECT SUM(f1) = 1.1, 1.1 = 1.1
FROM test_double;
输出:
- 定点类型
# 建表
CREATE TABLE test_decimal(
f1 DECIMAL(5, 2)
);
# 插入数据
INSERT INTO test_decimal
VALUES(0.47), (0.44), (0.19);
# 计算总和
SELECT SUM(f1)
FROM test_decimal;
输出:
SELECT SUM(f1) = 1.1, 1.1 = 1.1
FROM test_decimal;
输出:
通过上面两个例子可以得出结论,定点类型的精度要高于浮点类型。
4.4 如何选择
- 浮点数:相对于定点数的优点是在长度一定的情况下,浮点类型取值范围大,但是不精准,适用于需要取值范围大,又可以容忍微小误差的科学计算场景(比如计算化学、分子建模、流体动力学等)。
- 定点数:取值范围相对小,但是精准,没有误差,适合于对精度要求极高的场景 (比如涉及金额计算的场景)。
4.5 开发中的经验
由于 DECIMAL 数据类型的精准性,在我们的项目中,除了极少数(比如商品编号)用到整数类型
外,其他的数值都用的是 DECIMAL,原因就是这个项目所处的零售行业,要求精准,一分钱也不能
差。
5. 位类型
位类型 BIT 存储的是二进制值。
二进制字符串类型 | 长度 | 长度范围 | 占用空间 |
---|---|---|---|
BIT(M) | M | 1 <= M <= 64 | (M+7)/8 个字节 |
BIT类型,如果没有指定(M),默认是1位。这个1位,表示只能存1位的二进制值。这里(M)是表示二进制的位数,位数最小值为1,最大值为64。
举个例子,创建一张表 test_bit1
,由三个字段组成,数据类型都是 BIT ,如下代码所示:
CREATE TABLE test_bit1(
f1 BIT,
f2 BIT(5),
f3 BIT(64)
);
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/8eca1c00cb3e34dffe624c0eda356987.png)
往里面添加数据:
INSERT INTO test_bit1(f1)
VALUES(0), (1);
SELECT *
FROM test_bit1;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/248548f748b11c081a7020d0a9247c8d.png)
BIT 类型在实际开发中使用不多,作了解即可。
6. 日期时间类型
类型 | 名称 | 字节 | 日期格式 | 最小值 | 最大值 |
---|---|---|---|---|---|
YEAR | 年 | 1 | YYYY或YY | 1901 | 2155 |
TIME | 时间 | 3 | HH:MM:SS | -838:59:59 | -838:59:59 |
DATE | 日期 | 3 | YYYY-MM-DD | 1000-01-01 | 9999-12-03 |
DATATIME | 日期时间 | 8 | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 日期时间 | 4 | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC | 2038-01-19 03:14:07 UTC |
6.1 YEAR
年份 YEAR 有两种表示方式,分别是 4 位格式的 YYYY 和 2 位格式的 YY 。由于 2 位格式的表示方式过于繁琐和恶心,因此从 MySQL 5.5.27 开始就不推荐使用 2 位格式了。即 YEAR 默认格式就是 4 位的 YYYY ,因此没有必要再显式地声明成 YEAR(4)
了。
举个例子:
-- 建表
CREATE TABLE test_year(
f1 YEAR,
f2 YEAR(4)
);
-- 显示表属性
DESC test_year;
输出:
可以看到,即使不显式地声明 YEAR 为 4 位格式,MySQL 5.7 还是默认设置为 4 位格式。
接下来插入年份数据,有两种方式,分别是以字符串形式 (推荐) 插入和以数值插入,MySQL 都会隐式转换成对应的日期时间类型。
-- 插入数据
INSERT INTO test_year(f1)
VALUES('2017'), (2021);
-- 查询
SELECT *
FROM test_year;
查询结果:
超过 YEAR 表示范围会报错。
-- 插入最大值,成功
INSERT INTO test_year(f1)
VALUES('2155');
-- 超过最大值,失败
INSERT INTO test_year(f1)
VALUES('2156');
输出:
错误代码: 1264
Out of range value for column 'f1' at row 1
6.2 DATE
DATE 的标准格式是 YYYY-MM-DD ,插入时也可以使用非标准格式 YYYYMMDD ,其会自动隐式转换为标准格式。
举个例子:
-- 建表
CREATE TABLE test_date(
f1 DATE
);
-- 显示表属性
DESC test_date;
输出:
![](https://i-blog.csdnimg.cn/blog_migrate/b7a2db7410ab6d8765b96b888f82149d.png)
使用 3 种方式插入 DATE 数据:
-- 插入数据
INSERT INTO test_date
VALUES('2017-09-01'), ('20210730'), (20140618);
-- 查询
SELECT *
FROM test_date;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/994f2cdd8a6ea32b18bac413ecb961ee.png)
【注意】标准格式 YYYY-MM-DD 只能以单引号形式插入,去掉单引号插入会报错。如下代码所示:
-- 错误的标准格式
INSERT INTO test_date
VALUES(2018-10-09);
输出:
错误代码: 1292
Incorrect date value: '1999' for column 'f1' at row 1
【结论】以后统一用单引号的标准格式:'YYYY-MM-DD'
。
- 可以使用前面学习到的单行函数 CURDATE() 、CURRENT_DATE 和 NOW() 插入当前的日期。注意,虽然函数 NOW() 是包含了日期和时间,但是会 MySQL 会自动只截取日期。
-- 使用单行函数插入当前日期
INSERT INTO test_date
VALUES(CURDATE()), (CURRENT_DATE()), (NOW());
--查询
SELECT *
FROM test_date;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/ac3562c4649f02edec04d758837397df.png)
6.3 TIME
时间类型 TIME 的标准格式是 ‘HH:MM:SS’ 。
TIME 的表示范围之所以会超过 24 小时,能表示 -838:59:59 ~ 838:59:59 这么宽的范围,是因为 TIME 还能表示一段时间间隔。
举个例子:
-- 建表
CREATE TABLE test_time(
f1 TIME
);
-- 显示表属性
DESC test_time;
输出:
- TIME 支持的格式
TIME 支持以下格式:
‘D HH:MM:SS’ | ‘HH:MM:SS’ | ‘HH:MM’ | ‘D HH:MM’ | ‘D HH’ | ‘SS’ |
---|---|---|---|---|---|
‘HHMMSS’ | HHMMSS | MMSS |
举个例子:
-- 插入TIME数据
INSERT INTO test_time
VALUES
('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'), ('1 05'), ('45');
INSERT INTO test_time
VALUES('123520'), (124011), (1210);
-- 查询
SELECT *
FROM test_time;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/56e1211476e34c65c9380b094212d147.png)
- 使用单行函数 CURRENT_TIME 和 NOW() 来插入 TIME 数据
注意,虽然函数 NOW() 是包含了日期和时间,但是会 MySQL 会自动只截取时间部分。
-- 三种单行函数插入TIME数据
INSERT INTO test_time
VALUES(CURTIME()), (CURRENT_TIME()), (NOW());
-- 查询
SELECT *
FROM test_time;
查询结果:
6.4 DATETIME
DATETIME 数据类型是日期时间类型中占据存储空间最大的类型,需要占据 8 个字节的空间。它能同时记录日期和时间,因此是实际开发中使用最多的日期时间类型。
其标准格式为 ‘YYYY-MM-DD HH:MM:SS’ 。
举个例子:
-- 建表
CREATE TABLE test_datetime(
f1 DATETIME
);
-- 显示表属性
DESC test_datetime;
输出:
- 插入数据
插入时最推荐使用标准格式 ‘YYYY-MM-DD HH:MM:SS’ ,可读性是最好的,别整其他花里胡哨的什么少写一点偷懒,到最后麻烦的还是自己。
-- 三种单行函数插入DATETIME数据
INSERT INTO test_datetime
VALUES
('2021-01-01 06:50:30'),
('20210101065030'),
(19990101000000);
-- 查询
SELECT *
FROM test_datetime;
查询结果:
- 使用单行函数 NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP() 来插入 DATETIME 数据
-- 五种单行函数插入DATETIME数据
INSERT INTO test_datetime
VALUES
(NOW()), (SYSDATE()), (CURRENT_TIMESTAMP()),
(LOCALTIME()), (LOCALTIMESTAMP());
-- 查询
SELECT *
FROM test_datetime;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/26f10a3e3e32c73f5cf078580db79c75.png)
6.5 TIMESTAMP
TIMESTAMP 是时间戳,底层存储的是毫秒数。其标准格式与 DATETIME 相同,都是 ‘YYYY-MM-DD HH:MM:SS’ 。区别是,TIMESTAMP 只占 4 个字节,与 8 字节的 DATETIME 相比,其表示范围要更窄,只能表示 1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC 的时间范围,插入的日期时间若超过该范围就会报错。
- 插入数据
插入时最推荐使用标准格式 ‘YYYY-MM-DD HH:MM:SS’ ,可读性是最好的。也可以使用数值 YYYYMMDDHHMMSS
插入,MySQL会自动进行隐式转换,但是可读性稍差。
-- 建表
CREATE TABLE test_timestamp(
f1 TIMESTAMP
);
-- 显示表属性
DESC test_timestamp;
输出:
-- 插入TIMESTAMP数据
INSERT INTO test_timestamp
VALUES('2018-11-23 20:30:30'),
(20210701084500);
-- 查询
SELECT *
FROM test_timestamp;
查询结果:
- 根据时区自动更改到该时区的时间
下面这个例子将对比 DATETIME 和 TIMESTAMP 在时区变化时,其是否会作出相应变化的试验。
-- 建表
CREATE TABLE temp_time(
d1 DATETIME,
d2 TIMESTAMP
);
-- 查看表属性
DESC temp_time;
输出:
-- 插入数据
INSERT INTO temp_time
VALUES
('2021-09-02 14:30:12', '2021-09-02 14:30:12'),
(NOW(), NOW());
-- 查看
SELECT *
FROM temp_time;
查询结果:
-- 修改时区
SET time_zone = '+9:00';
-- 再次查看
SELECT *
FROM temp_time;
查询结果:
结论
存储数据的时候需要对当前时间所在的时区进行转换,查询数据的时候再将时间转换回当前的时区。因此,使用TIMESTAMP存储的同一个时间值,在不同的时区查询时会显示不同的时间。
- DATETIME 和 TIMESTAMP 的区别
- TIMESTAMP 存储空间比较小,表示的日期时间范围也比较小。
- 底层存储方式不同,TIMESTAMP 底层存储的是毫秒值,距离 1970-01-01 00:00:00 UTC 毫秒的毫秒值。
- 两个日期比较大小或日期计算时,TIMESTAMP 更方便、更快。
- TIMESTAMP 和时区有关。TIMESTAMP 会根据用户的时区不同,显示不同的结果。而 DATETIME 则只能反映出插入时当地的时区,其他时区的人查看数据必然会有误差的。
6.6 开发中的经验
- 在实际开发中,DATETIME 是使用最多的日期时间类型,在开发中应该尽量使用 DATETIME 。
- 此外,注册时间、商品发布时间等,建议使用时间戳单行函数 UNIX_TIMESTAMP() 进行存储。因为 DATETIME 不方便进行计算。
7. 文本字符串类型
MySQL中字符串类型的汇总表格如下:
![](https://i-blog.csdnimg.cn/blog_migrate/92ba2c37f8b37d95996046a77bcb4eeb.png)
7.1 CHAR类型
类型 | 特点 | 长度 | 长度范围 | 所占空间 |
---|---|---|---|---|
CHAR(M) | 固定长度 | M | 0 <= M <= 255 | M Bytes |
VARCHAR(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度+1) Bytes |
- 声明字段为 CHAR 类型时,若不声明长度,则默认为 1 ,即 CHAR(1) 。
- CHAR 类型是固定长度的。例如你声明一个字段为 CHAR(5) ,然而你插入长度不足 5 的数据时,如 ‘ab’ ,你只占用了 2 个长度,由于 CHAR 固定长度的特性,MySQL会自动在 ‘ab’ 后面填充 3 个空格至长度为 5 。但是在查询时MySQL会自动不显示这些填充的空格。
7.2 VARCHAR类型
- VARCHAR 必须声明长度,否则会报错。
- 在 MySQL 4.0 之前长度指的是字节长度,而 MySQL 5.0 之后长度指的是字符 (根据设置的字符集) 的长度。举个例子,现在我MySQL设置的默认的字符集是 UTF8 ,用 3 个字节表示一个汉字字符。那么 VARCHAR 能存储的汉字的长度范围是 0 <= M <= 65535 3 = 21845 \cfrac{65535}{3}=21845 365535=21845 个汉字长度的字符串。
- 与 CHAR 的固定长度不同,VARCHAT 是可变长度的。例如你声明一个字段为 VARCHAR(10) ,然而你插入长度为 5 的字符串,那么该数据的长度就会自动变为 5 。这也是为什么 VARCHAR 占的空间为 M + 1 个字节的原因,这个
+1
的字节就是用来记录当前一共占用了多少个长度。
7.3 CHAR与VARCHAR对比
类型 | 特点 | 空间上 | 时间上 | 适用场景 |
---|---|---|---|---|
CHAR(M) | 固定长度 | 浪费存储空间 | 效率高 | 存储不大,速度要求高 |
VARCHAR(M) | 可变长度 | 节省存储空间 | 效率低 | 非CHAR的情况 |
情况1:存储很短的信息。比如门牌号码101,201……这样很短的信息应该用char,因为varchar还要占1个 byte 用于存储信息长度,本来打算节约存储的,结果得不偿失。
情况2:固定长度的。比如使用 UUID 作为主键,那用 CHAR 更合适。因为它固定长度,VARCHATR 动态根据长度的特性就消失了,而且还要占1个长度信息。
情况3:十分频繁改变的column。因为varchar每次存储都要有额外的计算,得到长度等工作,如果一个非常频繁改变的,那就要有很多的精力用于计算,而这些对于char来说是不需要的。
情况4:具体存储引擎中的情况:
- MyISAM 数据存储引擎和数据列:MyISAM数据表,最好使用固定长度(CHAR)的数据列代替可变长
度(VARCHAR)的数据列。这样使得整个表静态化,从而使数据检索更快,用空间换时间。 - MEMORY 存储引擎和数据列:MEMORY数据表目前都使用固定长度的数据行存储,因此无论使用
CHAR或VARCHAR列都没有关系,两者都是作为CHAR类型处理的。 - InnoDB 存储引擎,建议使用VARCHAR类型。因为对于InnoDB数据表,内部的行存储格式并没有区
分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针),而且主要影响性能的因素
是数据行使用的存储总量,由于char平均占用的空间多于varchar,所以除了简短并且固定长度的,
其他考虑varchar。这样节省空间,对磁盘I/O和数据存储总量比较好。
7.4 TEXT类型
在 MySQL 中,TEXT 是用来存储文本数据的类型。例如帖子,新闻文字,时事评论、文章。
类型 | 特点 | 长度 | 长度范围 | 所占空间 |
---|---|---|---|---|
TINYTEXT | 小文本、可变长度 | L | 0 <= L <= 255 | L + 2 字节 |
TEXT | 文本、可变长度 | L | 0 <= L <= 65535 | L + 2 字节 |
MEDIUMTEXT | 中等文本、可变长度 | L | 0 <= L <= 16777215 | L + 3 字节 |
LONGTEXT | 大文本、可变长度 | L | 0 <= L <= 4294967295 (4GB) | L + 4 字节 |
**由于实际存储的长度不确定,MySQL 不允许 TEXT 类型的字段作主键。**遇到这种情况,只能采用 CHAR(M) 或 VARCHAR(M) 。
举个例子:
-- 建表
CREATE TABLE test_text1(
tx TEXT
);
-- 查看表属性
DESC test_text1;
输出:
![](https://i-blog.csdnimg.cn/blog_migrate/a4f476ee2386a03b418d496c2344661f.png)
-- 插入数据
INSERT INTO test_text1
VALUES('Ocean University of China'); -- 有3个空格
-- 查询
SELECT *
FROM test_text1;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/1c2bb907e1eec7acf1a3c8071eb713fc.png)
-- 查询文本长度
SELECT CHAR_LENGTH(tx)
FROM test_text1;
查询结果:
可见,TEXT 统计长度时,是把空格也计算在内的。这也符合文本数据的需求。
【开发中的经验】
由于 VARCHAR 最多存储 21845 个汉字,因此当汉字数超过 21845 时,就可以考虑使用 TEXT 来存储了。
TEXT 文本类型,可以存比较大的文本段,搜索速度稍慢,因此如果不是特别大的内容,建议使用CHAR,VARCHAR来代替。
还有TEXT类型不用加默认值,加了也没用。而且text和blob类型的数据删除后容易导致 “空洞”,使得文件碎片比较多,所以频繁使用的表不建议包含TEXT类型字段,建议单独分出去,单独用一个表。
8. ENUM类型
ENUM 也称枚举类型。当声明字段为 ENUM 类型时,只允许从成员中选取单个值,不能一次选取多个值。其所需要的存储空间由定义 ENUM 类型时指定的成员个数决定。
类型 | 长度 | 长度范围 | 所占空间 |
---|---|---|---|
ENUM | L | 1 <= M <= 65535 | 1或2 Bytes |
举个例子:
-- 建表
CREATE TABLE test_enum(
season ENUM('春', '夏', '秋', '冬', 'unknow')
);
-- 查看表属性
DESC test_enum;
输出:
- 插入数据
-- 只能插入声明的成员,且忽略大小写
INSERT INTO test_enum
VALUES('冬'), ('夏');
INSERT INTO test_enum
VALUES('UNKNOW');
-- 查询
SELECT *
FROM test_enum;
查询结果:
也可以插入对应的索引值,从 1 开始:
INSERT INTO test_enum
VALUES('1'), (4);
SELECT *
FROM test_enum;
查询结果:
没有限制非空的约束下,可以添加 NULL 值。
插入非枚举成员的值会报错:
-- 插入非枚举成员
INSERT INTO test_enum
VALUES('人');
输出:
错误代码: 1265
Data truncated for column 'season' at row 1
一次选取多个成员值也会报错:
INSERT INTO test_enum
VALUES('夏', '秋');
输出:
错误代码: 1136
Column count doesn't match value count at row 1
9. SET类型
上面提到,ENUM 插入数据时只能选取一个成员值。而 SET 则可以一次选取多个成员值。但 SET 的成员个数的上限为 64 。
成员个数范围(L表示实际成员个数) | 所占空间 |
---|---|
1 <= L <= 8 | 1字节 |
9 <= L <= 16 | 2字节 |
17 <= L <= 24 | 3字节 |
25 <= L <= 32 | 4字节 |
33 <= L <= 64 | 8字节 |
从上表可以看出,SET 类型成员个数越多,其占用的存储空间越大。
举个例子:
-- 建表
CREATE TABLE test_set(
s SET('A', 'B', 'C')
);
-- 查看表属性
DESC test_set;
输出:
![](https://i-blog.csdnimg.cn/blog_migrate/f139e13c4c932c3bfd4d82fd1e67a6b8.png)
- 插入数据
-- 插入数据
INSERT INTO test_set(s)
VALUES('A'), ('A,B'); -- 不能有空格
-- 查询
SELECT *
FROM test_set;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/1251bed8fdc40917e4ef8a21b84e2960.png)
- 插入数据时,会自动去重:
INSERT INTO test_set(s)
VALUES('A,B,C,A');
-- 查询
SELECT *
FROM test_set;
查询结果:
- 插入不存在的成员变量会报错:
INSERT INTO test_set(s)
VALUES('A,B,C,D');
-- 查询
SELECT *
FROM test_set;
查询结果:
错误代码: 1265
Data truncated for column 's' at row 1
10. 二进制字符串类型
MySQL 除了存储可读性的文本、数值等数据,还可以存储如图片、音频、视频等二进制数据。
10.1 BINARY与VARBINARY类型
BINARY(M) 与 VARBINARY(M) 之间的关系与前面的 CHAR 与 VARCHAR 是类似的,其特性表格如下所示:
类型 | 特点 | 长度 | 长度范围 | 所占空间 |
---|---|---|---|---|
BINARY(M) | 固定长度 | M | 0 <= M <= 255 | M Bytes |
VARBINARY(M) | 可变长度 | M | 0 <= M <= 65535 | (实际长度+1) Bytes |
- BINARY 可以不指明长度 M ,默认只能存储 1 个字节长度的二进制数据。其与 CHAR 一样是固定长度,当实际长度小于 M 时,自动填充
0
。 - VARBINARY(M) 必须指明长度 M ,否则报错。VARBINARY 还需要 1~2 个字节来存储实际使用的长度。
举个例子:
-- 建表
CREATE TABLE test_binary(
f1 BINARY,
f2 BINARY(3),
f3 VARBINARY(10)
);
-- 查看表属性
DESC test_binary;
输出:
- 插入数据
由于一个英文字符占 1 字节大小,因此我们插入英文字符来举例。
-- 插入数据
INSERT INTO test_binary(f1, f2)
VALUES('a', 'abc');
-- 查询
SELECT *
FROM test_binary;
查询结果:
- 体会 BINARY 的固定长度
-- 插入数据
INSERT INTO test_binary(f2, f3)
VALUES('ab', 'ab');
-- 查询
SELECT *
FROM test_binary;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/7427e8cd4481d7e26390d8cc6f374fa6.png)
查询字段 f2 和 f3 的长度:
SELECT LENGTH(f2), LENGTH(f3)
FROM test_binary;
查询结果:
可以看到,同样是插入 2 字节大小的英文字符串 ‘ab’ ,VARBINARY 的长度正常显示为 2 ,BINARY 长度为 3 ,这进一步验证了 BINARY 数据长度小于声明长度是会自动填充 0
直至满足声明长度。
10.2 BLOB类型
BLOB 类型是用来存储大二进制对象的,如图片、音频、视频,其也是可变长度的。
【注意】
虽然 MySQL 具备存储图片、音频、视频二进制文件的能力,但在实际开发中并不会使用 MySQL 来存储图片、音频、视频。而是直接把图片、音频、视频直接存储在服务器的硬盘上,在把这些资源对应的存储路径 (或URL地址) 存储在 MySQL 中。
类型 | 特点 | 长度 | 长度范围 | 所占空间 |
---|---|---|---|---|
TINYBLOB | 小二进制对象、可变长度 | L | 0 <= L <= 255 | L + 2 字节 |
BLOB | 二进制对象、可变长度 | L | 0 <= L <= 65535 | L + 2 字节 |
MEDIUMBLOB | 中等二进制对象、可变长度 | L | 0 <= L <= 16777215 | L + 3 字节 |
LONGBLOB | 大二进制对象、可变长度 | L | 0 <= L <= 4294967295 (4GB) | L + 4 字节 |
举个例子:
-- 建表
CREATE TABLE test_blob(
id INT,
img MEDIUMBLOB
);
-- 查看表属性
DESC test_blob;
输出:
想要通过浏览器端/客户端上传图片,经过后端服务器存储到 MySQL 数据库中,需要应用到 IO 流和网络传输的知识。这里就不写 Java 代码演示了,仅用 SQLyog 中图形化方式插入图片:
-- 插入ID数据
INSERT INTO test_blob(id)
VALUES(1001);
-- 查询
SELECT *
FROM test_blob;
查询结果:
![](https://i-blog.csdnimg.cn/blog_migrate/02d4d196cff0a91cd48aea386ee04376.png)
然后插入图片,先在 SQLyog 侧边栏选中表 test_blob
,再点击 4 表数据
:
然后点击字段 img
下想要插入图片的表格,SQLyog 就会弹出一个图形化窗口,通过此窗口就可以图形化地插入图片:
最后点击保存,提交到数据库:
提交以后再次查询数据库,是看不了图片这种非可读性的二进制文件的,可以通过 Java 中的输出流来读取。
10.3 TEXT和BLOB的使用注意事项
在使用 TEXT 和 BLOB 字段类型时要注意以下几点,以便更好的发挥数据库的性能。
① BLOB 和 TEXT 值也会引起自己的一些问题,特别是执行了大量的删除或更新操作的时候。删除这种值
会在数据表中留下很大的 “空洞” ,以后填入这些 “空洞” 的记录可能长度不同。为了提高性能,建议定期
使用 OPTIMIZE TABLE 功能对这类表进行碎片整理。
② 如果需要对大文本字段进行模糊查询,MySQL 提供了前缀索引 (下篇讲解) 。但是仍然要在不必要的时候避免检索大型的 BLOB 或 TEXT 值。例如,SELECT * 查询就不是很好的想法,除非你能够确定作为约束条件的 WHERE 子句只会找到所需要的数据行。否则,你可能毫无目的地在网络上传输大量的值。
③ 把 BLOB 或 TEXT 列分离到单独的表中。在某些环境中,如果把这些数据列移动到第二张数据表中,可
以让你把原数据表中的数据列转换为固定长度的数据行格式,那么它就是有意义的。这会减少主表中的
碎片,使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过
网络传输大量的 BLOB 或 TEXT 值。
11. JSON类型
11.1 什么是JSON
JSON (JavaScript Object Notation) ,是一种轻量级的数据交换格式。用于在浏览器端/客户端与服务器端之间的数据交换。由于 JSON 具有更加简洁和清晰的层次结构,现已逐渐代替 XML 。
JSON 的本质就是带有固定格式的字符串。
11.11.2 存储JSON类型
举个例子:
-- 建表
CREATE TABLE test_json(
js json
);
-- 查看表属性
DESC test_json;
输出:
![](https://i-blog.csdnimg.cn/blog_migrate/a0d72827a78e5df8b24c00a8d1bc22f9.png)
- 插入JSON数据
-- 插入JSON数据
INSERT INTO test_json(js)
VALUES('{"name":"XieSihang", "age":23, "address":{"province":"guangdong", "city":"zhaoqing"}}');
-- 查询
SELECT *
FROM test_json;
查询结果:
- 提取解析JSON中的信息
MySQL 还支持提取和解析 JSON 中的信息。
SELECT js -> '$.name' AS `name`,
js -> '$.age' AS age,
js -> '$.address.province' AS province,
js -> '$.address.city' AS city
FROM test_json;
查询结果:
12. 空间类型
空间类型在后端开发中使用非常少,只在地图开发等场景中才会用到。因此仅作了解即可。
13. 小结及选择建议
在定义数据类型时,整数用 INT,小数用定点数类型 DECIMAL(M, D) ,日期时间用 DATETIME 。
这样做的好处是,首先确保你的系统不会因为数据类型定义出错。不过,凡事都是有两面的,可靠性
好,并不意味着高效。比如,TEXT 虽然使用方便,但是效率不如 CHAR(M) 和 VARCHAR(M)。
关于字符串的选择,建议参考如下阿里巴巴的《Java开发手册》规范:
阿里巴巴《Java开发手册》之MySQL数据库:
- 任何字段如果为非负数,必须是 UNSIGNED ;
- 【强制】小数类型为 DECIMAL,禁止使用 FLOAT 和 DOUBLE。
- 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候,得
到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围,建议将数据拆成整数和小数并
分开存储。
- 说明:在存储的时候,FLOAT 和 DOUBLE 都存在精度损失的问题,很可能在比较值的时候,得
- 【强制】如果存储的字符串长度几乎相等,使用 CHAR 定长字符串类型。
- 【强制】VARCHAR 是可变长字符串,不预先分配存储空间,长度不要超过 5000。如果存储长度大于此值,定义字段类型为 TEXT,独立出来一张表,用主键来对应,避免影响其它字段索引效率。