mysql常用数据类型讲解

1 数值类型

取值范围

类型	字节	取值范围(有符号)	取值范围(无符号)
Tinyint	1	-128~127	0~255
Smallint	2	-32768~32767	0~65535
Mediumint	3	-8388608~~8388607	0~16777215
Int	4	-2147483648~2147483647	0~4294967295
bigint	8	-20位~20位	0~20位
小数类型
Float	4	-3.4028234E+38	3.40283466E+38
Double	8	1.7976931348623157E+308	1.7976931348623157E+308
Decimal	变长	1.7976931348623157E+308	1.7976931348623157E+308

语法和可选参数

整数型语法和可选参数

语法：数值类型[(M)] [unsigend] [zerofill]
示例1：tinyint(2) unsigned zerofill
示例2：int(3) unsigned zerofill
数值类型可选参数：
(M)：这里M表示数字，对于插入的数字长度没有任何影响，当作默认int(11)对待。不管有没有跟zerofill参数，int(5)是能插入小于、等于、大于5的任何数的（前提满足默认长度）。
unsigned：用来控制是否有正负数。加了该参数，就不能插入负数了，只能插入正数。
zerofill：用来进行前导0填充。如对于int取值范围是11位，插入数字9，则会在前面补充10个0。
注意：对于zerofill参数，加上之后就是无符号，不能插入负数，虽然没写unsigned参数。

小数类型语法和可选参数

语法：小数类型[(M,D)] [unsigned] [zerofill]
示例1：float
示例2：float(5,3) unsigned zerofill
小数类型可选参数：
(M,D)：M代表该值的总共长度，D代表小数点后面的长度，M和D又称为精度和标度。mysql保存值时会四舍五入，当然这个四舍五入是根据标度决定的，如D=3，而插入的小数位数大于3个，最后一位会四舍五入。如：123.4567–>123.457
unsigned：用来控制是否有正负数。加了该参数，就不能插入负数了，只能插入正数。
zerofill：用来进行后导0填充，举例（float(5,3) zerofill，插入小数3.4，则会变为03.004），也就是说，如果小数点左边的数<10，则会给前面补0，效果00~~09，小数点左边的数>=10则不会做任何填充；其次，小数点后面长度不满足情况下就给后面补0，3.4—>03.004
注意：针对小数类型，使用zerofill。通过下面的示例4，得出结论和上面的说辞有一定出入。通过实验得出，小数点后面不会补0，这也许和mysql版本有一定关系。

用法示例

-- tinyint， 有符号（-128~~127），无符号（0-255）
-- 默认有符号，1字节，最长取值3位

-- smallint，有符号（-32768~~32767），无符号（0~~65535）
-- 默认有符号，2字节，最长取值3位

-- mediumint，有符号（-8388608~~8388607），无符号（0~~16777215）
-- 默认有符号，3字节，最长取值8位

-- int，有符号（-2147483648~~2147483647），无符号（0~~4294967295）
-- 默认有符号，4字节，最长取值11位

--bigint，有符号（-20位~~20位），无符号（0~~20位数）
-- 默认有符号，8字节，最长取值20位

-- 示例1，创建表，不加选项默认有符号，也就是能插入负数
create table sxl1(id01 tinyint);
insert into sxl1 values (-200);    -- 失败，最小值是-128
insert into sxl1 values (-128);    -- 成功
insert into sxl1 values ('-125');    -- 成功
insert into sxl1 values (200);    -- 失败，最大值是127
insert into sxl1 values (127);    -- 成功
insert into sxl1 values (128);    -- 失败，最大值是127

-- 示例2，创建无符号的表，也就是只能插入正数的表
create table sxl2(
id01 tinyint(3) unsigned zerofill,
id02 int(3) zerofill,
id03 int(5)
);
insert into sxl2 values(0,1,1); -- 成功,中间的1会给前面补2个0，补0个数根据int(3)决定
insert into sxl2 values(100,233,1); -- 成功
insert into sxl2 values(-1,1,1); -- 失败，不能插入负数
insert into sxl2 values(11,2000,300); -- 成功，int(5)和int3不影响插入数字的宽度范围。
insert into sxl2 values(300,2000,300); -- 失败，tinyint正数的范围是0~~255，300大于该范围。


-- 示例3，创建没有格式的表。小数类型。
-- 实验证明：可以插入正小数、负小数、正数
create table sxl3(f1 float, f2 double);    -- 成功
insert into sxl3 values (-1.1,234);    -- 成功
insert into sxl3 values (-1.01,234.23);    -- 成功
insert into sxl3 values (10,0.001);    -- 成功
insert into sxl3 values (10.099,99.999);    -- 成功

-- 示例4，创建有格式控制的表。小数类型
-- 实验证明：只能插入正小数
create table sxl4(f1 float(5,2) zerofill, f2 double(6,3) zerofill);
desc sxl4;
insert into sxl4 values(12.34,12.34); -- 成功 12.34 12.34
insert into sxl4 values(1.1,1.2);    -- 成功，01.1 02.2
insert into sxl4 values(123.45,123.456);    -- 成功 123.45 123.456
insert into sxl4 values(123,456);    -- 成功 123 456
insert into sxl4 values(123.599,456.9999); -- 成功 123.6 457
insert into sxl4 values(-123.45,-123.456);    -- 失败，不能插入负小数
select * from sxl4;

2 日期类型

取值范围

类型	存储空间	显示格式	取值
Datetime	8	YYYY-MM-DD HH:MM:SS	‘10000-01-01 00:00:00’到’9999-12-31 23:59:59’
TimeStamp	4	YYYY-MM-DD HH:MM:SS	1970-01-01到’2038-01-19 03:14:07’
Date	3	YYYY-MM-DD	‘1000-01-01’到’9999-12-31’
Time	3	HH:MM:SS	-838:59:59到838:59:59
Year	1	YYYY	1901到2155

示例

-- 示例1，创建datetime类型的表
-- datetime，默认格式'YYYY-MM-DD HH:MM:SS'
create table sxld1(mydate datetime);
desc sxld1;
insert into sxld1 values ('20221009');    -- ok，时分秒会自动变为00:00:00
insert into sxld1 values ('2022-10-09 11:10:09');    -- ok,推荐写法
insert into sxld1 values (20221008122345);    -- ok
select * from sxld1;

-- 示例2，创建timestamp类型的表
-- datetime，默认格式'YYYY-MM-DD HH:MM:SS'
create table sxld2(mydate timestamp);
desc sxld2;
insert into sxld2 values ('20221009');    -- ok
insert into sxld2 values ('2022-10-09 11:10:09');    -- ok
insert into sxld2 values (20221008122345);    -- ok
select mydate+0 from sxld2;    -- 通过+0，查出来的结果就是整数，没有格式。

-- 示例3，创建date类型的表
-- date，默认格式YYYY-MM-DD
create table sxld3(mydate date);
desc sxld3;
insert into sxld3 values ('20221009');    -- ok
insert into sxld3 values ('2022-10-09 11:10:09');    -- ok，只显示日期
insert into sxld3 values (20221008122345); -- ok，只显示日期
insert into sxld3 values ('2022-10-10');    -- ok 推荐写法
select * from sxld3;

-- 示例4，创建time类型的表
-- time，默认格式HH:MM:SS
create table sxld4(mydate time);
desc sxld4;
insert into sxld4 values ('10:20:20');    -- ok
insert into sxld4 values ('5 10:20:20');   -- ok，前面的5表示5天。5*24+10
select * from sxld4;

-- 示例5，创建year类型的表
-- year，默认格式YYYY
create table sxld5(mydate year);
desc sxld5;
insert into sxld5 values ('2020');    -- ok
insert into sxld5 values (2021);    -- ok
select * from sxld5;

3 字符串类型

取值范围

类型	最大长度	备注
char	255	char(M)，M:字符数
varchar	65535,但需要1-2个保存信息，同时由于记录的限制，因此最大长度为65532	编码不同字符数不同 GBK<=32767,UTf8<=21845-(1~2个占位符)
tinyText	L+n。L为最大长度 2^8+1	定义时，通常不用指定长度，可以自己计算
Text	2^16+2	同上
mediumtext	2^24+3	同上
longtext	2^32+4	同上
enum	1、2.权举选项量	–
set	1、2、3、4。元素数量：64	–
Binary	–	二进制数据（字节而非字符）
Varbinary	–	二进制数据（字节而非字符）
blob	–	二进制数据（字节而非字符）

示例

-- 示例1，char类型（定长），不指定长度
-- 默认不指定字符长度情况下，char的长度为1。
create table sxlc1(idx char)
desc sxlc1
insert into sxlc1 values ('sxl');    -- 失败，不指定长度，char默认长度是1。
insert into sxlc1 values ('s');    -- ok
select * from sxlc1

-- 示例2，char类型（定长），指定长度
create table sxlc2(idx char(10))
desc sxlc2
insert into sxlc2 values ('sxl');    -- ok
insert into sxlc2 values ('s');    -- ok
insert into sxlc2 values ('12345678901');    -- 失败，11位超出长度限制。
select * from sxlc2

-- 示例3，varchar类型，变长，不指定长度   --> 会创建失败

-- 示例4，varchar类型。变长，指定长度
create table sxlc3(idx varchar(10))
desc sxlc3
insert into sxlc3 values ('sxl');    -- ok
insert into sxlc3 values ('s');    -- ok
insert into sxlc3 values ('12345678901');    -- 失败，11位超出长度限制
select * from sxlc3

-- 示例5
create table sxlc4(idx varchar(65535));    -- 失败
create table sxlc4(idx varchar(21845));    -- 失败
create table sxlc4(idx varchar(21844));     -- ok
create table sxlc4(idx varchar(21843));     -- ok
-- 注意： 
-- 1、对于字符集utf8./utf8mb4,最大长度是21845-(1~2个存储占位符)，即21844/21843
-- 2、对于字符集gbk，varchar的最大长度是32767


-- 示例6 文本类型text
-- tinytext, 最多存255个字符
create table text1(id int,name tinytext)    -- ok

-- text, 最多存65535个字符
create table text1(id int,name text)    -- ok

-- mediumtext, 最多存16777215个字符
create table text1(id int,name mediumtext)    -- ok

-- longtext, 最多存4294967295个字符
create table text1(id int,name longtext)    -- ok


-- 示例7，enum 枚举, 相当于单选
-- enum，最多65535个枚举项
create table enum1(output enum('Y','N'));
desc enum1;
insert into enum1 values ('Y');    -- ok
insert into enum1 values ('N');    -- ok
insert into enum1 values ('C');    -- 失败，定义的枚举项中没有C
select * from enum1

create table enum2(output enum('1','2','3','4','5'));
desc enum2;
insert into enum2 values ('1');    -- ok
insert into enum2 values ('2');    -- ok
insert into enum2 values ('5');    -- ok
insert into enum2 values ('5');    -- ok
insert into enum2 values ('2','4');    -- 失败，只能插入其中一个值
insert into enum2 values ('A');    -- 失败，定义的枚举选项中没有A
select * from enum2;

-- 示例8，set 集合，相当于多选
create table set1 (output set('A','B','C','D'));
desc set1;
insert into set1 values ('a');    -- ok，小写会转换为大写
insert into set1 values ('A');    -- ok
insert into set1 values ('B,D');    -- ok
insert into set1 values ('A,B,C,D');    -- ok
insert into set1 values ('A,A');    -- ok,最终只显示一个A
insert into set1 values ('D,C');    -- ok，最终按顺序显示C,D
insert into set1 values ('C,H');    -- 失败。集合中没有H选项

JSON数据类型

create table json1 (id int, info json);
desc json1;
insert into json1 values (1,'{"name":"sxl1","sex":"男","age":"25","address":"甘肃"}');    -- ok
insert into json1 values (1,'{"name":"sxl2","sex":"男","age":"30","address":"山东"}');    -- ok
insert into json1 values (1,json_object("name","sxl3","sex","女","age","22","address","海南"));    -- ok, 使用json_object(),里面的冒号换逗号，效果是一样的。
select * from json1

-- 对json里面的数据进行查询。info是字段名，格式是json。$.name是json数据里面的一个键
select * from json1 where info -> '$.name' = 'sxl1';    

-- 对json里面的数据进行查询。info是字段名，格式是json。$.name是json数据里面的一个键
-- 和上面的结果一样，只是写法不同而已。
select * from json1 where json_extract(info,'$.name') = 'sxl2';

select json_type(info -> '$.address') from json1;    -- 返回json中键的类型