本文介绍了MySQL中的数据类型，包括整数类型、日期与时间类型、文本类型等，并详细解释了每种类型的用途及取值范围。此外，还涵盖了常用的运算符及函数，帮助开发者更好地理解和使用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个字节。
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表达式所耗费的时间 |
扫一扫
1864
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
