【MySQL】数据类型的基本用法

最新推荐文章于 2022-10-01 11:58:46 发布

Hackyle

最新推荐文章于 2022-10-01 11:58:46 发布

阅读量1k

点赞数 1

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

本文链接：https://blog.csdn.net/ComputerGeekHack/article/details/108809109

版权

Database 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

和众多编程语言一样，SQL（结构化查询语言）也有自己的数据类型，在这片文章中，我将简单介绍MySQL中的数据类型，及其如何应用和一些注意点。

MySQL数据类型一览：

整数类型 （如int）
浮点数类型 （如double）
定点数类型 （如decimal）
日期和时间类型 （如date）
字符串类型 （如char、varchar）
二进制类型 （如binary、varbinary）
其他类型 （如enum、json、bit）

Table of Contents

数字型 - 整型

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;