網站製作學習誌

在 MySQL 4.1 以上版本怎麼把某個 table 的 COUNT 總數複製到另一個 table 對應的欄位裡呢？

假設這裡有 a 和 b 兩個 table ，其中 b 表有一個欄位 a_id 對應到 a 表的 id 主鍵，而 a 表有個欄位 total 要記錄在 b 表中屬於 a 表的記錄總共有多少筆。一直記得 SQL 可以很簡單做到，可以一下子就是想不起來。

感謝 Neo 一語驚醒夢中人，用 Sub Query 就搞定了：

UPDATE a, (SELECT a_id, COUNT(*) AS total
           FROM b
           GROUP BY a_id) AS c
SET a.total = c.total
WHERE a.id = c.a_id; 

這裡記下來先，不然到時候又忘了；可能有更好的方法，不過目前先完成工作要緊。 :)

在 Spanner 看到一篇 MySQL 語法的效能比較，記下來先：

PHP Mysql tips

Continuing from my earlier post on PHP performance, I thought I’d share a few Mysql tips that I’ve learnt over the years. Hope it helps someone and please leave a comment with your own tips or provide any corrections to the ones mentioned.

Word searching

1.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword')

(Fastest)

2.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('+Keyword' IN BOOLEAN MODE)

(Fast)

3.

SELECT * FROM TABLE WHERE RLIKE '(^| +)Keyword($| +)'

OR

SELECT * FROM TABLE WHERE
RLIKE '([[:space:]]|[[:<:]])Keyword([[:space:]]|[[:>:]])'

(Slow)

Contains searching

1.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('Keyword*' IN BOOLEAN MODE)

(Fastest)

2.

SELECT * FROM TABLE WHERE FIELD LIKE 'Keyword%'

(Fast)

3.

SELECT * FROM TABLE WHERE MATCH (`field`) AGAINST ('*Keyword*' IN BOOLEAN MODE)

(Slow)

4.

SELECT * FROM TABLE WHERE FIELD LIKE '%Keyword%'

(Slow)

Recordsets

1.

SELECT SQL_CALC_FOUND_ROWS * FROM TABLE WHERE Condition LIMIT 0, 10

SELECT FOUND_ROWS()

(Fastest)

2.

SELECT * FROM TABLE WHERE Condition LIMIT 0, 10

SELECT COUNT(PrimaryKey) FROM TABLE WHERE Condition

(Fast)

3.

$result = mysql_query("SELECT * FROM table", $link);
$num_rows = mysql_num_rows($result);

(Very slow)

Joins

Use an INNER JOIN when you want the joining table to only have matching records that you specify in the join. Use LEFT JOIN when it doesn’t matter if the records contain matching records or not.

SELECT * FROM products
INNER JOIN suppliers ON suppliers.SupplierID = products.SupplierID

Returns all products with a matching supplier.

SELECT * FROM products
LEFT JOIN suppliers ON suppliers.SupplierID = products.SupplierID

WHERE suppliers.SupplierID IS NULL

Returns all products without a matching supplier.

Best practice

1. Always use lowercase for table names. (If you use different OS’s this is a must)
2. Always prepend the table name to the field. E.g. ProductName, SupplierPostCode.
This makes multiple joins very easy.
3. Always create a primary id field with the name of the table followed by the id. e.g. ProductID
4. Index fields used for joins.
5. Use a separate logging table or transactions for logs of table updates, deletes etc.

先前提過一篇 A MySQL 4.1 Story ，裡面把 MySQL 4.1的編碼解釋的還滿清楚的；但是我想還是有很多人在使用 MySQL 4.0 (或以下的版本)，因此裡面的解法就不適用了。

所以這次就來好好做個實驗吧！我將會把我遇到的所有狀況列舉出來，並且提出解決的方法。

不過往下看之前，請先確定你會使用 PHP 操作 MySQL ，也曉得 MySQL 版本之間的差異；最重要的是，你得知道 big5 、 latin1 及 utf8 是什麼東西。

這是一篇有關 MySQL 4.1 編碼的文章，我想看完後大概能解決很多人的疑惑。

註：本來想自己寫的，既然有人發難了 (而且寫的比我知道的還多) ，就直接拿來參考吧。

文章網址： A MySQL 4.1 Story (簡體中文)

重點提示：

因為 latin1 在許多地方作為默認的字符集，成功的蒙蔽了許多 PHP 程序的開發者和用戶，掩蓋了在中文等語言環境下會出現的問題；

我在公司的 MySQL 4.0 + phpMyAdmin 2.7.0 pl2 中把資料用 SQL 格式一次匯出，想轉到另一台主機的 MySQL 5.0 上，結果試了 UTF-8 、 BIG5 、 latin1 等等，都無法順利將資料匯入 (許功蓋的問題) 。結果改用 binary 格式後就成功了，特此記之。

補充：看了 axis 的回應，我要特此聲明一下，這個方式我測過只適用我們公司，其他環境是否是一樣的做法，我不能確定！因為這些匯入匯出的方式和 mysql 的字元校對、 client 和 server 端的預設編碼還有 phpMyAdmin 的 Language 設定都有很大的關係，這些有機會我再做一次完整的說明。

前陣子看到 MySQL 5 已經正式推出了，支援 Stored Procedures 、 Functions 、 Views 、 Triggers 等。

目前 phpMyAdmin 2.6.4 好像還不支援這些新特色 (View 可以看得到，但是沒有建立的介面？) ，不過 MySQL 官方自己推了一個 MySQL Query Browser ，還不錯用， 缺點是中文的支援性不佳 (輸入中文時會怪怪的) 。

小試了一下用 MySQL Query Browser 寫一個 Stored Procedure ：

DELIMITER $$

DROP PROCEDURE IF EXISTS `spTest` $$
CREATE PROCEDURE `spTest`(p_title VARCHAR(200), p_description TEXT)
    DETERMINISTIC
BEGIN
  INSERT INTO category (title, description) 
  VALUES (p_title, p_description);
END $$

DELIMITER ;

其中 DELIMITER 是用來決定 SQL 分隔符號的，在寫 Stored Procedure 前，要先把預設的分號換掉，不然按下 MySQL Query Browser 的 Execute 按鈕時，會真的去跑裡面的 INSERT 指令。寫完 Stored Procedure 的 routine_body 後，再回復成預設的分號。

另外我在寫這個 Stored Procedurce 時，發現一定要加上 characteristic 才行，也就是上面的 DETERMINISTIC 。可是官方手冊明明寫可略過，這我就沒時間研究為什麼了。

另外就是參數的名稱， MySQL 5 不像 MSSQL 是用 @param 來表示 Stored Procedure 的參數？這樣有點難以分辨參數及資料表欄位，不過也不是不能解決。

執行這個 Stored Procedure 的方法是用 CALL：

CALL spTest 'TEST', 'ABC';

當然語法不同，就會造成寫 PHP 程式的不方便，改天來找找看有沒有比較好的抽象層資料庫存取介面 (就是像 PEAR::DB 或 ADOdb 之類的物件類別庫) ，可以支援不同類型資料庫的 Stored Procedure 。

Stored Procedure 的寫法可參考：http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html

Trigger 的寫法可參考：http://dev.mysql.com/doc/refman/5.0/en/triggers.html

View 的寫法可參考：http://dev.mysql.com/doc/refman/5.0/en/views.html