和众多编程语言一样,SQL(结构化查询语言)也有自己的数据类型,在这片文章中,我将简单介绍MySQL中的数据类型,及其如何应用和一些注意点。
MySQL数据类型一览:
- 整数类型 (如int)
- 浮点数类型 (如double)
- 定点数类型 (如decimal)
- 日期和时间类型 (如date)
- 字符串类型 (如char、varchar)
- 二进制类型 (如binary、varbinary)
- 其他类型 (如enum、json、bit)
Table of Contents
数字型 - 整型
数字型 - 浮点型
时间型
字符串 - 小串
字符串 - 大文本
二进制
超大二进制
JSON
枚举类型(enum)
bit
数字型 - 整型
tinyInt | 1字节 | 表示范围[-127, +127],当无符号时为[0, 255] |
smallint | 2字节 | 范围[-2^15, 2^15-1](-32768, 32767) |
mediumint | 3字节 | 范围[-8388608, 8388607] |
int(integer) | 4字节 | 范围[-2^31 (-2,147,483,648), 2^31 - 1 (2,147,483,647)] |
bigint | 8字节 | 范围[-2^63, 2^63-1] (-9223372036854775808, 9223372036854775807) |
注意:如果在插入时数据大得超过的存储范围,则会报错
CREATE TABLE test_int ( -- 创建一个测试表
id INT AUTO_INCREMENT PRIMARY KEY,
ti tinyint,
si SMALLINT,
mi MEDIUMINT,
ii INT,
bi bigint
);
数字型 - 浮点型
float | 4字节 | 单精度浮点数(参照IEEE754标准) |
double | 8字节 | 双精度浮点数 |
decimal(m,d) | M+2个字节 | 其实decimal并非浮点型,顶点型。小数点前后数据不损失。 M代表总共能存储M位,N代表小数点后保留位数,1 < M < 254, 0 < N < 60; |
使用选择:
- float:仅能精确保存长度为6位数字的浮点型数字,超出的将会被截断(四舍五入)。
- double:仅能精确保存长度为16位数字的浮点型数字,超出的将会被截断(四舍五入)。其中,小数点后能精度为9位。此外,关于float和double的存储小数的原理,是IEEE754标准规定的。
- decimal:能精确保存长度为m位数字,且小数点后的d位数字也能精确地保存,不会发生截断。
深度理解float
DROP TABLE IF EXISTS test_floating;
CREATE TABLE test_floating
(
id int AUTO_INCREMENT PRIMARY KEY, -- 表主键
my_float float -- 仅能保存长度为6位数字的浮点数
);
-- 共10位,float只能保证前6位数字精确,即3.14159; 其后的数字将不会精确
INSERT INTO test_floating (my_float) VALUES (3.141592653);
-- 共12位,float只能保证前6位数字精确,即333.141; 其后的数字将不会精确
INSERT INTO test_floating (my_float) VALUES (333.141592653);
SELECT * FROM test_floating ;
深度理解double
DROP TABLE IF EXISTS test_double;
CREATE TABLE test_double
(
id int AUTO_INCREMENT PRIMARY KEY,
my_double double -- 仅能保存长度为13位数字的浮点数
);
-- 共14位,double能保证16位的精度,全部会被存储
INSERT INTO test_double (my_double) VALUES (3.1415926535898);
-- 共21位,但double只能保证前16的精度, 即3.141592653589895
INSERT INTO test_double (my_double) VALUES (3.14159265358989546545);
-- 共24位,但double只能保证前16位的精度, 即3333333333.1415925
INSERT INTO test_double (my_double) VALUES (3333333333.1415926535898);
SELECT * FROM test_double ;
深度理解decimal
DROP TABLE IF EXISTS test_decimal;
CREATE TABLE test_decimal
(
id int AUTO_INCREMENT PRIMARY KEY,
my_decimal decimal(10,6) -- 能保存总长为10个,小数点后为6个
);
-- 共14位,但decimal被要求精度为小数点后6个,即3.141593
INSERT INTO test_decimal (my_decimal) VALUES (3.1415926535898);
-- 共22位
INSERT INTO test_decimal (my_decimal) VALUES (33333333.1415926535898);
-- 执行时报错:Out of range value for column 'my_decimal' at row 1
-- 原因:总长不能超过decimal中定义的长度
时间型
Year | 1字节 | 范围:1901~2155 支持的类型为:YYYY YY(即2030年,可以写成30) YYYY YY |
Time | 3字节 | 范围:-838:59:59~838:59:59 hh:mm:ss hhmmss hhmmss |
Date | 4字节 | 范围:1000-01-01~9999-12-31 YYYY-MM-DD YY-MM-DD YYYYMMDD YYMMDD YYYYMMDD YYMMDD |
Timestamp 时间戳 | 4字节 | 范围:19700101000000 到 2038-01-19 03:14:07 YY-MM-DD hh:mm:ss YYYYMMDDhhmmss YYMMDDhhmmss YYYYMMDDhhmmss YYMMDDhhmmss |
Datetime | 8字节 | 范围:1000-01-01 00:00:00~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS |
如何插入日期、时间、时间戳
CREATE TABLE test_date
(
id INT AUTO_INCREMENT PRIMARY KEY,
mydate DATE, -- 日期
mytime TIME, -- 时间
myts TIMESTAMP -- 时间戳
)
INSERT INTO test_date(mydate,mytime,myts) VALUES
('1997-01-06','20:30:00','19970106203000'),
('2020-06-01','19:16:00','20200601191600')
SELECT * FROM test_date;
字符串 - 小串
char(M) | 1字节 | 固定长度的字符串,M为0~255之间的整数 指定了M,就会开辟存储M个字符的存储空间 效率较高 |
VARCHAR(M) | 4字节 | 可变长度的字符串,M为0~65535之间的整数 指定了M,还是会根据实际的字符数,开辟存储空间 效率较低 |
特性:
- 这里的char(M)、varchar(M)中的M,指的是字符的个数,一个中文汉字、一个字母、一个数字、一个标点符号都算一个字符
- 为什么char占空间1字节,M却能取到255?—— 这是因为1字节的有符号取值范围为[-127, +127],而无符号的取值范围为[0, 255]。
超过了范围就会报错
超过了数据类型的容量
varchar(数字):数字就是表示可存储字符的个数
字符串 - 大文本
大文本数据(存储如文章内容、评论)
- 超长字符串
- 超越varchar(65535)的字符串
TINYTEXT | 可存0~255字符(约1字节) | 常应用于博文摘录,文章摘要等。 效率低于char。所以一般情况下比较少使用该数据类型。 |
TEXT | 可存0~65535字符(4字节) | 可存储文章的正文。 注意到varchar也能存储65535个字符,且text较慢,所以尽量选择varchar |
MEDIUMTEXT | 可存0~16777215字符(约16MB) | 用于超出了text、varchar的存储范围时使用 |
LONGTEXT | 可存0~4294967295字符(约4GB) |
|
特性:
- text 在定义时,不需要定义长度,也不会计算总长度。
- text 类型在定义时,不可给default值
- TEXT数据不存储在数据库服务器的内存中,因此,每当查询TEXT数据时,MySQL都必须从磁盘读取它,这与CHAR和VARCHAR相比要慢得多。
二进制
binary(M) | 1字节,M的范围[0, 255] | 固定长度的二进制数据(基于字节,而char、varchar是基于字符) |
varbinary(M) | 4字节,M的范围[0,65535] | 可变长度的二进制数据 |
超过了数据类型的范围:
超大二进制
大二进制数据(存储如图片、PDF)
- 关键字BLOB(binary large object)
- 存储超大二进制数据
TINYBLOB | 允许长度0~255(1字节) |
|
BLOB | 允许长度0~65535(4字节) |
|
MEDIUMBLOB | 允许长度0~16777215字节(约16MB) |
|
LONGBLOB | 允许长度0~4294967295字节(4GB) |
|
JSON
5.7版本提供了对json格式的支持, type类型就是json.
创建表
create table json_test(
id int unsigned auto_increment primary key,
info json not null
);
插入数据
insert into json_test VALUES (null,'{"name":"jack","age":20,"gender":"male","height":180}');
枚举类型(enum)
理解:
- 和C、Java语言中的枚举类型一样
- 如果确定某个字段的数据只有那么几个值:如性别字段的可选值只会为:男,女,保密。系统就可以在设定字段的时候规定当前字段只能存在固定的几个值
系统提供了1到2个字节来存储枚举数据:
- 通过计算enum列举的具体值来选择实际的存储空间, 如果数据值列表在255以内, 1个字节就够了
- 如果超过255但是小于65535, 那么系统采用两个字节来保存。
create table test_enum(
gender enum('男','女','保密')
);
特性:
- 只有插入enum设定的这几个值才能插入成功, 否则的话系统会报错.
- 枚举enum的存储原理:实际上字段上所存储的值并不是真正的字符串,而是字符串对应的下标;
- 当系统设定枚举类型的时候,会给枚举中每个元素定义一个下标,这个下标规则从1开始Enum(1=>‘男’,2=>’女’,3=>’保密’).
- 在MySQL中系统是自动进行类型转换的:如果数据碰到“+、-、*、/”系统就会自动将数据转换成数值:而普通字符串转换成数值为0。例如:Select 字段名 + 0 from 表名.
- 既然实际enum字段存储的结果是数值:那么在进行数据插入的时候,就可以使用对应的数值来进行.
bit
用于存储bit值,能够存储比特长度范围为1~64,默认为1
快速理解bit的用法
CREATE TABLE test_bit
(
id int PRIMARY KEY AUTO_INCREMENT,
my_bit bit(5)
);
-- MySQL会自动将十进制的4,转为二进制存入。
INSERT INTO test_bit (my_bit) VALUES (4);
-- 如果十进制的数字转换为二进制,所得二进制长度超过了规定的长度,报错
INSERT INTO test_bit (my_bit) VALUES (44); -- 报错: Data truncation: Data too long for column 'my_bit'
-- 直接插入二进制
INSERT INTO test_bit (my_bit) VALUES (b'01011')
SELECT * FROM test_bit;
定义bit时的长度不能超过64: