跳转到内容

MySQL/String

添加链接
维基教科书，自由的教学读本

group_concat

[编辑]
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

将分组中要连接的字段对应的多行的值按照排序字段升序或者降序进行连接。如果用到了DISTINCT，表示不重复的column1的取值。如果没有指定分隔符，默认以 ','分隔。

GROUP_CONCAT会忽略要连接的字段的NULL值。

lower

[编辑]

LOWER(column|str)：将字符串参数值转换为全小写字母后返回。LCASE() 是 LOWER() 的同义词 

mysql> select lower('SQL Course');
+---------------------+
| lower('SQL Course') |
+---------------------+
| sql course          |
+---------------------+

upper与UCASE

[编辑]

UPPER(column|str)：将字符串参数值转换为全大写字母后返回

UCASE() 是UPPER() 的同义词 

mysql> select upper('Use MYsql');
+--------------------+
| upper('Use MYsql') |
+--------------------+
| USE MYSQL          |
+--------------------+

函数CONV(N from_base, to_base)

[编辑]

所述CONV()函数的目的是在基数之间进行转换。该函数返回值N从from_base到to_base转换的字符串。最小基数值是2，最大值为36。如果任一参数为NULL，则该函数返回NULL。考虑下面的例子，其中数字 5 将从基数16转为基数2 ： 

SELECT CONV(13,16,2);
10011(13|16进制转换成2进制)
SELECT CONV(13,10,2);
1101
SELECT CONV(13,8,2);
1101

函数 HEX(N_or_S )与函数 UNHEX(str)

[编辑]

函数HEX(N_or_S )：如果N_OR_S 是一个数字，则返回一个 十六进制值 N 的 字符串表示，在这里， N 是一个longlong (BIGINT) 数。这相当于 CONV(N,10,16) 。如果N_OR_S 是一个字符串，则返回值为一个N_OR_S 的十六进制字符串表示，其中每个N_OR_S 里的每个字符被转化为两个十六进制数字。

函数 UNHEX(str)：执行从 HEX(str ) 的反向操作。就是说，它将参数中的每一对十六进制数字理解为一个数字，并将其转化为该数字代表的字符。结果字符以二进制字符串的形式返回 

select HEX('abc')  
616263
select UNHEX('616263')   
abc
select HEX(16)
10

select HEX('你好')
E4BDA0E5A5BD
select 0xE4BDA0E5A5BD 
你好
SELECT UNHEX('E4BDA0E5A5BD') 
你好

concat

[编辑]

CONCAT(column|str1, column|str2,...)：将多个字符串参数首尾相连后返回 

mysql> select concat('My','S','QL');
+-----------------------+
| concat('My','S','QL') |
+-----------------------+
| MySQL                 |
+-----------------------+

如果有任何参数为null，则函数返回null 

mysql> select concat('My',null,'QL');
+------------------------+
| concat('My',null,'QL') |
+------------------------+
| NULL                   |
+------------------------+

如果参数是数字，则自动转换为字符串。如果所有参数均为非二进制字符串，则结果为非二进制字符串。 如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况，可使用显式类型 cast, 例如：SELECT CONCAT(CAST(int_col AS CHAR), char_col) 

mysql> select concat(14.3,'mysql');
+----------------------+
| concat(14.3,'mysql') |
+----------------------+
| 14.3mysql            |
+----------------------+

SELECT CONCAT('My', 'S', 'QL');
MySQL
SELECT CONCAT('My', null, 'QL');
null
SELECT CONCAT(14.321);
14.321

concat_ws

[编辑]

CONCAT_WS(separator,str1,str2,...)：将多个字符串参数以给定的分隔符separator首尾相连后返回。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL ，则结果为 NULL 。函数会忽略任何分隔符参数后的 NULL 值。 

mysql> select concat_ws(';','First name','Second name','Last name');
+-------------------------------------------------------+
| concat_ws(';','First name','Second name','Last name') |
+-------------------------------------------------------+
| First name;Second name;Last name                      |
+-------------------------------------------------------+

也就是函数圆括号里的第一个项目用来指定分隔符

注意：如果有任何参数为null，则函数不返回null，而是直接忽略它 

mysql> select concat_ws(',','id',null,'name');
+---------------------------------+
| concat_ws(',','id',null,'name') |
+---------------------------------+
| id,name                         |
+---------------------------------+


打开和关闭管道符号“||”的连接功能。PIPES_AS_CONCAT：将“||”视为字符串的连接操作符而非或运算符

基本格式： 　mysql> select 列名1 || 列名2 || 列名3 from 表名;

在mysql中，进行上式连接查询之后，会将查询结果集在一列中显示(字符串连接)，列名是‘列名1 || 列名2 || 列名3’； 

mysql> select s_no || s_name || s_age
   -> from student;
+-------------------------+
| s_no || s_name || s_age |
+-------------------------+
| 1001张三23              |
| 1002李四19              |
| 1003马五20              |
| 1004甲六17              |
| 1005乙七22              |
+-------------------------+

注意：

  1. 如果不显示结果，是因为sql_mode参数中没有PIPES_AS_CONCAT，只要给sql_mode参数加入PIPES_AS_CONCAT，就可以实现像CONCAT一样的功能；
  2. 如果不给sql_mode参数加入PIPES_AS_CONCAT的话，|| 默认是or的意思，查询结果是一列显示是1。

left

[编辑]
mysql> select left('example.com', 3);
+-------------------------+
| left('example.com', 3) |
+-------------------------+
| exa                     |
+-------------------------+
[编辑]
mysql> select right('example.com', 3);
+--------------------------+
| right('example.com', 3) |
+--------------------------+
| com                      |
+--------------------------+

substr

[编辑]

SUBSTR(str,pos[,len])：从源字符串str中的指定位置pos开始取一个字串并返回。

SUBSTRING(str FROM pos ) , SUBSTRING(str FROM pos FOR len ) : 函数使用说明：不带有len 参数的格式从字符串str 返回一个子字符串，起始于位置 pos 。带有len 参数的格式从字符串str 返回一个长度同len 字符相同的子字符串，起始于位置 pos 。 使用 FROM 的格式为标准 SQL 语法。也可能对pos 使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

注意：

  1. len指定子串的长度，如果省略则一直取到字符串的末尾；len为负值表示从源字符串的倒数字符开始取起。
  2. 函数SUBSTR()、mid()是函数SUBSTRING()的同义词。
mysql> select substring('hello world',5);
+----------------------------+
| substring('hello world',5) |
+----------------------------+
| o world                    |
+----------------------------+

mysql> select substr('hello world',5,3);
+---------------------------+
| substr('hello world',5,3) |
+---------------------------+
| o w                       |
+---------------------------+

mysql> select substr('hello world',-5);
+--------------------------+
| substr('hello world',-5) |
+--------------------------+
| world                    |
+--------------------------+

select substring('example.com', 4);
mple.com
select substring('example.com', 4, 2);
mp
select substring('example.com', -4);
.com
select substring('example.com', -4, 2);
.c

substring_index(str,delim,count)

[编辑]

count参数可以为负，表示倒数的第几个分界符。如果在字符串中找不到 delim 参数指定的值，就返回整个字符串。在定界符 delim 以及count 出现前，从字符串str 返回自字符串。若count 为正值, 则返回最终定界符( 从左边开始) 左边的一切内容。若count 为负值，则返回定界符（从右边开始）右边的一切内容。 

mysql> select substring_index('www.example.com', '.', 2);
+------------------------------------------------+
| substring_index('www.example.com', '.', 2) |
+------------------------------------------------+
| www.example                               |
+------------------------------------------------+

select substring_index('www.example.com', '.', -2);
example.com
select substring_index('www.example.com', '.coc', 1);
www.example.com

length

[编辑]

LENGTH(str)：返回字符串的存储长度。单位为字节。一个多字节字符算作多字节。这意味着 对于一个包含 5 个 2 字节字符的字符串， LENGTH() 的返回值为 10, 而 CHAR_LENGTH() 的返回值则为5 。OCTET_LENGTH() 是 LENGTH() 的同义词。

  • length: 是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符
  • char_length:不管汉字还是数字或者是字母都算是一个字符
mysql> select length('text'),length('你好');
+----------------+------------------+
| length('text') | length('你好')   |
+----------------+------------------+
|              4 |                6 |
+----------------+------------------+

注意：编码方式不同字符串的存储长度就不一样(‘你好’:utf8是6，gbk是4)


char_length

[编辑]

CHAR_LENGTH(str)：返回字符串中的字符个数。一个多字节字符算作一个单字符。对于一个 包含五个二字节字符集 , LENGTH() 返回值为 10, 而 CHAR_LENGTH() 的返回值为 5

函数 CHARACTER_LENGTH(str ) 是 CHAR_LENGTH() 的同义词。 

mysql> select char_length('text'),char_length('你好');
+---------------------+-----------------------+
| char_length('text') | char_length('你好')   |
+---------------------+-----------------------+
|                   4 |                     2 |
+---------------------+-----------------------+


instr

[编辑]

INSTR(str, substr)：从源字符串str中返回子串substr第一次出现的位置 

mysql> select instr('foobarbar','bar');
+--------------------------+
| instr('foobarbar','bar') |
+--------------------------+
|                        4 |
+--------------------------+


lpad

[编辑]

LPAD(str, len, padstr)：在源字符串的左边填充给定的字符padstr，使字符串的总长度达到指定的长度len，返回填充后的字符串 

mysql> select lpad('hi',5,'??');
+-------------------+
| lpad('hi',5,'??') |
+-------------------+
| ???hi             |
+-------------------+


rpad

[编辑]

RPAD(str, len, padstr)：在源字符串的右边填充给定的字符padstr到指定的长度len，返回填充后的字符串 

mysql> select rpad('hi',6,'??');
+-------------------+
| rpad('hi',6,'??') |
+-------------------+
| hi????            |
+-------------------+

trim

[编辑]

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)：

从源字符串str中去掉两端、前缀或后缀字符remstr并返回；如果不指定remstr，则去掉str两端的空格；不指定BOTH、LEADING、TRAILING ，则默认为 BOTH。 

mysql> select trim('  bar  ');
+-----------------+
| trim('  bar  ') |
+-----------------+
| bar             |
+-----------------+

mysql> select trim(leading 'x' from 'xxxbarxxx');
+------------------------------------+
| trim(leading 'x' from 'xxxbarxxx') |
+------------------------------------+
| barxxx                             |
+------------------------------------+

mysql> select trim(both 'x' from 'xxxbarxxx');
+---------------------------------+
| trim(both 'x' from 'xxxbarxxx') |
+---------------------------------+
| bar                             |
+---------------------------------+

mysql> select trim(trailing 'xyz' from 'barxxyz');
+-------------------------------------+
| trim(trailing 'xyz' from 'barxxyz') |
+-------------------------------------+
| barx                                |
+-------------------------------------+

replace

[编辑]

REPLACE(str, from_str, to_str)：在源字符串str中查找所有的子串from_str（大小写敏感），找到后使用替代字符串to_str替换它。返回替换后的字符串 

mysql> select replace('www.mysql.com','w','Ww');
+-----------------------------------+
| replace('www.mysql.com','w','Ww') |
+-----------------------------------+
| WwWwWw.mysql.com                  |
+-----------------------------------+

ltrim、rtrim

[编辑]

LTRIM(str)，RTRIM(str)：去掉字符串的左边或右边的空格(左对齐、右对齐) 

mysql> SELECT  ltrim('   barbar   ') rs1, rtrim('   barbar   ') rs2;
+-----------+-----------+
| rs1       | rs2       |
+-----------+-----------+
| barbar    |    barbar |
+-----------+-----------+

repeat

[编辑]

REPEAT(str, count)：将字符串str重复count次后返回。返回一个由重复的字符串str 组成的字符串，字符串str 的数目等于count 。 若 count <= 0, 则返回一个空字符串。若str 或 count 为 NULL ，则返回 NULL 。 

mysql> select repeat('MySQL',3);
+-------------------+
| repeat('MySQL',3) |
+-------------------+
| MySQLMySQLMySQL   |
+-------------------+

reverse

[编辑]

REVERSE(str)：将字符串str反转后返回 

mysql> select reverse('abcdef');
+-------------------+
| reverse('abcdef') |
+-------------------+
| fedcba            |
+-------------------+

char

[编辑]

CHAR(N,... [USING charset_name])：将每个参数N解释为整数（字符的编码），并返回每个整数对应的字符所构成的字符串(NULL值被忽略)。 

mysql> select char(77,121,83,81,'76'),char(77,77.3,'77.3');
+-------------------------+----------------------+
| char(77,121,83,81,'76') | char(77,77.3,'77.3') |
+-------------------------+----------------------+
| MySQL                   | MMM                  |
+-------------------------+----------------------+

默认情况下，函数返回二进制字符串，若想返回针对特定字符集的字符串，使用using选项 

mysql> SELECT charset(char(0x65)), charset(char(0x65 USING utf8));
+---------------------+--------------------------------+
| charset(char(0x65)) | charset(char(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary              | utf8                           |
+---------------------+--------------------------------+

format

[编辑]

FORMAT(X,D[,locale])：以格式‘#,###,###.##’格式化数字X。D指定小数位数，locale指定国家语言(默认的locale为en_US)。以四舍五入的方式保留到小数点后 D 位 , 而返回结果为一个字符串。 


mysql> SELECT format(12332.123456, 4),format(12332.2,0);
+-------------------------+-------------------+
| format(12332.123456, 4) | format(12332.2,0) |
+-------------------------+-------------------+
| 12,332.1235             | 12,332            |
+-------------------------+-------------------+

mysql> SELECT format(12332.2,2,'de_DE');
+---------------------------+
| format(12332.2,2,'de_DE') |
+---------------------------+
| 12.332,20                 |
+---------------------------+


space

[编辑]

SPACE(N)：返回由N个空格构成的字符串 

mysql> select space(3);
+----------+
| space(3) |
+----------+
|          |
+----------+

left

[编辑]

LEFT(str, len)：返回最左边的len长度的子串 

mysql> select left('chinaitsoft',5);
+-----------------------+
| left('chinaitsoft',5) |
+-----------------------+
| china                 |
+-----------------------+

right

[编辑]

RIGHT(str, len)：返回最右边的len长度的子串 

mysql> select right('chinaitsoft',5);
+------------------------+
| right('chinaitsoft',5) |
+------------------------+
| tsoft                  |
+------------------------+


strcmp

[编辑]

STRCMP(expr1,expr2)：如果两个字符串是一样的则返回0；如果第一个小于第二个则返回-1；否则返回1 

mysql> select strcmp('text','text');
+-----------------------+
| strcmp('text','text') |
+-----------------------+
|                     0 |
+-----------------------+

mysql> SELECT strcmp('text', 'text2'),strcmp('text2', 'text');
+-------------------------+-------------------------+
| strcmp('text', 'text2') | strcmp('text2', 'text') |
+-------------------------+-------------------------+
|                      -1 |                       1 |
+-------------------------+-------------------------+

函数 COMPRESS、UNCOMPRESS、UNCOMPRESSED_LENGTH

[编辑]

函数COMPRESS(string_to_compress )使用说明： COMPRESS( 压缩一个字符串。这个函数要求 MySQL 已经用一个诸如 zlib 的压缩库压缩过。 否则，返回值始终是 NULL 。 UNCOMPRESS() 可将压缩过的字符串进行解压缩 ) 。

函数UNCOMPRESS(string_to_uncompress )使用说明：对经COMPRESS() 函数压缩后的字符串进行解压缩。若参数为压缩值，则结果为 NULL 。这个函数要求 MySQL 已被诸如zlib 之类的压缩库编译过。否则, 返回值将始终是 NULL

函数 UNCOMPRESSED_LENGTH(compressed_string )使用说明： 返回压缩字符串压缩前的长度。

函数 ascii(str)

[编辑]

返回值为字符串 str 的最左字符的数值。假如 str 为空字符串，则返回值为 0 。假如 str 为 NULL ，则返回值为 NULL 。 ASCII() 用于带有从 0 到 255 的数值的字符。 

select ascii('M');  
77
select ascii('你');  
228

函数 BIN(N)

[编辑]

函数用法说明：返回值为 N 的二进制值的字符串表示，其中 N 为一个 longlong (BIGINT) 数字。这等同于 CONV(N ,10,2) 。假如 N 为 NULL ，则返回值为 NULL 。 

select BIN(7);  
111

函数 ELT(N ,str1 ,str2 ,str3 ,…)

[编辑]

函数使用说明：若 N = 1 ，则返回值为 str1 ，若 N = 2 ，则返回值为 str2 ，以此类推。 若 N 小于 1 或大于参数的数目，则返回值为 NULL 。 ELT() 是 FIELD() 的补数 

SELECT ELT(2, 'ej', 'Heja', 'hej', 'foo');
Heja
SELECT ELT(5, 'ej', 'Heja', 'hej', 'foo');
null

函数 EXPORT_SET(bits ,on ,off [,separator [,number_of_bits ]])

[编辑]

按位取值返回相应的串 

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

按bits排列字符串集,只有当位等于1时插入字串on,否则插入off(separator默认值”,”,number_of_bits参数使用时长度不足补0而过长截断)

  • bits,传入的数值
  • on,位值为1的时候返回的字符串
  • off,位值为0的时候返回的字符串
  • separator 分隔符
  • number_of_bits 总位数
SELECT EXPORT_SET(5,'Y','N',',',4); -> 'Y,N,Y,N' (5在二进制中为0101,对应的0替换为N,1替换为1,用分隔符',') 
SELECT EXPORT_SET( 5, 'aa', 'bb', ',', 4 ) -> aa,bb,aa,bb 
select EXPORT_SET(5,'Y','N',',',4)

　　-> Y,N,Y,N 

SELECT EXPORT_SET(6,'1','0',',',10); -> '0,1,1,0,0,0,0,0,0,0'

函数 FIELD(str, str1, str2, str3, ……)

[编辑]

函数使用说明：返回值为 str1 , str2 , str3 ,…… 列表中的 str 指数。在找不到 str 的情况下，返回值为 0 。如果所有对于 FIELD() 的参数均为字符串，则所有参数均按照字符串进行比较。如果所有的参数均为数字，则按照数字进行比较。否则，参数按照双倍进行比较。如果 str 为NULL ，则返回值为 0 ，原因是 NULL 不能同任何值进行同等比较。 FIELD() 是 ELT() 的补数。 

select FIELD('1','2','3','4','1')
4
select FIELD('2','2','3','4','1')
1

根据自定义status顺序进行排序 

select DISTINCT status from orders
Shipped
Resolved
Cancelled
On Hold
Disputed
In Process

SELECT 
   status
FROM
   orders
ORDER BY FIELD(status,
       'In Process',
       'On Hold',
       'Cancelled',
       'Resolved',
       'Disputed',
       'Shipped');

函数FIND_IN_SET(str, strlist)

[编辑]

函数使用说明： 假如字符串 str 在由 N 子链组成的字符串列表 strlist 中， 则返回值的范围在 1 到 N 之间 。一个字符串列表就是一个由一些被 ‘,’ 符号分开的自链组成的字符串。如果第一个参数是一个常数字符串，而第二个是 type SET 列，则 FIND_IN_SET() 函数被优化，使用比特计算。如果 str 不在 strlist 或 strlist 为空字符串，则返回值为 0 。如任意一个参数为 NULL ，则返回值为 NULL 。 这个函数在第一个参数包含一个逗号 (‘,’) 时将无法正常运行。 

select FIND_IN_SET('1', '11,22,1') 
3
select FIND_IN_SET('22', '11,22,1') 
2

函数INSTR(str,substr)

[编辑]

函数使用说明：返回字符串 str 中子字符串的第一个出现位置。这和LOCATE() 的双参数形式相同，除非参数的顺序被颠倒。 

select instr('www.111cn.net','.') 
4
select instr('www.111cn.net','1') 
5

函数 LOAD_FILE(file_name)

[编辑]

读取文件并将这一文件按照字符串的格式返回。 文件的位置必须在服务器上 , 你必须为文件制定路径全名，而且你还必须拥有 FILE 特许权。文件必须可读取，文件容量必须小于 max_allowed_packet 字节。若文件不存在，或因不满足上述条件而不能被读取， 则函数返回值为 NULL

函数 LOCATE(substr ,str ) , LOCATE(substr ,str ,pos )

[编辑]

函数使用说明：第一个语法返回字符串 str 中子字符串substr 的第一个出现位置。第二个语法返回字符串 str 中子字符串substr 的第一个出现位置, 起始位置在pos 。如若substr 不在str 中，则返回值为0 。POSITION(substr IN str ) 是 LOCATE(substr ,str ) 同义词。 

SELECT LOCATE('bar', 'foobarbar')   
4
SELECT LOCATE('baar', 'foobarbar')  
0
SELECT LOCATE('bar', 'foobarbar', 5)
7

函数 MAKE_SET(bits ,str1 ,str2 ,…)

[编辑]

函数使用说明： 返回一个设定值 ( 一个包含被 ‘,’ 号分开的字字符串的字符串 ) ，由在 bits 组中具有相应的比特的字符串组成。 str1 对应比特 0, str2 对应比特 1, 以此类推。 str1 , str2 , … 中的 NULL 值不会被添加到结果中。 

SELECT MAKE_SET(6 | 3,'hello','nice','world','aaaa','bbbb')
0110 0111 0111
hello,nice,world

函数 OCT(N )

[编辑]

函数使用说明：返回一个 N 的八进制值的字符串表示，其中 N 是一个 longlong (BIGINT) 数。这等同于 CONV(N,10,8) 。若 N 为 NULL ，则返回值为 NULL 。 

select OCT(10)
12
select OCT(null)
空

函数ORD(str )

[编辑]

ORD() 函数返回字符串第一个字符的 ASCII 值。 

SELECT ORD('i');
105
SELECT ASCII('i');
105
SELECT ORD('NowaMagic');
78
SELECT ORD('简明现代魔法');
15183488 
SELECT ORD('简');
15183488

函数QUOTE(str )

[编辑]

函数使用说明：引证一个字符串，由此产生一个在SQL 语句中可用作完全转义数据值的结果。 返回的字符串由单引号标注，每例都带有单引号 (‘’’) 、 反斜线符号 (‘\’) 、 ASCII NUL 以及前面有反斜线符号的Control-Z 。如果自变量的值为NULL, 则返回不带单引号的单词“NULL” 。 

select quote("12!/\3");
'12!/3'

函数 SOUNDEX(str )

[编辑]

函数使用说明：从str 返回一个soundex 字符串。 两个具有几乎同样探测的字符串应该具有同样的 soundex 字符串。一个标准的soundex 字符串的长度为4 个字符，然而SOUNDEX() 函数会返回一个人以长度的字符串。 可使用结果中的SUBSTRING() 来得到一个标准 soundex 字符串。在str 中， 会忽略所有未按照字母顺序排列的字符。所有不在A-Z 范围之内的国际字母符号被视为元音字母。 

select SOUNDEX('HHwello')
H400

函数expr1 SOUNDS LIKE expr2

[编辑]

函数使用说明： 这相当于SOUNDEX(expr1 ) = SOUNDEX(expr2 ) 。

检索自“https://zh.wikibooks.org/w/index.php?title=MySQL/String&oldid=173802”