mysql函数之常用操作
2013-07-23 15:59:59 
阿炯
MySQL中提供了很多操作函数，包含了字符串相关，数值相关、日期相关等。
字符串函数
| 函数名 | 作用 |
|---|---|
| CANCAT(S1,S2,...Sn) | 连接 S1,S2,...Sn 为一个字符串 |
| 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 |
| TRIM(str) | 去掉字符串行尾和行头的空格 |
| SUBSTRING(str,x,y) | 返回从字符串 str x 位置起 y 个字符长度的字串 |
数学函数
| 函数名 | 作用 |
|---|---|
| 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 位小数的结果 |
日期时间函数
| 函数名 | 作用 |
|---|---|
| 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 之间的天数 |
流程函数
| 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,否则 |
其他函数
| 函数名 | 作用 |
|---|---|
| DATABASE() | 返回当前数据库名 |
| VERSION() | 返回当前数据库版本 |
| USER() | 返回当前登录用户名 |
| INET_ATON(IP) | 返回 IP 地址的数字表示 |
| INET_NTOA(num) | 返回数字代表的 IP 地址 |
| PASSWORD(str) | 返回字符串 str 的加密版本 |
| MD5() | 返回字符串 str 的 MD5 值 |
前面介绍了字符串处理、数值运算、日期时间、流程控制等函数，这里介绍一些与mysql本身相关的函数参考。
函数--功能
database()--返回当前数据库名
version()--当前数据库版本
user()--当前登录的用户
password(str)--返回字符串str的加密串
md5(str)--返回字符串str的md5码
inet_aton(ip)--返回ip地址的数字表示
inet_ntoa(num)--返回数字代表的ip地址
group_concat--返回用特定字符分隔的合并列
示例：
version()--当前数据库版本
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.1.66rel14.2-log |
+-------------------+
user()--当前登录的用户
mysql> select current_user(); #user()功能相同
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
inet_aton(ip)--返回ip地址的数字表示
mysql> select inet_aton('192.168.18.98');
+----------------------------+
| inet_aton('192.168.18.98') |
+----------------------------+
| 3232240226 |
+----------------------------+
inet_ntoa(num)--返回数字代表的ip地址
mysql> select inet_ntoa(3232240227);
+-----------------------+
| inet_ntoa(3232240227) |
+-----------------------+
| 192.168.18.99 |
+-----------------------+
这两个函数可以方便地在ip或网段间进行比较，比如在表里存入了ip地址，需要查找指定的ip段里有多少个符合的地址：
mysql>select * from t where ip>='192.168.18.98' and ip<='192.168.18.200';
这样返回为空，这就需要借助inet_aton(ip)来实现。
mysql>select * from where inet_aton(ip)>=inet_aton('192.168.18.98') and inet_aton(ip)<=inet_aton'192.168.18.200');
password()函数仅用来修改用户密码，而不是加密数据，后者多用md5()函数来实现。
group_concat--返回用特定字符分隔的合并列
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
mysql> select group_concat(id) as id,group_concat(title SEPARATOR "|") as title from freeoa where id<5;
+---------+--------------------------------------------------------------------------------------------------+
| id | title |
+---------+--------------------------------------------------------------------------------------------------+
| 1,2,3,4 | 欢迎来到FreeOA主页|数据库管理工具-DBeaver|什么是OA|数据库管理工具-CoolSQL |
+---------+--------------------------------------------------------------------------------------------------+
按相关字段来分组
mysql> select group_concat(id),group_concat(name separator "-"),parent from menu group by parent;
还可以对其中的计算字符进行再运算，像group_concat(distinct name separator "-")，group_concat(distinct name order by name desc separator "-")
需要注意的问题
1、如果想合并的列是int型的，合并出来提示[BLOB - 7 字节]之类的提示，这个时候要GROUP_CONCAT( cast( id AS char ) ) AS id 而不能GROUP_CONCAT( id ) AS id，cast是一个转换函数。
2、group_concat对长度是有限制的，mysql默认的是1024字节。
mysql> show variables like "%concat%";
mysql> SELECT @@global.group_concat_max_len;
如果超过了这个长度，数据取不全，如果想改大的话，在my.cnf中的[mysqld]加上以下内容'group_concat_max_len = 10240'，重置一下mysql就行了。
警告如下所示：
+---------+------+--------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------+
| Warning | 1260 | 1 line(s) were cut by GROUP_CONCAT() |
超过最大长度就会被截断掉，可以对系统参数进行设置：
SET @@global.group_concat_max_len=40000;