字符函数
length 获取字节量
## 查看英文的字节长度
mysql> select length(‘haha‘);
+----------------+
| length(‘haha‘) |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
## 查看中文的字节长度
mysql> select length(‘哈哈‘);
+------------------+
| length(‘哈哈‘) |
+------------------+
| 6 |
+------------------+
1 row in set (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 in set (1.65 sec)
查看emoji表情的字节长度
concat函数 拼接字符串
mysql> select concat("我是","中国","人");
+---------------------------------+
| concat("我是","中国","人") |
+---------------------------------+
| 我是中国人 |
+---------------------------------+
1 row in set (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 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.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 in set (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 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.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 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.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 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‘) as test;
+--------+
| test |
+--------+
| world |
+--------+
1 row in set (0.00 sec)
mysql> select trim(‘hello‘ from ‘ hello world‘) as test;
+--------------+
| test |
+--------------+
| hello world |
+--------------+
1 row in set (0.00 sec)
mysql> select trim(‘world‘ from ‘ hello world‘) as test;
+---------+
| test |
+---------+
| hello |
+---------+
1 row in set (0.00 sec)
mysql> select trim(‘world‘ from ‘ hello world ‘) as test;
+---------------+
| 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.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 in set (0.00 sec)
rpad 右侧填充
replace 替换字符串
mysql> select uuid();
+--------------------------------------+
| uuid() |
+--------------------------------------+
| 5a87e51c-aac4-11ea-b4fc-000c295e277d |
+--------------------------------------+
1 row in set (0.01 sec)
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.00 sec)
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.00 sec)
mysql> select ceil(3.12);
+------------+
| ceil(3.12) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
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.00 sec)
mysql> select floor(3.12);
+-------------+
| floor(3.12) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
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.00 sec)
mysql> select mod(-10,3);
+------------+
| mod(-10,3) |
+------------+
| -1 |
+------------+
1 row in set (0.00 sec)
rand 取随机数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.9151140050172005 |
+--------------------+
1 row in set (0.00 sec)
mysql> select rand()*10;
+-------------------+
| rand()*10 |
+-------------------+
| 8.334071122421019 |
+-------------------+
1 row in set (0.00 sec)
mysql> select floor(rand()*10);
+------------------+
| floor(rand()*10) |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)
MySQL内置函数-单行函数（字符函数）