---
# System prepended metadata

title: MySQL-3

---

# MySQL-3

## 數據備份與還原
> - 數據備份還原方式: 數據表備份, 單表數據備份, SQL 備份, 增量備份

### 數據表備份(這把我的MySQL 搞掛了==)
> - 直接找到數據庫文件夾, 複製對應的表結構與數據文件
> - 還原時, 直接導入備份內容
> - 數據表備份根據不同存儲引擎, 會有不同區別
>   - 存儲引擎: 
>     - mysql 存儲數據方式
>     - 主要為 innodb 與 myisam, 因為免費
>       - innodb 
>         - 雖然有存儲限制, 但限制數據大小已經大到沒有限制的級別(64TB)
>         - innodb 批量插入速度低
>           - 主因是 innodb 是行鎖
>             - archive 雖然是行鎖, 但是他是在內存中操作的
>           - 現實中除非特別節慶, 否則很少需要批量插入數據
>         - innodb 佔用空間大的原因在於他是集中式存儲
>           - innodb 只有表結構, 其他數據都存在 `ibdate1` 文件中
>             - 優點: 效率高
>             - 缺點: 算法複雜
>           - innodb 的數據表只能在系統創建的數據庫下面, 手動複製不會被識別
>             亦即數據表備份 innodb 無法用
>       - myisam 存儲數據方式為單獨分開存儲 (表, 數據, 索引)
>         - 數據表備份通常是用於 `myisam` 引擎, 直接複製貼上三個文件即可
>           表, 數據, 索引都是單獨存在, 不會綁定數據庫
>         - 但是我一複製貼上, mysql 就掛了...


> - 創建使用 myisam 引擎的表
```sql
mysql> -- 創建一個 myisam 存儲引擎的表
mysql> create table my_myisam (id int) charset utf8 engine = myisam;
Query OK, 0 rows affected, 1 warning (0.01 sec)
```
```shell
$  % tree /usr/local/var/mysql/test2                      
├── my_class.ibd
├── my_myisam.MYD      # 數據
├── my_myisam.MYI      # 索引
├── my_myisam_462.sdi  # 結構
└── my_stu.ibd
```


> - 手動備份與還原(失敗且害我的 mysql 掛了= =)
```sql
mysql> use test_engine;
Database changed
mysql> show tables;
Empty set (0.00 sec)
```

```shell
$ # 直接去/usr/local/var/mysql/test2 裡ctrl c + v 
$ # 複製一組 myisam 跟 一張 innodb 到 test_engine 資料夾
$ tree  /usr/local/var/mysql/test_engine 
├── my_class.ibd
├── my_myisam.MYD
├── my_myisam.MYI
└── my_myisam_462.sdi


$ # 我一手動複製貼上文件到另外一個數據庫, 我就發現我的 mysql 掛了...
$ mysql.server restart
ERROR! MySQL server PID file could not be found!
Starting MySQL
. ERROR! The server quit without updating PID file
```


### 存儲引擎

||Myisam|innodb|BDB|Memory|Archive|
|:--:|:--:|:--:|:--:|:--:|:--:|:--:|
|批量插入速度|高|低|高|高|非常高|
|事務安全||支持|支持|||
|全文索引|支持|5.5後支持||||
|鎖|表鎖|行鎖|頁鎖|表鎖|行鎖|
|存儲限制|無|64TB|無|有|無|
|B樹索引|支持|支持|支持|支持||
|Hash索引||支持||支持||
|集群索引||支持||||
|數據緩存||支持||支持||
|索引緩存|支持|支持||支持||
|數據壓縮|支持||||支持|
|空間使用|低|高|低|N/A|非常低|
|緩存使用|低|高|低|中|低|
|外鍵||支持||||


### 單表數據備份 (遇到問題, 沒測試成功)
> - 顧名思義, 每次只能備份一張表, 且備份的內容為數據(沒有表結構)
> - 使用場景: 將表中的數據導出到文件

> #### 基本語句
> `select */字段列表 into outfile '文件所在路徑' from 數據源`
> - 前提: 外部文件不存在

```sql
mysql> select * from my_class;
+----+--------------+------+
| id | name         | item |
+----+--------------+------+
|  1 | 三年三班     | JS   |
|  2 | 二年四班     | PHP  |
|  3 | 一年五班     | C    |
+----+--------------+------+
3 rows in set (0.00 sec)



mysql> -- 備份
mysql> select * into outfile '~/Desktop/my_class.txt' from my_class;


mysql> -- 問題: 5.7 後, MySQL 有做了一些限制, 必須去修改 my_cnf 或 my_ini
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
```

> #### 1290 解法
> - 參考
>   - https://blog.csdn.net/fdipzone/article/details/78634992
>   - https://ithelp.ithome.com.tw/articles/10197804?sc=rss.qu
> - 原因: 
>   - 預設 `secure_file_priv` 的參數為 NULL
>     - `NULL` : 不允許導出導入
>     - `路徑` : 只允許導入到這個路徑中
>     - 沒有值 : 沒有限制 
```sql
mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)
```


> - 找到 `my_cnf` 檔, 在裡面新增 `secure_file_priv=''` 後重啟 mysql
>   - 有陰影啦幹, 先不亂改
```shell
$ find /* -name my.cnf
find: ...
/System/.../usr/local/etc/my.cnf
/System/.../Cellar/mysql/8.0.19/.../my.cnf
find: ...

$ vi /System/.../Cellar/mysql/8.0.19/.../my.cnf
# 在裡面加入 secure_file_priv='' 後重啟 mysql
# 不過我在弄數據表備份時已經把我 MySQL 搞掛過一次了, 先不要亂搞, 放著等看到更多資料再說
```

> #### 高級備份
> `select */字段列表 into outfile '文件所在路徑' [fields 字段處理 lines 行處理] from 數據源`
> - 假設一切正常備份, 應該會出現一個文檔, 以上面那張表為例, 應該會長這樣
```txt
1    三年三班  JS
2    二年四班  PHP
3    一年五班  C
```
> - `fields` : 字段處理
>   - `enclosed by` : 字段用什麼內容包裹, 默認為`''` (空字符串)
>   - `terminated by` : 字段以什麼結束, 默認為 `\t` (tab)
>   - `escaped by` : 特殊符號用什麼方式處理, 默認用 `\\` (反斜線轉譯)
> - `lines` : 行處理
>   - `starting by` : 每行以什麼開始, 默認為 `''` (空字符串)
>   - `terminated by` : 每行以什麼結束, 默認為 `\n` (換行符)
```sql
mysql> select * into outfile '~/Desktop/my_class.txt' 
     > fields enclosed by '"' terminated by '|'
     > lines starting by 'Start: '
     > from my_class;
```
```txt
Start: "1"|"三年三班"|"JS"
Start: "2"|"二年四班"|"PHP"
Start: "3"|"一年五班"|"C"
```


> #### 數據還原
> `load data infile 文件路徑 into table 表名[(自段列表)] [fields 字段處理 lines 行處理]`
> - 將外部數據恢復到表中
> - 表結構必須存在, 因為備份出去的只有數據沒有表結構
> - 怎麼備份的就怎麼還原, 亦即如果備份有特別處理字段跟行, 還原也要寫一次

```sql
mysql> load data infile '~/Desktop/my_class.txt' into table my_class
     > fields enclosed by '"' terminated by '|'
     > lines starting by 'Start: ';
```



### SQL 備份
> - 備份 SQL 語句: 
>   - 系統對結構與數據進行處理而全部變成 SQL 語句來備份
>   - 還原時執行 SQL 語句即可

> - 優點: 可以備份表結構
> - 缺點: 
>   - 浪費空間(額外多一堆SQL指令), 
>   - 備份時間也比較長(一定是整個表備份, 如果原先存在表結構, 該表結構會先被幹掉)

> #### 備份
> - MySQL 沒有提供備份指令, 需要利用 MySQL 提供的 mysqldump

```shell
$ tree /System/Volumes/Data/usr/local/Cellar/mysql/8.0.19/bin/
...
├── mysql  # 客戶端
├── mysql.server -> ../support-files/mysql.server
...
├── mysqld  # 服務端
...
├── mysqldump # 備份用的客戶端
...
```

> #### mysqldump 備份
> `$ mysqldump [-h電腦] [-p端口] -u帳號 -p密碼 數據庫名 [數據表名1 [數據表名2...]] > 外部文件目錄(最好使用 .sql)`
> - mysqldump 也是一種客戶端, 所以當然需要連接認證
```shell
$ # 整張表備份
$ mysqldump -uroot -p test my_class > ~/Desktop/my_class.sql # 備份
$ tree ~/Desktop/             
└── my_class.sql  # 備份成功


$ # 整個數據庫備份
$ mysqldump -uroot -p test > ~/Desktop/test.sql     
$ tree ~/Desktop/ 
├── my_class.sql
└── test.sql

$ cat ~/Desktop/my_class.sql  # 看一下裡面寫啥
-- MySQL dump 10.13  Distrib 8.0.19, for osx10.15 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	8.0.19

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `my_class`
--

DROP TABLE IF EXISTS `my_class`;  -- 如果裡面有同名表, 執行導入時會被幹掉
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `my_class` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `item` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `my_class`
--

LOCK TABLES `my_class` WRITE;   -- 寫數據的時候會鎖表(別人不能用)
/*!40000 ALTER TABLE `my_class` DISABLE KEYS */;
INSERT INTO `my_class` VALUES (1,'三年三班','JS'),(2,'二年四班','PHP'),(3,'一年五班','C');
/*!40000 ALTER TABLE `my_class` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-01-20  1:08:25
```


#### 還原
> - 還原有兩種方式: mysql 客戶端還原 跟 

> #### mysql 客戶端還原
> `$ mysql -hpup 數據庫名 < 備份文件目錄`
```sql
mysql> -- 刪除數據
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_class       |
+----------------+
1 row in set (0.00 sec)

mysql> drop table my_class;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)
```

```shell
$ # 還原數據
$ mysql -uroot -p test < ~/Desktop/my_class.sql 
```

```sql
mysql> -- 查看成功與否
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_class       |
+----------------+
1 row in set (0.01 sec)

mysql> select * from my_class;
+----+--------------+------+
| id | name         | item |
+----+--------------+------+
|  1 | 三年三班     | JS   |
|  2 | 二年四班     | PHP  |
|  3 | 一年五班     | C    |
+----+--------------+------+
3 rows in set (0.00 sec)       -- 還原成功
```

> #### SQL 指令備份
> `source 備份SQL文件路徑`
```sql
mysql> -- 刪除數據
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_class       |
+----------------+
1 row in set (0.00 sec)

mysql> drop table my_class;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)


mysq> -- 備份數據
mysql> source ~/Desktop/my_class.sql

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| my_class       |
+----------------+
1 row in set (0.00 sec)

mysql> select * from my_class;
+----+--------------+------+
| id | name         | item |
+----+--------------+------+
|  1 | 三年三班     | JS   |
|  2 | 二年四班     | PHP  |
|  3 | 一年五班     | C    |
+----+--------------+------+
3 rows in set (0.00 sec)
```



### 增量備份
> - 針對 MySQL 服務器的日誌文件做備份
>   - 所有操作都會備份
> - 指定時間開始進行備份
> - 備份數據不會重複
>   - 不會刪表備份
>   - 上次備份到哪, 下次接著備份
>   - 效率較高


## 事務(transaction)
> - 問題: A轉帳給B的過程斷電, A帳戶減少, B帳戶沒有增加
>   - 解決: A減少錢並不馬上修改數據表, 待B收到錢後, 同時修改數據表
> - 事務: 一系列要發生的連續操作
>   - 轉帳需要A轉給B, 且B收到
>   - 領錢需要插卡>驗證身份>選擇領錢>輸入金額>銀行服務器確認你有錢>確認ATM有錢>吐錢>還卡
> - 事務安全: 保護連續操作同時滿足的機制

> - 事務前提: 存儲引擎必須為 `innodb` 或 `BDB`(收費)
> - 事務原理: 
>   - 事務開啟後, 所有操作都會保存到`事務日誌`
>   - `事務日誌` 會有兩種情況清空
>     - `commit;` : 提交數據到數據庫後清空
>     - `rollback, 關閉連接SQL, 不正常關閉...等` : 直接清空

> - 事務流程
>   - 客戶端連接服務器時, 服務器會做三件事
>     - 驗證身份
>     - 建立連接
>     - 開啟臨時事務日誌(當前用戶當次連接)
>   - 客戶端直接寫 SQL 時, 服務器會
>     - 接收 SQL
>     - 執行 SQL
>     - 同步到數據庫表(自動事務)
>       - 事實上他也是會寫到臨時日誌中
>       - 只是 MySQL 預設自動提交(`autocommit`), 所以寫進去馬上就提交了
>     - 查詢操作也會直接從數據庫表拿數據後返回
>   - 客戶端開啟事務 (`start transaction;`)
>     - 服務器會改變原來的機制, 所有寫入操作都會先寫道臨時事務日誌中
>     - 寫入操作
>       - 接收 SQL
>       - 執行 SQL
>       - 將結果寫入臨時事務日誌
>     - 查詢操作
>       - 從數據表拿數據
>       - 將數據拿到臨時日誌加工
>         (所以用戶操作時會看到修改後數據, 但實際上沒有改到數據庫)
>       - 返回數據
>   - 事務結束 (`commit`, `rollback`, 斷開連接)
>     - `commit` : 將臨時日誌數據提交數據庫, 清空臨時日誌
>     - `rollvack` 或 斷開SQL 連接: 清空臨時日誌

> - 事務操作是針對數據操作, 千萬別以為開事務寫 drop table 不用坐牢~~

```sql
mysql> create table my_count(
    -> id int primary key auto_increment,
    -> num char(16) not null unique comment '帳戶',
    -> name varchar(20) not null,
    -> money decimal(10,2) default 0.0 comment '餘額') charset utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)


mysql> insert into my_count values(
    -> null, '0000000000000001', '小明', 1000),(
    -> null, '0000000000000002', '小花', 2000);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0


mysql> select * from my_count;
+----+---------------+--------+---------+
| id | num           | name   | money   |
+----+---------------+--------+---------+
|  1 | 0000000000001 | 小明   | 1000.00 |
|  2 | 0000000000002 | 小花   | 2000.00 |
+----+---------------+--------+---------+
2 rows in set (0.00 sec)


mysql> -- 沒有事務操作而遇到停電時
mysql> update my_count set money = money - 1000 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> exit;  -- 扣了一千就結束
Bye
```

### 事務操作
> - 事務操作分為自動事務(默認)跟手動事務


#### 手動事務
> - 開啟事務
>   `start transaction`
>   - 告訴系統以下操作先別寫到數據庫, 
>   - 而是先放到事務日誌(`ib_logfile()` 與 `ib_logfile1`)
```sql
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
```
> - 事務操作
>   - 事務操作時, 並不會真的修改數據庫
>     ![](https://i.imgur.com/SYEAZP3.png)
```sql
mysql> select * from my_count;
+----+---------------+--------+---------+
| id | num           | name   | money   |
+----+---------------+--------+---------+
|  1 | 0000000000001 | 小明   | 1000.00 |
|  2 | 0000000000002 | 小花   | 2000.00 |
+----+---------------+--------+---------+
2 rows in set (0.00 sec)


mysql> update my_count set money = money - 1000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update my_count set money = money + 1000 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
> - 關閉事務
>   - 選擇將日誌文件保存到數據庫或直接清空
>   - 提交事務:
>     `commit;`
>     - 提交後, 別人就會看到你修改後的數據
>       ![](https://i.imgur.com/G96JYTi.png)
>   - 回滾事務(不提交直接清掉)
>     `rollback;`
>     - rollback 後, 啥事都當作沒發生
```sql
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from my_count;
+----+---------------+--------+---------+
| id | num           | name   | money   |
+----+---------------+--------+---------+
|  1 | 0000000000001 | 小明   |    0.00 |
|  2 | 0000000000002 | 小花   | 3000.00 |
+----+---------------+--------+---------+
2 rows in set (0.00 sec)
```


### 回滾點
> - 在某個成功操作的數據結果標記一個記憶點, 以提供後續失敗操作的返回點, 而不是全部返回, 該點就是回滾點
> - 設置回滾點
>   `savepoint 回滾點名字`
> - 回到回滾點
>   `rollback to 回滾點名字`

> - 測試: 
>   - 給小明 20000
>   - 扣手續費五趴卻扣到小花
```sql
mysql> select * from my_count;
+----+---------------+--------+---------+
| id | num           | name   | money   |
+----+---------------+--------+---------+
|  1 | 0000000000001 | 小明   |    0.00 |
|  2 | 0000000000002 | 小花   | 3000.00 |
+----+---------------+--------+---------+
2 rows in set (0.00 sec)


mysql> -- 開啟事務
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> -- 給小明 20000
mysql> update my_count set money = money + 20000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 20000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- 設定回滾
mysql> savepoint sp1;
Query OK, 0 rows affected (0.00 sec)



mysql> -- 扣手續費扣錯人
mysql> update my_count set money = money - 20000 * 0.05 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 20000.00 |
|  2 | 0000000000002 | 小花   |  2000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- 回到回滾
mysql> rollback to sp1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 20000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- 扣對人
mysql> update my_count set money = money - 20000 * 0.05 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 19000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- 提交
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
```


### 自動事務(autocommit)
> - MySQL 默認都是自動事務, 用戶操作後會立即同步到數據庫表中
> - 系統透過 `autocommit` 控制是否自動提交

> #### 查看 `autocommit` 狀態
> `show variables like 'autocommit';`
```sql
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)
```

> #### 開關自動事務
> - 關閉: `set autocommit = off/0;`
> - 開啟: `set autocommit = on/1;`
```sql
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
```
> - 再次執行操作時, 就不會自動提交了, 必須手動提交
>   ![](https://i.imgur.com/xmOoqk8.png)
```sql
mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 19000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- 關閉自動提交後, 必須手動提交數據才能同步到數據庫表中
mysql> update my_count set money = money - 9000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 10000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)
```

### 事務特性(ACID)
> #### A (Atomic) : 
> - 原子性, 事務操作是一個整體, 不可分割(要馬成功要馬失敗)

> #### C (Consistency) : 
> - 一致性, 數據操作前後, 數據表中沒有變化(commit才會改)

> #### I (Isolation) : 
> - 隔離性, 事務操作互相隔離而不受影響
>   - 當客戶端開啟事務後, 數據就會被拿到臨時事務文件處理, 此時外面怎樣修改都不影響自己的數據

> - 開啟事務時, 
>   - 查詢操作只會去數據庫表拿一次數據而放在臨時文件中
>   - 操作數據時, 會再次去拿數據來操作, 而非剛剛查詢的那筆數據
```sql
mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 21000.00 |
|  2 | 0000000000002 | 小花   |  5000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)

mysql> -- A 客戶端開啟事務 -------------------------------
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
```
```sql
mysql> -- 此時 B 客戶端馬上修改資料
mysql> update my_count set money = 3000 where id = 2;
```
```sql
mysql> -- A 客戶端查詢操作會拿到最新的數據(他會拿到臨時文件)
mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 21000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)
```
```sql
mysql> -- 但是在A客戶端拿過數據之後 B客戶端再改資料
mysql> update my_count set money = 4000 where id = 2;
```
```sql
mysql> -- 此時結果證明 A 客戶端不會再去數據庫拿資料, 而是直接使用剛剛拿的
mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 21000.00 |
|  2 | 0000000000002 | 小花   |  3000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- 但是此時如果 A 客戶端是操作數據時, 會再次去數據庫表拿數據來操作
mysql> update my_count set money = money - 3000 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 21000.00 |
|  2 | 0000000000002 | 小花   |  1000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)

mysql> -- 字面上減 3000 應該為 0, 但事實上還有一千, 證明他還有去拿一次最新的(4000)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)
```

> - 鎖機制:
>   - `innodb` 默認為行鎖(操作哪一行時, 別人就不能操作該行)
>   - 但如沒用到索引, 系統會自動轉為表鎖(操作某張表時, 別人不能操作該表)

> - 行鎖 (鎖住索引行)
```sql
mysql> select * from my_count;
+----+---------------+--------+----------+
| id | num           | name   | money    |
+----+---------------+--------+----------+
|  1 | 0000000000001 | 小明   | 21000.00 |
|  2 | 0000000000002 | 小花   |  1000.00 |
+----+---------------+--------+----------+
2 rows in set (0.00 sec)


mysql> -- A 客戶端開啟事務
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> -- 操作有用到索引
mysql> update my_count set money = money - 1000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
```sql
mysql> -- 此時B客戶端如果也操作該行, 會等一段時間, 如果A表都沒提交, 就會報錯
mysql> update my_count set money = money + 2000 where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> -- 但是操作其他行就不會有問題, 因為使用事務操作索引數據只會行鎖
mysql> update my_count set money = money + 2000 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```

> - 表鎖 (鎖住整張表)
```sql
mysql> -- A 客戶端開啟事務
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> -- 操作時沒有用到索引時
mysql> update my_count set money = money - 2000 where name = '小明';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
```
```sql
mysql> -- B 客戶端整張表都不能動了, 因為會改成表鎖
mysql> update my_count set money = money + 2000 where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
```

> #### D (Durability) : 
> - 持久性, 數據一提交, 永久改變數據




## 變量
### 系統變量
> - 系統定義好的變量
> - 系統用來控制服務器所使用的

> #### 查看系統變量
> `show variables;`

> #### 查看具體變量值
> `select @@變量名[,@@變量名[,@@...]];`
> - 任何有數據返回的都是用 select 查看

```sql
mysql> select @@version, @@autocommit;
+-----------+--------------+
| @@version | @@autocommit |
+-----------+--------------+
| 8.0.19    |            1 |
+-----------+--------------+
1 row in set (0.00 sec)
```

> #### 修改系統變量
> - 會話級別(臨時修改)
>   `set 變量名 = 值;` 或 `set @@變量名 = 值;`
>   - 當前客戶端當次連接有效
>     <img src='https://i.imgur.com/4vrfiL5.png' style='width: 400px'/>
> - 全局級別
>   `set global 變量名 = 值`
>   ![](https://i.imgur.com/dbybf59.png)
>   - 必須退出重登後才生效, 當次連接尚未生效
>     ![](https://i.imgur.com/BZ39cix.png)

### 自定義變量
> - 所有自定義變量為會話級別(當次連接有效), 因為他只保存在內存中
> - 自定義變量屬於用戶級別, 不認數據庫, 在哪都能叫

> #### 定義變量
>`set @變量名 = 值;` 或 `set @變量名 := 值`
> - 系統為了區分系統變量, 規定自定義變量使用一個 @
> - MySQL 的 `=` 通常為比較符, MySQL 因此創造了 `:=` 來表達賦值符
```sql
mysql> set @name = '小美';
Query OK, 0 rows affected (0.00 sec)

mysql> set @age := 18;
Query OK, 0 rows affected (0.00 sec)
```

> #### 查看變量
> `select @變量名;`
```sql
mysql> select @name;
+--------+
| @name  |
+--------+
| 小美   |
+--------+
1 row in set (0.00 sec)
```


> #### select 賦值1
> `select @變量名 := 字段名 from 數據源`
> - 從字段取值給變量名
> - 如果寫成 `@變量名 = 字段名` 會變成比較符而輸出 0 跟 1, 沒有賦值效果
> - 這方法會顯示 Select Return
```sql
mysql> select * from my_class;
+----+--------------+------+
| id | name         | item |
+----+--------------+------+
|  1 | 三年三班     | JS   |
|  2 | 二年四班     | PHP  |
|  3 | 一年五班     | C    |
+----+--------------+------+
3 rows in set (0.00 sec)



mysql> select @name = name from my_class;
+--------------+
| @name = name |
+--------------+
|            0 | -- 比較 @name(小美) 是否 = name(三年三班) => 0
|            0 | -- 比較 @name(小美) 是否 = name(二年四班) => 0
|            0 | -- 比較 @name(小美) 是否 = name(一年五班) => 0
+--------------+
3 rows in set (0.00 sec)

mysql> select @name;
+--------+
| @name  |
+--------+
| 小美   |  -- 沒有賦值
+--------+
1 row in set (0.00 sec)
```

```sql
mysql> select @name := name from my_class;
+---------------+
| @name := name |
+---------------+
| 三年三班      |  -- 跑第一圈時, @name 被賦值成 '三年三班'
| 二年四班      |
| 一年五班      |  -- 跑第三圈時, @name 被賦值成 '一年五班'
+---------------+
3 rows in set, 1 warning (0.00 sec)

mysql> select @name;
+--------------+
| @name        |
+--------------+
| 一年五班     |
+--------------+
1 row in set (0.00 sec)
```

> #### select 賦值2
> `select 自段列表 from 數據源 [where] into 變量列表;`
> - 嚴格要求數據只能有一條(MySQL不支持陣列)
> - 不會顯示 select Return
```sql
mysql> -- 多條數據會報錯
mysql> select name, item from my_class into @name, @item;
ERROR 1172 (42000): Result consisted of more than one row


mysql> -- 單條數據沒事兒
mysql> select name, item from my_class where id = 1 into @name, @item;
Query OK, 1 row affected (0.01 sec)

mysql> select @name, @item;
+--------------+-------+
| @name        | @item |
+--------------+-------+
| 三年三班     | JS    |
+--------------+-------+
1 row in set (0.00 sec)
```

## 觸發器 (trigger)
> - 事先為表綁定一段 code, 當表中某些內容改變時(增刪改), 執行該段代碼
> - 說白了就是類似 JS 的事件
> - 一張表只能擁有一種觸發時間, 
>   一種觸發時間只能擁有一種事件類型,
>   故最多6種觸發器(`before`, `after`) x (`增`, `刪`, `改`)
>   - 觸發時間: `before` 或 `after`
>   - 事件類型: `insert` 或 `delete` 或 `update`
>   - 觸發對象: 每一條數據(行)

> - 千萬記得, 避免死循環
```sql
create trigger tmp after update on my_order for each row  -- 訂單更新數據時觸發
begin
    update my_order set .....;  -- 觸發內容是更新數據 => 死循環 GG
end
```
### 創建觸發器
> - MySQL 中沒有大括號, 都是利用對應字符符號代替
```sql
delimiter 自定義符號 -- 後續代碼碰到此符號才算結束

create trigger 觸發器名字 觸發時間 事件類型 on 表名 for each row
begin -- 觸發器開始

    xxx; -- 觸發器內容

end -- 觸發器結束

自定義符 -- 表示結束

delimiter  ; -- 把結束符改回來
```
> #### 修改語句結束符
> - 預設語句結束符為 `;`, 但是在這裡, 觸發器內容也必須寫 `;` 來表達一條語句結束
>   但是實際上還沒結束整個觸發器, 所以必須在創建觸發器之前修改結束符, 
>   讓系統看到 `;` 不會以為結束而報錯
```sql
mysql> ;  -- 預設結束符
ERROR: 
No query specified

mysql> delimiter $$  -- 修改結束符為 $$
mysql> $$
ERROR: 
No query specified

mysql> ;    -- 因為結束符修改過了, 所以這裡沒有結束
    -> $$   -- 而是這裡結束
ERROR 1065 (42000): Query was empty


mysql> delimiter ;  -- 改回來
mysql> ;
ERROR: 
No query specified
```
```sql
mysql> -- 創建一些測試用數據
mysql> select * from my_goods;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |  100 |
|  2 | banana | 25.00 |  200 |
+----+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from my_order;
Empty set (0.00 sec)



mysql> delimiter |   -- 臨時結束符

mysql> create trigger after_order after insert on my_order for each row      -- 觸發語句
    -> begin  -- 觸發開始 ( 類似 '{' )
    -> update my_goods set inv  = inv - 1 where id = 2;  -- 綁定的內容
    -> -- 在這裡如果沒有臨時結束符, 系統會以為; 代表結束而結束這東西, 故必須改結束符
    -> end  -- 觸發結束 ( 類似 '}' )
    -> |    -- 真正的結束符
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> -- 整個事件就是在說, 
mysql> -- 當每次 my_order 有數據插入後, my_goods 的第二項商品(香蕉)數量 -1
```


### 查看觸發器

> #### 查看所有觸發器(模糊查詢)
> `show triggers [like 'pattern'];`
```sql
mysql> show triggers\G
*************************** 1. row ***************************
             Trigger: after_order
               Event: INSERT
               Table: my_order
           Statement: begin
update my_goods set inv  = inv - 1 where id = 2;
end
              Timing: AFTER
             Created: 2020-01-20 17:08:34.50
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
```

> #### 查看觸發器創建語句
> `show create trigger 觸發器名;`
```sql
mysql> show create trigger after_order\G
*************************** 1. row ***************************
               Trigger: after_order
              sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `after_order` AFTER INSERT ON `my_order` FOR EACH ROW begin
update my_goods set inv  = inv - 1 where id = 2;
end
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2020-01-20 17:08:34.50
1 row in set (0.00 sec)
```
> #### information_schema.triggers
> `select * from information_schema.triggers;`
> - 所有的觸發器都會存在 `information_schema.triggers` 中
```sql
mysql> select * from information_schema.triggers\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_insert_set_user
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
    IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
        SET NEW.set_by = USER();
    END IF;
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2020-01-18 00:19:06.82
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_update_set_user
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
    IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
        SET NEW.set_by = USER();
    END IF;
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2020-01-18 00:19:06.82
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: after_order
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: my_order
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: begin
update my_goods set inv  = inv - 1 where id = 2;
end
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD   -- 觸發器紀錄
  ACTION_REFERENCE_NEW_ROW: NEW   -- 觸發器紀錄
                   CREATED: 2020-01-20 17:08:34.50
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_general_ci
        DATABASE_COLLATION: utf8mb4_general_ci
3 rows in set (0.01 sec)
```

### 觸發觸發器
> - 當綁定事件觸發時, 就會觸發觸發器
```sql
mysql> select * from my_goods;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |  100 |
|  2 | banana | 25.00 |  200 |
+----+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from my_order;
Empty set (0.01 sec)

mysql> desc my_order;
+-------+------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra          |
+-------+------+------+-----+---------+----------------+
| id    | int  | NO   | PRI | NULL    | auto_increment |
| g_id  | int  | NO   |     | NULL    |                |
| g_num | int  | NO   |     | NULL    |                |
+-------+------+------+-----+---------+----------------+
3 rows in set (0.01 sec)


mysql> -- 當訂單數據插入後, my_goods 的香蕉會 -1
mysql> insert into my_order values (null, 1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_goods;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |  100 |
|  2 | banana | 25.00 |  199 |  -- 香蕉 -1
+----+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from my_order;
+----+------+-------+
| id | g_id | g_num |
+----+------+-------+
|  1 |    1 |     1 |  -- 數據成功插入
+----+------+-------+     -- 雖然有點問題, 但這只是證明觸發器有成功觸發
1 row in set (0.00 sec)
```

### 修改觸發器 & 刪除觸發器
> - 觸發器只能刪除後新增
> #### 刪除觸發器
> `drop trigger 觸發器名`
```sql
mysql> drop trigger after_order;
Query OK, 0 rows affected (0.01 sec)

mysql> show triggers\G
Empty set (0.00 sec)
```


### 觸發器紀錄
> - 不管觸發器是否觸發, 當某種操作準備執行時, 系統就會將當前的狀態與即將執行的狀態記錄下來, 記錄在
>   - `ACTION_REFERENCE_OLD_ROW` : 紀錄當前紀錄
>   - `ACTION_REFERENCE_NEW_ROW` : 紀錄即將執行的新紀錄(假設會執行的結果)
>   - 刪除沒有NEW, 新增沒有OLD
> - NEW 與 OLD 都代表紀錄本身, 除了數據還有字段
> - 使用方式: 
>   `old.字段名 / new.字段名`


```sql
mysql> select * from my_goods;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |  100 |
|  2 | banana | 25.00 |  199 |
+----+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from my_order;
+----+------+-------+
| id | g_id | g_num |
+----+------+-------+
|  1 |    1 |     1 |
+----+------+-------+
1 row in set (0.00 sec)



mysql> -- 寫觸發器
mysql> delimiter  |
mysql> create trigger order_after after insert on my_order for each row
    -> begin 
    -> -- INSERT 只有 NEW, 代表新的紀錄
    -> -- 中文應該是: 當新增一條數據時, 訂單購買的品項數量減少訂單購買數量
    -> update my_goods set inv = inv - new.g_num where id = new.g_id;
    -> end
    -> |
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;


mysql> -- 觸發觸發器, 買蘋果三十個
mysql> insert into my_order value (null, 1,30);
Query OK, 1 row affected (0.01 sec)

mysql> select * from my_order;
+----+------+-------+
| id | g_id | g_num |
+----+------+-------+
|  1 |    1 |     1 |
|  2 |    1 |    30 |  -- 買蘋果三十個
+----+------+-------+
2 rows in set (0.00 sec)

mysql> select * from my_goods;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |   70 |  -- 扣蘋果三十個 !
|  2 | banana | 25.00 |  199 |  -- 剛剛不管買啥都是扣香蕉一個!
+----+--------+-------+------+
2 rows in set (0.00 sec)
```



```sql
mysql> select trigger_name,ACTION_REFERENCE_OLD_ROW,action_reference_new_row from information_schema.triggers;
+----------------------------+--------------------------+--------------------------+
| TRIGGER_NAME               | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW |
+----------------------------+--------------------------+--------------------------+
| sys_config_insert_set_user | OLD                      | NEW                      |
| sys_config_update_set_user | OLD                      | NEW                      |
| order_after                | OLD                      | NEW                      |  -- 有狀態, 但 INSERT 的 OLD 其實為空
+----------------------------+--------------------------+--------------------------+
3 rows in set (0.00 sec)
```


## 代碼執行結構
> - 順序結構, 分支結構, 循環結構

### 分支結構
> - MySQL 只有 if 分支
```sql
if 條件 then 
    -- 滿足條件要幹嘛
[else]
    -- 否則要幹嘛
end if;   -- 結束 if 判斷
```

> - 新增訂單前, 先判斷庫存是否足夠
```sql
mysql> delimiter ^^
mysql> create trigger before_order before insert on my_order for each row
    -> begin
    -> -- 獲取庫存
    -> select inv from my_goods where id = new.g_id into @g_inv;
    -> -- 判斷庫存是否足夠
    -> if @g_inv < new.g_num then 
    -> -- 如果庫存不夠, 由於觸發器沒有阻止事件發生的方法, 所以只能暴力報錯來阻止
    -> insert into OOOOOOOO values (XXXXXXX);
    -> end if;
    -> end 
    -> ^^
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;



mysql> -- 查看效果
mysql> select * from my_goods;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |   69 |
|  2 | banana | 25.00 |  199 |
+----+--------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from my_order;
+----+------+-------+
| id | g_id | g_num |
+----+------+-------+
|  1 |    1 |     1 |
|  2 |    1 |     1 |
|  3 |    1 |    30 |
+----+------+-------+
3 rows in set (0.00 sec)


mysql> -- 新增一筆一千顆蘋果的訂單
mysql> insert into my_order values (null, 1, 1000);
ERROR 1146 (42S02): Table 'test.OOOOOOOO' doesn't exist -- 報錯

mysql> -- 訂單沒有增加
mysql> select * from my_order;
+----+------+-------+
| id | g_id | g_num |
+----+------+-------+
|  1 |    1 |     1 |
|  2 |    1 |     1 |
|  3 |    1 |    30 |
+----+------+-------+
3 rows in set (0.01 sec)

mysql> -- 當然也就沒有觸發剛剛定義的 after_order 觸發器
mysql> select * from my_goods;;
+----+--------+-------+------+
| id | name   | price | inv  |
+----+--------+-------+------+
|  1 | apple  | 20.00 |   69 |
|  2 | banana | 25.00 |  199 |
+----+--------+-------+------+
2 rows in set (0.00 sec)
```


### 循環結構
> - 某段代碼在指定條件下重複執行

> #### 基本語法
```sql
while 條件判斷 do
    -- 滿足條件要執行啥
    -- 變更循環條件(想辦法停止循環)
end while;
```

> #### 循環控制
> `iterate/leave 循環名字`
> - MySQL 中沒有 `continue` 跟 `break`
> - `iterate` : 迭代, 類似 `continue` > 後面代碼不執行, 跳下一次循環
> - `leave` : 離開, 類似 `break` > 跳出循環
> - 既然循環控制後面需要接循環名字, 當然就要先定義循環名字
```sql
循環名字: while 條件 do  -- 定義循環名字
    -- 循環體
    -- 循環控制
    iterate/leave 循環名字  -- 循環控制循環名字
end while;
```

## 函數
> - 將一段代碼封裝到一個結構中, 需要執行時, 調用結構執行即可(代碼復用)

### 系統函數
> - 任何函數都有返回值
>   - 任何有數據返回的, 都是用 `select` 查看
>   - 所以函數是用 `select` 調用
> - MySQL 中, 字符串基本操作通常以`字符`為單位
>   - 有些語言會以字節(`Byte`), 有些語言會以位(`bit`) 為單位
>   - 以 `Byte` 或 `bit` 都容易有亂碼的情形發生
>   - 以字符為單位來操作就不會有亂碼發生

#### string function
> #### substring
> `substring(str, pos, len)`
> - MySQL 下標從 1 開始
> - 中文沒有亂碼, 證明以字符擷取

```sql
mysql> set @tmp1 = 'GODJJ';
Query OK, 0 rows affected (0.00 sec)

mysql> set @tmp2 = '八八包';
Query OK, 0 rows affected (0.00 sec)

mysql> select substring(@tmp1, 1,1);
+-----------------------+
| substring(@tmp1, 1,1) |
+-----------------------+
| G                     |
+-----------------------+
1 row in set (0.00 sec)

mysql> select substring(@tmp2, 1,1);
+-----------------------+
| substring(@tmp2, 1,1) |
+-----------------------+
| 八                    |
+-----------------------+
1 row in set (0.00 sec)
```


> #### char_length & length
> - `char_length(str)`: 字符長度
> - `length(str)`: 字節長度
```sql
mysql> select char_length(@tmp1), char_length(@tmp2);
+--------------------+--------------------+
| char_length(@tmp1) | char_length(@tmp2) |
+--------------------+--------------------+
|                  5 |                  3 |
+--------------------+--------------------+
1 row in set (0.01 sec)

mysql> select length(@tmp1), length(@tmp2);
+---------------+---------------+
| length(@tmp1) | length(@tmp2) |
+---------------+---------------+
|             5 |             9 |
+---------------+---------------+
1 row in set (0.00 sec)
```
> - 中文3字符9字節, 因為當前環境為 `utf8`: 一個中文佔 3B
> - 英文存儲會進行優化, 最終一個英文只佔 1B
>   - 但是分配單位還是必須以至少 15B(5*3B) 來分配, 
>   - 因為必須保證可以插入中文

> #### instr
> `instr(str, substr)`
> - 判斷字符串是否在某個字符串中存在, 返回位置
>   - 起始位置為 1
>   - 0 表示沒找到
```sql
mysql> select instr(@tmp1, 'DJ'), instr(@tmp2, '包'), instr('喜八路媽', 'WOW');
+--------------------+---------------------+------------------------------+
| instr(@tmp1, 'DJ') | instr(@tmp2, '包')  | instr('喜八路媽', 'WOW')     |
+--------------------+---------------------+------------------------------+
|                  3 |                   3 |                            0 |
+--------------------+---------------------+------------------------------+
1 row in set (0.00 sec)
```

> #### LPAD & RPAD
> `lpad(str,  len, padstr)`
> - 用 `padstr` 將 `str` 填滿到 `len` 的長度
> - 如果原本 `str` 就超過 `len`, 那會被縮減到 `len` 的長度

```sql
mysql> select lpad(@tmp1, 10, 'abc'), lpad(@tmp2, 10, '一二三');
+------------------------+--------------------------------+
| lpad(@tmp1, 10, 'abc') | lpad(@tmp2, 10, '一二三')      |
+------------------------+--------------------------------+
| abcabGODJJ             | 一二三一二三一八八包           |
+------------------------+--------------------------------+
1 row in set (0.02 sec)

mysql> select rpad(@tmp1, 10, 'abc'), rpad(@tmp2, 10, '一二三');
+------------------------+--------------------------------+
| rpad(@tmp1, 10, 'abc') | rpad(@tmp2, 10, '一二三')      |
+------------------------+--------------------------------+
| GODJJabcab             | 八八包一二三一二三一           |
+------------------------+--------------------------------+
1 row in set (0.01 sec)


mysql> -- 截斷都是從右截斷
mysql> select rpad(@tmp1, 2, 'abc'), rpad(@tmp2, 2, '一二三');
+-----------------------+-----------------------------+
| rpad(@tmp1, 2, 'abc') | rpad(@tmp2, 2, '一二三')    |
+-----------------------+-----------------------------+
| GO                    | 八八                        |
+-----------------------+-----------------------------+
1 row in set (0.00 sec)

mysql> select lpad(@tmp1, 2, 'abc'), lpad(@tmp2, 2, '一二三');
+-----------------------+-----------------------------+
| lpad(@tmp1, 2, 'abc') | lpad(@tmp2, 2, '一二三')    |
+-----------------------+-----------------------------+
| GO                    | 八八                        |
+-----------------------+-----------------------------+
1 row in set (0.00 sec)
```

> #### insert
> `insert(str, pos, len, newstr)`
> - 替換, 找到目標位置, 指定要被替換的長度, 用指定字符串替換
> - 在 `str` 的第 `pos` 位置開始用 `newstr` 替換 `len` 個字符串
```sql
mysql> select insert(@tmp1, 3, 1 ,'t');
+--------------------------+
| insert(@tmp1, 3, 1 ,'t') |
+--------------------------+
| GOtJJ                    |  -- 在`GODJJ`的第3位開始用`t`替換1個字符
+--------------------------+
1 row in set (0.00 sec)


mysql> -- 替換的字數如果超過剩餘的字數也沒差
mysql> select insert(@tmp1, 3, 5,'t');
+-------------------------+
| insert(@tmp1, 3, 5,'t') |
+-------------------------+
| GOt                     | -- 在`GODJJ`的第3位開始用`t`替換5個字符
+-------------------------+
1 row in set (0.00 sec)



mysql> -- 但如果找不到 pos 的字符, 會返回原字符串
mysql> select insert(@tmp1, 10, 5,'t');
+--------------------------+
| insert(@tmp1, 10, 5,'t') |
+--------------------------+
| GODJJ                    |
+--------------------------+
1 row in set (0.00 sec)
```



> #### strcmp
> `strcmp(str, str)`
> - cmp = compare, 字符集比較
> - 一參跟二參比, 從左至右每位相比編碼大小
>   - 小者 -1
>   - 相等 0
>   - 大者 1
> - 區不區分大小寫是以校對集而定

```sql
mysql> select strcmp('abc', 'bcde'), strcmp('abc', 'ABC'), strcmp('bcde', 'abc');
+-----------------------+----------------------+-----------------------+
| strcmp('abc', 'bcde') | strcmp('abc', 'ABC') | strcmp('bcde', 'abc') |
+-----------------------+----------------------+-----------------------+
|                    -1 |                    0 |                     1 |
+-----------------------+----------------------+-----------------------+
1 row in set (0.00 sec)
```


### 自定義函數

> #### 定義函數
```sql
-- 定義函數
create function 函數名([形參列表]) returns 數據類型 deterministic/NO SQL/READS SQL DATA
[begin]  -- 如果只有一條 return 的話, begin 跟 end 可以省略
    -- 函數體
    -- 返回值
[end]

-- 調用函數
select 函數名([實參列表])
```
> - 必須指定返回值得數據類型
>   - 如果實際返回的值不是當時指定的數據類型, 那不會返回
>   - 但是在編譯時(定義函數)不會報錯, 因為編譯時還沒執行, 不會知道數據是啥
> - 看不懂為啥要加 `deterministic`/`NO SQL`/`READS SQL DATA`, 反正就是要選一個寫, 三個差別是什麼我也看不懂==，反正就是安全理由
>   - https://www.iteye.com/blog/bijian1013-2306796
>   - [第十三章p335](https://books.google.com.tw/books?id=xmgmDwAAQBAJ&pg=PA335&lpg=PA335&dq=DETERMINISTIC,+NO+SQL,or+READS+SQL+DATA+差別&source=bl&ots=hMilf6TvYy&sig=ACfU3U2qlFEmV2QqqAaGmKKpQZhO7VIdtQ&hl=zh-TW&sa=X&ved=2ahUKEwi626qi2JPnAhVsF6YKHWBUAUMQ6AEwA3oECAoQAQ#v=onepage&q=DETERMINISTIC%2C%20NO%20SQL%2Cor%20READS%20SQL%20DATA%20差別&f=false)

```sql
mysql> create function int_100() returns int deterministic 
    -> return 100;
Query OK, 0 rows affected (0.00 sec)

mysql> select int_100();
+-----------+
| int_100() |
+-----------+
|       100 |
+-----------+
1 row in set (0.00 sec)
```


> #### 查看函數
> - 查看所有函數
>   `show function status [like 'pattern']`
```sql
mysql> show function status\G
...
*************************** 23. row ***************************
                  Db: test                 -- 他有綁定數據庫
                Name: int_100
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2020-01-21 10:45:58
             Created: 2020-01-21 10:45:58
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
23 rows in set (0.00 sec)
```

> - 查看創建函數語句
>   `show create function 函數名`
```sql
mysql> show create function int_100\G
*************************** 1. row ***************************
            Function: int_100
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `int_100`() RETURNS int
    DETERMINISTIC
return 100
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
```

> #### 修改函數 & 刪除函數
> `drop function 函數名`
```sql
mysql> show create function int_100\G
*************************** 1. row ***************************
            Function: int_100
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `int_100`() RETURNS int
    DETERMINISTIC
return 100
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)



mysql> drop function int_100;
Query OK, 0 rows affected (0.01 sec)



mysql> show create function int_100\G
ERROR 1305 (42000): FUNCTION int_100 does not exist
```

> #### 參數
> `function 函數名 (形參名 字段類型) returns 數據類型...`
> - 實參: 可以是數值, 也可以是變量
> - 形參: 必須指定數據類型

```sql
mysql> create function printNum(num int) returns int deterministic return num;
Query OK, 0 rows affected (0.01 sec)

mysql> select printNum(1);
+-------------+
| printNum(1) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select printNum(100);
+---------------+
| printNum(100) |
+---------------+
|           100 |
+---------------+
1 row in set (0.00 sec)
```

> #### 作用域
> - MySQL 作用域跟 JS 一模一樣
>   - 全局變量:
>     - 可以在任何地方使用
>     - 使用 `set` 聲明, 且使用 @符號標誌 `set @i = 1;`
>   - 局部變量:
>     - 只能在函式內使用
>     - 使用 `declare` 聲明, 沒有 @ 符號
>     - 但是任何變量修改都還是要使用 set !

> - 計算 1 ~ n 的和
>   - 修改變量都要使用 set
>   - MySQL 沒有 += 之類的東西
```sql
delimiter ^^ 
create function sum_1_to_n (n int) returns int deterministic
begin

set @i = 1;    -- 全局變量
set @res = 0;
while @i <= n do
    -- 任何變量要修改, 都需要使用 set 
	-- MySQL 沒有 += ++ ...
	set @res = @res + @i;
	set @i = @i + 1;
end while;

return @res;
end 
^^
delimiter ;

mysql> select sum_1_to_n(10);
+----------------+
| sum_1_to_n(10) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)


mysql> -- 在外面就可以取循環跑到最後的值
mysql> select @i, @res;
+------+------+
| @i   | @res |
+------+------+
|   11 |   55 |
+------+------+
1 row in set (0.00 sec)
```


> - 求 1~n 的和, 5的倍數不取
```sql
mysql> delimiter ^_^
mysql> create function sum_1_to_n_not_5 (n int) returns int deterministic
    -> begin
    -> declare i int default 1;
    -> declare res int default 0;
    -> mywhile: while i < n do  -- 因為要用到 iterate, 所以命名
    -> if i % 5 = 0 then        -- 判斷是否為 5 的倍數
    -> set i = i + 1;           -- 如果是, 先讓控制項+1, 否則原控制項跳出會死循環
    -> iterate mywhile;         -- 跳出這圈循環
    -> end if;                  -- 結束判斷
    -> set res = res + i;       -- 不是5的倍數會繼續走這來加值
    -> set i = i + 1;
    -> end while;
    -> return res;
    -> end
    -> ^_^
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter ;
mysql> select sum_1_to_n_not_5;
ERROR 1054 (42S22): Unknown column 'sum_1_to_n_not_5' in 'field list'
mysql> select sum_1_to_n_not_5(10);
+----------------------+
| sum_1_to_n_not_5(10) |
+----------------------+
|                   40 |
+----------------------+
1 row in set (0.00 sec)


mysql> -- 局部變量外面就拿不到了, 
mysql> select i;
ERROR 1054 (42S22): Unknown column 'i' in 'field list'
mysql> select res;
ERROR 1054 (42S22): Unknown column 'res' in 'field list'
```

### 存儲過程(procedure)
> - 簡稱過程
> - 一種沒有返回值的函數
>   - 有些增刪改操作並不需要返回值, 此時既可以考慮用 `procedure`
>     - 如果過程想要顯示數據, 就在過程體裡寫 select 就好了

> #### 創建過程
```sql
create procedure 過程名 ([參數列表])
begin
    -- 過程體
end
```
```sql
mysql> create procedure pro1()
    -> select * from my_class;
Query OK, 0 rows affected (0.00 sec)
```
> #### 調用過程
> `call 過程名([實參列表])`
> - 過程沒有返回值, `select` 是無法訪問的
```sql
mysql> call pro1();
+----+--------------+------+
| id | name         | item |
+----+--------------+------+
|  1 | 三年三班     | JS   |
|  2 | 二年四班     | PHP  |
|  3 | 一年五班     | C    |
+----+--------------+------+
3 rows in set (0.00 sec)
```



> #### 查看過程
> - 查看所有過程
>   `show procedure status [like 'pattern'];`
```sql
mysql> show procedure status\G
...
*************************** 27. row ***************************
                  Db: test
                Name: pro1
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2020-01-21 15:24:42
             Created: 2020-01-21 15:24:42
       Security_type: DEFINER
             Comment: 
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
27 rows in set (0.00 sec)
```
> - 查看過程創建語句
>   `select create procedure 過程名`
```sql
mysql> show create procedure pro1\G
*************************** 1. row ***************************
           Procedure: pro1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro1`()
select * from my_class
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
```

> #### 修改過程 & 刪除過程
> `drop procedure 過程名`
> - 過程只能先修改後刪除
```sql
mysql> show create procedure pro1\G
*************************** 1. row ***************************
           Procedure: pro1
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `pro1`()
select * from my_class
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)



mysql> -- 刪除過程
mysql> drop procedure pro1;
Query OK, 0 rows affected (0.01 sec)


mysql> -- 找不到了
mysql> show create procedure pro1\G
ERROR 1305 (42000): PROCEDURE pro1 does not exist
```


> #### 過程參數
> - 過程參數嚴格限定種類
>   - `in` : 
>     - 接受從外面傳值 (值傳遞) 進去,
>     - 可以是值或變量
>   - `out` : 
>     - 不使用外部數據, 且==最後==會返回局部變量給全局變量 (傳址, 引用傳遞)
>       - 想像: 假設A送B一個禮盒, B 會把禮盒的東西全部丟掉, 然後只留盒子, 最後還會裝一些東西回送給A
>     - 只接受變量
>   - `inout` : 
>     - 使用外部數據, 且==最後==會返回局部變量給全局變量 (傳址, 引用傳遞)
>     - 只接受變量

> - 基本用法
>   `create procedure 過程名([in/out/inout 形參名 數據類型])`
```sql
mysql> delimiter ^_^

mysql> create procedure por1 (in x int, out y int, inout z int)
    -> begin
    -> select x,y,z;
    -> end
    -> ^_^
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;


mysql> -- out 跟 inout 只接受變量(傳址)
mysql> call por1(1,2,3);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine test.por1 is not a variable or NEW pseudo-variable in BEFORE trigger


mysql> set @x = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @y = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> set @z = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> call por1(@x,@y,@z);
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    1 | NULL |    3 |  -- out 不使用外部數據!!!
+------+------+------+
1 row in set (0.00 sec)


mysql> -- out 跟 inout 如果修改了參數值, 會直接修改傳進去的變量
mysql> select @x,@y,@z;
+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
|    1 | NULL |    3 |  -- @y 被改為 NULL 了
+------+------+------+
1 row in set (0.00 sec)
```

> - 存儲過程對於變量的操作, 是在存儲過程調用結束後, 才會重新將內部修改的值賦予外部傳入的變量
```sql
mysql> delimiter ^_^
mysql> create procedure pro2 (in x int, out y int, inout z int)
    -> begin 
    -> select x,y,z;  -- 查看傳進來的變量值
    -> -- 修改局部變量
    -> set x = 9;
    -> set y = 99;
    -> set z = 999;
    -> select x,y,z;    -- 查看局部變量
    -> select @x,@y,@z; -- 查看全局變量有沒有被修改
    -> --  修改全局變量
    -> set @x = 'a';
    -> set @y = 'b';
    -> set @z = 'c';
    -> select @x,@y,@z;  -- 查看這時的
    -> end
    -> ^_^
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;



mysql> -- 重設變量值
mysql> set @x = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set @y = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> set @z = 3;
Query OK, 0 rows affected (0.00 sec)


mysql> -- 調用並傳變量
mysql> call pro2(@x,@y,@z);
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    1 | NULL |    3 |  -- in 不使用外部數據(禮物被丟了)
+------+------+------+
1 row in set (0.00 sec)



-- 修改局部變量之後
+------+------+------+
| x    | y    | z    |
+------+------+------+
|    9 |   99 |  999 |   -- 局部變量被修改了
+------+------+------+
1 row in set (0.00 sec)

+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
|    1 |    2 |    3 |  -- 局部變量與全局變量無關
+------+------+------+
1 row in set (0.00 sec)


-- 全局變量被修改之後
+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
| a    | b    | c    |   -- 全局變量被修改了
+------+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


mysql> -- 在存儲過程結束之後, 再看一次全局變量
mysql> select @x,@y,@z;
+------+------+------+
| @x   | @y   | @z   |
+------+------+------+
| a    |   99 |  999 |  -- out 跟 inout 被改成當時局部變量的值了
+------+------+------+  -- 在調用完後, 這兩類形參會將外部傳來的實參賦值返回
1 row in set (0.00 sec)
```