MySQL2——MySQL的数据类型

• 数据类型

数据类型分类:

数值类型：

1. tinyint类型

有符号：-128~127

无符号：0~255

//数值越界测试
/*有符号的tinyint类型:-128~127*/
create table tt1(num tinyint);

insert into tt1 (num) values(1);

insert into tt1 (num) values(128);
/*ERROR 1264 (22003): Out of range value for column 'num' at row 1*/
insert into tt1 (num) values(-129);
/*ERROR 1264 (22003): Out of range value for column 'num' at row 1*/

select * from tt1;

/*无符号的tinyint类型0~255*/
create table tt2(num tinyint unsigned);

insert into tt2 (num) values(23);

insert into tt2 (num) values(-1);
/*ERROR 1264 (22003): Out of range value for column 'num' at row 1*/
insert into tt2 (num) values(256);
/*ERROR 1264 (22003): Out of range value for column 'num' at row 1*/

select * from tt2;

注意:

1.在MySQL中，整型可以指定是有符号的和无符号的，默认是有符号的

2.可以通过UNSIGNED来说明某个字段是无符号的

3.尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，可将int类型提升为bigint类型。

2. bit类型

语法：bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1。

create table tt2( id int, a bit(8));

insert into tt2 values(10, 10);

select * from tt2;
/*此处显示：
+------+------+
| id   | a    |
+------+------+
|   10 |
    |
+------+------+
*/

/*注意：
1.bit字段在显示时，是按照ASCII码对应的值显示
2.bit(1)用来存放0或1，相比布尔类型bit(1)更节省空间(布尔类型的0和1是以字节的形式存放)*/

insert into tt2 values (65,65);
/*此处显示:
+------+------+
| id   | a    |
+------+------+
|   10 |
    |
|   65 | A    |
+------+------+
*/

3. float类型

语法：float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占4个字节

float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入

create table tt3(id int,f float(4,2));

insert into tt3 values(100,99.99);

//数值范围测试
insert into tt3 values(100,999.99);
insert into tt3 values(100,99.999);
/*ERROR 1264 (22003): Out of range value for column 'f' at row 1*/

//四舍五入测试
insert into tt3 values(100,99.994);
/*四舍五入(100,99.99)*/
insert into tt3 values(100,-99.995);
/*ERROR 1264 (22003): Out of range value for column 'f' at row 1*/

• 如果定义的是float(4,2) unsigned 为无符号的数，范围：0 ~ 99.99

//查看无符号数的数据越界警告
create table tt4(id int , f float(4,2)unsigned);
insert into tt4 values(100,-0.1);
show warnings;
/*Error | 1264 | Out of range value for column 'f' at row 1*/

4. decimal类型

语法：decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

注意：

• decimal和float很相似，但decimal的精度更高
• float表示的精度大约是7位。
• decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0。如果m被省略，默认是 10。

5. char类型

语法：char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

注意：char(2) 表示可以存放两个字符,可以是字母或汉字，但是不能超过2个,最多只能是255

create table tt5(id int,name char(2));
insert into tt5 values(1,'ad');
insert into tt5 values(2,'张三');

6. varchar类型

语法：varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

关于varchar(len)的len值：

• varchar长度指定为0-65535，其中有1-3个记录数据大小的字节，其有效字节为65532
• utf8编码：len=65532/3=21844(utf中一个字符占3个字节)
• gbk编码：len=65532/2=32766(gbk中一个字符占2个字节)

create table tt5(name varchar(21845))charset=gbk; //通过

create table tt5(name varchar(21845))charset=utf8;
/*ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs*/

char和varcahr比较

• char：定长字符串，效率高
• varchar:变长字符串，效率低

7. 日期和时间类型

• datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ ，表示范围：1000-9999，占八字节
• date:日期 ‘yyyy-mm-dd’，占三字节
• timestamp：时间戳，从1970年开始的 ‘yyyy-mm-dd HH:ii:ss’,占四字节，在写sql语句时，时间戳会自动生成

create table tt6(t1 date,t2 datetime,t3 timestamp);
insert into tt6(t1,t2) values ('2019-03-10','2019-03-10 11:22:01');
update tt6 set t1 = '2019-03-11';
select * from tt6;

8. enum类型

语法：enum：枚举，“单选”类型

enum(‘选项1’,‘选项2’,‘选项3’,…);

注意：enum只是提供若干项可选值，实际只存储其中一个值，存储的是数字，每个选项都对应一个数字1-65535，添加枚举值时也可以添加对应的数字编号

create table tt7(
id int,
name varchar(30),
gender enum('男','女'));

insert into votes values(1,'张三','男');
insert into votes values(2,'李四',2);

select * from tt7 where gender=2;

9. set类型

语法：set：集合，“多选”类型

set(‘选项值1’,‘选项值2’,‘选项值3’, …);

注意：set只是提供若干项可选值，可存储其中任意多个值，也存储的是数字，添加枚举值时也可以添加对应的数字编号：1，2，4，16，32…最多64个

create table tt7(
id int,
name varchar(30),
gender enum('男','女'),
hobby set('登山','游泳','篮球','武术'));

insert into votes values(1,'张三','男','登山,武术');
insert into votes values(2,'李四',2,'游泳');

select * from tt7 where gender=2;

10. find_ in_ set类型

语法：find_in_set(sub,str_list)

模糊查询：如果sub在str_list中，则返回下标；如果不在，返回0。(str_list：用逗号分隔的字符串)

//在字符串中找字符段
select find_in_set('a', 'a,b,c');

//在tt7表中查询爱好游泳的人：
select * from tt7 where find_in_set('游泳', hobby);