MySQL(数据类型)

目录

1. 数值类型

2. bit类型

3.小数类型

3. 字符串类型

4 日期和时间类型

5. enum和set

---

1. 数值类型

对标C语言：

tinyint->char(1字节)：       有符号：127 ~ 255                             无符号：0 ~ -128。

smallint->shot int(2字节)：有符号：32767 ~ 65535                     无符号：0 ~ -32768。

mediumint(3字节)              有符号：8388607 ~ 16777215           无符号：0 ~ -8388608。

int(4字节)                           有符号：2147483647 ~ 4294967295 无符号：0 ~ -2147483648。

bigint->long long(8字节)    有符号：9223372036854775807 ~ 18446744073709551615 无符号：0 ~ -9223372036854775808。

插入数值必须在指定的类型范围之类，否则插入失败。反之插入到MySQL中的数据一定是合法的，也就是一种约束(使用者)。

在语言中把一个超过该类型范围的值赋给该类型，可能会产生截断或者类型转换，在MySQL里直接拦截。

1. 下面用 tinyint 测试数值越界情况

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

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

无符号案例：

2. bit类型

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

3.小数类型

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

示例：float(4.2)

小数位数不足补0，超过小数位数按四舍五入进位，99.996 进位成 100.00，超过总长度(d)，则插入失败。

下面float长度为默认的

插入较长的值会导致精度丢失。

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

decimal整数最大位数m为65。支持小数最大位数d是30。如果d被省略，默认为0.如果m被省略， 默认是10。

decimal(5,2) 表示的范围是 -999.99 ~ 999.99

decimal(5,2) unsigned 表示的范围 0 ~ 999.99

decimal 和 float 很像，但是有区别 : float和 decimal 表示的精度不一样。

3. 字符串类型

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

在Innodb一个汉字占3个字节，但插入的数据明显超过3个字节，还能插入进去，在MySQL中只有字符的概念，比如'a',1 2 3 4,一个汉字,都称为1个字符，虽然汉字占3个字节，但被称为一个字符，所以能插入2个字符，超过2字符就插入失败。

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

varchar最大长度为65535(包括用于记录实际分配的大小1~3字节)个字节，并不是字符，在编码为utf8mb4最大只能开辟16383个字符。

varchar 长度可以指定为 0 到 65535 之间的值，但是有 1 - 3 个字节用于记录数据大小，所以说有效字

节数是 65532 。

关于 varchar(len),len 到底是多大，这个 len 值，和表的编码密切相关：

当我们的表的编码是utf8mb4时， varchar(n) 的参数 n 最大值是 65532/4=16383[因为utf8mb4中，一个字符占用4个字节 ]，如果编码是utf8，varchar(n) 的参数 n 最大是 65532/3=21845（因为utf8中，一个字符占用3字节）。

3. char和varchar的区别

char类似c语言开辟了一段定长大小的空间，varchar大小则是变长的。

比如 char和varchar的大小都为20，后续不管插入多少个字符，char都只给你20个，varchar则会根据你插入了多少个字符而分配多少个字符。

utf8为例：

如何选择定长或变长字符串？

1. 如果数据确定长度都一样，就使用定长（ char ），比如：身份证，手机号， md5

2. 如果数据长度有变化 , 就使用 varchar，  比如：名字，地址，但是你要保证最长的能存的进去。

3. 定长的磁盘空间比较浪费，但是效率高。

4. 变长的磁盘空间比较节省，但是效率低。

5. 定长的意义是，直接开辟好对应的空间。

6. 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

4 日期和时间类型

常用的日期有如下三个：

1. date：           日期 'yyyy - mm - dd' (年月日) ，占用三字节。

2. datetime：    时间日期格式 'yyyy - mm -dd HH:ii:ss' (年月日+时分秒) 表示范围从 1000 到 9999 ，占用八字节。

3. 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')

timestamp不需要手动插入，默认会用当前的时间戳缺省，会随之时间自动变换。

比如评论时会自动显示当前时间，过了一段时间又会更新。

5. enum和set

enum：枚举，“单选”类型； enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535

个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型；set('选项值1','选项值2','选项值3', ...)

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

创建表：

create table test8( name varchar(20), age enum('男','女'), hobby set('足球','看电影','代码','看书','旅游') );

插入数据：

enum可以用定义时的字面值，也可以用枚举常量(1 ~ 65535)，只能选择其中一个。

set可以用定义时的字面值，可以选择多个，但常量代表对应的二进制位数(1~64)，比如3-> 11，也就是前2个数据，1就是1，也就是前1个数据。

set的查询

这种写法是严格匹配的，并不会把自己别的爱好也显示出来。

find_in_set函数

find_in_set函数的返回结果如果是非0就代表真，也是集合对应的下标(从1开始)

这样就能显示包含该查询数据，也能包含自己其他的数据。