寫在前面...

這個blog的第一篇正式文章。是我的其中一個筆記，我在review同事的code時發現的神奇寫法。可能是同事google來的或從古老的codebase中挪用的，是一個很奇怪的方法。因此就寫了筆記記錄下來了。文末有一小段我稱之為"Docker Lab"的章節，講如何用Docker做實驗。不用sql-fiddle也不用安裝XAMPP，只要Docker。做完實驗後把容器刪除電腦環境還是乾淨溜溜。

目前沒有評論區，所以任何問題、勘誤請在twitter上DM我, Thanks!

TL;DR 實作單行可切換過濾器的簡單方法

實作可切換的過濾器(filter)並不需要用到神奇的 COALESCE 函式。以下是我認為較為簡單及可讀的方法，請別使用下方提到的牽扯到 COALESCE 的方法，除非你想要增加閱讀SQL的困難度XD

PHP

// $payOnly should be a boolean
function query($payOnly) {
  $filterToggle = $payOnly ? "0" : "1"
  return $this->db->query("SELECT * FROM users WHERE ($filterToggle OR users.pay);");
}

*在SQL中嵌入字串或輸入資料要注意SQL Injection

與神奇COALESCE用法的邂逅

(筆記本文開始)

我在review PHP的程式時看到這段程式碼，第一時間沒辦法看懂。

以下是我自己重現的程式碼(並非直接擷取自codebase)：

function query($payOnly = "null") {
  return $this->db->query("SELECT * FROM users WHERE users.pay = COALESCE($payOnly, users.pay);");
}

是一段查詢 users 的SQL

以下是簡化過的users 表的結構：

CREATE TABLE `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(100) NOT NULL,
  `pay` TINYINT NOT NULL DEFAULT 0
);

從變數名稱我們可以猜這個function在沒傳任何參數的情況下

$results = query();

是執行以下SQL

SELECT * FROM users WHERE users.pay = COALESCE(null, users.pay);

而當 $payOnly 有值時，假設傳入字串 "1" 會執行以下SQL

SELECT * FROM users WHERE users.pay = COALESCE(1, users.pay);

可以從參數名稱payOnly猜測payOnly是用來切換是否只選擇有付錢的使用者。不過這段程式是怎麼達成切換的呢？我想重點在於

WHERE users.pay = COALESCE(1, users.pay)

什麼是COALESCE函式？

Mysql Manual :

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. The return type of COALESCE() is the aggregated type of the argument types.

這函式會從傳入的參數中尋找第一個非 Null 的值並回傳。如果傳入的全都是 Null 則回傳 Null

範例：

(想做實驗可以參考下方的Docker Lab)

SELECT COALESCE(null, "hello", "world",null);
# 回傳：
#+---------------------------------------+
#| COALESCE(null, "hello", "world",null) |
#+---------------------------------------+
#| hello                                 |
#+---------------------------------------+
SELECT COALESCE(null, null, "world",null);
#+------------------------------------+
#| COALESCE(null, null, "world",null) |
#+------------------------------------+
#| world                              |
#+------------------------------------+
SELECT COALESCE(null, null, null,null);
#+---------------------------------+
#| COALESCE(null, null, null,null) |
#+---------------------------------+
#|                            NULL |
#+---------------------------------+

users.pay = COALESCE($payOnly, users.pay)

了解COALESCE函式的行為後接下來分析users.pay = COALESCE($payOnly, users.pay)的行為

由於$payOnly 、users.pay 是一布林值，而 where 裡面的表達式也是以布林值為主，所以我們可以做一張真值表來了解這段程式碼的行為

真值表

Case	$payOnly	users.pay＊	COALESCE($payOnly, users.pay)	users.pay = COALESCE($payOnly, users.pay)
1	1	1 [T]	COALESCE(1, 1) = 1 [T]	T
2	null	1 [T]	COALESCE(null,1) = 1 [T]	T
3	1	0 [F]	COALESCE(1,0) = 1 [T]	F
4	null	0 [F]	COALESCE(null,0) = 0 [F]	T

*MySQL中的布林值是以TINYINT表達。1是 true ；0是 false

初步觀察

users.pay = COALESCE($payOnly, users.pay) 是一段在 where 裡的表達式(expression)，因此當它被計算為 false 時，該行會從回傳的結果中剔除。

參考上方真值表我們會發現這個表達式只有在一種情況下為 false 。也就是case 3，當 $payOnly 為 1 而 users.pay 為 false 時。

現在我們了解這個程式碼的初衷了，我們可以想想有沒有更簡單的方法？

為了簡化而重新思索

坦白說一開始看到 users.pay = COALESCE($payOnly, users.pay) 時我就覺得這段”臭臭的“(Code Smell)

看到它讓我不舒服，一部份原因是因為我不熟悉 COALESCE 另一部份是因為他長得像是隱函數。

布林判斷式的兩側都有 users.pay 就好像一個兩邊都有 x的隱函數。

試問$x = 10$ 跟 $x = \frac {-x^2 - 120} 2$ 比起來哪個簡單? 當然是 $x = 10$

程式碼的初衷是在只透過變動一個參數( $payOnly)的情況下，在全部使用者與付費使用者之間切換。

從上方的真值表知道我們需要造一個布林判斷試，使得當 $payOnly 是 true 而 users.pay 是 false 時回傳 false 其他組合都回傳 true ，因此在PHP我們可以寫成

!$payOnly || users.pay

由於 or 的性質 or 兩側的參數只要有一個恆為 true 我們就可以說這個布林判斷是恆為 ture 。我們反轉 $payOnly 使得當 $payOnly 為 false 時不對資料作過濾，當 $payOnly 為 true 代表過濾器啟動時才進而考慮 users.pay

And That's it! 單行可切換的過濾器

Docker Lab

如果你想實驗以上的SQLs而且電腦有安裝Docker，可以考慮用Docker開啟一個測試用的MySQL server來做實驗。Yes, 我在寫這篇筆記時就是用Docker驗證這些SQLs的。

創建實驗用的MySQL容器

首先，開啟一個MySQL容器，由於是為了做實驗而非保存資料，我們不用設置Volume去保存MySQL的檔案。這也意味著，當容器刪除時資料庫的資料會被一併刪除! 所以請不要將下面提供的指令用於生產環境!

使用以下指令產生一個實驗用的MySQL容器

docker run -it \
  --name testdb \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=true \
  -e MYSQL_DATABASE=lab \
  mysql:latest

如果docker運作正常，你會看到創建出來的docker容器開始印出一堆log。等到看到這行時代表容器已經完成資料庫初始化，可以開始接受clients的連線了:

[Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL

連線至容器內的MySQL資料庫

接著，你可以選擇用docker的快捷鍵 Ctrl-p + Ctrl-q 來detach容器的stdout回到terminal或是你可以再開啟另一個terminal

執行以下指令在 testdb 容器內執行 mysql 也就是Mysql的command line client

docker exec -it testdb mysql lab

tonyduan@TonydeMBP project % docker exec -it testdb mysql lab
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

稍微解釋上方指令的原理，這簡短的指令裡有許多巧合

由於mysql容器的使用者為root，而 mysql (終端機客戶端)在不設定使用者的情況下(沒有 -u <user> 參數)會使用作業系統使用者作為mysql的使用者嘗試連線。
又由於，我們在創建容器時用 -e MYSQL_ALLOW_EMPTY_PASSWORD=true 將環境變數 MYSQL_ALLOW_EMPTY_PASSWORD 設為 true ，因此使用者 root 沒有密碼，使我們在以 root 登入時不用使用密碼
我們在創件容器時也同時設定了 -e MYSQL_DATABASE=lab 將環境變數 MYSQL_DATABASE 設為 lab 。這會使得容器內的entrypoint腳本在初始化MySQL server時，順便幫我們新增一個名為 lab 的資料庫。
在docker exec指令中： ... mysql lab ，在 mysql 後方接上的參數是資料庫的名字。讓我們連線後直接使用 lab 資料庫，不用再用 USE lab 切換資料庫。

探索及實驗！

一個可以做實驗的資料庫準備好了，你可以透過上方指令開啟的 mysql 客戶端做實驗。實驗結束後Ctrl-c 中斷mysql客戶端

要關閉伺服器則是 docker stop testdb 先關閉伺服器後，docker rm testdb刪除測試伺服器的容器(注意：測試資料會遺失)。

SQL單行可切換過濾器