常用函數

IFNULL，ISNULLMySQL詳細基礎語法

SELECT IFNULL(null, 1);

ifnull1.png

SELECT IFNULL(0, 1);

ifnull2.png

SELECT IF('', 1), HEX('');

-- HEX()函數可將數字或者字符串轉換為16進制格式的字符串

ifnull3.png

SELECT ISNULL(NULL),ISNULL(0),ISNULL(1/0);

isnull.png

NULL的特點

null只能用is NULL比較，和數值、字符串等進行比較都沒有結果集。'<=>'是MySQL特有的判斷符號。叫等價于。

SELECT 1=1,NULL=NULL,1=NULL,1<>NULL,NULL is NULL;

null.png

SELECT 1<=>1,NULL<=>NULL,1<=>NULL,1 IS NOT NULL,NULL IS NOT NULL;

null1.png

時間函數now，sysdate

Now() 返回的是語句執行的時間，而sydate()返回的是實時的時間戳

SELECT now(),sleep(2),now();

time.png

SELECT sysdate(),sleep(2),sysdate();

time1.png

time2.png

該寫方案如下

time3.png

時間函數實際優化案例(CURDATE()函數為返回當前的日期)

time4.png

字符串處理函數RPAD()，LPAD()，RTRIM()，LTRIM()，TRIM()，CONCAT()

str.png

RPAD/LPAD 數據庫某列要保證某個長度，不足的使用特定字符右/左填充

RTRIM、LTRIM和TRIM是去除字段值得空字符

CONCAT字符串合并函數

日期類型的特性

MySQL日期類型不需要使用日期函數轉換，只要按照日期類型格式編寫即可，其他數據庫則必須進行轉換

date.png

使用日期格式轉化后

date2.png

BETWEEN IN

字符類型不能使用between， 性能較差

between.png

可以將字符類型的between轉化為in查詢，性能較好

between1.png

也可以使用union all改寫

SELECT * FROM dept_emp2 WHERE dept_no = 'd003' AND emp_no = 10005

UNION ALL

SELECT * FROM dept_emp2 WHERE dept_no = 'd004' AND emp_no = 10005

UNION ALL

SELECT * FROM dept_emp2 WHERE dept_no = 'd005' AND emp_no = 10005

between2.png

like的基本用法和特點

使用like對字符類型的列進行模糊查詢，最好把%寫在后面，如‘aa%’。這樣可以使用一些索引。而且盡量使用字段代替*來減少查詢面積

日期類型一定不能用like查詢 無法使用索引 查詢某一時間段的數據可以使用between and

選擇率

對優秀的選擇率的說明

如下2個SQL中去除重復值后越接近count(*)選擇率越高

change.png

OR

使用OR的時候必須兩邊加括號 否則結果完全不一樣 OR條件如果復雜的情況 可以適當考慮union all分離

對于相同的列 or條件等同于in

SELECT * FROM employees WHERE (emp_no = 10001 OR emp_no = 10002 OR emp_no = 10003);

or.png

OR優化

DESC SELECT

mci.PRODUCT_ID,

mci.PRODUCT_CODE,

mci.PRODUCT_NAME,

mci.PRODUCT_LOGO,

mci.SHOP_NAME,

mci.IS_ENABLE,

mci.CATEGORY_N01,

mci.CATEGORY_N02,

mci.CATEGORY_N03,

ccv2_1.CATEGORYNAME CATEGORY_N01_NAME,

ccv2_2.CATEGORYNAME CATEGORT_N02_NAME,

ccv2_3.CATEGORYNAME CATEGORY_N03_NAME,

mci.THIRD_STORE_COMMISSION,

mci.COMMODITY_TYPE

FROM

mall_ccmmodity_info AS mci

LEFT JOIN comnodity_category_v2 AS ccv2_1 ON ccv2_1.UNIQUE_NO = mci.CATEGORY_N01

LEFT JOIN comnodity_category_v2 AS ccv2_2 ON ccv2_2.UNIQUE_NO = mci.CATEGORY_N02

LEFT JOIN comnodity_category_v2 AS ccv2_3 ON ccv2_3.UNIQUE_NO = mci.CATEGORY_N03

LEFT JOIN (

SELECT

pprod.PRODOCT_ID,

COUNT( 0 ) count

FROM

promotion_product pprod

INNER JOIN promotion_info pinfo ON pinfo.PROMOTION_ID = pprod.PROMOTION_ID

AND pprod.IS_ENABLE = 1

AND pinfo.IS_ENABLE = 1

AND pinto.BELONG_T0 = 1

AND pinto.END_TIME >= NOW( )

AND NOT ( pinfo.ONSHELVE_TIME > '2018-06-30 00:00:' OR pinfo.END_TIME < '2017-12-06 17:00:00' )

GROUP BY

pprod.PRODOCT_ID

) AS pc ON pc.PRODUCT_ID = mci.PRODUCT_ID

WHERE

mci.IS_ENABLE = 0

AND mci.COMMODITY_TYPE IN ( '1', '5', '6' )

AND ( pc.count = 0 OR pc.count IS NULL )

LIMIT 0,

5;

執行計劃如下

or1.png

分析上面的執行計劃可以發現，在derived2這個位置，類型是all，發生了全表掃描，并且沒有auth_key產生。注意pc.count = 0 OR pc.count IS NULL這個SQL，去掉之后執行計劃就變成如下

or2.png

產生了auth_key所以優化的關鍵點在于pc.count = 0 OR pc.count IS NULL這個位置，所以優化SQL如下

```mysql

DESC SELECT

mci.PRODUCT_ID,

mci.PRODUCT_CODE,

mci.PRODUCT_NAME,

mci.PRODUCT_LOGO,

mci.SHOP_NAME,

mci.IS_ENABLE,

mci.CATEGORY_N01,

mci.CATEGORY_N02,

mci.CATEGORY_N03,

ccv2_1.CATEGORYNAME CATEGORY_N01_NAME,

ccv2_2.CATEGORYNAME CATEGORT_N02_NAME,

ccv2_3.CATEGORYNAME CATEGORY_N03_NAME,

mci.THIRD_STORE_COMMISSION,

mci.COMMODITY_TYPE

FROM

mall_ccmmodity_info AS mci

LEFT JOIN comnodity_category_v2 AS ccv2_1 ON ccv2_1.UNIQUE_NO = mci.CATEGORY_N01

LEFT JOIN comnodity_category_v2 AS ccv2_2 ON ccv2_2.UNIQUE_NO = mci.CATEGORY_N02

LEFT JOIN comnodity_category_v2 AS ccv2_3 ON ccv2_3.UNIQUE_NO = mci.CATEGORY_N03

LEFT JOIN (

SELECT

pprod.PRODOCT_ID,

COUNT( 0 ) count

FROM

promotion_product pprod

INNER JOIN promotion_info pinfo ON pinfo.PROMOTION_ID = pprod.PROMOTION_ID

AND pprod.IS_ENABLE = 1

AND pinfo.IS_ENABLE = 1

AND pinto.BELONG_T0 = 1

AND pinto.END_TIME >= NOW( )

AND NOT ( pinfo.ONSHELVE_TIME > '2018-06-30 00:00:' OR pinfo.END_TIME < '2017-12-06 17:00:00' )

GROUP BY

pprod.PRODOCT_ID

) AS pc ON pc.PRODUCT_ID = mci.PRODUCT_ID

WHERE

mci.IS_ENABLE = 0

AND mci.COMMODITY_TYPE IN ( '1', '5', '6' )

AND case when pc.count is null then 0 else pc.count end = 0

LIMIT 0,

5;

```

執行計劃如下

or3.png

優化前為7s，優化后為0.029s

or優化案例2

優化前的SQL如下

DESC SELECT

COUNT( t2.store_id )

FROM

lp_liquidator t1

INNER JOIN lp_liquidator_store t2 ON t1.liquidator_id = t2.liquidator_id

LEFT JOIN lp_store_bank t3 ON t2.store_id = t3.store_id

LEFT JOIN lp_witness_bind_bank t4 ON t4.lp_store_id = t2.store_id

WHERE

t4.bank_card_user = '洪哥'

OR t3.username = '洪哥';

執行計劃如下

or4.png

查詢耗時15s

根據條件復雜時可考慮使用union替換or的原則，優化SQL如下

```mysql

DESC SELECT

COUNT( t2.store_id )

FROM

(

SELECT

t2.store_id

FROM

lp_liquidator t1

INNER JOIN lp_liquidator_store t2 ON t1.liquidator_id = t2.liquidator_id

LEFT JOIN lp_store_bank t3 ON t2.store_id = t3.store_id

WHERE

t3.username = '洪哥' UNION

SELECT

t2.store_id

FROM

lp_liquidator t1

INNER JOIN lp_liquidator_store t2 ON t1.liquidator_id = t2.liquidator_id

LEFT JOIN lp_witness_bind_bank t4 ON t2.store_id = t4.lp_store_id

WHERE

t4.bank_card_user = '洪哥'

) b;

```

優化后，執行計劃如下

or5.png

SUBQUERY(子查詢)

SUBQUERY按照出現的位置可以分為INLINE VIEW SUBQUERY，SCALA SUBQUERY， IN EXISTS使用的SUBQUERY

INLINE VIEW SUBQUERY 用在FORM后面的子查詢 相當于臨時表

MySQL想要使用這種類型的子查詢，需要滿足如下條件

如果subquery不包含集合函數 union all，union，limit等關鍵字。查看執行計劃，保證該subquery第一個執行或者不出現temp file，file sort等

如果包含上述關鍵字的subquery必須保證第一個執行，否則對性能有重大影響。原因是當數據量很大時，要盡量減少auth_key的數量，否則產生太大auth_key會嚴重影響速度

鼓勵第二種情況下的subquery 且保持一個subquery

SCALA SUBQUERY(標量子查詢) 是放在select和from之間的subquery

這種subquery相當于函數

但是這種subquery因為運行返回行數多 所以最好不要再返回行數多的query中運行 并且必須要有很好的索引

標量子查詢一定也寫在最外面

subquery.png

如果沒有良好的索引，并且要對處于業務高峰的語句進行優化，下面有如下例子

subquery1.png

由于標量子查詢只能返回一條數據或者空，則優化代碼如下

subquery2.png

$\color{red}{優化前查詢較慢的原因如下: 先忽略子查詢，語句為}$

```mysql

select t1.emp_no from t1 limit 100;

```

$\color{red}{這樣會得到100行數據，之后的執行操作就是雙重for循環。假如子查詢的結果集為200行。外層循環1次內層循環就要循環200次，并且就算在第一次循環時就滿足了e.emp_no=t1.emp_no條件后續循環一樣會執行，不會停止。這樣就造成了沒必要的循環操作。加上limit后，找到滿足條件的之后，循環就會停止，進入第二次循環，這樣就大大提升了效率。}$

2. 這種子查詢不會是最后結果集 往往可以和left join互相替換

在外層查詢結果較少，join連接的表row很大，并且需要進行group by操作的時候，將left join改寫成標量子查詢后，往往可以提升效率

3. SCALA SUBQUERY可進行分頁查詢

IN 和 NOT IN

in和not in 相當于or條件

In 和not in不能識別NULL，并且not in的條件中絕對不能有NULL，否則永遠沒有查詢結果如下圖中以in為實例

in.png

將語句進行改寫，使用or進行查詢，查詢條件改為id等價于null反而可以查詢出結果。如下圖

in1.png

使用in查詢時，參數類型要保持一致，否則會導致索引無法使用

。

in2.png

not in語句改寫left join示例

not in語句如下

SELECT * FROM t_order WHERE emp_no NOT IN (SELECT emp_no FROM employees WHERE emp_no > 20000);

改寫后的語句如下

SELECT t.emp_no,s.emp_no FROM t_order t

LEFT JOIN (SELECT emp_no FROM employees WHERE emp_no > 20000) s ON t.emp_no=s.emp_no

WHERE s.emp_no IS NULL

AND t.emp_no IS NOT NULL;

EXISTS和NOT EXISTS

exists 和 not exists最大的區別是可以允許條件為null，實例如下圖

exists.png

Limit

主要用于分頁優化空間在于延遲join方式的優化

實例如下，源語句

SELECT * FROM employees e ORDER BY first_name LIMIT 100000 , 0;

優化后語句

SELECT a.* FROM (

SELECT first_name,emp_no FROM employees e ORDER BY first_name LIMIT 100000 , 0

) s straight_join employees a WHERE s.emp_no=a.emp_no;

Union, union all中不能使用limit

如下圖所示

limit.png

為了避免這種操作，我們可以對查詢語句進行如下優化，將原先的查詢變為子查詢進行優化

limit1.png

in + limit是不支持的

例如

limit2.png

解決辦法也同樣事改寫成子查詢繞過即可

SELECT t1.emp_no FROM t1 WHERE t1.emp_no in

(

SELECT b.emp_no FROM (

SELECT e.emp_no FROM employees e WHERE e.emp_no BETWEEN 10001 AND 11000 LIMIT 1000

) b

) LIMIT 5

當然還可以使用join進行改寫，語句如下

SELECT t1.emp_no FROM t1 JOIN

(

SELECT DISTINCT a.emp_no FROM (

SELECT e.emp_no FROM employees e WHERE e.emp_no BETWEEN 10001 AND 11000 LIMIT 1000

) a

) e ON t1.emp_no = e.emp_no limit 5

執行計劃如下

limit3.png

總結

以上是生活随笔為你收集整理的mysql特有语法_MySQL详细的基础语法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。