跳至內容

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」