MySQL常用函数

1、字符串函数
1.1 char_length或character_length:求字符串长度
返回字符串的长度，如果一个字符占用多个字节，也算一个。

mysql> SELECT CHAR_LENGTH('我是中国人');
     >5

1.2 CONCAT(str1, str2, str3, …):拼装字符串

mysql> SELECT CONCAT("my","sq","l");
     >mysql

1.3 CONCAT_WS(separator, str1, str2, str3, …): 拼装字符串，在字符串中间加上分隔符separator

mysql> SELECT CONCAT_WS(",", "my","sq","l");
     >my,sq,l

1.4 LOWER:将字符串中的字母全部转成小写:

mysql>SELECT LOWER("MYSQL");
     > mysql

1.5 UPPER(str)或UCASE(str): 将字符串中的字母全部转成大写

mysql>SELECT UPPER("mysql");
           > MYSQL

1.6 LENGTH(str):返回字符串str占有的字节数，一般字母和数字占1字节，汉字占3字节。

mysql> SELECT LENGTH('123text中');
     >10

1.7 BIT_LENGTH(str):返回字符串占用的位数

mysql>SELECT BIT_LENGTH('1中国');
     >56

每个数字或字母占8位，也就是1个字节；每个汉字占24位，也就是3个字节。
1.8 SUBSTRING(str, pos, len), SUBSTRING(str, pos):返回字符串str的子串，从第pos个开始，长度是len.

mysql>SELECT SUBSTRING("abcdef",2,3);
     >bcd

1.9 REPLACE(str, from_str, to_str):字符串替换

mysql>SELECT REPLACE("abcdabc", "a", "2");
     >2bcd2bc

1.10 STRCMP(str1, str2):字符串比较函数，如果str1>str2，则返回1；如果str1=str2，则返回0；如果str1<str2，则返回-1。

mysql>SELECT STRCMP("li2", "li");
     >1
mysql>SELECT STRCMP("li", "li");
     >0
mysql>SELECT STRCMP("li2", "li");
     >-1

2、数字函数
2.1 BIN(N):十进制数转换成二进制数

mysql> SELECT BIN(10);
     > 1010

2.2 HEX(N):将十进制数转换成十六进制数，同CONV(N,10,16);

mysql>SELECT HEX(100);
     > 64

2.3 CONV(N, begin, end);
将整数从一种进制begin，转换成另外一种进制end.

mysql> SELECT CONV(1010,2,8);
     >12

2.4 FORMAT(X,D)
将浮点数按D取精度，最后一位四舍五入。

mysql> SELECT FORMAT(1234.123, 2);
     > 1,234.12

2.5 RAND():产生一个0~1之间的随机数

mysql> SELECT RAND();
     > 0.4919027131906736

3、日期和时间函数
3.1 NOW():当前日期和时间

mysql>SELECT NOW();
     > 2014-11-11 22:11:20

3.2 CURDATE():当前日期

mysql>SELECT CURDATE();
     > 2014-11-11

3.3 CURTIME():当前时间

mysql>SELECT CURTIME();
     > 22:11:59

3.4 DATE:日期时间字符串保留日期

mysql>SELECT DATE('2003-12-31 01:02:03');
     >2003-12-31

3.5 TIME:日期时间字符串保留时间

mysql>SELECT TIME('2003-12-31 01:02:03');
     >01:02:03

3.6 DATEDIFF(expr,expr2):计算两个日期之前的差值
字符的格式是日期，也可以是日期和时间，但最终只会取日期进行计算

mysql> SELECT DATEDIFF('2014-10-30 23:59:59','2014-09-30 23:59:58');
     >30

3.7 DATE_ADD(date,INTERVAL expr type):日期增加函数
在当前时间下添加某个单位的值；如果值为负数，则减少这个单位的值。
比如：

mysql> SELECT DATE_ADD(NOW(),INTERVAL -1 DAY);
     > 2014-11-10 23:38:01

typeValue	ExpectedexprFormat
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND	'MINUTES.MICROSECONDS'
MINUTE_SECOND	'MINUTES:SECONDS'
HOUR_MICROSECOND	'HOURS.MICROSECONDS'
HOUR_SECOND	'HOURS:MINUTES:SECONDS'
HOUR_MINUTE	'HOURS:MINUTES'
DAY_MICROSECOND	'DAYS.MICROSECONDS'
DAY_SECOND	'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE	'DAYS HOURS:MINUTES'
DAY_HOUR	'DAYS HOURS'
YEAR_MONTH	'YEARS-MONTHS'

 3.8 DATE_SUB(date,INTERVAL expr type):日期减少函数
在当前时间下减少某个单位的值；如果值为负数，则增加这个单位的值。
比如：

mysql>SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
     > 2014-11-10 23:42:49
mysql>SELECT DATE_SUB(NOW(),INTERVAL -1 DAY);
     > 2014-11-12 23:43:46

3.9 DATE_FORMAT(date,format):日期格式化函数
比如：

mysql>SELECT NOW(),DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
     >2014-11-12 23:03:48  2014-11-12 23:11:48
mysql>SELECT NOW(),DATE_FORMAT(NOW(), '%Y-%m-%d');
     >2014-11-12 23:03:48  2014-11-12

Format的格式是前面带个%，后面的字符如下：

值	说明
%a	Abbreviated weekday name (Sun..Sat)
%b	Abbreviated month name (Jan..Dec)
%c	Month, numeric (0..12)
%D	Day of the month with English suffix (0th,1st, 2nd,3rd, …)
%d	Day of the month, numeric (00..31)
%e	Day of the month, numeric (0..31)
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%j	Day of year (001..366)
%k	Hour (0..23)
%l	Hour (1..12)
%M	Month name (January..December)
%m	Month, numeric (00..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed byAM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%U	Week (00..53), where Sunday is the first day of the week
%u	Week (00..53), where Monday is the first day of the week
%V	Week (01..53), where Sunday is the first day of the week; used with%X
%v	Week (01..53), where Monday is the first day of the week; used with%x
%W	Weekday name (Sunday..Saturday)
%w	Day of the week (0=Sunday..6=Saturday)
%X	Year for the week where Sunday is the first day of the week, numeric, four digits; used with%V
%x	Year for the week, where Monday is the first day of the week, numeric, four digits; used with%v
%Y	Year, numeric, four digits
%y	Year, numeric (two digits)
%%	A literal ‘%’ character
%x	x, for any ‘x’ not listed above

4.IP地址转换函数

4.1 INET_ATON(ip_addr):IP地址转换成数字

转换的公式是：ip1*256^3+ip2*256^2+ip3*256^1+ip4

比如IP地址是：127.0.0.1 结果为127*256^3+0*256^2+0*256^1+1=2130706433

mysql>SELECT INET_ATON('127.0.0.1');     
     > 2130706433

4.2 INET_NTOA(num)

将数字转换成IP地址，是4.1转换公式的反运算。

mysql>SELECT INET_NTOA(2130706433);
     > 127.0.0.1