記錄一次SQL函數和優化的問題

• 2022 年 3 月 22 日
• 筆記
• Mysql學習

一、前言

上次在年前快要放假的時候記錄的一篇安裝SSL證書的內容，因為當時公司開始居家辦公了，我也打算回個家
畢竟自己在蘇州這半年一個人也是很想家的，所以就打算年過完來重新寫博客。不巧的是，當時我2月中旬剛到蘇州，
沒想到蘇州疫情爆發了，直接隔離十四天，自己平時就完成公司的開發任務以及自己的畢設，把寫博客的事有點淡忘了，
最近也算自己手頭的一些事都開始可以順利進行了，想起也是時候去記錄一下了。
很多新學的東西自己感覺就是入了個門，所以平時基本就直接放語雀上了，這次也是打算做一個簡短的總結吧！

二、SQL函數

注：以下基於官方文檔理解（ MySQL5.7文檔 ）

官方的內容，可以利用好CTRL+F，進行網頁內搜索，隨時查看各種函數用法，而且也會避免一些不規範的問題，比較官方的例子和要求最緻密。

內置函數和運算符參考

2.1字符串函數

FIND_IN_SET(param1，param2)

這是一個字符串相關的函數

FIND_IN_SET(needle,haystack) 裏面有兩個參數，我們可以簡單的看作find_in_set(param1,param2)

第一個參數param1：它是我們要查找的某一個具體的值

第二個參數param2：它是我們要查找的字符串列表

• 當param2在param2這個列表中的話，函數返回一個正整數
• 當param1不在param2中，或者param2這個列表是個NULL，函數返回0
• 當兩個參數param1或param2為NUll的時候，函數放回NULL

這裡以LEFT JOIN舉個「栗」子：

LEFT JOIN：說的簡單點就是，左表記錄會全部返回，同時如果與右表有記錄相等的數據會返回右表的一些相關信息，如果沒有，右表返回的記錄就是NULL
（這個可以去參考CSDN此篇博客：Sql之left join（左關聯）、right join（右關聯）、inner join（自關聯）的區別

這裡假設有a和b兩張表，a表中的id是個bigint類型，b表中的relate_a_id是個varchar類型，存放的是關聯的a表中的id
（這裡僅僅是舉個栗子，表的設計一定要符合規範，比如這種關聯的可以新增一張關聯表的操作）
select a.id,
       a.name,
       IFNULL(b.id,0) AS flag,
       b.relate_a_id
       from a left join b on FIND_IN_SET(a.id,relate_a_id) and a.id = b.id
       
這樣如果a.id在這個b表中relate_a_id這個字段的列表中的話就返回b.id，如果不在就放回0。這裡起了個別名為flag作為判斷量

單表的操作更簡單，總而言之，這個函數就是為了判斷一個值是否在一個字符串列表中的操作。

同理和NULL、NOT NULL一樣，如果要判斷不在當中就直接NOT FIND_IN_SET()就可以了

這裡要提一點的就是，以上操作看起來和IN這個操作符很像，所以這裡我的理解是：

雖然
1 IN(1,2,3)  和  FIND_IN_SET(1,"1,2,3")  最終的結果是一樣的，但是如下：
IN它是「值」對「值」，而FIND_IN_SET(param1，param2)是「值」對「一個列表」，而且FIND_IN_SET這個函數有自己的固定的兩個參數
+ 不同點一：比較內容不同
+ 不同點二：函數格式不同

以上也是自己的一些淺見，如有錯誤，請各位大佬虛心賜教！

其他

字符串函數其實還有很多比較常見的，比如：

CONCAT(param1,param2,……)

這個函數裏面也是有參數的，就是把兩個或多個參數組合到一起的函數，當然還有CONCAT_WS(seperator,param1,param2,……)
根據第一個參數「分隔符」，來組合參數列表。

對於這個函數比較熟悉的就是寫動態SQL的時候與LIKE操作符的應用，比如：
select a.name from a where a.is_delete =0
<if test="param.serachName!=''">
    and a.name like concat('%',#{param.serachName},'%')
</if>

這也是為了單純寫like去傳參數的話，會出現SQL注入的風險，所以採用這種方式來防止SQL注入

REPLACE(str,oldStr,newStr)

這裡要注意的是mysql擴展中REPLACE是個插入更新語句，但它沒有where字句，具體可以自行搜索查看

舉個例子：
REPLACE('aaa.yuyueq.cn'，'a'，'w')
結果為：www.yuyueq.cn

SUBSTRING(str,index)

它會從一個特定長度的位置開始，提取一個子字符串。

也可以寫為SUBSTRING(str FROM index),舉個例子：
SUBSTRING('www.yuyueq.cn',5)
結果為：'yuyueq.cn'
要注意的是它不在遵循計算機的規律，也就是它是從1開始數的，並不是0，如果index參數是0.則返回一個空字符串

當然也可以截取字符串中字符串，比如

(substring(str,index,length)和下面這個是一樣的)
SUBSTRING(str FROM index FROM length),舉個例子：
SUBSTRING('www.yuyueq.cn',5,6)
結果為：'yuyueq'

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str)

是從字符串中刪除不需要的字符

TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值為 LEADING (起頭), TRAILING (結尾), or BOTH (起頭及結尾)。
這個函數將把 [要移除的字串] 從字串的起頭、結尾，或是起頭及結尾移除。如果我們沒有列出 [要移除的字串] 是什麼的話，那空白就會被移除

trim操作個人感覺多用於動態SQL中吧，可以看看簡書的這篇：mybatis動態SQL – trim where set標籤

FORMAT(N,D,locale)

格式化具有特定區域設置的數字，舍入到小數位數。

N是要格式化的數字。

D是要舍入的小數位數。

locale是一個可選參數，用於確定千個分隔符和分隔符之間的分組。如果省略locale操作符，MySQL將默認使用en_US。以下鏈接提供MySQL支持的所有區域名稱：

LEFT()

獲取指定長度的字符串的左邊部分。

LENGTH()函數&CHAR_LENGTH()

它是以位元組和字符獲取字符串的長度。

2.2 聚合函數

COUNT()

首先官方已經說了,count(*)和count(1)沒有區別

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

其次，count(字段)和count(*)、count(1)的區別是：

• count(字段)會進行全表掃描，效率會很差，不計算NULL值
• count()、count(1)會計算NULL值，而且count()等同於count(0)
• count如果沒有匹配的行，count()它直接返回0

四個計算

AVG()：取平均值

SUM()：求和

這裡要注意的是，如果沒有匹配的行，則 SUM()返回 NULL。

min()：最小值

max()：最大值

GROUP_CONCAT()

此函數返回一個字符串結果

舉個例子:
假設a是用戶表，b是一張用戶興趣(id)關聯表，c是一張興趣表；
下面的意思就是我們查詢這個用戶相關信息的時候，將相關聯的興趣放到一個字符串字段中，相當於顯示用戶詳情的操作
   SELECT
        group_concat(DISTINCT c.name) AS interestName
        FROM
        a
        left join b on a.id = b.user_id
        left join c on b.interest_id = c.id

官方例子：GROUP_CONCAT( [DISTINCT] expr [,expr …] [ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name …] ] [SEPARATOR str_val])

由官方例子可以看出裏面可以進行去重、排序、用特定的分隔符展示（默認是「,」）

其次還要注意的是，它是不可以和IN操作符使用的，原因就和find_in_set那個函數一樣，IN的列表的是值列表，group_concat是個字符串列表

2.3 控制流函數

控制流 是計算機執行一個程序中語句的順序。 程序會從第一行代碼開始執行直至最後一行，除非遇到（實際中是非常普遍地）改變控制流的代碼結構，比如條件語句和循環。

注：函數和sql語句的用法是不一樣，所以要多注意一點，此處都是函數的用法。

IF(expr1,expr2,expr3)

官方例子最致命
如果expr1是TRUE (expr1 不等於0 和 expr1 IS NOT NULL)，則返回expr2，否則，返回expr3.
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

strcmp函數可以看這裡：//dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

IFNULL(expr1,expr2)

官方例子最致命
如果expr1不是 NULL， 則返回 expr1；否則返回 expr2。
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

CASE

官方例子最致命
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
->     WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
如果第一個參數等於第二個參數，則返回NULL，否則返回第一個參數

官方例子最致命
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1

2.4 日期函數

三、SQL優化

上面的內容其實也設計到了很多規範的問題，但畢竟我是為了舉例子所以在這提一些規範的操作。

數據庫設計

• 冷熱數據的分離，從而可以減少表的寬度
• 列的字段類型盡量可小去滿足ta，否則建立索引需要的空間會很大，影響性能
• 盡量不要使用TEXT,BLOB數據類型
• 儘可能把所有列定義為 NOT NULL，這也是為了防止查詢的時候NullPointException異常的出現
• 及時給數據庫表和字段增添注釋
• TIMESTAMP(4 個位元組) 或 DATETIME 類型 (8 個位元組) 存儲時間
  • 兩者比時間戳更直觀，但TIMESTAMP會有2038年的問題，
  • TIMESTAMP具有’1970-01-01 00:00:01’UTC 到’2038-01-19 03:14:07’UTC 的範圍
  • 我個人是比較傾向於時間戳的，數據庫中用bigint存儲，編程中用Long值傳遞，至於前端展示就在前端做處理，很方便，就是數據庫查看時間的時候不直觀
  • mysql 數據庫存時間最好是時間戳還是格式的時間

MySQL將TIMESTAMP值從當前時區轉換為UTC進行存儲，並從UTC返回到當前時區進行檢索。
(默認情況下，每個連接的當前時區是服務器的時間。時區可以在每個連接的基礎上設置。只要時區設置保持不變，你就會得到與你存儲的相同的值。
如果你存儲一個TIMESTAMP值，然後改變時區並檢索該值，檢索到的值與你存儲的值不同。出現這種情況是因為在兩個方向的轉換中沒有使用相同的時區。當前的時區可以作為time_zone系統變量的值。
要注意MySQL中日期值解釋的某些屬性。
MySQL允許對指定為字符串的值採用 “寬鬆 “格式，其中任何標點符號都可以用作日期部分或時間部分之間的分隔符。在某些情況下，這種語法可能具有欺騙性。例如，像’10:11:12’這樣的值可能看起來像一個時間值，因為有:，但如果在日期上下文中使用，則被解釋為年份’2010-11-12’。值’10:45:15’被轉換為’0000-00-00’，因為’45’不是一個有效的月份。
在日期和時間部分與小數秒部分之間，唯一可識別的分隔符是小數點。
服務器要求月和日的值是有效的，而不僅僅是分別在1到12和1到31的範圍內。在禁用嚴格模式的情況下，無效的日期如’2004-04-31’被轉換為’0000-00-00’併產生一個警告。在啟用嚴格模式的情況下，無效的日期產生一個錯誤。要允許這樣的日期，請啟用ALLOW_INVALID_DATES。參見第5.1.10節 “服務器SQL模式”，以了解更多信息。
MySQL不接受在日或月列中包含零的TIMESTAMP值或不是有效日期的值。這個規則的唯一例外是特殊的 “零 “值”0000-00-00 00:00:00″，如果SQL模式允許這個值。準確的行為取決於是否啟用了嚴格的SQL模式和NO_ZERO_DATE SQL模式；參見章節5.1.10, “服務器SQL模式”。
包含2位數年值的日期是模糊的，因為世紀是未知的。MySQL使用這些規則解釋2位數的年值。
00-69範圍內的年值成為2000-2069。
在70-99範圍內的年值成為1970-1999。

具體參見官方：//dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html

SQL語句

• 盡量使用【select 字段】，而不要去使用【select *】
• 盡量將子查詢變為JOIN語句，並且也要減少JOIN語句的使用，如果業務存在特殊要求，可以嘗試使用虛擬表來提高查詢效率
• where語句中，還是不要對列進行函數轉換和計算
• 左右內連接要注意的是：ON後面的條件是為了生成兩者臨時表的條件，而where是為了篩選臨時表中內容的條件
  • 而且不管on上的條件是否為真都會返回left或right表中的記錄；但inner jion沒有這個特殊性，當條件放在on中和where中，沒有區別，返回的結果集是相同的
• 我們都知道union關鍵字後，可以獲取去重後的數據，而union all關鍵字，獲取的是所有數據，包含重複的數據
  • 所以當我們知道查出來的數據中沒有重複值的時候選擇union all，而且一般情況下儘可能的去選擇union all，畢竟去重操作會遍歷排序等等操作，消耗cpu資源。
• 以小表驅動大表
  • 小表並不是指數據量很小的表，而是與另一張表對比，在同一條件下，哪張表檢索量小，才是小表

要注意的是具體查詢的時候要根據業務需求來，確定主表，不能為了小表驅動大表，而破壞查詢邏輯
當連接查詢沒有where條件時，左連接查詢時，前面的表是驅動表，後面的表是被驅動表，右連接查詢時相反，內連接查詢時，哪張表的數據較少，哪張表就是驅動表
當連接查詢有where條件時，帶where條件的表是驅動表，否則是被驅動表
in 適用於左邊大表，右邊小表。
exists 適用於左邊小表，右邊大表。

• 盡量不要在group by後面使用having語句，通常都是where在前，group by在後的過濾篩選操作

• 對應同一列進行 or 判斷時，使用 in 代替 or

  • in 的值不要超過 500 個，in 操作可以更有效的利用索引，or 大多數情況下很少能利用到索引

• 最後最重要的其實就是索引的問題，很多情況下要看sql到底有沒有走索引，導致查詢很慢，可以用explain命令去查看

  • 索引這塊涉及點是比較多的，這裡不作過多內容

• 索引失效的情況

  • 參見此篇文章：//zhuanlan.zhihu.com/p/338545029
  • 網上各種轉載，還不給轉載信息，也是替原作者可惜啊！

四、最後

有些情況下，我們要根據自己的業務來判斷怎麼使用SQL，但大多數情況下還是要遵循開發中默認好的規範操作。

這次是簡單的記錄了一下對與sql函數的應用理解，以及對於sql優化的應用，下次打算總結一下設計模式，和開發模型，

以前是因為很大程度上都是自己悶頭學，企業級的項目也沒有接觸過，撐着這次實習把這些內容深刻的體會一下吧。