05 数据类型

目录

1. 分类
2. 数值类型
3. 小数类型
4. 字符串类型
5. 日期和时间类型
6. 集合类型

---

1. 分类

2. 数值类型

tinyint

create table t1 (num tinyint);
insert into t1 values (1);
insert into t1 values (128); – 越界插入，报错
select * from t1;

说明:

• 在mysql中，整形可以指定是右符号和无符号的，默认是有符号的

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

• 无符号案例

create table t2(num tinyint unsigned);
insert into t2 values(-1); – 无符号，范围是：0-255
insert into t2 values(255);
selsect * from t2;

• 其他类型自己推导

数据类型本身是一种约束，如果插入了不合法的数据，mysql会阻止对应的操作，如果插入成功，那数据就是合法的。这种是对使用者的约束，倒逼使用者规范正确的插入，如果出错，mysql也可以保证合法性，最终保证数据库中的数据可预期，完整

bit

基本语法：

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

举例：

create table t4(id int, online bit(1));
insert into t4 values(10, 10);
select * from t4; 发现怪异现象，数据没有出现

• bit字段在显示时，是按照ASCII码对应的值显示，有些值是不可显示

insert into t4 values(1, 65);
insert into t4 values(2, ‘A’);
select * from t4;

• 如果我们有这样的值，只存放0或1，这时可以定义bit(1)，可以节省空间

create table t5(gender bit(1));
insert into t5 values(0);
insert into t5 values(1);
insert into t5 values(2); —插入2时，已经越界
ERROR 1406 (22001): Data too long for column ‘gender’ at row 1

3. 小数类型

float

语法：

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

案例：
小数：float(4,2)表示的范围是-99.99~99.99，保存值时会四舍五入

create table t3(id int, salary float(4,2));
insert into t3 values(100, -99.99);
insert into t3 values(101, -99.991); --多的一点会四舍五入
select * from t3;

位数不够会自动补全，当四舍五入超过范围时，也不能插入

insert into t3 values(100, -99.997);
insert into t3 values(100, 2.1);

如果定义的是无符号，这时范围上限不变，下限必须大于等于0，范围是0~99.99

create table t4(id int, salary float(4,2) unsigned);
insert into t4 values(100, -0.1);
show warnings;
>insert into t4 valyes(100, -0);
insert into t4 valyes(100, 99.99);

float在数值较大时不能保证准确性，会有一定的精度丢失

alter table modify t4 salary float;

decimal

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

decimal(5,2) 表示的范围是-999.99~999.99
decimal(5,2) unsigned 表示的范围是0~999.99
decimal和float很像，但是有区别，可以表示的精度不一样

create table t5(id int, salary float(10,8), salary2 decimal(10,8));
insert into t5 values(100,23.12345612,23.12345612);

说明：float表示的精度大约是7位
decimal整数最大位数m为65，支持小数最大位数d是30，如果d被忽略，默认是0，如果m忽略，默认是10

如果需要精度高，就用decimal

4. 字符串类型

char

语法：

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

create table t6(id int, name char(2));
insert into values(100, ‘ab’);
insert into values(100, ‘中国’);
select * from t6;

说明：
char(2) 表示可以存放两个字符，可以是字母或汉字，utf一个汉字3字节，一个汉字或字母算一个字符，但是不能超过2个。L限制最多只能是255

create table t6(id int, name char(256));
ERROR 1074 (42000): Column length too big for column ‘name’ (max = 255); use
BLOB or TEXT instead

varchar

语法：

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

案例：

create table t7(id int, name varchar(6)); --表示可以存放6个字符
insert into t7 values(100, ‘hello’);
insert into t7 values(100, ‘我爱你，中国’);
select * from t7;

说明：
varchar是变长字符串，实际len值多大，和表的编码密切相关

• varchar长度可以指定为0-65536之间的值，但是有1-3个字节用于记录数据大小，所以说有效字节数是65532
• 当表的编码是utf8时，carchar的参数n最大值是65532/3=21844，因为utf中，一个字符是3个字节。如果编码是gbk，varchar的参数n最大是65532/2=32766，因为gbk，一个字符占2字节

create table t8(name varcahr(21845))charset=utf8; --验证了utf8不能超过21844
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535. You have to change some columns to
TEXT or BLOBs
create table tt11(name varchar(21844)) charset=utf8;
Query OK, 0 rows affected (0.01 sec)

char和varchar比较

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

5. 日期和时间类型

常用的日期有如下三个：
data：日期’yyyy-mm-dd’，占用三字节
datetime的时间日期格式’yyyy-mm–dd HH::ii:ss’ 表示范围从1000到9999，占用八字节
timestamp：时间戳，从1970年开始的’yyyy-mm–dd HH::ii::ss’格式和datetime完全一致，占用四字节

案例：

//创建表
create table birthday(t1 date, t2 datetime, t3 timestamp);
//插入数据
insert into birthday(t1,t2) values(‘1997-7-1’, ‘2008-8-8 12:1:1’); --插入两种时间
select * from birthday;

//更新数据
update birthday set t1=‘2000-1-1’;
selsect * from birthday;

6. 集合类型

enum和set

语法：

enum：枚举，‘单选’类型;
enum（‘选项1’，‘选项2’，‘选项3’，…);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值，而且处于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依对应如下数字：1,2,3…最多65535个，当我们添加枚举值时，也可以添加对应的数字编号

set：集合，“多选”类型；
set(“选项值1”，“选项值2”，“选项值3”，…);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值，而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值对应如下数字：1,2，4,8,16,32…最多64个

不建议在添加枚举值，集合值的时候采用数字的方式，因为不利于阅读

案例：
有一个调查表votes，需要调查人的喜好，比如（登山，游泳，篮球，武术）中去选择（可以多选）（男，女）[单选]

create table votes(
username varchar(30),
hobby set(‘登山’，‘游泳’，‘篮球，‘武术’’), --注意，使用数字标识每个爱好的时候，想想linux权限，采用比特位和set中的爱好对应
gender enum(‘男’，‘女’); --注意，使用数字标识的时候，就是正常的数组下标

插入数据：

insert into votes values(‘李华’， ‘登山，武术’，‘男’);
insert into votes values(‘juse’， ‘登山，武术’，‘2’);
select * from votes where gender=2;

有以下数据，想查找所有喜欢登山的人：

使用如下查询语句：

select * from votes where hobby=‘登山’

不能查出所有爱好为登山的人

集合查询find_in_set函数

find_in_set（sub, str_list）：如果sub在str_list中，则返回下标，如果不在，返回0，str_list用逗号分隔的字符串

这个函数只能返回1个结果，不能查询两个，例如（‘ab’, ‘a,b,c’）

查询爱好为登山的人

select * from votes where find_in_set(‘登山’, hobby);