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

和众多编程语言一样,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;

使用选择:

  1. float:仅能精确保存长度为6位数字的浮点型数字,超出的将会被截断(四舍五入)。
  2. double:仅能精确保存长度为16位数字的浮点型数字,超出的将会被截断(四舍五入)。其中,小数点后能精度为9位。此外,关于float和double的存储小数的原理,是IEEE754标准规定的。
  3. 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,还是会根据实际的字符数,开辟存储空间

效率较低

特性:

  1. 这里的char(M)、varchar(M)中的M,指的是字符的个数,一个中文汉字、一个字母、一个数字、一个标点符号都算一个字符
  2. 为什么char占空间1字节,M却能取到255?—— 这是因为1字节的有符号取值范围为[-127, +127],而无符号的取值范围为[0, 255]。

超过了范围就会报错

超过了数据类型的容量

varchar(数字):数字就是表示可存储字符的个数

字符串 - 大文本

大文本数据(存储如文章内容、评论)

  1. 超长字符串
  2. 超越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)

 

特性:

  1. text 在定义时,不需要定义长度,也不会计算总长度。
  2. text 类型在定义时,不可给default值
  3. TEXT数据不存储在数据库服务器的内存中,因此,每当查询TEXT数据时,MySQL都必须从磁盘读取它,这与CHAR和VARCHAR相比要得多。

二进制

binary(M)

1字节,M的范围[0, 255]

固定长度的二进制数据(基于字节,而char、varchar是基于字符)

varbinary(M)

4字节,M的范围[0,65535]

可变长度的二进制数据

超过了数据类型的范围: 

超大二进制

大二进制数据(存储如图片、PDF)

  1. 关键字BLOB(binary large object)
  2. 存储超大二进制数据

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)

理解:

  1. 和C、Java语言中的枚举类型一样
  2. 如果确定某个字段的数据只有那么几个值:如性别字段的可选值只会为:男,女,保密。系统就可以在设定字段的时候规定当前字段只能存在固定的几个值

系统提供了1到2个字节来存储枚举数据:

  1. 通过计算enum列举的具体值来选择实际的存储空间, 如果数据值列表在255以内, 1个字节就够了
  2. 如果超过255但是小于65535, 那么系统采用两个字节来保存。
create table test_enum(
    gender enum('男','女','保密')
);

特性:

  1. 只有插入enum设定的这几个值才能插入成功, 否则的话系统会报错.
  2. 枚举enum的存储原理:实际上字段上所存储的值并不是真正的字符串,而是字符串对应的下标;
  3. 当系统设定枚举类型的时候,会给枚举中每个元素定义一个下标,这个下标规则从1开始Enum(1=>‘男’,2=>’女’,3=>’保密’).
  4. 在MySQL中系统是自动进行类型转换的:如果数据碰到“+、-、*、/”系统就会自动将数据转换成数值:而普通字符串转换成数值为0。例如:Select 字段名 + 0 from 表名.
  5. 既然实际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:


 

©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页