標籤: MySQL

建立一個 table 並塞 fake data 來當作測試的資料表：

use `test`;

CREATE TABLE `people` (
  `id` bigint UNSIGNED NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth` date NOT NULL,
  `phone` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `addr` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `people`
  ADD PRIMARY KEY (`id`);

正常情況下要查詢 rows count 大概就是以下的 query 方法：

SELECT count(*) from prople ;

這個 query 會跑去 scan table 或是 scan index，當資料量到一定規模，scan 會花上好幾分鐘。

另一個方法是去 information_schema 裡面做查詢，可以找到每個 table 的大概的狀況 (不會是準確的數字)：

SELECT * 
FROM `TABLES` 
WHERE     TABLE_SCHEMA = 'test' 
      and TABLE_NAME = 'people'

不過我在找問題時用了 explain，意外發現一個更快、更準確的方法：explain 會列出 index size，如果是使用 primary key + auto increment，這個 index size 其實就是 table size：

> explain select count(9) from `people` \G

id            | 1
select_type   | SIMPLE
table         | people
partitions    | <null>
type          | index
possible_keys | <null>
key           | PRIMARY
key_len       | 8
ref           | <null>
rows          | 11366812
filtered      | 100.0
Extra         | Using index

最近在研究 DBMS 的 trigger，不同 DBMS 的 trigger 的功能上差異不小，這裡僅對 MySQL 的 trigger 做筆記。

trigger 的意思同詞一樣，設計目的是讓 DB 的特定事件發生以後，去執行指定的動作。例如某 table 新增了一筆資料以後，將該筆資料記錄到另一個 table。

這次先使用「當使用者註冊成功以後，在 audit log 留下使用者 ID 和註冊的時間。先來建立 users 和 user_audit_log 這二個 table：

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_name` VARCHAR(200) NOT NULL,
    `mail` varchar(255) NOT NULL
);

CREATE TABLE `user_audit_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_name` VARCHAR(200) NOT NULL,
    `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP()
);

然後來看一下 trigger 的 語法，trigger 可以針對 insert / update / delete 這幾個動作作出反應，另外也可以將 trigger 的動作放在以上幾個動作的之前 BEFORE 與之後 AFTER 執行，也就是說總共有以下這 6 種 trigger：

More “MySQL trigger 介紹” »

MySQL 中要使用 IF ... ELSE 挺麻煩的，如果要在 insert 失敗 (資料已存在) 以後改對資料做 update 的話，可以考慮使用 INSERT .... ON DUPLICATE KEY。

假設要在 user 的 table 中，新增 user_id 和 mail：

INSERT INTO `user` (
    `user_id`,
    `mail`
) VALUES (
    "zero",
    "zero@zeroplex.tw"
)

當 user_id 是 unique index 時，上面的語法會失敗：duplicated key。
這時可以使用 MySQL 中的 INSERT … ON DUPLICATE KEY 語法，來讓 MySQL 自動判斷要使用 INSERT 還是 UPDATE：

INSERT INTO `user` (
    `user_id`,
    `mail`
) VALUES (
    "zero",
    "zero@zeroplex.tw"
) ON DUPLICATE KEY UPDATE 
    `mail` = "zero@zeroplex.tw"

上述語法，會讓 MySQL 執行時，若發現 zero 這個使用者已經存在時，變自動去更新他的 mail。