【半夜学习MySQL】数据库中的数据类型（含数值类型、文本二进制类型、时间类型、String类型详谈）

🏠关于专栏：半夜学习MySQL专栏用于记录MySQL数据相关内容。
🎯每天努力一点点，技术变化看得见

---

数据类型分类

数值类型

对于上述类型中，本文仅挑选具有代表性的类型进行演示讲述，余下类型与演示类型具有相似性

bit类型

基本语法：

bit[(M)]

★ps：该类型为位字段类型，M表示比特位数，范围为1到64。如果M被省略，默认为1

举例：
例子1：

create table TestBit(id int,b bit(8));
insert into TestBit values (1001, 65);
insert into TestBit values (1002, 66);
insert into TestBit values (1002, 67);

★ps：从上面结果可以发现：bit字段在显示时，是按照ASCII码对应得值进行显示的。

例子2：

create table TestBit_a(id int,b bit);
insert into TestBit_a values (1001, 1);
insert into TestBit_a values (1002, 0);
insert into TestBit_a values (1002, 1);

★ps：由于bit字段在显示时，是按照ASCII码对应得值进行显示的。而ASCII码为0和1的字符不可显，故上述select语句执行后，结果中并没有显示任何字符。

测试：
TestBit_a表的b字段只占一个比特位，因而b的取值只能为0或1↓↓↓

如果插入非0或1的值呢？如下图所示，插入2、-1后，mysql直接拒绝了用户的操作↓↓↓

★ps：如果我们向mysql特定的类型中插入不合法的数据，mysql一般都是直接拦截我们，不让我们做对应的操作。反过来，如果我们已经有数据被成功插入到mysql中了，一定插入的时候是合法的。

★ps：所以。mysql中，一般而言，数据类型本身也是一种：约束（倒逼程序员，让程序员尽可能正确的插入，约束使用者；另外，如果你不是一个很好的使用者，mysql也能保证数据插入的合法性）。就能保证数据库中数据是可预期、完整的。

使用的注意事项：
● 在没有显示给出bit的位数时，默认为1↓↓↓

●如果某个字段只存在两种取值，则可以使用0表示第一种取值，1表示另一种取值。从而可以使用bit(1)存储，可节省空间。例如：下表中的性别使用1个bit表示↓↓↓

tinyint类型

基本语法：

tinyint [unsigned]

举例：
有符号tinyint示例：

create table TestTinyint(num tinyint);
insert into TestTinyint values (127);
insert into TestTinyint values (-128);
insert into TestTinyint values (0);
select * from TestTinyint;

无符号tinyint示例：

create table TestTinyint_un(num tinyint unsigned);
insert into TestTinyint_un values (0);
insert into TestTinyint_un values (255);
insert into TestTinyint_un values (123);
select * from TestTinyint_un;

测试：
这里演示了有符号与无符号tinyint的取值边界，及越界后，mysql会拒绝插入
对有符号tinyint的测试：

insert into TestTinyint values (128);
insert into TestTinyint values (-129);

对无符号tinyint的测试：

insert into TestTinyint_un values (-1);
insert into TestTinyint_un values (256);

使用的注意事项：
尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其如此，还不如设计时，将int类型提升为bigint类型。

★ps：在MySQL中，整型可以指定为有符号和无符号两种，默认是有符号的。可以通过在字段类型最后添加UNSIGNED来说明某个字段是无符号的。

int类型

基本语法：

int(M) zerofill

举例：
示例1：

create table TestInt(num int);
insert into TestInt values(-2147483648);
insert into TestInt values(2147483647);
insert into TestInt values(0);

示例2：
如果希望某个字段取值最多有3位数，少于3位数的，需要给该数添加前导0↓↓↓

create table TestInt_a(num int(3) zerofill);
insert into TestInt_a values(1);
insert into TestInt_a values(22);
insert into TestInt_a values(333);
select * from TestInt_a;

★ps：int类型后面的数字主要是为了显示目的，并不影响存储或值的范围。在实际应用中，除非有特殊的显示需求，否则通常可以忽略这个显示宽度。

测试：
这里演示了有符号与无符号tinyint的取值边界，及越界后，mysql会拒绝插入

insert into TestInt values(2147483648);
insert into TestInt values(-2147483649);

float类型

基本语法：

float[(m,d)] [unsigned]

★ps：m指定显示长度，d指定小数位数，float占用4字节空间。即整数部分的位数=m-d。

举例：
示例1：
设计一个表，该表的num字段可以存储-99.99到99.99的数值

create table TestFloat(num float(4,2));
insert into TestFloat values(99.99);
insert into TestFloat values(-99.99);
insert into TestFloat values(3.7);
insert into TestFloat values(0.1);
insert into TestFloat values(8);

★ps：只要在当前字段范围内的数值，若该数值的小数位数不满d位，mysql会在最后自动补0

同时，float类型会自动进行四舍五入（由于99.999四舍五入后会变为100.00，超出float(4,2)表示范围，会报错）↓↓↓

insert into TestFloat values(9.957);
insert into TestFloat values(28.999);
insert into TestFloat values(99.999);

示例2：
如果num字段要求0到99.99，则此时可以使用unsigned进行修饰↓↓↓

create table TestFloat_un(num float(4,2) unsigned);
insert into TestFloat_un values(99.99);
insert into TestFloat_un values(0);
insert into TestFloat_un values(8.846);
insert into TestFloat_un values(9.732);

测试：
这里演示了有符号与无符号float的取值边界，及越界后，mysql会拒绝插入

insert into TestFloat values(-100.00);
insert into TestFloat values(100.00);
insert into TestFloat values(-99.9999);
insert into TestFloat values(99.9999);

insert into TestFloat_un values(-1);
insert into TestFloat_un values(100.00);
insert into TestFloat_un values(99.9999);

使用的注意事项：

create table TestFloat_b(num float);
insert into TestFloat_b values(123456789.123456789);
select * from TestFloat_b;

★ps：从上述结果可以看出，新插入的数值精度丢失。由于mysql中float占4字节（32个比特位），1个比特位表示符号位，23个比特位表示数值部分，8比特表示科学计数法的指数部分（而指数部分中1位标识指数的正负）。即数值部分表示的数值为0到2^23（8388608），而上述123456789.123456789的数值部分为123456789123456789，超出float数值部分表示返回，故发生了精度丢失。

decimal类型

基本语法：

decimal[(m,d)] [unsigned]

★ps：m指定长度，d表示小数点的位数

举例：

create table TestDecimal(num decimal(4,2));
insert into TestDecimal values(99.99);
insert into TestDecimal values(-99.99);
insert into TestDecimal values(8.979);
insert into TestDecimal values(9.432);

★ps：decimal相比float、double具有更高的精度，用法与float类似，如果希望小数的精度高，推荐使用decimal。

文本、二进制类型

char类型

基本语法：

char(L)

★ps：char类型为固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255
举例：

create table TestChar(ch char(6));
insert into TestChar values('aa');
insert into TestChar values('bbbb');
insert into TestChar values('cccccc');
select * from TestChar;

★ps：char(L)中的L表明的是字符的个数，即使是中文字符也算一个字符，char并不考虑字符占据的字节数。不同编码下，各个字符占据的空间可能是不同的，例如：英文字符utf8编码需要1个字节存储，中文字符在utf8编码需要3个字节存储。

insert into TestChar values('小明六六六六');
select * from TestChar;

测试：
如果插入字符个数超出字段char(L)中的L长度，则会报错↓↓↓

insert into TestChar values('abcabcabc');

同时，char最长字符为255个，定义表时，如果超出255，将不被允许创建↓↓↓

create table TestChar_a(ch char(256));

varchar类型

基本语法：

varchar(L)

★ps：可变长度字符串，L表示字符长度，最大长度65535个字节

举例：

create table stu(id int, name varchar(6));
insert into stu values(1001, '小明');
insert into stu values(1002, '小唐');
insert into stu values(1003, '小张');
select * from stu;

测试：
如果插入超出varchar(L)指定的长度L，则会报错↓↓↓

insert into stu values(1004,'这是一个很长的名字');

注意事项：
关于varchar(len),len到底是多大，这个len值，和表的编码密切相关↓↓↓
varchar长度可以指定为0到65535之间的值，但是有1到3个字节用于记录数据大小，所以说有效字节数是65532。
当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

如何选择定长或变长字符串？
● 如果数据确定长度都一样，就使用定长，即char类型，比如：身份证，手机号，md5。
● 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
● 定长的磁盘空间比较浪费，但是效率高。
● 变长的磁盘空间比较节省，但是效率低。
● 定长的意义是，直接开辟好对应的空间
● 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

下面对比一下char和varchar存储同一个数据的差异↓↓↓

实际存储	char(4)	varchar(4)	char占用字节数	varchar占用字节数
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
abcde	无法存储	无法存储	数据超出长度	数据超出长度

上述4*3+1的+1操作，这1个字节用于存储字符串长度。这个数值的具体取值根据字符串长度的边长而增大，占用空间范围为1到3字节。

★ps：数据库中，每行数据的长度也有上限。如果只有一个类型为varchar的字段，则varchar可以取最大值21844（但一旦大于21844，mysql将不允许创建表）；如果表中不仅包含类型为varchar的字段，还包含其他字段，则varchar的最大取值需要先减去其他字段所占用的空间。

时间类型

常用的日期类型有3中，分别如下：
● date：日期yyyy-mm-dd，占用三个字节
●datetime：时间日期yyyy-mm-dd hh:mm:ss，占用八个字节
●timestamp：时间戳，从1970年开始经过的秒数，表示格式与datetime一致yyyy-mm-dd hh:mm:ss，占用四个字节
举例：

create table TimeTable(dat date, dt datetime, ttp timestamp, command varchar(128));
insert into TimeTable(command) values ('666');
insert into TimeTable values ('2024-8-8', '2024-8-8 12:23:23', '2024-8-8 12:23:23', 'nice bro!');

★对于类型为timestamp字段，在没有显式插入数值时，会被设置为当前时间。

如果对某行数据做更新，timestamp也会自动更新为当前时间↓↓↓

String类型

enum类型

基本语法：

enum(可选选项1,可选选项2,可选选项3,...)

enum提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值，即enum只支持单选。可设置的选项最多65535个。

举例：

create table vip(id int, name varchar(6), gender enum('男', '女'), grade enum('普通', '会员'));
insert into vip values(1001, '小明', '男', '会员');
insert into vip values(1002, '小翔', '男', '普通');
insert into vip values(1003, '小汤', '男', '会员');
select * from vip;

测试：
如果给类型为enum的字段插入非列举的值，则会报错↓↓↓

insert into vip(1004, '小雅', '女', '程序员');
insert into vip(1005, '小光', '保密', '会员');

注意事项：
enum为了提高存储效率，实际存储的是数字，上述vip表中的gender字段，1表示’男’，2表示’女’。因而我们要插入一个男生的信息，可以这么插入↓↓↓

insert into vip values(1004, '小光', 1, '会员');
select * from vip;

★ps：单选选项对应的数字是从1开始编号的，而不是从0开始编号的。

set类型

基本语法：

set(可选选项1,可选选项2,可选选项3,...)

★ps：该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值。最多可以设置64个选项。
举例：

create table hobby(name varchar(6), hobby set('编程', '羽毛球', '马拉松'));
insert into hobby values('小明', '编程');
insert into hobby values('小光','编程,羽毛球');
insert into hobby values('小翔','编程,羽毛球,马拉松');

★ps：set支持多选，如果多选的话，需要使用逗号间隔多个选项。

如果要选择爱好中包含’编程’的行呢？

select * from hobby where hobby='编程';

使用上述方式进行查找时，只会找到hobby只包含’编程’的。如果想找到爱好包含编程的，需要使用如下语句↓↓↓

select * from hobby where find_in_set('编程', hobby);

★ps：find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list是用逗号分隔的字符串。

测试：
如果插入不在set选项中的值，则会报错↓↓↓

insert into hobby values('小天', '蹦迪');

注意事项：
set使用位图的方式存储各个选项，第一个选项对应1（$2^0$次方），第二个选项对应2（$2^1$），第三个选项对应4（$2^2$）…这样存储可以大大提高存储效率

例如上述的三个爱好中，'编程’对应最低1个比特位，即数字1；‘羽毛球’对应倒数第二个比特位，即数字2；‘马拉松’对应倒数第三个比特位，即数字4。如果同时选择’编程’和’马拉松’，则使用数字5表示。

下面示例，插入一个爱好为’编程’+'马拉松’的数据行↓↓↓

insert into hobby values('小刚', 5);
select * from hobby;

★ps：不建议在enum中使用选项对应的枚举值，集合值的时候采用数字的方式，因为不利于阅读。

🎈欢迎进入半夜学习MySQL专栏，查看更多文章。
如果上述内容有任何问题，欢迎在下方留言区指正b(￣▽￣)d