目录
数值数据类型:包括整数类型tinyint、smallint、mediumint、int、bigint,浮点小数数据类型float 和 double, 定点小数类型 decimal。
日期/时间类型:包括year、time、date、datetime 和 timestamp。
字符串类型:包括char、varchar、binary、varbinary、blob、text、enum 和 set 等等。 字符串类型又分为文本字符串和二进制字符串。
1.整数类型
显示宽度问题:第一,显示宽度与数据的取值范围没有任何关系,比如创建表,id int(6),这表明id列的数据类型是int,取值范围还是上表中所示,还是占用4个字节,那个6只是显示宽度而已,仅仅用于显示数据的宽度。
假设显示宽度为M,如果没有声明了zerofill,那么显示宽度什么作用也没有,如果声明了zerofill,会默认将该列设置为无符号的,当存入的数的位数大于M时,原样存入(没影响),当存入的数的位数小于M时,左边补充0扩展到M位,然后存入数据库。
举几个例子说明一下:
id int(6) zerofill,这么声明一个id列的话,zerofill会默认将id列设置为无符号的,另外,如果存入一个413,数据库里会存000413,
如果存入23456813,数据库里会存23456813。
2.浮点数类型和定点数类型
浮点数类型就是小数部分的位数不固定,比如1.2,1.245,23.467。如果浮点类型指定了M 和 N,那和定点类型没区别。
定点数类型就是小数部分的位数是固定的,比如1.20,99.00,2345.45。 小数部分位数是可以指定的。
精度要求高的话,还是用定点数,因为浮点数类型有取值范围,长度有限,当两个大浮点数相乘的时候,很容易超出范围,进而丢失超出的数据,失去精度,但是定点数类型可以任意指定长度,精度更高。
3. 日期与时间类型
每个类型都有合法的取值范围,如果插入不合法的数据,就会用0代替。
总结一句话,每种类型的格式太多,太混乱,有很多简写格式(转换规则),如果你写的简写格式需要转换的话,数据库会尽量替你解析你写的内容,如果解析不出来,那就直接为0。个人认为不要那么花里胡哨的,使用最标准的日期格式一定不会错,而且清晰易读。
1. year类型
执行插入或者修改SQL的时候,4位字符串或者4位数字的年值都行,比如'2020' 和 2020 都可以。
用2位字符串表示年值时,字符范围是'00'~'99',其中'00'~'69'会转换为2000~2069,'70'~'99'会转换成1970~1999。
用2位数字表示年值时,范围是1~99,其中1~69会转换成2001~2069,70~99会转换成1970~1999。
不管是用4位,还是2位来表示,只要超过了取值范围,就被转换成0000。
2. time类型
3. Date类型
完整的 'YYYY-MM-DD' 或 'YYYYMMDD' 格式表示的话,只能用字符串格式,不能用数字(因为用YYYY-MM-DD会直接存为0000-00-00)。
用 'YY-MM-DD'、'YYMMDD'、YY-MM-DD 或 YYMMDD 表示的话,YY部分按照 00~69 转成 2000~2069, 70~99 转成 1970~1999。
不管哪种格式,超过范围就会被0000-00-00代替。
注意:这种标点符号间隔的方法只能在 ' ' 字符串的格式中有用,间隔符可以是不同的,比如 “98@11&31”,这样也行。
4. DateTime类型
'YYYY-MM-DD HH:MM:SS' 、'YYYYMMDDHHMMSS'、'YY-MM-DD HH:MM:SS'、'YYMMDDHHMMSS'、YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 。6种格式。
5. TimeStamp类型
此类型和datetime类型差不多,唯一区别就是实际存储的是世界标准时间。即存储时,换算成当前时区的时间存储,当读取时,读取到的时间也是转换成当前时区的时间,即一次存储,全球使用,任何地方的人读到的时间都是对应各自系统里的时区的时间。
可以设置时区。
show variables like ‘%time_zone%’; 查看时区。
1. 在线修改
set time_zone = ‘时区’; 设置当前会话时区。
set global time_zone = '时区'; ##修改mysql全局时区为北京时间,即我们所在的东8区
flush privileges; #立即生效
2. 配置文件中永久修改
在my.cnf配置文件中,设置默认时区
default-time_zone = '时区'
4.文本字符串类型
4.1 char类型
char(M) 表示固定长度存储字符串,比如char(5);存入'abc'时,在数据库里的值就是'abc ',后面两个空格,当读取的时候,得到的是'abc',后面没有空格。存入'abc '时,后面有2个空格,但是数据库里存入的确实'abc',因为char会自动先删除后面的空格。 如果存入的字符串长度超过M,会自动截断,然后再存入数据库。
char的处理速度比varchar块,就是空间换时间。
4.2 varchar类型
varchar(M)表示可变长度存储字符串,比如varchar(5),存入'abc'时,在数据库里存的就是'abc'+结束符,结束符占1个字节,存入'abc '时,数据库里的也是'abc ',连空格也要存。 如果存入的字符串长度超过M,会自动截断,然后再存入数据库。
4.3 text类型
各种长度的text类型,他们不可以自定义地去指定最大存储长度,因为他们都是有默认最大存储长度的,功能上类似varchar,存入什么字符串,数据库里就存什么,不删出尾部的空格,长度就是多少+相应的字节。
4.4 enum 类型
enum枚举类型,在create创建表的时候就已经声明好了枚举类型列,声明方式: 字段名 enum('值1','值2',...,'值n'), 枚举值最多可以有65535个,每一个枚举值都有一个索引值,从1开始,枚举类型的列中存储的是一个枚举值的索引值,索引值为0的枚举值为空字符串'',索引值为null的枚举值为null。如果不指明索引值的话,默认就是索引值为1的元素,如果枚举类型声明为null,那默认值就是null。
注意:enum也是用于存储字符串的。
4.5 set 类型
set类型,类似enum类型,声明方式: 字段名 set('值1','值2',...,'值n');最多可以有64个值,每个值都有索引,和枚举一样,但是set类型存的时候可以存储其中的一个或多个值,而枚举类型是只能存1个。比如定义{"a","g","t"},那么可以插入{“a”}、{“a,g”}等等。当多个值的的时候,值的顺序可以混乱,因为数据库在存的时候会按照定义的顺序给它存储顺了;还有,当插入的数据有重复值时,数据库会自动去重,当插入的数据有不属于声明时的集合时,数据库会忽略掉这些集合之外的值。
本人亲自测试过,插入set类型值时,不能使用索引值。
5. 二进制字符串类型
5.1 bit 类型
bit类型,以二进制方式存储,bit(n),n为位数,1到64,即最多存储64位二进制字符串,如果没有指定n,默认为1。 以固定长度存储二进制字符串,如果插入的值不够n位,那就左边填充0。如果插入的值超过n位,会报错。
5.2 binary 类型 和 varbinary 类型
binary(M) 类型以固定长度M存储二进制字节字符串,M表示的是M个字节,而不是M个字符,不足M长度时,在末尾添加 '\0' 补充长度为M。读出时,是M个字节,包括 '\0',不会删除或者丢失任何一个字节。
varbinary(M) 类型以变长存储二进制字节字符串,M表示最大字节。存的是什么就是什么。
这两个都是按照字节来存的,没有字符集,比较也是按照字节来比较。
5.3 blob 类型
类似于 text,只是blob类型系列存的是二进制字符串(字节字符串)。
varbinary 类型 binary类型 和 char类型 varchar类型暂时还没有弄清楚区别。blob类型也没有弄清楚。