@TOC
目录
数据类型的作用:
分类 | 数据类型 | 说明 |
数值类型 | BIT(M) | 位类型:M指定位数,默认值为1,范围为1-64 |
BOOL | 布尔类型:使用1表示真,使用0表示假 | |
TINYINT [UNSIGNED] | 占用1字节,默认为有符号 | |
SMALLINT [UNSIGNED] | 占用2字节,默认为有符号 | |
MEDIUMINT [UNSIGNED] | 占用3字节,默认为有符号 | |
INT [UNSIGNED] | 占用4字节,默认为有符号 | |
BIGINT [UNSIGNED] | 占用8字节,默认为有符号 | |
FLOAT[(M,D)] [UNSIGNED] | M指定显示长度,D指定小数位数,占用4字节 | |
DOUBLE[(M,D)] [UNSIGNED] | M指定显示长度,D指定小数位数,占用8字节 | |
DECIMAL(M,D) [UNSIGNED] | M指定显示长度,D指定小数位数,每4个字节表示9个数字,小数点占用1字节 | |
文本、二进制类型 | CHAR(L) | 固定长度字符串:L指定字符串长度,最大为255 |
VARCHAR(L) | 可变长度字符串:L指定字符串长度上限,最多占用65535字节 | |
BLOB | 用于存储二进制数据 | |
TEXT | 用于存储大文本数据 | |
时间日期 | DATE / DATETIME | 日期类型:YYYY-MM-DD格式 / YYYY-MM-DD HH:MM:SS格式 |
TIMESTAMP | 时间戳:以YYYY-MM-DD HH:MM:SS格式进行显示 | |
字符串类型 | ENUM | 枚举类型:ENUM类型的取值范围需要在定义字段时进行指定,设置字段值时只允许从成员中选取单个值,其所需的存储空间由定义ENUM类型时指定的成员个数决定 |
SET | 集合类型:SET类型的取值范围需要在定义字段时进行指定,设置字段值时可以从成员中选取一个或多个值,其所需的存储空间由定义SET类型时指定的成员个数决定 |
注:MySQL本身是不支持bool类型的,当把一个数据设置成bool类型时,数据库会自动将其转换成tinyint(1)的数据类型,其实这个就是变相的bool类型,因为tinyint(1)只有1和0两种取值,可以分别对应bool类型的true和false。
创建一个表,表当中包含一个tinyint类型的列,默认其为有符号类型。如下:
由于tinyint类型占用1字节,因此有符号tinyint的取值范围为-128~127,插入该范围内的数据时都能成功插入。如下:
如果插入的数据不在-128~127范围内,那么插入数据时就会产生报错。如下:
创建一个表,表当中包含一个tinyint类型的列,并指定其为无符号类型。如下:
由于tinyint类型占用1字节,因此无符号tinyint的取值范围为0~255,插入该范围的数据时都能成功插入。如下:
如果插入的数据不在0~255范围内,那么插入数据时就会产生报错。如下:
建议:除非场景要求数值类型必须是无符号,否则尽量不要使用无符号,因为有符号的数值类型存不下的数据,其对应的无符号类型同样可能存不下,这时应该直接将数值类型进行提升。
创建一个表,表当中包含一个int类型的id列和一个8位bit类型的a列。如下:
向表中插入一条记录,记录中指定id和a的值均为10,插入记录后查看表会发现a的值显示的并不是10。如下:
根本原因是因为bit类型在显示时,是按照ASCII码对应的值进行显示的,而在ASCII码表中10对应的是控制字符LF,表示换行的意思。如果向表中插入记录时指定id和a的值均为65,由于ASCII码表中65对应的是字符A,因此插入记录后查看表就会发现a的值显示的是A。如下:
创建一个表,表当中包含用户名name和用户性别gender,其中gender的类型可以指定为1位bit类型,因为性别只有男和女两种取值,使用1个比特位来表示用户的性别就可以节省空间。如下:
如果规定gender列插入0表示男,插入1表示女,那么在插入用户信息时就可以通过插入0和1来指定用户的性别。如下:
如果插入gender列的数据不是0或1,那么插入数据时就会产生报错。如下:
建议:
创建一个表,表当中包含一个float(4,2)类型的列,默认其为有符号类型。如下:
由于float(4,2)的取值范围为-99.99~99.99,因此插入该范围内的数据都能成功插入。如下:
此外,由于MySQL在保存值时会进行四舍五入,因此实际可插入float(4,2)的范围为-99.994~99.994,如果插入的数据不在该范围内,那么插入数据时就会产生报错。如下:
创建一个表,表当中包含一个float(4,2)类型的列,并指定其为无符号类型。如下:
无符号float类型的取值范围,实际就是把对应有符号float类型中的负数部分拿走了,因此float(4,2)的取值范围为0~99.99,实际可插入的范围是0~99.994。如下:
如果插入的数据不在0~99.994范围内,那么插入数据时就会产生报错。如下:
decimal和float类型的使用方式一样,但decimal的精度比float更高。
创建一个表,表当中分别包含一个float(10,8)的列和一个decimal(10,8)的列。如下:
向表当中插入一条记录,指定float和decimal的值均为3.14159267,但最终查表时会发现decimal保持了数据的原貌,而float则会存在一定的精度损失。如下:
创建一个表,表当中包含一个char(6)的content列。如下:
由于char(6)中最多可存储6个字符,因此只要插入的字符个数不超过6个都是能够成功插入的。如下:
如果插入的字符个数超过了6个,那么在插入数据时就会产生报错。如下:
需要注意的是,这里所说的字符并不只是指一个英文字母,一个汉字也是一个字符,因此只要插入的汉字个数不超过6个也是可以插入的。
好处:
varchar类型测试
创建一个表,表当中包含一个varchar(6)的content列。如下:
由于varchar(6)中最多可存储6个字符,因此只要插入的字符个数不超过6都是能够成功插入的。如下:
如果插入的字符个数超过了6个,那么在插入数据时就会产生报错。如下:
varchar类型可指定的字符个数上限
varchar类型最多占用65535字节,其中有1~2字节用来表示实际数据长度,还有1字节来存储其他控制信息,因此varchar类型的有效字节数最多是65532字节。
而varchar类型可指定的字符个数上限,与表的编码格式有关:
因此在定义编码格式为utf8的表时,varchar(L)中的L如果超过了21844,则会产生报错。如下:
而在定义编码格式为gbk的表时,varchar(L)中的L如果超过了32766,则会产生报错。如下:
char和varchar的区别如下:
如何选取char和varchar类型?
char和varchar的优缺点如下:
如果要存储的数据是定长的,那就使用char类型进行存储,比如身份证号码、手机号、md5等。如果要存储的数据是变长的,那就使用varchar类型进行存储,比如名字、地址等。
三种时间日期类型
常用的三种时间日期类型如下:
创建一个表,表当中包含date、datetime和timestamp三种时间日期类型的列。如下:
查看表结构可以看到,timestamp类型的t3列是不允许为空的,它的默认值为CURRENT_TIMESTAMP
。如下:
因此如果插入数据时不插入t3列,那么就会自动插入当前的时间戳。如下:
timestamp类型使用案例
创建一个评论表,表当中包含评论人的昵称、评论的内容和评论的发布时间。如下:
向评论表中插入记录时,只需要指明评论人的昵称和评论的内容,评论的发布时间默认会设置成该记录的插入时间。如下:
如果评论人修改了评论内容,那么就需要对评论表进行更新,更新表的同时评论的发布时间也会更新为修改表的时间。如下:
enum和set类型的区别如下:
比如人的性别只能从男和女中进行二选一,因此可以定义成enum类型,而人的爱好在提供的选项中可能存在多个,因此可以定义成set类型。
创建一个调查表,表当中包含被调查人的姓名、性别和爱好。如下:
向表中插入记录时,被调查人的性别只能从男和女中进行二选一,被调查人的爱好可以从提供的若干个选项中进行多选一或多选多,多个爱好之间需要通过英文逗号隔开。如下:
在插入记录时,除了通过指明男女来设置性别,还可以通过插入数字1和2来设置性别。如下:
根本原因在于,MySQL出于效率考虑,在存储enum值时实际存储的都是数字,enum中提供的选项值依次对应数字1、2、3、…,最多65535个,因此在设置enum值时可以通过数字的方式进行设置。
在插入记录时,除了通过指明多个选项来设置爱好,还可以通过数字的方式来设置。如下:
因为MySQL存储set值时实际存储的也是数字,set中提供的选项值依次对应数字1、2、4、8、…,最多64个,因此在设置set值时可以通过数字的方式进行设置。
建议:
如果想要筛选出调查表中所有女同志的信息,那么直接在筛选时指明gender='女'
即可,因为enum类型的值只能多选一。如下:
但如果要筛选出调查表中爱好包含敲代码的人的信息就比较麻烦了,如果继续使用上述方式,那么最终筛选出来的是爱好仅为敲代码的人的信息。如下:
这时需要借助find_in_set(str,strlist)函数,该函数的作用是查询strlist中是否包含str,如果包含则返回str在strlist中的位置(从1开始),否则返回0。
通过select可以对find_in_set函数进行验证,依次查找集合a,b,c中是否包含字符a、b、d,这时在查找字符a和b时就会得到其在集合中的下标,而在查找字符d时就会得到0值。如下:
这时就可以通过select搭配find_in_set函数,来筛选出爱好包含打乒乓球的人的信息了。如下: