mysql单行函数case函数,MySQL内置函数-单行函数（字符函数）

字符函数

length 获取字节量

## 查看英文的字节长度

mysql> select length(‘haha‘);+----------------+

| length(‘haha‘) |

+----------------+

| 4 |

+----------------+

1 row in set (0.00sec)

## 查看中文的字节长度

mysql> select length(‘哈哈‘);+------------------+

| length(‘哈哈‘) |

+------------------+

| 6 |

+------------------+

1 row in set (0.00sec)

## 查看表中某一列的字节长度

mysql> select length(first_name) as len fromemployees group by len ;+-----+

| len |

+-----+

| 6 |

| 7 |

| 5 |

| 9 |

| 4 |

| 8 |

| 10 |

| 3 |

| 11 |

| 12 |

| 13 |

| 14 |

+-----+

12 rows in set (1.65 sec)

查看emoji表情的字节长度

concat函数  拼接字符串

mysql> select concat("我是","中国","人");+---------------------------------+

| concat("我是","中国","人") |

+---------------------------------+

| 我是中国人 |

+---------------------------------+

1 row in set (0.00sec)

mysql> select concat(first_name ,"的生日是：",birth_date) from employees limit 10;+----------------------------------------------------+

| concat(first_name ,"的生日是：",birth_date) |

+----------------------------------------------------+

| Georgi 的生日是： 1953-09-02 |

| Bezalel 的生日是： 1964-06-02 |

| Parto 的生日是： 1959-12-03 |

| Chirstian 的生日是： 1954-05-01 |

| Kyoichi 的生日是： 1955-01-21 |

| Anneke 的生日是： 1953-04-20 |

| Tzvetan 的生日是： 1957-05-23 |

| Saniya 的生日是： 1958-02-19 |

| Sumant 的生日是： 1952-04-19 |

| Duangkaew 的生日是： 1963-06-01 |

+----------------------------------------------------+

10 rows in set (0.00 sec)

备份数据库语句的拼接

mysql> select concat("mysqldump -uroot -p123",table_schema," ",table_name,"> /bak/",table_schema,"_",table_name,".sql") from informatiion_schema.tables where table_schema=‘world‘;+-------------------------------------------------------------------------------------------------------------+

| concat("mysqldump -uroot -p123",table_schema," ",table_name,"> /bak/",table_schema,"_",table_name,".sql") |

+-------------------------------------------------------------------------------------------------------------+

| mysqldump -uroot -p123 world City > /bak/world_City.sql |

| mysqldump -uroot -p123 world Country > /bak/world_Country.sql |

| mysqldump -uroot -p123 world CountryLanguage > /bak/world_CountryLanguage.sql |

+-------------------------------------------------------------------------------------------------------------+

3 rows in set (0.00 sec)

upper && lower 大小写转换

mysql> select first_name from employees limit 10;+------------+

| first_name |

+------------+

| Georgi |

| Bezalel |

| Parto |

| Chirstian |

| Kyoichi |

| Anneke |

| Tzvetan |

| Saniya |

| Sumant |

| Duangkaew |

+------------+

10 rows in set (0.00sec)

mysql> select upper(first_name) from employees limit 10;+-------------------+

| upper(first_name) |

+-------------------+

| GEORGI |

| BEZALEL |

| PARTO |

| CHIRSTIAN |

| KYOICHI |

| ANNEKE |

| TZVETAN |

| SANIYA |

| SUMANT |

| DUANGKAEW |

+-------------------+

10 rows in set (0.11sec)

mysql> select lower(first_name) from employees limit 10;+-------------------+

| lower(first_name) |

+-------------------+

| georgi |

| bezalel |

| parto |

| chirstian |

| kyoichi |

| anneke |

| tzvetan |

| saniya |

| sumant |

| duangkaew |

+-------------------+

10 rows in set (0.00 sec)

substr 截取字符串

mysql> select substr(birth_date,1,4) year from employees limit 10;+------+

| year |

+------+

| 1953 |

| 1964 |

| 1959 |

| 1954 |

| 1955 |

| 1953 |

| 1957 |

| 1958 |

| 1952 |

| 1963 |

+------+

10 rows in set (0.00sec)

mysql> select substr(birth_date,6) date from employees limit 10;+-------+

| date |

+-------+

| 09-02 |

| 06-02 |

| 12-03 |

| 05-01 |

| 01-21 |

| 04-20 |

| 05-23 |

| 02-19 |

| 04-19 |

| 06-01 |

+-------+

10 rows in set (0.00 sec)

instr 返回字符串首次出现的索引，没有找到就返回0

mysql> select instr(birth_date,‘80‘) from employees limit 10;+------------------------+

| instr(birth_date,‘80‘) |

+------------------------+

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

| 0 |

+------------------------+

10 rows in set (0.00sec)

mysql> select instr(birth_date,‘19‘) from employees limit 10;+------------------------+

| instr(birth_date,‘19‘) |

+------------------------+

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

| 1 |

+------------------------+

10 rows in set (0.00 sec)

mysql> select id,instr(name,"qingdao") as a from City where CountryCode =‘CHN‘ having a>0;

+------+---+

| id | a |

+------+---+

| 1903 | 1 |

+------+---+

1 row in set (0.01 sec)

trim 去掉行首和行尾的指定字符，默认为空格

mysql> select trim(‘hello‘ from ‘hello world‘) astest;+--------+

| test |

+--------+

| world |

+--------+

1 row in set (0.00sec)

mysql> select trim(‘hello‘ from ‘hello world‘) astest;+--------------+

| test |

+--------------+

| hello world |

+--------------+

1 row in set (0.00sec)

mysql> select trim(‘world‘ from ‘hello world‘) astest;+---------+

| test |

+---------+

| hello |

+---------+

1 row in set (0.00sec)

mysql> select trim(‘world‘ from ‘hello world‘) astest;+---------------+

| test |

+---------------+

| hello world |

+---------------+

1 row in set (0.00 sec)

Lpad 左填充

mysql> select concat(lpad(floor(rand()*24),2,0),‘:‘,lpad(floor(rand()*60),2,0),‘:‘,lpad(floor(rand()*60),2,0))d ;+----------+

| d |

+----------+

| 05:00:23 |

+----------+

1 row in set (0.00sec)

mysql> select concat(lpad(floor(rand()*24),2,0),‘:‘,lpad(floor(rand()*60),2,0),‘:‘,lpad(floor(rand()*60),2,0))d ;+----------+

| d |

+----------+

| 22:33:54 |

+----------+

1 row in set (0.00 sec)

rpad 右侧填充

replace 替换字符串

mysql> selectuuid();+--------------------------------------+

| uuid() |

+--------------------------------------+

| 5a87e51c-aac4-11ea-b4fc-000c295e277d |

+--------------------------------------+

1 row in set (0.01sec)

mysql> select replace(uuid(),‘-‘,‘‘);+----------------------------------+

| replace(uuid(),‘-‘,‘‘) |

+----------------------------------+

| bc8c03aeaac411eab4fc000c295e277d |

+----------------------------------+

1 row in set (0.00 sec)

数学函数

round 四舍五入

mysql> select round(10.105);+---------------+

| round(10.105) |

+---------------+

| 10 |

+---------------+

1 row in set (0.00sec)

mysql> select round(10.10569,3);+-------------------+

| round(10.10569,3) |

+-------------------+

| 10.106 |

+-------------------+

1 row in set (0.00 sec)

ceil 向上取整

mysql> select ceil(-3.12);+-------------+

| ceil(-3.12) |

+-------------+

| -3 |

+-------------+

1 row in set (0.00sec)

mysql> select ceil(3.12);+------------+

| ceil(3.12) |

+------------+

| 4 |

+------------+

1 row in set (0.00sec)

mysql> select ceil(3.00);+------------+

| ceil(3.00) |

+------------+

| 3 |

+------------+

1 row in set (0.00 sec)

floor 向下取整

mysql> select floor(3.00);+-------------+

| floor(3.00) |

+-------------+

| 3 |

+-------------+

1 row in set (0.00sec)

mysql> select floor(3.12);+-------------+

| floor(3.12) |

+-------------+

| 3 |

+-------------+

1 row in set (0.00sec)

mysql> select floor(-3.12);+--------------+

| floor(-3.12) |

+--------------+

| -4 |

+--------------+

1 row in set (0.00 sec)

truncate 截取浮点数小数点后的位数

mysql> select truncate(3.1415,3);+--------------------+

| truncate(3.1415,3) |

+--------------------+

| 3.141 |

+--------------------+

1 row in set (0.00 sec)

mod 取模

mysql> select mod(10,3);+-----------+

| mod(10,3) |

+-----------+

| 1 |

+-----------+

1 row in set (0.00sec)

mysql> select mod(-10,3);+------------+

| mod(-10,3) |

+------------+

| -1 |

+------------+

1 row in set (0.00 sec)

rand 取随机数

mysql> selectrand();+--------------------+

| rand() |

+--------------------+

| 0.9151140050172005 |

+--------------------+

1 row in set (0.00sec)

mysql> select rand()*10;+-------------------+

| rand()*10 |

+-------------------+

| 8.334071122421019 |

+-------------------+

1 row in set (0.00sec)

mysql> select floor(rand()*10);+------------------+

| floor(rand()*10) |

+------------------+

| 4 |

+------------------+

1 row in set (0.00 sec)

原文：https://www.cnblogs.com/zh-dream/p/13084082.html