測試環境為 CentOS 8 x86_64 虛擬機.
- 字串函數 ( String Functions ) 與 型別轉換 – 這邊介紹.
- 數字函數 (Numeric Functions) – http://benjr.tw/101970
- 日期與時間函數 (Date & Time Functions) – http://benjr.tw/102964
- 資訊函數 (Information Functions) – http://benjr.tw/102961
- 搭配 GROUP BY 函數 (Aggregate Functions) – http://benjr.tw/102967
- 流程控制函數 (Control Flow Functions) – http://benjr.tw/102747
先建立一個測試用資料庫 (testdb) , 與 tables (employee) ,下面範例會使用到 . 格式為 K1 int(11) – auto_increment & PRIMARY KEY , Name char(20), Dept char(20), jobTitle char(20) ,email char(30) 以及 Salary int(11).
SELECT 查詢需注意資料庫字元編碼 Character Sets 與 文字排序 Collations ,請參考資料庫編碼 – http://benjr.tw/102156 .
[root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.3.11-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.001 sec) MariaDB [(none)]> USE testdb; Database changed MariaDB [testdb]> CREATE TABLE employee (K1 int(11) NOT NULL auto_increment, Name char(20) NOT NULL, Dept char(20) NOT NULL, JobTitle char(20) NOT NULL, Salary int(11) NOT NULL , email char(30) NOT NULL, PRIMARY KEY (K1)) ; Query OK, 0 rows affected (0.010 sec) MariaDB [testdb]> DESCRIBE employee; +----------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+----------------+ | K1 | int(11) | NO | PRI | NULL | auto_increment | | Name | char(20) | NO | | NULL | | | Dept | char(20) | NO | | NULL | | | JobTitle | char(20) | NO | | NULL | | | Salary | int(11) | NO | | NULL | | | email | char(30) | NO | | NULL | | +----------+----------+------+-----+---------+----------------+ 6 rows in set (0.004 sec)
透過 INSERT 新增加了五筆資料.
MariaDB [testdb]> INSERT INTO employee (Name , Dept , JobTitle , Salary , email) VALUES ('Ben' , 'Testing' , 'Engineer' , '45000' , 'ben@benjr.tw') , ('Afa' , 'Power' , 'Engineer' , '48000' , 'Afa@benjr.tw') , ('Boss' ,'Testing' , 'Manager' , '75000' , 'Boss@benjr.tw') , ('JoJo' , 'Testing' , 'Engineer' , '45000' , 'jojo@benjr.tw') , ('Ian' , 'PM' , 'Engineer' , '85000' , 'ian@benjr.tw');
Query OK, 5 rows affected (0.002 sec)
Records: 5 Duplicates: 0 Warnings: 0
MariaDB [testdb]> SELECT * FROM employee;
+----+------+---------+----------+--------+---------------+
| K1 | Name | Dept | JobTitle | Salary | email |
+----+------+---------+----------+--------+---------------+
| 1 | Ben | Testing | Engineer | 45000 | ben@benjr.tw |
| 2 | Afa | Power | Engineer | 48000 | Afa@benjr.tw |
| 3 | Boss | Testing | Manager | 75000 | Boss@benjr.tw |
| 4 | JoJo | Testing | Engineer | 45000 | jojo@benjr.tw |
| 5 | Ian | PM | Engineer | 85000 | ian@benjr.tw |
+----+------+---------+----------+--------+---------------+
5 rows in set (0.001 sec)
- CONCAT
CONTACT 函數可以將兩個或更多個字串連接在一起.CONCAT(expression1, expression2, expression3,...)
MariaDB [(none)]> SELECT CONCAT("APPLE", ", Banana", ", Cherry") AS Fruit; +-----------------------+ | Fruit | +-----------------------+ | APPLE, Banana, Cherry | +-----------------------+ 1 row in set (0.00 sec)要注意使用 CONTACT 函數中有 NULL 時跟任何字串連接在一起還是 NULL.
MariaDB [(none)]> SELECT CONCAT(NULL, ", Banana", ", Cherry") AS Fruit; +-------+ | Fruit | +-------+ | NULL | +-------+ 1 row in set (0.001 sec)
- CONCAT_WS
相較於 CONTACT 函數 CONCAT_WS 可以加上固定的分隔字元.CONCAT_WS(separator, expression1, expression2, expression3,...)
MariaDB [(none)]> SELECT CONCAT_WS(", ", "APPLE", "Banana", "Cherry") AS Fruit; +-----------------------+ | Fruit | +-----------------------+ | APPLE, Banana, Cherry | +-----------------------+ 1 row in set (0.01 sec) - GROUP_CONCAT
通常搭配 GROUP BY 使用,透過 GROUP_CONCAT 函數,可以把同 Group 的欄位文字整合在一起.GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | Ben,Boss,JoJo | +---------+---------------+ 3 rows in set (0.001 sec)
如果 GROUPBY 的資料要按照順序,可以加 ORDER BY DESC 或是 ASC
MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name DESC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | JoJo,Boss,Ben | +---------+---------------+ 3 rows in set (0.001 sec) MariaDB [testdb]> SELECT Dept, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ',') AS "Member Name" FROM employee GROUP BY Dept; +---------+---------------+ | Dept | Member Name | +---------+---------------+ | PM | Ian | | Power | Afa | | Testing | Ben,Boss,JoJo | +---------+---------------+ 3 rows in set (0.001 sec)
- LOCATE
檢視字串裡面是否有要搜尋的字,substr (搜尋關鍵字), str (字串), pos(從字串的哪一個字元開始搜尋).LOCATE(substr,str), LOCATE(substr,str,pos)
MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar') AS 'Search Result'; +---------------+ | Search Result | +---------------+ | 4 | +---------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT LOCATE('xbar', 'foobar') AS 'Search Result'; +---------------+ | Search Result | +---------------+ | 0 | +---------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT LOCATE('bar', 'foobarbar', 5) AS 'Search Result'; +---------------+ | Search Result | +---------------+ | 7 | +---------------+ 1 row in set (0.001 sec) - TRIM
移除特定字串.TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)可以用 BOTH (全部), LEADING (字頭) , TRAILING (字尾) 來決定取代哪邊的字串.
MariaDB [testdb]> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx') AS 'TRIM Result'; +-------------+ | TRIM Result | +-------------+ | barxxx | +-------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx') AS 'TRIM Result'; +-------------+ | TRIM Result | +-------------+ | bar | +-------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT TRIM(TRAILING 'x' FROM 'xxxbarxxx') AS 'TRIM Result'; +-------------+ | TRIM Result | +-------------+ | xxxbar | +-------------+ 1 row in set (0.000 sec)
- REPLACE
用 REPLACE 可以取代字串裡的特定字串.REPLACE(str, find_string, replace_with)
\n 為 Linux 的換行字元.
MariaDB [testdb]> SELECT '123\n456' AS ' TEXT'; +---------+ | TEXT | +---------+ | 123 456 | +---------+ 1 row in set, 1 warning (0.001 sec) MariaDB [testdb]> SELECT REPLACE('123\n456' ,'\n' ,'') AS ' Replace Result'; +----------------+ | Replace Result | +----------------+ | 123456 | +----------------+ 1 row in set, 1 warning (0.000 sec) - REGEXP_REPLACE
可以搭配 REGEXP (正規表示式) 使用的 REPLACE (取代字串裡的特定字串),更多關於 REGEXP_REPLACE 請參考 – http://benjr.tw/102758 .REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])
下面範例用來剔除 HTML 語法的標籤(TAG).
MariaDB [(none)]> SELECT REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+?>",''); +------------------------------------------------------------------------------+ | REGEXP_REPLACE("<h2 title=I'm a header>The title Attribute</h2>","<.+?>",'') | +------------------------------------------------------------------------------+ | The title Attribute | +------------------------------------------------------------------------------+ 1 row in set (0.004 sec) - FIND_IN_SET
回傳值為 string 位於 string_list (須以 , 分隔)的哪一個位置(不存在時,回傳 0 ).FIND_IN_SET(string, string_list)
MariaDB [testdb]> SELECT FIND_IN_SET('b' , 'a,b,c') AS 'FIND_IN_SET Result'; +--------------------+ | FIND_IN_SET Result | +--------------------+ | 2 | +--------------------+ 1 row in set (0.001 sec) MariaDB [testdb]> SELECT FIND_IN_SET('f' , 'a,b,c') AS 'FIND_IN_SET Result'; +--------------------+ | FIND_IN_SET Result | +--------------------+ | 0 | +--------------------+ 1 row in set (0.001 sec)雖然 FIND_IN_SET 函數可以解決單一欄位儲存多個資料 (以欄位中的 “, comma separated ” 為分隔) 所造成的搜尋問題,但不建議儲存成這樣的欄位內容.使用資料庫正規化 (Database normalization) – http://benjr.tw/101850 重新定義 Table 與其相關性或是使用 JSON 格式來儲存 http://benjr.tw/103200 .
- SUBSTR , SUBSTRING
從一字串取出一段選定的字串,SUBSTR , SUBSTRING 的使用方式一樣.SUBSTR(string, start, length)
OR:
SUBSTR(string FROM start FOR length)
從字串中取出第一個字元到第四個字元.
MariaDB [(none)]> SELECT SUBSTR("This is a Book", 1, 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This | +---------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT SUBSTRING("This is a Book", 1, 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This | +---------------+ 1 row in set (0.000 sec) MariaDB [(none)]> SELECT SUBSTR("This is a Book" FROM 1 FOR 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This | +---------------+ 1 row in set (0.000 sec)字元起始位置也可以從後面算起,-4 代俵從後面算起來第 4 個字元,並取出 4 個字元.
MariaDB [(none)]> SELECT SUBSTR("This is a Book",-4, 4) AS ExtractString; +---------------+ | ExtractString | +---------------+ | Book | +---------------+ 1 row in set (0.001 sec) - SUBSTRING_INDEX
依據自定 delimiter (分隔符號) 來從一字串中取出一段選定的字串.SUBSTRING_INDEX(string, delimiter, number)
以空格作區分,從字串中取第兩個空格字串前的字串.
MariaDB [(none)]> SELECT SUBSTRING_INDEX("This is a Book"," ", 2) AS ExtractString; +---------------+ | ExtractString | +---------------+ | This is | +---------------+ 1 row in set (0.000 sec)– (負數)代表從後面算起,-2 代表字串從後面算起取第兩個空格字串後的字串.
MariaDB [(none)]> SELECT SUBSTRING_INDEX("This is a Book"," ", -2) AS ExtractString; +---------------+ | ExtractString | +---------------+ | a Book | +---------------+ 1 row in set (0.001 sec) - STR_TO_DATE()
mysql 標準 timestamp 格式為 yyyy-mm-dd hh:mm:ss (24 小時制),如果從程式讀取的格式不同就可以透過這個函數作轉換.STR_TO_DATE(string, format)
MariaDB [testdb]> SELECT STR_TO_DATE("2/25/2020 11:13:03 PM", "%m/%d/%Y %h:%i:%s %p") AS TimeStamp; +---------------------+ | TimeStamp | +---------------------+ | 2020-02-25 23:13:03 | +---------------------+ 1 row in set (0.000 sec)更多參數請參考 – https://mariadb.com/kb/en/str_to_date/
- %m : Month with 2 digits.
- %d : Day with 2 digits.
- %Y : Year with 4 digits.
- %h : Hour with 2 digits between 01-12.
- %i : Minute with 2 digits.
- %s : Seconds with 2 digits.
- %p : AM/PM according to current locale.
- LENGTH
計算字元有幾個.MariaDB [(none)]> SELECT CHAR_LENGTH('ABC'); +--------------------+ | CHAR_LENGTH('ABC') | +--------------------+ | 3 | +--------------------+ 1 row in set (0.000 sec) - QUOTE 函數
QUOTE 函數有以下功能.- 帶有單引號 ‘ , 反斜線 \ ,ASCII NUL 以及 Control-Z 帶有反斜線.
- 如果值是 NULL,則傳回為字串 “NULL”
在 SQL 的字串不能使用 ” (Double Quote) 或是 ‘ (Single Quote) 來表示,但可以使用 \ 反斜線 (Backslash Character) 這個跳脫字元來表示 (” 需用 \” , ‘ 需用 \’) – http://benjr.tw/102928 ,使用 QUOTE 函數可以還原原本資料.
MariaDB [testdb]> SELECT 'I\'m'; +-----+ | I'm | +-----+ | I'm | +-----+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT QUOTE('I\'m'); +---------------+ | QUOTE('I\'m') | +---------------+ | 'I\'m' | +---------------+ 1 row in set (0.000 sec)傳回為字串 “NULL” .
MariaDB [testdb]> SELECT QUOTE(NULL); +-------------+ | QUOTE(NULL) | +-------------+ | NULL | +-------------+ 1 row in set (0.001 sec)
- CAST
資料型別作轉換.CAST(expr AS type)
MariaDB [(none)]> SELECT CAST(1.09 AS SIGNED); +----------------------+ | CAST(1.09 AS SIGNED) | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec)
可轉換型別如下
BINARY , CHAR , DATE , DATETIME , DECIMAL[(M[,D])] , DOUBLE , FLOAT — From MariaDB 10.4.5 , INTEGER , SIGNED [INTEGER] , TIME , UNSIGNED [INTEGER] - CONVERT
資料型別作轉換.其中 CONVERT(expr, type) 與 CAST(expr AS type) 使用上是一樣的.CONVERT(expr,type), CONVERT(expr USING transcoding_name)
MariaDB [(none)]> SELECT CONVERT(1.09,SIGNED); +----------------------+ | CONVERT(1.09,SIGNED) | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec)
CONVERT 除了可以轉換型別,還可以做字符集編碼轉換,當在做資料比對 (LIKE) 時編碼不同時是無法比對的,如下的錯誤訊息.
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'
這時候需透過 CONVERT(expr USING transcoding_name) 做不同的字符集轉換.
MariaDB [(none)]> SELECT CONVERT('abc' USING utf8); +---------------------------+ | CONVERT('abc' USING utf8) | +---------------------------+ | abc | +---------------------------+ 1 row in set (0.00 sec) - LOAD_FILE
LOAD_FILE 函數指定載入檔案位置(只限定位於 MySQL Server Host 上的檔案),需注意權限問題(該使用者需有 File 的權限,可使用 SQL 指令 SHOW PRIVILEGES; 來檢視),無法讀取時 LOAD_FILE 函數會回傳 NULL .得到 NULL 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限.另外一個可能是當檔案大小超出 MYSQL 的預設時 max_allowed_packet 也無法存取,可以使用SQL 指令 SHOW VARIABLES LIKE ‘max_allowed_packet’; 來檢視.
MariaDB [(none)]> SHOW VARIABLES LIKE 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+ 1 row in set (0.00 sec)
[root@localhost ~]# cat /var/lib/mysql/test.txt ABC 123 [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 5.5.60-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> SELECT LOAD_FILE('/var/lib/mysql/test.txt'); +--------------------------------------+ | LOAD_FILE('/var/lib/mysql/test.txt') | +--------------------------------------+ | ABC 123 | +--------------------------------------+ 1 row in set (0.01 sec)更多關於 Load 載入資料到資料表,請參考 – http://benjr.tw/101779
- LOAD
現在透過 SQL 語法 LOAD DATA INFILE 把檔案匯入到資料庫,下面範例使用 /etc/passwd .MariaDB [dbtest]> LOAD DATA INFILE '/etc/passwd' INTO TABLE testdb.passwd FIELDS TERMINATED BY ':' ENCLOSED BY '"' LINES TERMINATED BY '\n' (Username , Password , UID , GID , IDInfo , HomeDir , Shell) ;
更多關於 LOAD DATA 請參考 – http://benjr.tw/103012
- ExtractValue
當我們把整個 XML 儲存在一個欄位 (建議使用 TEXT 資料型態欄位),可以再透過函數 ExtractValue 取出相對應的資料,更多關於 ExtractValue 請參考 – http://benjr.tw/102790
要怎麼透過函數 ExtractValue 把 XML 格式的資料擷取出來呢!函數 ExtractValue 參數需指定 XPath 其格式如下.- /tag
- 位於最上(外)層 (Root Element , 根元素 , 父元素) 且名稱匹配 <tag/> <tag/> 內的資料.
- /tag1/tag2
- 除了要匹配 <tag2> <tag2/> 外,還必須為 <tag1> <tag1/> 最上(外)層 (Root Element , 根元素 , 父元素) 的子元素.
MariaDB [testdb]> SET @xml = '<a><b>X</b><b>Y</b></a>'; Query OK, 0 rows affected (0.001 sec) MariaDB [testdb]> SELECT ExtractValue(@xml, '/a/b[1]'); +-------------------------------+ | ExtractValue(@xml, '/a/b[1]') | +-------------------------------+ | X | +-------------------------------+ 1 row in set (0.000 sec)
- //tag
- 匹配任何為 <tag> 的資料.
MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[1]'); +------------------------------+ | ExtractValue(@xml, '//b[1]') | +------------------------------+ | X | +------------------------------+ 1 row in set (0.000 sec) MariaDB [testdb]> SELECT ExtractValue(@xml, '//b[2]'); +------------------------------+ | ExtractValue(@xml, '//b[2]') | +------------------------------+ | Y | +------------------------------+ 1 row in set (0.000 sec)
- No Matching Element
- 如果沒符合的資料時,並不是回傳 NULL 而是 “”(空字串),要怎麼判斷是否沒資料(回傳空字串)還是真的是得到的是空字串.
MariaDB> SELECT ExtractValue('<a><b/></a>', '/a/b'); +-------------------------------------+ | ExtractValue('<a><b/></a>', '/a/b') | +-------------------------------------+ | | +-------------------------------------+ 1 row in set (0.00 sec) MariaDB> SELECT ExtractValue('<a><c/></a>', '/a/b'); +-------------------------------------+ | ExtractValue('<a><c/></a>', '/a/b') | +-------------------------------------+ | | +-------------------------------------+ 1 row in set (0.00 sec)- 上面範例一回傳的是有資料(內容物為空字串),範例二是沒有資料(一樣回傳空字串),我們要怎麼判斷,可以利用 COUNT() 函數.
MariaDB> SELECT ExtractValue('<a><b/></a>', 'count(/a/b)'); +-------------------------------------+ | ExtractValue('<a><b/></a>', 'count(/a/b)') | +-------------------------------------+ | 1 | +-------------------------------------+ 1 row in set (0.00 sec) MariaDB> SELECT ExtractValue('<a><c/></a>', 'count(/a/b)'); +-------------------------------------+ | ExtractValue('<a><c/></a>', 'count(/a/b)') | +-------------------------------------+ | 0 | +-------------------------------------+ 1 row in set (0.01 sec)- 有資料的 COUNT() 為 1 ,沒資料的是 0 .
- 上面範例一回傳的是有資料(內容物為空字串),範例二是沒有資料(一樣回傳空字串),我們要怎麼判斷,可以利用 COUNT() 函數.
沒有解決問題,試試搜尋本站其他內容