MySQL 字符串函数

字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
函数 | 功能
concat(s1,s2..sn)  |  链接字符串
insert(str,x,y,instr) | 将字符串str从第x位开始，y个字符长的zi串替换为字符串instr
lower(str) | 字符串转小写
upper(str)  | 字符串转大写
left(str,x) | 返回字符串str最左边的x个字符
right(str,x) | 返回字符串最右边的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) | 替换字符串a为b
strcmp(s1,s2) |  比较字符串s1,s2
trim(str) | 去掉字符串行位和航头的空格
substring(str,x,y) | 返回字符串str x位置起y个字符串长度

CONCAT(STR,STR1..STRN)

mysql> select concat(‘aa’,’bb’),concat(‘dd’,null);
+——————-+——————-+
| concat(‘aa’,’bb’) | concat(‘dd’,null) |
+——————-+——————-+
| aabb | NULL |
+——————-+——————-+
1 row in set (0.00 sec)

1
### INSERT(STR,X,Y,INSTR)

mysql> select insert(‘beijing2008you’,12,3,’me’);
+————————————+
| insert(‘beijing2008you’,12,3,’me’) |
+————————————+
| beijing2008me |
+————————————+
1 row in set (0.00 sec)

LOWER(STR)

字符串转小写

1
2
3
4
5
6
7
mysql> select lower('AAAAa');
+----------------+
| lower('AAAAa') |
+----------------+
| aaaaa          |
+----------------+
1 row in set (0.00 sec)

UPPER(STR)

字符串转大写

1
2
3
4
5
6
7
mysql> select upper('aaa');
+--------------+
| upper('aaa') |
+--------------+
| AAA          |
+--------------+
1 row in set (0.00 sec)

LEFT ,RIGHT

返回字符串最左边/最右边x个字符和最右边的x个字符，如果第二个字符是null不反悔任何字符

1
2
3
4
5
6
7
mysql> select left('beijing2008',7),left('beijing',null),right('beijing2008',4);
+-----------------------+----------------------+------------------------+
| left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |
+-----------------------+----------------------+------------------------+
| beijing               | NULL                 | 2008                   |
+-----------------------+----------------------+------------------------+
1 row in set (0.00 sec)

LPAD RPAD

用字符串pad对str最左边和最右边进行填充，知道长度为n个字符长度

1
2
3
4
5
6
7
mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008      | beijing2008200820082      |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

LTRIM RTRIM

去除空格

1
2
3
4
5
6
7
mysql> select ltrim('  |beijing'),rtrim('beijing   ');
+---------------------+---------------------+
| ltrim('  |beijing') | rtrim('beijing   ') |
+---------------------+---------------------+
| |beijing            | beijing             |
+---------------------+---------------------+
1 row in set (0.00 sec)

REPEAT

重复字符串制定次数

1
2
3
4
5
6
7
mysql> select repeat('mysql',3);
+-------------------+
| repeat('mysql',3) |
+-------------------+
| mysqlmysqlmysql   |
+-------------------+
1 row in set (0.00 sec)

REPLACE

替换字符

1
2
3
4
5
6
7
mysql> select replace('beijing2008',2008,2010);
+----------------------------------+
| replace('beijing2008',2008,2010) |
+----------------------------------+
| beijing2010                      |
+----------------------------------+
1 row in set (0.00 sec)

STRCMP

比较字符串ASCLL码值大小
(相等返回0，丹玉返回1，小于返回-1)

1
2
3
4
5
6
7
mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

TRIM

去除字符串两次空白

1
2
3
4
5
6
7
mysql> select trim('  |2008|  ');
+--------------------+
| trim('  |2008|  ') |
+--------------------+
| |2008|             |
+--------------------+
1 row in set (0.01 sec)

SUBSTRING

substring(str,x,y)返回从字符串str第x位置开始起y个长度的字符串

1
2
3
4
5
6
7
mysql> select substr('beijing2008',8,4),substring('beijing2008',1,7);
+---------------------------+------------------------------+
| substr('beijing2008',8,4) | substring('beijing2008',1,7) |
+---------------------------+------------------------------+
| 2008                      | beijing                      |
+---------------------------+------------------------------+
1 row in set (0.00 sec)

#mysql常用函数

1
2
3
4
5
6
7
8
函数 | 功能
abs(x) | 返回x的绝对值
ceil(x) | x向上取整
floor(x) | 向下取整
mod(x,y) | 返回x/y的模
rand() | 返回0到1的随机值
round(x,y) | 返回四舍五入值
truncate(x,y) | 返回x截断y位小数的结果

ABS 返回数值的绝对值

1
2
3
4
5
6
7
mysql> select abs(-0.8),abs(0.8);
+-----------+----------+
| abs(-0.8) | abs(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+
1 row in set (0.01 sec)

CEIL 返回向上取整值

1
2
3
4
5
6
7
mysql> select ceil(-0.8),ceil(0.8);
+------------+-----------+
| ceil(-0.8) | ceil(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+
1 row in set (0.00 sec)

FOOLR向下取整

1
2
3
4
5
6
7
mysql> select floor(-0.8),floor(0.8);
+-------------+------------+
| floor(-0.8) | floor(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+
1 row in set (0.00 sec)

MOD(x,y) 返回x/y的模

和x%y的结果相同，模数和被模数任何一个null结果都为null

1
2
3
4
5
6
7
mysql> select mod(15,10),mod(1,11),mod(null,10);
+------------+-----------+--------------+
| mod(15,10) | mod(1,11) | mod(null,10) |
+------------+-----------+--------------+
|          5 |         1 |         NULL |
+------------+-----------+--------------+
1 row in set (0.00 sec)

RAND() 返回0到1的随机值

1
2
3
4
5
6
7
mysql> select  rand(),rand();
+--------------------+---------------------+
| rand()             | rand()              |
+--------------------+---------------------+
| 0.2072962515124085 | 0.21381375492905616 |
+--------------------+---------------------+
1 row in set (0.00 sec)

ROUND(x,y) 返回x的四舍五入有y位小数的值

1
2
3
4
5
6
7
mysql> select  round(rand(),2),round(rand(),5);
+-----------------+-----------------+
| round(rand(),2) | round(rand(),5) |
+-----------------+-----------------+
|            0.63 |         0.37040 |
+-----------------+-----------------+
1 row in set (0.00 sec)

TRUNCATE(x,y) 返回截取y未的x值

1
2
3
4
5
6
7
mysql> select round(1.2345,2),truncate(1.2345,2);
+-----------------+--------------------+
| round(1.2345,2) | truncate(1.2345,2) |
+-----------------+--------------------+
|            1.23 |               1.23 |
+-----------------+--------------------+
1 row in set (0.00 sec)

#mysql中常用日期时间函数

1
2
3
4
5
6
7
8
9
函数 | 功能
CURDATE() | 返回当前日期
CURTIME() | 返回当前日期
NOW() | 返回但钱的时间和日期
UNIX_TIMESTAMP(date) | 返回日期date的unix时间戳
FROM_UNIXTIME(UNIXTIME) | 从时间戳转日期
WEEK(DATE) / YEAR(DATE) | 返回周 年
HOUR(TIME) MINUTE(TIME) | 返回小时 分钟
MONTHNAME(DATE) | 返回月份

CURDATE()函数

返回当前日期，只包含年与日

1
2
3
4
5
6
7
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2017-01-11 |
+------------+
1 row in set (0.00 sec)

CURTIME()

返回当前时间只包含时分秒

1
2
3
4
5
6
7
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 03:21:02  |
+-----------+
1 row in set (0.00 sec)

NOW()

返回当前日期和时间，包含年月日时分秒

1
2
3
4
5
6
7
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-11 03:21:39 |
+---------------------+
1 row in set (0.00 sec)

UNIX_TIMESTAMP(DATE)

返回日期date的unix时间戳

1
2
3
4
5
6
7
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1484104996 |
+-----------------------+
1 row in set (0.00 sec)

FROM_UNIXTIME(UNIXTIME)

时间戳转年月日

1
2
3
4
5
6
7
mysql> select from_unixtime(1484104996);
+---------------------------+
| from_unixtime(1484104996) |
+---------------------------+
| 2017-01-11 03:23:16       |
+---------------------------+
1 row in set (0.00 sec)

WEEK(DATE) / YEAR(DATE)

返回制定时间星期日期/年份

1
2
3
4
5
6
7
mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|           2 |        2017 |
+-------------+-------------+
1 row in set (0.00 sec)

HOUR(TIME) MINUTE(TIME)

时间转小时/分钟

1
2
3
4
5
6
7
mysql> select hour(curtime()),minute(curdate());
+-----------------+-------------------+
| hour(curtime()) | minute(curdate()) |
+-----------------+-------------------+
|               3 |                 0 |
+-----------------+-------------------+
1 row in set (0.00 sec)

MONTHNAME(DATE)

返回date的英文月份名称

1
2
3
4
5
6
7
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| January          |
+------------------+
1 row in set (0.00 sec)

DATE_FORMAT

1
2
3
4
5
6
7
mysql> select date_format(now(),'%M,%D,%Y');
+-------------------------------+
| date_format(now(),'%M,%D,%Y') |
+-------------------------------+
| January,11th,2017             |
+-------------------------------+
1 row in set (0.00 sec)

mysql日期时间格式


#其他函数

1
2
3
4
5
6
函数 | 功能
database() | 返回当前数据库名
version() | 返回当前数据库版本
user() | 返回当前登录用户名
inet_aton(ip) | 返回ip地址的数据自标示
inet_ntoa(num) | 返回数字代表的ip地址

DATABASE()

返回当前数据库名

1
2
3
4
5
6
7
mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

VERSION()

返回当前版本

1
2
3
4
5
6
7
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.29    |
+-----------+
1 row in set (0.00 sec)

USER()

返回当前用户名

1
2
3
4
5
6
7
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

INET_ATON()

返回ip的数字标示

1
2
3
4
5
6
7
mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.00 sec)

INET_NTOA

数字标示ip转为ip

1
2
3
4
5
6
7
mysql> select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777) |
+-----------------------+
| 192.168.1.1           |
+-----------------------+
1 row in set (0.00 sec)