文章目录
- 数据类型分类
- 数值类型
- tinyint类型
- bit类型
- 小数类型
- float
- decimal
- 字符串类型
- char
- varchar
- 日期和时间类型
- enum和set
数据类型分类
对于数据类型分类,这里简单分为数值类型(如BIT,BOOL,INT),文本、二进制类型(如CHAR,VARCHAR),时间日期(DATE),String类型(如ENUM类型),这里简单了解一下即可
数值类型
不同的类型所占字节数是不一样的。如TINYINT所占字节数为1,而INT所占字节数为4.同时数值范围也是有区别的,这些我们都需要去注意。
tinyint类型
用tinyint数据类型创建表t1:
mysql> create table if not exists t1(-> num tinyint-> );
插入tinyint类型数值范围内的数据是可以的:
mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values (127);
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)
超出范围就不可以了:
mysql> insert into t1 values (-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values (128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql>
用tinyint unsigned数据类型创建表t2:
mysql> create table if not exists t2(-> num tinyint unsigned-> );
Query OK, 0 rows affected (0.03 sec)
此时默认是无符号的unsigned:范围是0-255:插入数值超过范围就会直接ERROR
mysql> insert into t2 values(0);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values(255);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values(256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values(-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql>
对于MySql,如果我们向mysql特定的类型中插入不合法的数据,MySql一般都是直接拦截,不然我们做对应的操作!不会截断数据。
反过来,我们如果已经有数据被插入到MySql中了,一定是插入的时候是合法的
所以MySql中一般而言,数据类型本身也是一种
约束
,约束程序员尽可能进行正确的插入,约束使用者,如果你不是一个很好的使用者,MySql也能保证数据插入的合法性。就能保证数据库中的数据是可预期,完整的
MySql表中建立属性列:列名称 类型在后,如num tinyint unsigned;
这是tinyint类型我们所需要注意的,同时,尽量不使用unsigned,对于int类型可能存放不下的数据,int unsigned同样可能存放不下,与其如此,还不如设计时,将int类型提升为bigint类型。
bit类型
bit[(M)] : 位字段类型。M表示每个值的位数,范围从1到64。如果M被忽略,默认为1
注意数值范围,超出了会ERROR:
mysql> create table if not exists t4(-> id int,-> online bit(65)-> );
ERROR 1439 (42000): Display width out of range for column 'online' (max = 64)
mysql>
创建含有bit数据类型的表t3:
mysql> create table if not exists t3(-> id int,-> online bit(1)-> );
Query OK, 0 rows affected (0.03 sec)mysql>
此时只有一个比特位,只能插入0或者插入1,:
此时查看表t3:
我们发现online是位类型的,并没有显示,我们可以理解为当前online的按照ASCII值显示,如果想看到:在插入一条数据:
mysql> insert into t3 (id,online) values (123,0);
Query OK, 1 row affected (0.01 sec)
**select id,hex(online) from t3;**这时候就显示的看到0和1了
如果插入’a’或者97:
mysql> insert into t3 (id,online) values (123,'a');
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 (id,online) values (123,97);
Query OK, 1 row affected (0.01 sec)
bit字段在显示时,是按照ASCII码对应的值显示
小数类型
float
float[(m, d)] [unsigned] : M指定显示长度,d指定小数位数,占用空间4个字节
举个例子:
mysql> create table if not exists t5 (-> id int,-> salary float(4,2)-> );
Query OK, 0 rows affected (0.02 sec)
此时要求浮点数小数位数是两位:
mysql> insert into t5 (id,salary) values (1,99.99);
Query OK, 1 row affected (0.00 sec)mysql> insert into t5 (id,salary) values (1,-99.99);
Query OK, 1 row affected (0.00 sec)mysql> insert into t5 (id,salary) values (1,-10.0);
Query OK, 1 row affected (0.00 sec)
虽然插入的是-10.0,但是mysql插入的是-10.00,遵守小数是两位的规则。
mysql> insert into t5 (id,salary) values (1,23.456);
Query OK, 1 row affected (0.00 sec)
插入的如果是,23.456,会变成23.46(采用四舍五入)
插入的如果是,99.993是可以的,而如果是99.995那就不行了:(这个也可以理解,四舍五入之后不在合法)
mysql> insert into t5 (id,salary) values (1,99.993);
Query OK, 1 row affected (0.01 sec)mysql> insert into t5 (id,salary) values (1,99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql>
总结一下就是:小数:float(4,2)表示的范围是-99.99 ~ 99.99,MySQL在保存值时会进行四舍五入
现在,我们来看看无符号的浮点数:
mysql> create table if not exists t6(-> id bigint,-> salary float(4,2) unsigned-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t6 (id,salary) values (1,99.99);
Query OK, 1 row affected (0.01 sec)mysql> insert into t6 (id,salary) values (1,99.996);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 (id,salary) values (1,-99.99);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6 (id,salary) values (1,-0.01);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql>
负数此时是插入不了的了。
decimal
decimal也是mysql中的浮点数类型,float存储数据时,小数比较大,或者小数位点比较多存储的就不太准确了,这与浮点数存储规则有关(mysql中的float浮点数精度为是7)。
mysql> create table if not exists t7 (-> f1 float(10,8),-> f2 decimal (4,2)-> );
Query OK, 0 rows affected (0.03 sec)
mysql中的float浮点数精度为是7,如果我们设置为8会不会起效果呢:
mysql> insert into t7 (f1,f2) values (10.0,99.99);
Query OK, 1 row affected (0.00 sec)mysql> insert into t7 (f1,f2) values (10.0,99.999);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1mysql> insert into t7 (f1,f2) values (10.0,23.935);
Query OK, 1 row affected, 1 warning (0.01 sec)
进行修改:
mysql> alter table t7 modify f2 decimal(10,8);
Query OK, 2 rows affected (0.30 sec)
Records: 2 Duplicates: 0 Warnings: 0
同样插入相同的数据:
虽然float设置的是8位精度,但是此时存储的已经与插入的差别比较大了,而decimal插入什么就存储什么
float在精度过大会做一些动作,而decimal不会。
float表示的精度大约是7位。decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略,默认为0.如果m被省略,默认是10。
字符串类型
char
char(L): 固定长度字符串,L是可以存储的长度,单位为字符,最大长度值可以为255
mysql> create table if not exists t8(-> id int,-> name char(2)-> );
Query OK, 0 rows affected (0.03 sec)
如果插入汉字:对于gbk编码一个占用2个字节,utf8编码一个汉字占用3个字
mysql> insert into t8 (id,name) values (1,'中');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 (id,name) values (1,'中国');
Query OK, 1 row affected (0.00 sec)mysql> insert into t8 (id,name) values (1,'中国人');
ERROR 1406 (22001): Data too long for column 'name' at row 1
而我们这里插入2个汉字能够成功,这说明对于char单位为字符,mysql的字符与C/C++语言不同,mysql的字符代表的是符号,所以能够插入成功!
总结就是char(2) 表示可以存放两个字符,可以是字母或汉字,但是不能超过2个, 最多只能是255
mysql> create talbe if not exists t8 (-> id int,-> address char(256)-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'talbe if not exists t8 (
id int,
address char(256)
)' at line 1
mysql>
varchar
varchar(L): 可变长度字符串,L表示字符长度,最大长度65535个字节(mysql存储char类型,utf8默认是3字节,65535/3 = 21845)
举个例子:
mysql> create table if not exists t9(-> id int,-> name varchar(6)-> );
Query OK, 0 rows affected (0.02 sec)
关于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和varchar比较
如果数据确定长度都一样,就使用定长(char),比如:身份证,手机号,md5
如果数据长度有变化,就使用变长(varchar), 比如:名字,地址,但是你要保证最长的能存的进去。
定长的磁盘空间比较浪费,但是效率高。
变长的磁盘空间比较节省,但是效率低。
定长的意义是,直接开辟好对应的空间
变长的意义是,在不超过自定义范围的情况下,用多少,开辟多少。
日期和时间类型
常用的日期有如下三个:
**date 😗*日期 ‘yyyy-mm-dd’ ,占用三字节
datetime 时间日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范围从 1000 到 9999 ,占用八字节
**timestamp :**时间戳,从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致,占用四字节
举个例子:
mysql> create table if not exists t11 (-> t1 date,-> t2 datetime,-> t3 timestamp-> );
Query OK, 0 rows affected (0.02 sec)
对于时间戳timestamp:创建表结构,插入数据是tinmestamp会自动更新。所以不需要更改
mysql> insert into t11 (t1,t2) values ('2000-10-01','1949-10-01 08:00:00');
Query OK, 1 row affected (0.00 sec)
此时更新t1:
mysql> update t11 set t1='1999-01-01';
Query OK, 1 row affected (0.01 sec)
t1和t3都会被更新,这也说明了时间戳timestamp会自动更新,意义在于记住时间的更行。
而date的意义在于记录某一天即可。结合具体的场景即可。
enum和set
语法:
- enum:枚举,“单选”类型;
enum(‘选项1’,‘选项2’,‘选项3’,…);
该设定只是提供了若干个选项的值,最终一个单元格中,实际只存储了其中一个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,3,…最多65535个;当我们添加枚举值时,也可以添加对应的数字编号。
- set:集合,“多选”类型;
set(‘选项值1’,‘选项值2’,‘选项值3’, …);
该设定只是提供了若干个选项的值,最终一个单元格中,设计可存储了其中任意多个值;而且出于效率考虑,这些值实际存储的是“数字”,因为这些选项的每个选项值依次对应如下数字:1,2,4,8,16,32,…最多64个。
说明:不建议在添加枚举值,集合值的时候采用数字的方式,因为不利于阅读。
举个例子👇
mysql> create table if not exists votes (-> username varchar(30),-> gender enum('男','女'),-> hobby set('代码','乒乓球','羽毛球','足球')-> );
Query OK, 0 rows affected (0.03 sec)
现在插入数据:
mysql> insert into votes values('李四','','代码');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into votes values('李四','女','代码');
Query OK, 1 row affected (0.00 sec)mysql> insert into votes values('王五','unknow','代码');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
性别只能插入男或女,枚举类型提供了类型约束,只能插入枚举的类型。插入常量的下标也是可以的:下标数字从1开始,代表第一个
mysql> insert into votes values('坤坤',1,'代码');
Query OK, 1 row affected (0.01 sec)
当然,也可以插入多个hobby:
mysql> insert into votes values ('招八',1,'乒乓球,足球,羽毛球');
Query OK, 1 row affected (0.01 sec)
插入的hobby不能在集合中不存在!如果是多个用逗号分开即可。
可以插入NULL的集合
mysql> insert into votes (username) values ('曹操');
Query OK, 1 row affected (0.00 sec)
插入set下标为0:
mysql> insert into votes value ('流星',1,0);
Query OK, 1 row affected (0.00 sec)
NULL 与’ ‘是不一样的,没有是NULL,’ '是有,只不过是空串。
对于hobby这个set集合,数字代表的不是下标,对应的是
对于插入1:00000---------->00001,代表代码这个hobby
对于插入3:00000----------->00011,代表的是代码和乒乓球这两个hobby
对于插入7:00000------------>001111,代表的是代码乒乓球羽毛球这三个hobby
代表的是位图,从左到右。
- 进行查找
枚举进行查找:
mysql> select * from votes where gender='男';
mysql> select * from votes where gender=2;
当然用数字查找也是可以的:
set进行查找:
mysql> select * from votes where hobby='羽毛球';
但是有的人是有多种爱好的,包括了羽毛球,却没有显示出来,只是显示了爱好只有羽毛球的:
所以mysql提供了find_in_set函数:
mysql> select * from votes where find_in_set('羽毛球',hobby);
如果想查找hobby有足球和代码的呢:
mysql> select * from votes where find_in_set('代码',hobby) and find_in_set('足球',hobby);
find_in_set(sub,str_list) :如果 sub 在 str_list 中,则返回下标;如果不在,返回0;str_list 用逗号分隔的字符串。
举个例子:
find_in_set的作用是查对应的一个元素是否在一个集合里面。0表示假,非0表示真
如上就是在集合中的查找