MySQL常见字符函数简介

矫情吗；* 2022-04-12 12:48 313阅读 0赞

<table> 
 <thead> 
  <tr> 
   <th>函数名</th> 
   <th>用途简介</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>bin()</td> 
   <td>返回数字的二进制表示</td> 
  </tr> 
  <tr> 
   <td>bit_length()</td> 
   <td>返回字符的字长度，bit</td> 
  </tr> 
  <tr> 
   <td>char_length()</td> 
   <td>返回字符个数</td> 
  </tr> 
  <tr> 
   <td>concat()</td> 
   <td>字符连接函数</td> 
  </tr> 
  <tr> 
   <td>concat_ws()</td> 
   <td>字符连接函数，可以指定分割符，concat的变种</td> 
  </tr> 
  <tr> 
   <td>hex()</td> 
   <td>返回十六进制表示</td> 
  </tr> 
  <tr> 
   <td>insert()</td> 
   <td>在字符串指定位置插入内容</td> 
  </tr> 
  <tr> 
   <td>left()</td> 
   <td>返回字符串左侧指定的部分</td> 
  </tr> 
  <tr> 
   <td>length()</td> 
   <td>返回字符串字节长度</td> 
  </tr> 
  <tr> 
   <td>locate()</td> 
   <td>定位字符位置</td> 
  </tr> 
  <tr> 
   <td>lower()</td> 
   <td>将字符转换为小写，对二进制字符串无效</td> 
  </tr> 
  <tr> 
   <td>lpad()</td> 
   <td>在字符串左侧填充指定字符至指定长度</td> 
  </tr> 
  <tr> 
   <td>ltrim()</td> 
   <td>移除左侧开头的空格</td> 
  </tr> 
  <tr> 
   <td>repeat()</td> 
   <td>将字符串重复指定次数</td> 
  </tr> 
  <tr> 
   <td>replace()</td> 
   <td>将字符替换为指定内容</td> 
  </tr> 
  <tr> 
   <td>reverse()</td> 
   <td>将字符串反转</td> 
  </tr> 
  <tr> 
   <td>right()</td> 
   <td>返回字符串右侧指定的部分，类似left()</td> 
  </tr> 
  <tr> 
   <td>rpad()</td> 
   <td>在字符串右侧填充指定字符至指定长度，类似lpad</td> 
  </tr> 
  <tr> 
   <td>rtrim()</td> 
   <td>移除字符串右侧空格</td> 
  </tr> 
  <tr> 
   <td>space()</td> 
   <td>返回字符串的空格数量</td> 
  </tr> 
  <tr> 
   <td>strcmp()</td> 
   <td>比较两个字符串大小，相等返回0，第一个参数较小返回-1，第一个参数较大返回1</td> 
  </tr> 
  <tr> 
   <td>subtring()</td> 
   <td>返回指定的子串</td> 
  </tr> 
  <tr> 
   <td>trim()</td> 
   <td>移除字符串前后空格</td> 
  </tr> 
  <tr> 
   <td>index_substring</td> 
   <td>按照分隔符返回指定部分</td> 
  </tr> 
 </tbody> 
</table>

1.bin()

mysql> select bin(12);
    +---------+
    | bin(12) |
    +---------+
    | 1100    |
    +---------+
    1 row in set (0.00 sec)

2.bit\_length

mysql> select bit_length('星');
    +-------------------+
    | bit_length('星')  |
    +-------------------+
    |                24 |
    +-------------------+
    1 row in set (0.00 sec)

3.char\_length

mysql> select char_length('MySQL');
    +----------------------+
    | char_length('MySQL') |
    +----------------------+
    |                    5 |
    +----------------------+
    1 row in set (0.00 sec)

4.concat  
语法：CONCAT(str1,str2,…)  
用法：将需要连接的字符用逗号隔开，如果存在null，则返回null。

mysql> select concat('MySQL',' ','is a relational database');
    +------------------------------------------------+
    | concat('MySQL',' ','is a relational database') |
    +------------------------------------------------+
    | MySQL is a relational database                 |
    +------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select concat('MySQL',null);
    +----------------------+
    | concat('MySQL',null) |
    +----------------------+
    | NULL                 |
    +----------------------+
    1 row in set (0.00 sec)

5.concat\_ws  
语法：CONCAT\_WS(separator,str1,str2,…)  
用法：concat的变种，separator指定间隔符号，会跳过null。

mysql> select concat_ws(',','MySQL','relational database');
    +----------------------------------------------+
    | concat_ws(',','MySQL','relational database') |
    +----------------------------------------------+
    | MySQL,relational database                    |
    +----------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select concat_ws(',','MySQL',null,'relational database');  
    +---------------------------------------------------+
    | concat_ws(',','MySQL',null,'relational database') |
    +---------------------------------------------------+
    | MySQL,relational database                         |
    +---------------------------------------------------+
    1 row in set (0.00 sec)

6.insert()  
语法：INSERT(str,pos,len,newstr)  
用法：对于str字符串，从pos到len的位置，用newstr来替代。如果pos不在str长度范围，则不进行替换；如果len超过str从pos起的剩余长度，则从pos往后的所有部分都替换为newstr；如果任何参数是null，则返回null。

mysql> select insert('MySQL',2,2,'sam'); 
    +---------------------------+
    | insert('MySQL',2,2,'sam') |
    +---------------------------+
    | MsamQL                    |
    +---------------------------+
    1 row in set (0.00 sec)
    
    mysql> select insert('MySQL',-1,2,'sam');
    +----------------------------+
    | insert('MySQL',-1,2,'sam') |
    +----------------------------+
    | MySQL                      |
    +----------------------------+
    1 row in set (0.00 sec)
    
    mysql> select insert('MySQL',-1,null,'sam'); 
    +-------------------------------+
    | insert('MySQL',-1,null,'sam') |
    +-------------------------------+
    | NULL                          |
    +-------------------------------+
    1 row in set (0.00 sec)

7.left()  
语法：LEFT(str,len)  
用法：返回str左右len长度的部分。

8.length()

mysql> select length('MySQL');
    +-----------------+
    | length('MySQL') |
    +-----------------+
    |               5 |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> select length('星');
    +---------------+
    | length('星')  |
    +---------------+
    |             3 |
    +---------------+
    1 row in set (0.00 sec)

9.locate()  
语法：LOCATE(substr,str), LOCATE(substr,str,pos)  
用法：第一个返回substr在str第一次出现的位置，第二个返回str从pos开始，substr第一次出现的位置。如果substr不存在str中，则返回0；如果任何参数是null，则返回null。

mysql> select locate('SQL','MySQLSQL');     
    +--------------------------+
    | locate('SQL','MySQLSQL') |
    +--------------------------+
    |                        3 |
    +--------------------------+
    1 row in set (0.00 sec)
    
    mysql> select locate('SQL','MySQLSQL',5);
    +----------------------------+
    | locate('SQL','MySQLSQL',5) |
    +----------------------------+
    |                          6 |
    +----------------------------+
    1 row in set (0.00 sec)

10.lower()  
用法：将字符串转换为小写，需要注意对二进制字符串无效。

mysql> select lower('MySQL');
    +----------------+
    | lower('MySQL') |
    +----------------+
    | mysql          |
    +----------------+
    1 row in set (0.00 sec)
    
    mysql> set @str=binary 'MySQL';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select lower(@str),lower(convert(@str using utf8mb3));
    +-------------+------------------------------------+
    | lower(@str) | lower(convert(@str using utf8mb3)) |
    +-------------+------------------------------------+
    | MySQL       | mysql                              |
    +-------------+------------------------------------+
    1 row in set (0.00 sec)

11.lpad()  
语法：LPAD(str,len,padstr)  
用法：在str左侧填充padstr至len长度，如果len小于str长度，则将str截断至len长度并返回。

12.ltrim()

mysql> select ltrim('  MySQL');
    +------------------+
    | ltrim('  MySQL') |
    +------------------+
    | MySQL            |
    +------------------+
    1 row in set (0.00 sec)

13.repeat()

14.repeat()  
语法：REPLACE(str,from\_str,to\_str)  
此函数区分大小写。

mysql> select replace('MySQL','M','m');
    +--------------------------+
    | replace('MySQL','M','m') |
    +--------------------------+
    | mySQL                    |
    +--------------------------+
    1 row in set (0.00 sec)

15.substring()  
语法：SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),  
SUBSTRING(str FROM pos FOR len)  
用法：第一个从pos位置开始提取str的子串，第二个跟第一个相同，第三个从pos开始从str提取长度为len的子串，第四个跟第三个相同。

mysql> select substring('MySQL',2);
    +----------------------+
    | substring('MySQL',2) |
    +----------------------+
    | ySQL                 |
    +----------------------+
    1 row in set (0.00 sec)
    
    mysql> select substring('MySQL',2,2);
    +------------------------+
    | substring('MySQL',2,2) |
    +------------------------+
    | yS                     |
    +------------------------+
    1 row in set (0.00 sec)
    
    mysql> select substring('MySQL',-2,2);
    +-------------------------+
    | substring('MySQL',-2,2) |
    +-------------------------+
    | QL                      |
    +-------------------------+
    1 row in set (0.00 sec)

16.trim()  
语法：TRIM(\[\{BOTH | LEADING | TRAILING\} \[remstr\] FROM\] str), TRIM(\[remstr FROM\]  
str)  
用法：both指字符串两端，leading指字符串左侧，trailing指字符串右侧。

mysql> select trim(leading 'c' from 'cbabc');      
    +--------------------------------+
    | trim(leading 'c' from 'cbabc') |
    +--------------------------------+
    | babc                           |
    +--------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select trim(trailing 'c' from 'cbabc');       
    +---------------------------------+
    | trim(trailing 'c' from 'cbabc') |
    +---------------------------------+
    | cbab                            |
    +---------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select trim(both 'c' from 'cbabc');        
    +-----------------------------+
    | trim(both 'c' from 'cbabc') |
    +-----------------------------+
    | bab                         |
    +-----------------------------+
    1 row in set (0.00 sec)