MySQL函数的使用
以下列出mysql函数的使用，并不完全，涉及到多少写多少。
length(str)：返回字符串(str)的字符长度。一个汉字算三个字符，一个数字或字母算一个字符。
select length('测试'); -- 6 select length('123abc'); -- 6
char_length(str)：返回字符串(str)的字符长度。一个汉字、数字或字母都算一个字符。
select char_length('测试'); -- 2 select char_length('123abc'); -- 6
instr(str,substr)：返回指定字符串(substr)在字符串(str)中的第一次出现的位置。如果未找到则返回0。
select instr('football','f'); -- 1 select instr('football','o'); -- 2 select instr('football','ba'); -- 5
locate(substr,str)：返回指定字符串(substr)在字符串(str)中的第一次出现的位置。如果未找到则返回0。
select locate('ba','football'); -- 5 select locate('o','football'); -- 2
locate(substr,str,pos)：返回指定字符串(substr)在字符串(str)中的第(pos)位之后第一次出现的位置。如果未找到则返回0。
select locate('o','football',3); -- 3 select locate('o','football',4); -- 0
concat(str1,str2,...)：返回所有参数拼接起来产生的字符串。如果有任何一个参数位null，则返回null。
select concat('w','h','at'); -- what select concat('w','h','at',null); -- null
concat_ws(separator,str1,str2,...)：返回所有参数拼接起来产生的字符串，第一个参数作为后面参数拼接的分隔符。如果分隔符为null，则返回null，除分隔符外的其他参数为null,则忽略。
select concat_ws(',','first','second','third'); -- first,second,third select concat_ws(';','11','22','33',null); -- 11;22;33 select concat_ws(null,'11','22','33',null); -- null
left(str,length)：从字符串(str)左起第一个字符开始，返回指定长度(length)的子字符串。
select left('mysql',2); -- my
right(str,length)：从字符串(str)右起第一个字符开始，返回指定长度(length)的子字符串。
select right('mysql',3); -- sql
substring(str,pos)：返回字符串(str)从第(pos)个字符开始之后的所有字符组成的子字符串。pos为正数，从左起；pos为负数，从右起。
select substring('everyone',3); -- eryone select substring('everyone',-3); -- one
substring(str,pos,length)：返回字符串(str)从第(pos)个字符开始，之后指定长度(length)的子字符串。pos为正数，从左起；pos为负数，从右起。
select substring('everyone',1,5); -- every select substring('everyone',-3,3); -- one
substring_index(str,delim,count)：返回从字符串(str)第一个字符开始，到字符串中第(count)次出现的分隔符(delim)之间的子字符串。count为正数，从左起；count为负数，从右起。
如果在字符串(str)中未找到分隔符(delim)的位置，或者未找到指定次数(count)出现的分隔符的位置时，则返回整个字符串。分隔符(delim)不一定为符号，也可以为其它自定义字符。
select substring_index('11;22;33;',';',2); -- 11;22 select substring_index('11;22;33;',',',2); -- 11;22;33; select substring_index('11;22;33;',';',-2); -- 33;
convert(value,type)：类型或格式转换。可转换的类型是有限制的，可以是以下类型中之一：binary、char(n)、date、time、datetime、decimal、signed、unsigned。
-- 数字转换为字符串 select convert(33,char); -- 33(字符串类型) -- 字符串转换为数字 select convert('333',signed); -- 333(数字值类型) -- 把字符串编码格式转换为Unicode编码格式 select convert('一' using ucs2); -- 把字符串编码格式转换为UTF8编码格式 select convert('abc' using utf8); -- 把字符串编码格式转换为GBK编码格式 select convert('一' using gbk); -- 把字符串编码格式转换为GB2312编码格式 select convert('一' using gb2312);
cast(value as type)：类型转换。可转换的类型是有限制的，可以是以下类型中之一：binary、char(n)、date、time、datetime、decimal、signed、unsigned。
-- 数字转换为字符串 select cast(333 as char); -- 字符串转换为数字 select cast('3333' as signed);
hex(str)：把指定的字符串(str)转换为16进制值。
-- 把字符串转换为16进制值 select hex(convert('一' using ucs2)); -- 4E00
unhex(str)：把指定的16进制字符串(str)转换为字符串。
-- 把16进制值转换为字符串 select convert(unhex('4E00') using ucs2); -- 一
ord(str)：把指定的字符串(str)转换为ASCII值。
-- 把字符串转换为ASCII值 select ord(convert('A' using ucs2)); -- 65 select ord(convert('一' using ucs2)); -- 19968
ascii(str)：把指定的字符串(str)转换为ASCII值。
-- 把字符串转换为ASCII值 select ascii(convert('A' using utf8)); -- 65
now()：获取当前的日期与时间，即“YYYY-MM-dd HH:mm:ss”格式。
-- 获取当前日期时间 select now(); -- 2018-11-16 15:11:26
current_timestamp()：获取当前的时间戳，即“YYYY-MM-dd HH:mm:ss”格式。
-- 获取当前的时间戳 select current_timestamp; -- 2018-11-16 15:11:26 select current_timestamp(); -- 2018-11-16 15:11:26
curdate()：获取当前的日期，即“YYYY-MM-dd”格式。
-- 获取当前的日期 select curdate(); -- 2018-11-16
current_date()：获取当前的日期，即“YYYY-MM-dd”格式。
-- 获取当前的日期 select current_date(); -- 2018-11-16
curtime()：获取当前的时间，即“HH:mm:ss”格式。
-- 获取当前的时间 select curtime(); -- 15:11:26
current_time()：获取当前的时间，即“HH:mm:ss”格式。
-- 获取当前的时间 select current_time(); -- 15:11:26
last_day(datetime)：获取指定的日期时间中月份的最后一天，可以借此获取指定的月份有多少天。
-- 获取指定的日期时间中月份的最后一天 select last_day('2018-10-10'); -- 2018-10-31 select last_day('2018-10-10 12:30:30'); -- 2018-10-31 select day(last_day('2018-10-10')); -- 31
dayofweek(datetime)：获取指定的日期时间是星期几。（1表示星期日，2表示星期一。。。7表示星期六，ODBC标准）
-- 获取指定的日期时间是星期几 select dayofweek('2018-10-10'); -- 4 select dayofweek('2018-10-10 12:30:30'); -- 4
weekday(datetime)：获取指定的日期时间是星期几。（0表示星期一，1表示星期二。。。6表示星期日）
-- 获取指定的日期时间是星期几 select weekday('2018-10-10'); -- 2 select weekday('2018-10-10 12:30:30'); -- 2
dayofmonth(datetime)：获取指定的日期时间中是该月的第几天。（返回值范围为1至31）
-- 获取指定的日期时间中是该月的第几天 select dayofmonth('2018-10-10'); -- 10 select dayofmonth('2018-10-10 12:30:30'); -- 10
dayofyear(datetime)：获取指定的日期时间中是该年的第几天。
-- 获取指定的日期时间中是该年的第几天 select dayofyear('2018-10-10'); -- 283 select dayofyear('2018-10-10 12:30:30'); -- 283
dayname(datetime)：获取指定的日期时间是星期几的英文。
-- 获取指定的日期时间是星期几的英文 select dayname('2018-10-10'); -- Wednesday select dayname('2018-10-10 12:30:30'); -- Wednesday
monthname(datetime)：获取指定的日期时间中月份的英文。
-- 获取指定的日期时间中月份的英文 select monthname('2018-10-10'); -- October select monthname('2018-10-10 12:30:30'); -- October
quarter(datetime)：获取指定的日期时间是当年的第几季度。（1至3月表示第一季度。。。9-12月表示第四季度）
-- 获取指定的日期时间是当年的第几季度 select quarter('2018-10-10'); -- 4 select quarter('2018-10-10 12:30:30'); -- 4
sec_to_time(second)：把秒数转换为时间。
-- 把秒数转换为时间 select sec_to_time(120); -- 00:02:00 select sec_to_time(3600); -- 01:00:00
time_to_sec(time)：把时间转换为秒数。
-- 把时间转换为秒数 select time_to_sec('00:02:00'); -- 120 select time_to_sec('01:00:00'); -- 3600
date_format(datetime,format)：获取指定格式的日期时间。
-- 获取指定格式的日期时间 -- 格式 描述 -- %a 缩写星期名（Sun、Sat等） -- %b 缩写月名（Jan、Dec等） -- %c 月，数值（1-12） -- %D 带有英文前缀的月中的天(1th、3th、10th等) -- %d 月的天，数值(00-31) -- %e 月的天，数值(0-31) -- %f 微秒 -- %H 小时 (00-23) -- %h 小时 (01-12) -- %I 小时 (01-12) -- %i 分钟，数值(00-59) -- %j 年的天 (001-366) -- %k 小时 (0-23) -- %l 小时 (1-12) -- %M 月名（January、October等） -- %m 月，数值(01-12) -- %p AM 或 PM -- %r 时间，12-小时（hh:mm:ss AM 或hh:mm:ss PM） -- %S 秒(00-59) -- %s 秒(00-59) -- %T 时间, 24-小时 (hh:mm:ss) -- %U 周 (00-53) 星期日是一周的第一天 -- %u 周 (00-53) 星期一是一周的第一天 -- %V 周 (01-53) 星期日是一周的第一天，与 %X 使用 -- %v 周 (01-53) 星期一是一周的第一天，与 %x 使用 -- %W 星期名 -- %w 周的天 （0=星期日, 6=星期六） -- %X 年，其中的星期日是周的第一天，4 位，与 %V 使用 -- %x 年，其中的星期一是周的第一天，4 位，与 %v 使用 -- %Y 年，4 位 -- %y 年，2 位 select date_format('2018-10-10','%Y-%m-%d %H:%i:%s'); -- 2018-10-10 00:00:00 select date_format('2018-10-10 12:30:30','%Y-%m-%d'); -- 2018-10-10
time_format(datetime,format)：获取指定格式的时间。该方法与date_format()方法类似，但time_format()方法只处理时间部分。
-- 获取指定格式的时间。 select time_format('12:30:30','%H:%i:%s'); -- 12:30:30 select time_format('12:30:30','%r'); -- 12:30:30 PM
str_to_date(datetime,format)：将指定的字符串转换为日期时间的格式，即转换为“YYYY-MM-dd HH:mm:ss”的格式。
-- 将指定的字符串转换为日期时间的格式 select str_to_date('10/25/2018','%m/%d/%Y'); -- 2018-10-25 select str_to_date('10.25.2018 12.30.30','%m.%d.%Y %H.%i.%s'); -- 2018-10-25 12:30:30
makedate(year,dayofyear)：获取根据多个值拼成的日期。第一个参数表示年份，第二个参数表示指定该年的第多少天。
-- 获取根据多个值拼成的日期 select makedate(2018,180); -- 2018-06-29 select makedate(2018,15); -- 2018-01-15
maketime(hour,minute,second)：获取根据多个值拼成的时间。
-- 获取根据多个值拼成的时间 select maketime(12,20,30); -- 12:20:30 select maketime(23,59,59); -- 23:59:59
timestamp(datetime_exp1[,datetime_exp2])：获取指定日期时间的时间戳，以及两个日期时间的相加运算。
-- 获取指定日期时间的时间戳 select timestamp('2018-10-10'); -- 2018-10-10 00:00:00 select timestamp('2018-10-10 12:30:30'); -- 2018-10-10 12:30:30 -- 获取两个指定日期时间的参数的和的时间戳，即第一个日期时间加上第二个时间的和 select timestamp('2018-10-10 12:30:30','01:01:01'); -- 2018-10-10 13:31:31 -- 最多只能指定到日，不能指定月和年 select timestamp('2018-10-10 12:30:30','01 01:01:01'); -- 2018-10-11 13:31:31
timestampadd(unit,interval,datetime)：在指定的年、月、日、小时、分钟、秒等单位上加上指定的值。
-- 在指定的年、月、日、小时、分钟、秒等单位上加上指定的值 select timestampadd(day,1,'2018-10-10 12:30:30'); -- 2018-10-11 12:30:30 select timestampadd(month,1,'2018-10-10 12:30:30'); -- 2018-11-10 12:30:30
timestampdiff(unit,datetime_exp1,datetime_exp2)：比较两个日期时间相差的指定的年、月、日、小时、分钟、秒等单位的值。
-- 比较两个日期时间相差的指定的年、月、日、小时、分钟、秒等单位的值 select timestampdiff(day,'2018-10-11 12:30:30','2018-11-10 12:30:30'); -- 30 select timestampdiff(hour,'2018-10-11 12:30:30','2018-11-10 12:30:30'); -- 720
datediff(datetime_exp1,datetime_exp2)：比较两个日期时间中，第一个日期时间相对于第二个日期时间相差的天数。
-- 比较两个日期时间中，第一个日期时间相对于第二个日期时间相差的天数 select datediff('2018-10-11','2018-11-10'); -- -30 select datediff('2018-12-20 12:00:00','2018-10-25 12:30:30'); -- 56
timediff(time_exp1,time_exp2)：比较两个时间中，第一个时间相对于第二个时间相差的时:分:秒。
-- 比较两个时间中，第一个时间相对于第二个时间相差的时:分:秒 select timediff('09:00:00','12:30:30'); -- -03:30:30 select timediff('12:00:00','08:30:30'); -- 03:29:30
date_add(datetime,interval value unit)：在指定的日期时间上增加指定的年、月、日、小时、分钟、秒等单位的值。
-- 在指定的日期时间上增加指定的年、月、日、小时、分钟、秒等单位的值 select date_add('2018-10-10',interval 1 day); -- 2018-10-11 select date_add('2018-10-10 12:30:30',interval 3 month); -- 2019-01-10 12:30:30 select date_add('2018-10-10 12:30:30',interval -10 minute); -- 2018-10-10 12:20:30
date_sub(datetime,interval value unit)：在指定的日期时间上减少指定的年、月、日、小时、分钟、秒等单位的值。
-- 在指定的日期时间上减少指定的年、月、日、小时、分钟、秒等单位的值 select date_sub('2018-10-10',interval 1 day); -- 2018-10-09 select date_sub('2018-10-10 12:30:30',interval 3 month); -- 2018-07-10 12:30:30 select date_sub('2018-10-10 12:30:30',interval -10 minute); -- 2018-10-10 12:40:30
获取指定的日期时间中指定的年、月、日、小时、分钟、秒等单位的值。
set @dt='2018-10-10 12:30:30.123456'; select date(@dt); -- 2018-10-10 select time(@dt); -- 12:30:30.123456 select year(@dt); -- 2018 select quarter(@dt); -- 4 select month(@dt); -- 10 select week(@dt); -- 40 select day(@dt); -- 10 select hour(@dt); -- 12 select minute(@dt); -- 30 select second(@dt); -- 30 select microsecond(@dt); -- 123456 select yearweek(@dt); -- 201840
extract(unit from datetime)：获取指定的日期时间中指定的年、月、日、小时、分钟、秒等单位的值。
set @dt='2018-10-10 12:30:30.123456'; select extract(year from @dt); -- 2018 select extract(quarter from @dt); -- 4 select extract(month from @dt); -- 10 select extract(week from @dt); -- 40 select extract(day from @dt); -- 10 select extract(hour from @dt); -- 12 select extract(minute from @dt); -- 30 select extract(second from @dt); -- 30 select extract(microsecond from @dt); -- 123456 select extract(year_month from @dt); -- 201810 select extract(day_hour from @dt); -- 1012 select extract(day_minute from @dt); -- 101230 select extract(day_second from @dt); -- 10123030 select extract(day_microsecond from @dt); -- 10123030123456 select extract(hour_minute from @dt); -- 1230 select extract(hour_second from @dt); -- 123030 select extract(hour_microsecond from @dt); -- 123030123456 select extract(minute_second from @dt); -- 3030 select extract(minute_microsecond from @dt); -- 3030123456 select extract(second_microsecond from @dt); -- 30123456
