MySQL内置函数-单行函数（字符函数）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL内置函数-单行函数（字符函数），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7851字，纯文字阅读大概需要12分钟。
内容图文
字符函数
length 获取字节量
## 查看英文的字节长度
mysql> select length(‘haha‘); +----------------+ | length(‘haha‘) | +----------------+ | 4 | +----------------+ 1 row inset (0.00 sec)
## 查看中文的字节长度 mysql> select length(‘哈哈‘); +------------------+ | length(‘哈哈‘) | +------------------+ | 6 | +------------------+ 1 row inset (0.00 sec)
## 查看表中某一列的字节长度 mysql> select length(first_name) as len from employees group by len ; +-----+ | len | +-----+ | 6 | | 7 | | 5 | | 9 | | 4 | | 8 | | 10 | | 3 | | 11 | | 12 | | 13 | | 14 | +-----+ 12 rows inset (1.65 sec)
查看emoji表情的字节长度
concat函数 拼接字符串
mysql> select concat("我是","中国","人"); +---------------------------------+ | concat("我是","中国","人") | +---------------------------------+ | 我是中国人 | +---------------------------------+ 1 row inset (0.00 sec) 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 inset (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 inset (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 inset (0.00 sec) mysql> select upper(first_name) from employees limit 10; +-------------------+ | upper(first_name) | +-------------------+ | GEORGI | | BEZALEL | | PARTO | | CHIRSTIAN | | KYOICHI | | ANNEKE | | TZVETAN | | SANIYA | | SUMANT | | DUANGKAEW | +-------------------+ 10 rows inset (0.11 sec) mysql> select lower(first_name) from employees limit 10; +-------------------+ | lower(first_name) | +-------------------+ | georgi | | bezalel | | parto | | chirstian | | kyoichi | | anneke | | tzvetan | | saniya | | sumant | | duangkaew | +-------------------+ 10 rows inset (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 inset (0.00 sec) 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 inset (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 inset (0.00 sec) 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 inset (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‘) as test; +--------+ | test | +--------+ | world | +--------+ 1 row inset (0.00 sec) mysql> select trim(‘hello‘from‘ hello world‘) as test; +--------------+ | test | +--------------+ | hello world | +--------------+ 1 row inset (0.00 sec) mysql> select trim(‘world‘from‘ hello world‘) as test; +---------+ | test | +---------+ | hello | +---------+ 1 row inset (0.00 sec) mysql> select trim(‘world‘from‘ hello world ‘) as test; +---------------+ | test | +---------------+ | hello world | +---------------+ 1 row inset (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 inset (0.00 sec) 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 inset (0.00 sec)
rpad 右侧填充
replace 替换字符串
mysql> select uuid(); +--------------------------------------+ | uuid() | +--------------------------------------+ | 5a87e51c-aac4-11ea-b4fc-000c295e277d | +--------------------------------------+ 1 row inset (0.01 sec) mysql> select replace(uuid(),‘-‘,‘‘); +----------------------------------+ | replace(uuid(),‘-‘,‘‘) | +----------------------------------+ | bc8c03aeaac411eab4fc000c295e277d | +----------------------------------+ 1 row inset (0.00 sec)
数学函数
round 四舍五入
mysql> select round(10.105); +---------------+ | round(10.105) | +---------------+ | 10 | +---------------+ 1 row inset (0.00 sec) mysql> select round(10.10569,3); +-------------------+ | round(10.10569,3) | +-------------------+ | 10.106 | +-------------------+ 1 row inset (0.00 sec)
ceil 向上取整
mysql> select ceil(-3.12); +-------------+ | ceil(-3.12) | +-------------+ | -3 | +-------------+ 1 row inset (0.00 sec) mysql> select ceil(3.12); +------------+ | ceil(3.12) | +------------+ | 4 | +------------+ 1 row inset (0.00 sec) mysql> select ceil(3.00); +------------+ | ceil(3.00) | +------------+ | 3 | +------------+ 1 row inset (0.00 sec)
floor 向下取整
mysql> select floor(3.00); +-------------+ | floor(3.00) | +-------------+ | 3 | +-------------+ 1 row inset (0.00 sec) mysql> select floor(3.12); +-------------+ | floor(3.12) | +-------------+ | 3 | +-------------+ 1 row inset (0.00 sec) mysql> select floor(-3.12); +--------------+ | floor(-3.12) | +--------------+ | -4 | +--------------+ 1 row inset (0.00 sec)
truncate 截取浮点数小数点后的位数
mysql> select truncate(3.1415,3); +--------------------+ | truncate(3.1415,3) | +--------------------+ | 3.141 | +--------------------+ 1 row inset (0.00 sec)
mod 取模
mysql> select mod(10,3); +-----------+ | mod(10,3) | +-----------+ | 1 | +-----------+ 1 row inset (0.00 sec) mysql> select mod(-10,3); +------------+ | mod(-10,3) | +------------+ | -1 | +------------+ 1 row inset (0.00 sec)
rand 取随机数
mysql> select rand(); +--------------------+ | rand() | +--------------------+ | 0.9151140050172005 | +--------------------+ 1 row inset (0.00 sec) mysql> select rand()*10; +-------------------+ | rand()*10 | +-------------------+ | 8.334071122421019 | +-------------------+ 1 row inset (0.00 sec) mysql> select floor(rand()*10); +------------------+ | floor(rand()*10) | +------------------+ | 4 | +------------------+ 1 row inset (0.00 sec)
原文：https://www.cnblogs.com/zh-dream/p/13084082.html
内容总结
以上是互联网集市为您收集整理的MySQL内置函数-单行函数（字符函数）全部内容，希望文章能够帮你解决MySQL内置函数-单行函数（字符函数）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】