# 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)
```
> - 事務操作
> - 事務操作時, 並不會真的修改數據庫
> 
```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;`
> - 提交後, 別人就會看到你修改後的數據
> 
> - 回滾事務(不提交直接清掉)
> `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)
```
> - 再次執行操作時, 就不會自動提交了, 必須手動提交
> 
```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 變量名 = 值`
> 
> - 必須退出重登後才生效, 當次連接尚未生效
> 
### 自定義變量
> - 所有自定義變量為會話級別(當次連接有效), 因為他只保存在內存中
> - 自定義變量屬於用戶級別, 不認數據庫, 在哪都能叫
> #### 定義變量
>`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)
```
Sign in with Wallet
Connect another wallet