MySQL数据类型与运算-优快云博客

1. 数据类型

1.1 整数类型

整数类型一共有 5 种，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT。

具体的取值范围如下:

整数类型	字节	有符号数取值范围	无符号数取值范围
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

TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

1.2 日期与时间类型

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

1.2.1 YEAR类型

上表中的最大最小值表示的是四位字符串或者数字表示的YEAR，
以2位字符串格式表示YEAR类型，最小值为00，最大值为99。

当取值为01到69时，表示2001到2069；
当取值为70到99时，表示1970到1999；
当取值整数的0或00添加的话，那么是0000年；
当取值是日期/字符串的’0’添加的话，是2000年。

从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。

1.2.2 DATE类型

表示日期，格式为YYYY-MM-DD
向DATE类型的字段插入数据时：

以 YYYY-MM-DD 格式或者 YYYYMMDD 格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
以 YY-MM-DD 格式或者 YYMMDD 格式表示的字符串日期，此格式中，年份为两位数值或字符串满足YEAR类型的格式条件为：当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
使用 CURRENT_DATE() 或者 NOW() 函数，会插入当前系统的日期。

1.2.3 TIME类型

TIME类型用来表示时间，可以使用“HH:MM:SS”格式来表示TIME类型
向TIME类型的字段插入数据时：

可以使用带有冒号的字符串，比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM’、 'D HH:MM ‘、’ D HH ‘或’ SS '格式，都能被正确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。
当使用带有冒号并且不带D的字符串表示时间时，表示当天的时间，比如12:10表示12:10:00
可以使用不带有冒号的字符串或者数字，格式为’ HHMMSS '或者 HHMMSS 。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10
如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。
使用 CURRENT_TIME() 或者 NOW() ，会插入当前系统的时间。

1.2.4 DATETIME类型

以 YYYY-MM-DD HH:MM:SS 格式或者 YYYYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
以 YY-MM-DD HH:MM:SS 格式或者 YYMMDDHHMMSS 格式的字符串插入DATETIME类型的字段时，两位数的年份规则符合YEAR类型的规则，00到69表示2000到2069；70到99表示1970到1999。
使用函数 CURRENT_TIMESTAMP() 和 NOW() ，可以向DATETIME类型的字段插入系统的当前日期和时间。

1.2.5 TIMESTAMP 类型

TIMESTAMP类型也可以表示日期时间，显示格式为： YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫
作世界标准时间。

存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

DATETIME 类型保存了完整的日期和时间，且取值范围较大，因此建议在开发时使用DATETIME类型，但是一般注册时间等建议使用TIMESTAMP时间戳类型，便于计算

1.3 文本类型

MySQL中，文本字符串总体上分为 CHAR 、 VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、 LONGTEXT 、 ENUM 、 SET 等类型。

1.3.1 CHAR类型

CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数。

1.3.2 VARCHAR类型

VARCHAR(M) 定义时，必须指定长度M，否则报错。
MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）；MySQL5.0版本以上，varchar(20)：指的是20字符。
检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

varchar 创建时长度限制

char 与 varchar的选择

存储很短的信息建议使用char,因为varchar还需要一个字节存储字符串的实际长度
固定长度的，建议使用char类型
对于列频繁改变的场景，建议使用char,因为使用varchar每次存储需要额外的长度计算
具体存储引擎相关：
· MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。
· MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
· InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

1.4 TEXT类型

TEXT用来保存文本类型的字符串，总共包含4种类型，分别为TINYTEXT、TEXT、
MEDIUMTEXT 和 LONGTEXT 类型。

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度

1.5 二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。

MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB类型。

1.6 JSON 类型

JSON是一种轻量级的数据交换格式，JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

通过“->”和“->>”符号，从JSON字段中正确查询出了指定的JSON数据的值。
在这里插入图片描述

阿里巴巴《Java开发手册》之MySQL数据库：

任何字段如果为非负数，必须是 UNSIGNED
【强制】小数类型为DECIMAL，禁止使用 FLOAT 和 DOUBLE。
说明：在存储的时候，FLOAT 和 DOUBLE都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
【强制】VARCHAR是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率

2. 运算符

2.1 算术运算符

算数运算符包括：加、减、乘、除、取余
· 整数类型的值与浮点数进行加减操作，结果是一个浮点数。
· mysql中+表示数值相加，如果遇到非数值类型，会先尝试转成数值， 如果转化失败就按0计算
· 一个数除以一个整数，无论是否除尽，结果都为一个浮点数。
. 一个数除以另一个数，除不尽时结果为浮点数，且保留小数点后四位。
. Mysql中一个数除以0 结果为 Null

2.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。

等号运算符
. 如果等号两边的值都为字符串（字符串或字符串表达式）则mysql会比较字符串中的字符的ANSI编码是否相等
. 如果等号两边的值一个是整数，另一个是字符串，则mysql会将字符串转化为数字进行比较
. 如果等号两边有一个null，则比较结果为Null.
<=> 安全等于运算符，非null值 <=>null 结果为0，null <=>null 结果为1
不等于运算符 != <>
. 如果两边相等，则返回0，不相等则返回1，但是不能用来判断null值，两边如果存在一个null值，则结果为null。
非符号类型的比较运算符

运算符	作用	描述
is null 或isnull()	判空	如果为null 返回1，否则返回0
is not null	判非空	如果不为null 返回 1，否则返回0
least	多个值中返回最小值	当参数是整数或浮点数时，返回最小值；当参数为字符串时，返回字母表中顺序靠前的字符；当存在null值时，直接返回null
greatest	多个值中返回最大值	与least 类似，返回最大值，当存在null 时直接返回null
between and	两值之间的运算符	返回时包含左右边界值
in	判断一个值是否为列中的任意一个值	如果给定值为 null 则返回 null
not in	判断一个值是否不是一个列表中的任意一个值
like	模糊匹配运算符	用来匹配字符串，如果给定值为null，则返回null；通配符说明： ‘%’匹配0个或多个字符、 ‘-‘匹配一个字符
escape	回避特殊转义符	如果使用 ''作为转义符，则不适用escape，如果使用其他符号做转义，则需要使用
regexp	判断一个值是否符合正则表达式的规则	‘^’匹配以该字符后面的字符开头的字符串; '$'匹配以该字符前面的字符结尾的字符串; ‘-’匹配任何一个单字字符； ‘ [ …]'匹配在方括号内的任何字符’也可以使用 - 匹配一定范围的数据； ‘ * ’匹配 0个或多个在它前面的字符； ‘+’匹配该符号前面的字符一次或多次； [^]匹配不在括号中的任何字符；字符串{n,m} 匹配字符串最少n 次，最多 m次
rlike	判断一个值是否符合正则表达式的规则

regexp 和 like 都可以用来匹配字符串，like 如果要匹配的字符在字符串中间则不返回，而repexp 在这种情况下时返回的。

2.3 逻辑运算符

逻辑运算符包含 not(!) 、and(&&) 、or(||) 、xor

and 优先级高于 or 如果 and or 出现时，会先对 and 两边进行操作

2.4 位运算符

位运算符和会先将操作数转为二进制数，然后再进行位运算，运算完成后的结果再从2进制转为10进制

运算符	作用
&	按位与
\|	按位或
^	按位异或
~	按位取反
>>	按位右移
<<	按位左移

3. 函数

3.1 字符串函数

函数名	描述
ASCII(s)	返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)	返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)	返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,…,sn)	连接s1,s2,…,sn为一个字符串
INSERT(str, idx, len,replacestr)	将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr 字符串索引是从1开始的
REPLACE(str, a, b)	用字符串b替换字符串str中出现a的字符串
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(str,n)	返回字符串str最左边的n个字符
RIGHT(str,n)	返回字符串str最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)	去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)	去掉字符串s结尾处的s1 REPEAT(str, n) 返回str重复n次的结果
SPACE(n)	返回n个空格
STRCMP(s1,s2)	比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)	返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、 MID(s,n,len)相同
LOCATE(substr,str)	返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)	返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)	返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)	返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)	返回s反转后的字符串
NULLIF(value1,value2)	比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

3.2 日期和时间

3.2.1 获取日期或时间

函数	用法
CURDATE(),CURRENT_DATE()	返回当前日期，只包含年月日
CURTIME() ， CURRENT_TIME()	返回当前时间，包含时分秒
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统的日期和时间
UTC_DATE()/ UTC_TIME()	返回UTC（世界标准时间）日期/时间

3.2.2 日期和时间戳的转换

函数	用法
UNIX_TIMESTAMP()	以UNIX时间戳的形式返回当前时间。
UNIX_TIMESTAMP(date)	将时间date以UNIX时间戳的形式返回。
FROM_UNIXTIME(timestamp)	将UNIX时间戳的时间转换为普通格式的时间

3.2.3 获取月份、星期、星期数、天数等函数

函数	用法
YEAR(date) / MONTH(date) / DAY(date)	返回具体的日期值
HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
MONTHNAME(date)	返回月份：January，…
DAYNAME(date)	返回星期几：MONDAY，TUESDAY…SUNDAY
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
QUARTER(date)	返回日期对应的季度，范围为1～4 WEEK(date)
WEEKOFYEAR(date)	返回一年中的第几周
DAYOFYEAR(date)	返回日期是一年中的第几天
DAYOFMONTH(date)	返回日期位于所在月份的第几天
DAYOFWEEK(date)	返回周几，注意：周日是1，周一是2，。。。周六是7
EXTRACT(type FROM date)	返回指定日期中特定的部分，type指定返回的值

3.2.4 时间和秒钟转换的函数

函数	用法
SEC_TO_TIME(seconds)	将 seconds 描述转化为包含小时、分钟和秒的时间
TIME_TO_SEC(time)	将 time 转化为秒并返回结果值；转化的公式为:小时3600+分钟 60+秒

3.2.5 计算日期和时间的函数

函数	用法
DATE_ADD(datetime, INTERVAL expr type)， ADDDATE(date,INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)， SUBDATE(date,INTERVAL expr type)	返回与date相差INTERVAL时间间隔的日期

举例如下：

ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3, 
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4, 
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5

函数	用法
ADDTIME(time1,time2)	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2)	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2)	返回date1 - date2的日期间隔天数
TIMEDIFF(time1, time2)	返回time1 - time2的时间间隔
FROM_DAYS(N)	返回从0000年1月1日起，N天以后的日期
TO_DAYS(date)	返回日期date距离0000年1月1日的天数
LAST_DAY(date)	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)	将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)	返回time加上n后的时间

如：查询7天内的新增用户

SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7

3.2.6 日期的格式化与解析

函数	用法
DATE_FORMAT(date,fmt)	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	返回日期字符串的显示格式
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

如：

SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') FROM DUAL;
# 输出为： 2014-04-22 15:47:06

3.3 流程处理函数

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 … [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的switch…case…

简单举例：

SELECT employee_id,salary, 
CASE WHEN salary>=15000 THEN 'A' 
WHEN salary>=10000 THEN 'B'
WHEN salary>=8000 THEN 'C' 
ELSE 'D' 
END "描述" 
FROM employees;

3.4 加密与解密函数

主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取

函数	用法
PASSWORD(str)	返回字符串str的加密版本，41位长的字符串。加密结果不可逆常用于用户的密码加密
MD5(str)	返回字符串str的md5加密后的值，也是一种加密方式。若参数为NULL，则会返回NULL
SHA(str)	从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL。 SHA加密算法比MD5更加安全。
ENCODE(value,password_seed)	返回使用password_seed作为加密密码加密value
DECODE(value,password_seed)	返回使用password_seed作为加密密码解密value

3.5 Mysql信息函数

函数	用法
VERSION()	返回当前MySQL的版本号
CONNECTION_ID()	返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()	返回MySQL命令行当前所在的数据库
USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()	返回当前连接MySQL的用户名，返回结果格式为 “主机名@用户名”
COLLATION(value)	返回字符串value的比较规则
BENCHMARK(n,expr)	将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间