MySql支持多种数据类型,主要有数值类型、日期/时间类型和字符串类型。
一、MySql数据类型介绍
- 数值数据类型:整数型tinyint、smallint、mediumint、int、bigint;浮点数类型float、double;定点小数类型decimal。
- 日期/时间类型:year、time、date、datetime、timestamp。
- 字符串类型:char、varchar、binary、varbinary、blob、text、enum、set。
1、整数类型
整数类型用来存储数字,可以添加auto_increment自增约束条件。
1个字节(8bits)来存储,那么tinyint无符号数的最大值为2的8次方减一,也就是255。
类型名称 | 说明 | 存储需求 | 有符号 | 无符号 |
tinyint | 很小的整数 | 1个字节 | -128~127 | 0~255 |
smallint | 小的整数 | 2个字节 | -32768~32767 | 0~65535 |
mediumint | 中等大小的整数 | 3个字节 | -8388608~8388607 | 0~16777215 |
int(Integer) | 普通大小的整数 | 4个字节 | -2147483648~2147483647 | 0~4294967295 |
bigint | 大整数 | 8个字节 | -9223372036854775808~9223372036854775807 | 0~18446744073709551615 |
假设创建数据库时声明一个int类型的字段:year int(11),该声明指明,在year字段中的数据一般只显示4位数字的宽度。
注意:显示宽度和数据类型的取值范围无关。显示宽度只是指明MySql最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充;如果出入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入而且能显示出来。例如向year里面插入19999,select时还是会显示19999。
如果创建数据库的时候没有指定数据类型的宽度,MySql会默认给它们最大的宽度,要注意符号也算进宽度中。
2、浮点数类型和定点数类型
MySql使用浮点数类型和定点数类型来标识小数,浮点:float和double,定点:decimal;它们都可以用(M,N)来表示,其中M称为精度,表示总共的位数,N称为标度,表示小数的位数。
类型名称 | 说明 | 存储需求 | 有符号 | 无符号 |
float | 单精度浮点数 | 4个字节 | -3.402823466E+38~-1.175494351E-38 | 0和1.175494351E-38~3.402823466E+38 |
double | 双精度浮点数 | 8个字节 | -1.7976931348623157E+308~-2.2250738585072014E-308 | 0和2.2250738585072014E-308~1.7976931348623157E+308 |
decimal(M,D),DEC | 压缩的“严格”定点数 | M+2个字节 | -------------------------- | -------------------------- |
decimal的存储空间并不是固定的,而由其精度值M决定,占用M+2个字节。
这三个数据类型在用户指定精度之后,超出精度范围的都会进行四舍五入处理。
3、日期与时间类型
MySql中有多种表示日期的数据类型,如果插入不合法的值时系统将会将“零”值插入到数据库中。
类型名称 | 日期格式 | 日期范围 | 存储需求 |
year | YYYY | 1901~2155 | 1字节 |
time | HH:MM:SS | -838:59:59~838:59:59 | 3字节 |
date | YYYY-MM-DD | 1000-01-01~9999-12-31 | 3字节 |
datetime | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 8字节 |
timestamp | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:00 UTC~2038-01-19 03:14:07 UTC | 4字节 |
- year类型用来表示年。以四位字符串或者四位数字表示时,输入格式为YYYY或者‘YYYY’,范围是1901~2155,如输入‘2010’和2010,插入到数据库都是2010;以两位字符串或者两位数字表示year,范围是00~99,其中00~69和70~99分别表示2000~2069和1970~1999,这里如果直接输入0,会被转化成0000,而不是2000,输入‘0’会被转换成2000。例:‘2166’保存在year会报错,‘0’、‘00’、‘77’、‘10’分别会转换成2000、2000、1977、2010;0、78、11分别会转换成0000、1978、2011.
- time类型用于只需要时间信息的值。格式为“HH:MM:SS”,分别表示小时、分钟、秒。取值范围是-838:59:59~838:59:59,小时范围会如此大的原因是:time类型不仅可以表示一天的时间,还可能是某个事件过去的时间或者两个事件之间的时间间隔。‘D HH:MM:SS’也可以使用 ‘HH:MM:SS’、‘HH:MM’、‘D HH:MM’、‘D HH’或‘SS’。这里的D表示日,可取0~34之间的值,在插入数据库时2,D会转换为小时保存,格式是:“D*24+HH”。时间也可以使用‘HHMMSS’表示,但是小时分钟秒均不能超过他们的范围。例:‘10:05:05’、‘23:23’、‘2 10:10’、‘3 02’、‘10’分别会转换成10:05:05、23:23:23、58:10:00、74;00:00、00:00:10;‘101112’、111213,‘0’、107010分别会转换成10:11:12、11:12:13、00:00:00、报错。注意:time简写时,没有冒号,如‘1122’和1122会将最右边的两位视为秒,会转换成00:11:22;而有冒号时不一样,如‘11:12’会被转化成11:12:00。使用current_time和now()均可获得系统当前时间。
- date类型,用在仅需要日期值时,格式‘YYYY-MM-DD’,可以使用字符串或者数字类型插入,只要符合格式就行。可以使用‘YYYY-MM-DD’或者‘YYYYMMDD’;也可使用‘YY-MM-DD’或者‘YYMMDD’或YY-MM-DD和YYMMDD,‘00’~‘69’和‘70’~‘99’分别表示2000~2069和1970~1999;使用current_date和now()可以获得当前日期。
- datetime类型同时包含日期和时间。格式为:‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDD HHMMSS’;也可以使用‘YY-MM-DD HH:MM:SS’或者‘YYMMDD HHMMSS’,YY表示年份,规则和date类型一样;可以使用now()获取当前时间。
- timestamp小时格式和datetime一样,只是timestamp的取值范围小一些
4、字符串类型
字符串类型用来存储字符串数据,除此之外还可以存储其它数据,如图片和声音的二进制数据。
类型名称 | 说明 | 存储需求 |
char(M) | 固定长度非二进制字符串 | M字节,1<=M<=255 |
varchar(M) | 变长非二进制字符串 | L+1字节,在此L<=M和1<=M<=255 |
tinytext | 非常小的非二进制字符串 | L+1字节,在此 L 小于2的8次方 |
text | 小的非二进制字符串 | L+2字节,在此 L 小于2的16次方 |
mediumtext | 中等大小的非二进制字符串 | L+3字节,在此 L 小于2的24次方 |
longtext | 大的非二进制字符串 | L+4字节,在此 L 小于2的32次方 |
enum | 枚举类型,只能有一个枚举字符串值 | 1或2个字节,取决于枚举值的数目(最大值65535) |
set | 一个设置,字符串对象可以有0个或多个set成员 | 1、2、3、4或8个字节,取决于集合成员的数量(最多64个成员) |
这里面有些数据类型很少用到,常用的就是varchar。
5、二进制类型
类型名称 | 说明 | 存储需求 |
bit(M) | 位字段类型 | 大约(M+7)/8个字节 |
binary(M) | 固定长度的二进制字符串 | M个字节 |
varbinary(M) | 可变长度的二进制字符串 | M+1个字节 |
tinyblob(M) | 非常小的blob | L+1个字节,在此 L<2的8次方 |
blob(M) | 小的blob | L+2个字节,在此 L<2的16次方 |
mediumblob(M) | 中等大小的blob | L+3个字节,在此 L<2的24次方 |
longblob(M) | 非常大的blob | L+4个字节,在此 L<2的32次方 |
二、数据类型的选择
1、整数和浮点数
不需要小数部分,自然使用整数类型;需要小数就是用浮点数类型。精度要求高使用double,不高使用float。
2、浮点数和定点数
浮点数容易产生误差,对精度要求很高时使用decimal,decimal可用于定义货币等要求精度高的字段,要进行数值比较,最好使用decimal。
3、日期与时间类型
按照要求使用即可,比如只取年份时使用year,只记录时间用time就行了。
4、char和varchar
按需求使用即可。
5、enum和set
enum只能去单值,它的数据列表是一个枚举集合。set可取多值,它的合法值列表最多允许有64个成员。
6、blob和text
blob是二进制字符串,text是非二进制字符串,两者均可存放大容量的信息,blob主要存储图片、音频信息;text只能存储纯文本。
三、常见运算符的介绍
1、算数运算符
运算符 | 作用 |
+ | 加法运算 |
- | 减法运算 |
* | 乘法运算 |
/ | 除法运算,返回商 |
% | 取模运算,返回余数 |
2、比较运算符
运算符 | 作用 |
= | 等于 |
<=> | 安全的等于 |
<>(!=) | 不等于 |
<= | 小于等于 |
>= | 大于等于 |
> | 大于 |
IS NULL | 判断一个值是否为NULL |
IS NOT NULL | 判断一个值是否不为NULL |
LEAST 语法:LEAST(值1,值2,...值n) | 在有两个或多个参数时,取最小值 |
GREATEST 语法:GREATEST (值1,值2,..值n) | 在有两个或多个参数时,取最大值 |
between and 语法:expr between min and max | 判断一个值是否落在两个值之间,min<=expr<=max |
ISNULL | 与IS NULL相同的功能 |
IN | 判断一个值是否是IN列表中的任意一个值 |
NOT IN | 判断一个值不是IN列表中的任意一个值 |
LIKE | 通配符匹配(“%”匹配任何数目的字符,甚至包括零字符;“_”匹配一个字符) |
REGEXP | 正则表达式匹配 |
正则匹配符:
- “^”匹配以该字符后面的字符开头的字符串
- “$”匹配以该字符前面的字符结尾的字符串
- “.”匹配任何一个单字符
- “[...]”匹配在方括号内的任何字符
- “*”匹配零个或多个在它前面的字符
3、逻辑运算符
运算符 | 作用 |
NOT或者! | 逻辑非 |
AND或者&& | 逻辑与 |
OR或者|| | 逻辑或 |
XOR | 逻辑异或(不同返回1,相同返回0) |
4、位运算符
运算符 | 作用 |
| | 位或 |
& | 位与 |
^ | 位异或(不同返回1,相同返回0) |
<< | 位左移(例:expr<<n,这里n表示expr要移位的位数) |
>> | 位右移(例:expr>>n,这里n表示expr要移位的位数) |
~ | 位取反(注意:可以使用bin()查看1取反的结果:bin(~1)=111111111111111111111111111111110) |
5、运算符的优先级
优先级 | 运算符 |
最低 | =(赋值运算),:= |
||、OR | |
XOR | |
&&、AND | |
NOT | |
BETWEEN、CASE、WHEN、THEN、ELSE | |
=(比较运算)、<=>、>=、<=、>、<、<>、!=、IS、LIKE、REGEXP、IN | |
| | |
& | |
<<、>> | |
-、+ | |
*、/(DIV)、%(MOD) | |
^ | |
-(负号)、~(位反转) | |
最高 | ! |