目录
1 MySQL支持的数据类型
MySQL提供了多种数据类型,主要包括数值类型、字符串类型和时间类型。下面分别介绍:
1.1 数值类型
整数类型 | 字节 | 最小值 | 最大值 |
TINYINT | 1 | 有符号-128 无符号0 | 有符号127 无符号255 |
SMALLINT | 2 | 有符号-32768 无符号0 | 有符号32767 无符号65535 |
MEDIUMINT | 3 | ||
INT/INTEGER | 4 | ||
BIGINT | 8 | ||
浮点数类型 | 字节 | 最小值 | 最大值 |
FLOAT | 4 | ||
DOUBLE | 8 | ||
定点数类型 | 字节 | 描述 | |
DEC(M,D) DECIMAL(M,D) | M+2 | 最大取值范围与DOUBLE相同,给定DECIMAL的有效取值范围由M和D决定。 | |
位类型 | 字节 | 最小值 | 最大值 |
BIT(M) | 1~8 | BIT(1) | BIT(8) |
对于整数类型,int(5)表示当数值宽度小于5位的时候在数字前面填满宽度,如果不显示指定宽度则默认int(11)。还可以配合zerofill使用,就是在数字位数不够的时候用“0”填充。如果插入大于宽度限制的值,不会出现截断或报错,会按照类型的实际精度进行保存。
整数类型有一个可选属性:UNSIGNED(无符号),如果需要在字段里面保存非负数或者需要较大的上限值时,可选择该属性,它的取值范围是正常值的下限取0,上限取原值的2倍。如果一个列指定zerofill,则MySQL自动为该列添加UNSIGNED属性。还有一个AUTO_INCREMENT属性,这里不做展开。
对于小数类型,表示分为浮点数(float/double)和定点数(decimal),定点数在MySQL内部已字符串形式存放,比浮点数更精确,适合用来表示货币等精度高的数据。定点数可以用类型名称后加(M,D)的方式来表示。“(M,D)”表示该值一共显示M位数字(整数位+小数位),其中D位位于小数点后面,M和D又被称为精度和标度。
浮点数如果不写精度和标度,则会按照实际精度值显示,如果有精度和标度,超过则按四舍五入插入,系统不会报错;定点数如果不写精度和标度,则按照默认值decimal(10,0)进行操作,如果数据超过了精度和标度值,系统会报错。
1.2 日期时间类型
日期和时间类型 | 字节 | 最小值 | 最大值 | 备注 |
DATE | 4 | 1000-01-01 | 9999-12-31 | 表示年月日 |
DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | 表示年月日时分秒 |
TIMESTAMP | 4 | 19700101080001 | 2038年的某个时刻 | |
TIME | 3 | -838:59:59 | 838:59:59 | 表示时分秒 |
YEAR | 1 | 1901 | 2155 |
如果需要经常插入或更新日期为当前系统时间,则通常使用TIMESTAMP表示,改值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串,显示宽度固定为19个字符。如果想要获得数组的值,应在该列添加“+0”。和时区有关。
1.3 字符串类型
字符串类型 | 字节 | 描述及存储需求 |
CHAR(M) | M | M为0~255之间的整数 |
VARCHAR(M) | M为0~65535之间的整数,值的长度+1个字节 | |
TINYBLOB | 允许长度0~255字节,值的长度+1个字节 | |
BLOB | 允许长度0~65535字节,值的长度+2个字节 | |
MEDIUMBLOB | 值的长度+3个字节 | |
LONGBLOB | 值的长度+4个字节 | |
TINYTEXT | 允许长度0~255字节,值的长度+2个字节 | |
TEXT | 允许长度0~65535字节,值的长度+2个字节 | |
MEDIUMTEXT | 值的长度+3个字节 | |
LONGTEXT | 值的长度+4个字节 | |
VARBINARY(M) | 允许长度0~M个字节的变长字节字符串,值的长度+1个字节 | |
BINARY(M) | M | 允许长度0~M个字节的定长字节字符串 |
1.3.1 CHAR和VARCHAR类型
CHAR列的长度固定为创建表示声明的长度,而VARCHAR列值为可变长字符串。在检索的时候,CHAR列删除了尾部的空格,而VARCHAR则保留这些空格。
1.3.2 BINARY和VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是它们包含二进制字符串而不包含非二进制字符串。
1.3.3 ENUM类型
ENUM即枚举类型,它的值范围需要在创建表时通过枚举方式显示指定,对1~255个成员的枚举需要1个字节存储;否则需要2个字节存储,最多65535个成员。枚举类型忽略大小写的,并且只允许从值集合中选取单个值,而不能一次取多个值。
1.3.4 SET类型
SET和ENUM类型非常类似,也是一个字符串对象,里面包含0~64个成员,存储上所占字节也有所差异。SET类型可以一次取多个成员。
2 MySQL中的运算符
MySQL中支持的运算符主要包括:算术运算符、比较运算符、逻辑运算符和位运算符。下面分别介绍:
2.1 算数运算符
运算符 | 作用 |
+ | 加法 |
- | 减法 |
* | 乘法 |
/,DIV | 除法,返回商 |
%,MOD | 除法,返回余数 |
2.2 比较运算符
运算符 | 作用 |
= | 等于 |
<>或!= | 不等于 |
<=> | NULL安全的等于(NULL-safe) |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 存在于指定范围 |
IN | 存在于指定集合 |
IS NULL | 为NULL |
IS NOT NULL | 不为NULL |
LIKE | 通配符匹配 |
REGEXP或RLIKE | 正则表达式匹配 |
2.3 逻辑运算符
运算符 | 作用 |
NOT或! | 逻辑非 |
AND或&& | 逻辑与 |
OR或|| | 逻辑或 |
XOR | 逻辑异或 |
2.4 位运算符
运算符 | 作用 |
& | 位与 |
| | 位或 |
^ | 位异或 |
~ | 位取反 |
>> | 位右移 |
<< | 位左移 |
3 常用函数
3.1 字符串函数
函数 | 功能 |
CONCAT(S1,S2,...,Sn) | 连接S1,S2,...,Sn为一个字符串;示例:SELECT t.vc_fundCode,CONCAT('INSERT INTO fact_fund_hlddetail_',CRC32(t.vc_fundCode)%100,' SELECT * FROM fact_fund_hlddetail WHERE vc_fundCode=''',t.vc_fundCode,''';') FROM (SELECT DISTINCT vc_fundCode FROM fact_fund_hlddetail) t |
INSERT(str,x,y,instr) | 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr |
LOWER(str) | 将字符串str中所有字符变为小写 |
UPPER(str) | 将字符串str中所有字符变为大写 |
LEFT(str,x) | 返回字符串str最左边的x字符 |
RIGHT(str,x) | 返回字符串str最右边的x字符 |
LPAD(str,n,pad) | 用字符串pad对str最左边进行填充,直到长度为n个字符长度 |
RPAD(str,n,pad) | 用字符串pad对str最右边进行填充,直到长度为n个字符长度 |
LTRIM(str) | 去掉字符串str左侧的空格 |
RTRIM(str) | 去掉字符串str行尾的空格 |
REPEAT(str,x) | 返回str重复x次的结果 |
REPLACE(str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1,s2) | 比较字符串s1和s2 ASCII码 -1小 0相等 1大 |
TRIM(str) | 去掉字符串行尾和行头的空格 |
SUBSTRING(str,x,y) | 返回从字符串str x位置起y个字符长度的字串 |
3.2 数值函数
函数 | 功能 |
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回小于x的最大整数值 |
MOD(x,y) | 返回x/y的模 |
RAND() | 返回0~1内的随机值 |
ROUND(x,y) | 返回参数x的四舍五入的有y位小数的值 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
3.3 日期和时间函数
函数 | 功能 |
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前的日期和时间 |
UNIX_TIMESTAMP(date) | 返回日期date的UNIX时间戳 |
FROM_UNIXTIME | 返回UNIX时间戳的日期值 |
WEEK(date) | 返回日期date为一年中的第几周 |
YEAR(date) | 返回日期date的年份 |
HOUR(time) | 返回time的小时值 |
MINUTE(time) | 返回time的分钟值 |
MONTHNAME(date) | 返回date的月份名 |
DATE_FORMAT(date,fmt) | 返回按字符串fmt格式化日期date值 |
DATE_ADD(date,INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr,expr2) | 返回起始时间expr和结束时间expr2之间的天数 |
3.4 流程函数
函数 | 功能 |
IF(value,t f) | 如果value是真,返回t;否则返回f |
IFNULL(value1,value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [value1] THEN [result1] ...ELSE [default] END | 如果value1是真,返回result1,否则返回default |
CASE [expr] WHEN [value1] THEN[result1]...ELSE[default] END | 如果expr等于value1,返回result1,否则返回default |
3.5 其他常用函数
函数 | 功能 |
DATABASE() | 返回当前数据库名 |
VERSION() | 返回当前数据库版本 |
USER() | 返回当前登录用户名 |
INET_ATON(IP) | 返回IP地址的数字表示 |
INET_NTOA(num) | 返回数字代表的IP地址 |
PASSWORD(str) | 返回字符串str的加密版本 |
MD5() | 返回字符串str的MD5值 |