数据类型
1、数值数据类型
整数类型:tinyint、smallint、mediumint、int、bigint、
浮点数据类型:float、double
定点小数类型:decimal
2、日期/时间类型
year、time、date、datetime、timestamp
3、字符串类型
char、varchar、binary、varbinary、blob、test、enum、set
1.1 整数类型
默认存的是有符号数
类型名称 | 说明 | 存储需求 | 有符号数 | 无符号数 |
---|---|---|---|---|
tinyint | 很小的整数 | 一个字节 | -128~127 | 0~255 |
smallint | 小的整数 | 两个字节 | -2^15~2^15-1 | 0~2^16-1 |
mediumint | 中等大小的整数 | 三个字节 | -2^23~2^23-1 | 0~2^24-1 |
int | 普通大小的整数 | 四个字节 | -2^31~2^31-1 | 0~2^32-1 |
bigint | 大整数 | 八个字节 | -2^63~2^63-1 | 0~2^64-1 |
create table int_test(
a tinyint,
b smallint,
c mediumint,
d int,
e bigint
);
1.2 浮点数类型和定点数类型
类型名称 | 说明 | 存储需求 | 有符号数 | 无符号数 |
---|---|---|---|---|
float | 单精度浮点数 | 4个字节 | -3.402823466E+38~-1.175494351E-38 | 0和1.175494351E-38~3.402823466E+38 |
double | 双精度浮点数 | 8个字节 | -1.7976931348623157E+38~-2.2250738585072014E-308 | 0和-1.7976931348623157E-308~-2.2250738585072014E+308 |
decimal(M,D),dec | 压缩的‘严格’定点数 | M+2个字节 | 最大取值范围和double一样 |
//float(M,N)中M 称为精度,表示总共显示的位数,N称为标度,表示小数的位数
create table float_test(
a float,
b double,
c decimal,
a1 float(5,1),
b1 double(5,1),
c1 decimal(5,1)
);
//float和double会自动四舍五入,decimal也会四舍五入但会有一个warning,看下图
insert into float_test (a1,b1,c1) values(5.12,5.15,5.16);
1.3 日期与时间类型
类型名称 | 说明 | 存储需求 | 日期范围 |
---|---|---|---|
year | YYYY | 1个字节 | 1901~2155 |
time | HH:MM:SS | 3个字节 | -838:59:59~838:59:59 |
date | YYYY-MM-DD | 3个字节 | 1000-01-01~9999-12-31 |
datetime | YYYY-MM-DD HH:MM:SS | 8个字节 | 1000-01-01 00:00:00~9999-12-31 23:59:59 |
tiemstamp | YYYY-MM-DD HH:MM:SS | 4个字节 | 1970-01-01 00:00:01 UTC~ 2038-01-19 03:14:07 UTC |
1.3.1 year
- 以4为字符串或者4位数字格式表示year。输入格式为‘2018’或2018,数据库的数据均为2018。
- 以两位字符传串格式表示year,范围为‘00~’99’。’00’~’69’转化为2000~2069,’70’~’99’转化为1970~1999,’0’和’00’一样转化为2000。不在此范围内的,会出现超出范围异常:Out of range value for column ‘a’ at row 1。
- 以两位数字表示year,范围为1~99。1~69转化为2001~2069,70~99转化为1970~1999。0会转化为0000,其他不在范围的值会报超出范围异常:Out of range value for column ‘a’ at row 1。
1.3.2 time
- ‘D HH:MM:SS’格式的字符串。还可以使用下面任何一种“非严格”的语法:’HH:MM:SS’、’HH:MM’、’D HH:MM’、’D HH’、’SS’。这里的D表示日,可以去0~34之间的值,在插入数据库时,D会被转换为小时保存,格式为“D*24+HH”,在使用’D HH’时,HH一定要是两位的。
- ‘HHMMSS’格式的字符串或者数值,需要为有意义的时间,如果不合法会报错,不正常的时间值:Incorrect time value: ‘111299’ for column ‘b’ at row 1。
- now(),插入当前系统时间。
time不支持不严格的语法,不能像date和datetime一样,使用任何的标点符号作为时间部分的间隔符。
注意
如果没有冒号的格式,mysql解释时,会假定最右边的两位表示秒,1112和’1112’表示00:11:12,13或‘13’表示00:00:13。
如果使用冒号,‘11:12’表示11:12:00。
1.3.3 date
- 以‘YYYY-MM-DD’、’YYYYMMDD’和YYYYMMDD,形式均可保存。
- 以’YY-MM-DD’、’YYMMDD’和YYMMDD,其中00~69表示2000~2069,70~99表示1970~1999
- current_date和now(),插入当前系统日期。
不严格的语法,任何的标点符号都可以用作日期部分之间的间隔符。例如‘98-11-31’、‘98.11.31’、‘98/11/13’、‘98@11@13’、‘98-11@13’是等价的,都可以正确的插入到数据库中。
1.3.4 datetime
- 以’YYYY-MM-DD HH:MM:SS’、’YYYYMMDDHHMMSS’字符串格式输入
- 以’YY-MM-DD HH:MM:SS’、’YYMMDDHHMMSS’字符串格式输入,其中年份的表示同之前的year和date一样。
- 以YYMMDDHHMMSS、YYYYMMDDHHMMSS数字格式输入。
- 允许不严格的语法,任何标点符号都可以作为日期或时间部分之间的间隔符,同date一样
1.3.5 timestamp
- 输入格式和datetime一样
- 存储的字节和支持的范围不一样,见1.3的表格。
- 最大区别:datetime存入的数据与时区无关。如果在东八区存入10点的值,那么改动时区为东十区,则取出来还是10点。如果是timestamp,则取出来的是12点。
1.3.6 date、datetime、timestamp转换
- 如果为datetime、timestamp分配一个date值,则时间部分为设置为00:00:00。
- 如果为date分配一个datetime、timestamp值,则时间部分的值被删除。
1.4 文本字符串类型
- 数据字符编码:gbk,则一个字符占用2个字节,utf-8,则一个字符最多可占用3个字节
- 下表中的L 表示实际字符串的长度。
类型名称 | 说明 | 存储需求 |
---|---|---|
char(M) | 固定长度非二进制字符串 | (每个字符所占的字节数之和)字节,M个字符长度,一个中文也算一个字符,1<=M<=255,当超出范围,会报错不会自动转换 |
varchar(M) | 变长非二进制字符串 | (每个字符所占的字节数之和)+1字节,L<=M。M最大65535个字节,字符编码gbk时,M最大32766,utf-8时,21845 |
tinytext | 非常小的非二进制字符串 | (每个字符所占的字节数之和)+1字节,L<2^8 |
text | 小的非二进制字符串 | (每个字符所占的字节数之和)+2字节,L<2^16 |
mediumtext | 中等的非二进制字符串 | (每个字符所占的字节数之和)+3字节,L<2^24 |
longtext | 大的非二进制字符串 | (每个字符所占的字节数之和)+4字节,L<2^32 |
enum | 枚举类型,只能有一个枚举字符串值 | 1或2个字节,取决于枚举值的数目(最大65535(2^16-1)) |
set | 一个设置,一个字符串对象可以有零个或多个set成员 | 1、2、3、4或8个字节,取决于集合成员的数量(最多64个) |
1.4.1 char和varchar类型
- char(M)为固定长度的字符串,在定义时指定字符串的列长。当保存时在右侧填充空格以达到指定的长度。当select char值时,尾部的空格会被删除。所以当保存的数据末尾有空格时,在取值时,空格会没有。
- varchar(M)为长度可变的字符串,M表示可以存储的最长值,例如varchar(50),表示最长可以存储50个字符,如果实际插入的字符串只有10个字符,则数据库实际存储的为10个字符和一个字符串结束字符。varchar在值保存和检索时,尾部的空格仍保留。
1.4.2 text类型
当保存text类型的值时,不删除尾部空格。
1.4.3 enum类型
enum列总有一个默认值,如果将enum声明为null,null值则为该列的一个有效值,并且默认值为null。如果enum列被声明为not null,其默认值为允许的值列表的第一个元素。
1.4.4 set类型
- set是一个字符串对象,可以有零或多个值,最多可以有64个成员,其值为创建时规定的一列值。
- 以enum类型相同,set的值在内部用整数表示,列中的每一个值都有一个索引编号。当创建表时,set成员值尾部的空格将会自动被删除。也只能插入定义列中的值,如果set的值,存在重复会默认去重
- 与enum不同,enum字段只能从定义列值中选择一个值插入,set类型的列,可以从定义的列值中选择多个字符,中间以逗号“,”分割。
只能插入定义列中的值;插入的值存在重复会默认去重;如果插入时不是按顺序排列,则会自动按顺序插入;
1.5 二进制字符串类型
2 常见运算符
- 算数运算符,加减乘除,取余(模运算%)
- 比较运算符:大于,小于,等于,大于等于,小于等于,不等于,in,between and,is null ,greatest,least,like,regexp等
- 逻辑运算符:逻辑非(not或!),逻辑与(and 或者 &&),逻辑或(or 或 ||),逻辑异或(xor)
- 位操作运算,位操作运算参与运算的操作数按二进制位进行运算。位与(&),位或(|),位非(~),位异或(^),左移(<<),右移(>>)。
2.1 算数运算符
- 整数相除保留四位有效数字
- 被除数为0,则返回null
2.2 比较运算符
2.2.1 等于(=)
- 若有一个或两个参数为null,则比较运算的结果为null
- 若同一个比较运算中的两个参数都是字符串,则按照字符串进行比较(Windows下不区分大小写,在字段前使用binary关键字)
- 若两个参数均为整数,则按整数进行比较
- 若一个字符串和一个整数进行比较,则mysql自动将字符串转换成数字,不能讲非数字型字符串转化成数字比较
2.2.2 安全等于(<=>)
和等于唯一的区别:<=>可以对null进行判断,两个都为null返回true,一个不为null返回false。
2.2.3 不等于(<>,!=),小于等于,小于,大于等于,大于
不能用于判断空值null。与null比较返回null。
2.2.4 is null(isnull),in not null
判断是否为null。
2.2.5 between and
可以比较数字也可以比较字符串
2.2.6 least
比较多列,取最小值
如果有null,则返回null
2.2.7 greatest
比较多列,取最大值
如果有null,则返回null
2.2.8 in,not in
不能用来判断null。
2.2.9 like
- % 匹配任何数目的字符,包括零个字符
- _ 只能匹配一个字符
- 变量或匹配条件为null,则返回null。
2.2.10 regexp
使用正则表达式匹配
变量或匹配条件为null,则返回null。
2.3 逻辑运算符
所有逻辑运算符的求值所得的结果均为true,false,null。
2.3.1 not,!
如果操作数为null,则返回null
2.3.2 and,&&,or,||,xor
如果操作数含有null,则返回null
a xor b 等同于 (a and (not b)),((not a) and b)