Kevin-Blog
使用函数的时候会导致性能降低

1. 常用的字符串内置函数

  • char_length(str) -> 返回字符串的长度

select char_length('字符串');


  • concat(str,str,……) -> 拼接多个字符串

select concat('字符串1','字符串2');


  • format(n, d) -> 将数字 n 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 d 位， 并将结果以字符串的形式返回。若 d 为 0, 则返回结果不带有小数点，或不含小数部分

select format(12332.1, 4);


  • substring() -> 从指定位置开始截取特定长度的字符串

    • substring(str, pos)

select substring('Py世界第一，php', 3);


    • substring(str from pos) -> 和 substring(str, pos) 是一样的只不过 from 是标准的SQL语句写法

select substring('Py世界第一，php' from 3);


    • substring(str, pos, len)

select substring('Py世界第一，php', 3, 4);


    • substring(str from pos for len)

select substring('Py世界第一，php' from 3 for 4);


  • insert(str, pos, len, newstr) -> 在 str 指定的位置插入字符串
    • pos: 要替换位置的起始位置
    • len: 要替换的长度
    • newstr: 需要插入的字符串
    • 如果pos超过原字符串长度，则返回原字符串
    • 如果len超过原字符串长度，则由新字符串完全替换

select insert('Py世界第一，php', 3, 4, 'What');


  • instr(str, substr) -> 查询指定字符在该字符串的位置

select instr('abcde', 'b');


  • locate(substr, str) -> 查询指定字符在该字符串的位置 -> 和 instr 是一样的只是写法不一样

select locate('b','abcde');


  • left(str, num) -> 从左边开始截取 num 个字符

select left('abcde', 3);


  • right(str, num) -> 从右边开始截取 num 个字符

select right('abcde', 3);


  • lower(str) -> 将字符串变成小写

select lower('ABCD');


  • upper(str) -> 将字符串变成大写

select upper('abcd');


  • trim() 

    • trim(str) ->清除字符串两端的空格/换行符/回车

select trim('   abcd   ');


    • trim(leading remstr from str) -> 清除左边的指定字符

select trim(leading 'x' from 'xxxbarxxx');


    • trim(both remstr from str) -> 清除两端的指定字符

select trim(both 'x' from 'xxxbarxxx');


    • trim(both remstr from str) -> 清除右边的指定字符

select trim(trailing 'xyz' from 'barxxyz');


  • ltrim(str) -> 清除字符串左边的空格/换行符/回车

select ltrim('   abcd');


  • rtrim(str) -> 清除字符串右边的空格

select rtrim('abcd   ');


  • repeat(str, num) -> 返回一个由字符串重复num次组成的字符串

select repeat('mysql', 3);


select repeat('mysql', -1);


select repeat('mysql', null);


select repeat(null, 3);


  • replace(str, from_str, to_str) -> 替换字符串中的某个字符

select replace('Hello World', 'World', 'Python');


  • reverse(str) -> 字符串反转

select reverse('Hello World');


  • space(num) -> 返回一个由num个空格组成的字符串

select space(5);


2. 常用的时间内置函数

  • now() -> 返回当前日期

    • now()的显示格式是'YYYY-MM-DD HH:MM:SS'

select now();


    • now()+0的显示格式是'YYYYMMDDHHMMSS'

select now() + 0;


  • curtime(fps) -> 返回当前时间，只包含时分秒 -> fsp指定小数秒的精度，取值0--6,可以不用传

select curtime();


select curtime(2);


  • curdate() -> 返回当前日期，只包含年月日

    • curdate()的显示格式是'YYYY-MM-DD'

select curdate();


    • curdate() +/- num 的显示格式是'YYYYMMDD' -> 如果进行了加减运算那么就会在当前的日前 加 或 减 num 天

select curdate() + 0;


select curdate() + 2;


select curdate() - 2;


  • date_format(date, format) -> 日期格式化（常用）

    • format 的时间格式会和 Python 中的有些不同

format 时间格式
%a
缩写星期名
%b
缩写月名
%c
月，数值
%D
带有英文前缀的月中的天
%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
月名
%m
月，数值（00-12）
%p
AM 或 PM
%r
时间，12-小时（hh:mm:ss AM 或 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(now(), '%Y-%m-%d %k:%i:%s');


select date_format(now(), '%Y-%m-%d');


select date_format(now(), '%k:%i:%s');


  • timediff(time1, time2) -> 返回两个时间相减后相差的时间数

select timediff('12:18:00', '08:45:00');


  • datediff(date1, date2) -> 返回两个日期相减后相差的天数

select datediff('2019-3-22', '1997-3-20');


  • 日期时间运算函数 -> 分别为指定的日期加上(add)或减去(sub)一个时间的间隔值expr -> 通俗理解: 对指定的日期进行加减运算

    • date_add(date, interval expr unit); -> 加
    • date_sub(date, interval expr unit); -> 减

    • date -> 需要进行运算的日期
    • interval -> 间隔类型的关键字
    • expr -> 表达式，对应 unit 类型
    • unit -> 时间间隔单位类型
unit时间间隔单位类型
hour
小时
minute
分
second
秒
microsecond
毫秒
year
年
month
月
day
日
week
周
quarter
季
year_month
年和月
day_hour
日和小时
day_minute
日和分钟
day_second
日和秒
hour_minute
小时和分
hour_second
小时和秒
minute_second
分钟和秒
    • date_add(date, interval expr unit); -> 加

# 在当前的日期上加多 1 天

select now() as '当前时间', date_add(now(), interval 1 day);


    • date_sub(date, interval expr unit); -> 减

# 减1天1小时1分1秒

select now() as '当前时间', date_sub(now(), interval '1 1:1:1' day_second);


    • 不使用函数，也可以写表达式进行日期的加减

# 在当前的时间上加多 1 秒

select '2008-12-31 23:59:59' + interval 1 second;


# 在当前的时间上减去 1 秒

select '2005-01-01' - interval 1 second;


  • 选取日期时间的各个部分（常用）

    • date(date) -> 日期

select now(), date(now());


    • time(date) -> 时间

select now(), time(now());


    • year(date) -> 年

select now(), year(now());


    • quarter(date) -> 季度

select now(), quarter(now());


    • month(date) -> 月

select now(), month(now());


    • week(date) -> 周

select now(), week(now());


    • day(date) -> 日

select now(), day(now());


    • hour(date) -> 小时

select now(), hour(now());


    • minute(date) -> 分钟

select now(), minute(now());


    • second(date) -> 秒

select now(), second(now());


    • microsecond(date) -> 微秒

select now(), microsecond(now());


  • extract(unit from date) -> 选取日期时间的各个部分（和上面的功能是一样的只不过是写法不一样）

    • year -> 年

select now(), extract(year from now());


    • month -> 月

select now(), extract(month from now());


    • week -> 周

select now(), extract(week from now());


    • day -> 日

select now(), extract(day from now());


    • hour -> 小时

select now(), extract(hour from now());


    • minute -> 分钟

select now(), extract(minute from now());


    • second -> 秒

select now(), extract(second from now());


    • year_month -> 年月

select now(), extract(year_month from now());


    • hour_minute -> 时分

select now(), extract(hour_minute from now());


  • 返回日期在这一周、一月、一年中是第几天

    • dayofweek(date) -> 返回日期在这一个周中是第几天

select now(), dayofweek(now());


    • dayofmonth(date) -> 返回日期在这一个月中是第几天

select now(), dayofmonth(now());


    • dayofyear(date) -> 返回日期在这一年中是第几天

select now(), dayofyear(now());


  • 返回日期的星期和月份名称

    • 名称显示中文 或 英文是由系统变量 lc_time_names 控制（默认值是'en_US'）

show variables like 'lc_time_names';


    • 更改系统变量 lc_time_names 为中文（zh_CN）

set lc_time_names = 'zh_CN';

    • 返回日期的星期名称

select dayname(now());



    • 返回日期的月份名称

select monthname(now());



3. 自定义函数

  • 自定义函数和触发器一样需要修改SQL语句终止符
  • 自定义函数里面不能写SQL语句不然就会报错

delimiter \\
create function f1 ( -- 创建一个名为 f1 的函数
    i1 int, -- 形参名 必须定义该形参的sql数据类型
    i2 int -- 形参名 必须定义该形参的sql数据类型
) returns int -- 定义函数返回值的sql数据类型
begin

declare num int; -- 定义一个变量sql数据类型是 int
set num = i1 + i2; -- 对这个变量进行赋值
return num; -- 将 num 返回出去

end\\
delimiter ;

# 使用自定义函数

select f1(1,2);



  • 在MySQL中创建函数时出现这种错误的解决方法

set global log_bin_trust_function_creators=TRUE;

  • 删除函数

# drop function 函数名;

drop function f1;


← 唯一索引 →