COSCUP Why should we move to MySQL 8.0筆記

程式技術

•

2020-08-06 (最後更新於 2021-09-12)

•

MySQL

講者彭立勛

議程連結

引言

這是一篇研討會的筆記，再加上個人的理解和實驗。對一些東西不太了解，所以有些地方會一筆帶過。

解決的BUG

1. Bug #199 Innodb autoincrement stats los on restart

從bug編號就能看出這個BUG多麼遠古……

這個問題是每次MySQL重啟時，AUTO_INCREMENT的值是以這個資料表的最大AUTO_INCREMENT值為準，這個問題可能影響有外鍵的表。參考這個範例。

以下範例（參考資料2）

建立一個有auto_increment主鍵的表，並插入三筆資料

mysql> CREATE TABLE a(id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT);

Query OK, 0 rows affected (0.05 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

+----+

3 rows in set (0.00 sec)

接下來刪除、插入資料

mysql> DELETE FROM a WHERE id = 3;

Query OK, 1 row affected (0.01 sec)



mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  4 |

+----+

3 rows in set (0.00 sec)



mysql> DELETE FROM a WHERE id = 4;

Query OK, 1 row affected (0.01 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

+----+

2 rows in set (0.00 sec)

重啟MySQL，再插入一筆資料

MySQL5.7 MySQL8.0

mysql> INSERT INTO a VALUES(NULL);

Query OK, 1 row affected (0.01 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

+----+

3 rows in set (0.00 sec)

mysql> insert into a values(NULL);

Query OK, 1 row affected (0.02 sec)



mysql> SELECT * FROM a;

+----+

| id |

+----+

|  1 |

|  2 |

|  5 |

+----+

3 rows in set (0.00 sec)

可以發現兩個版本插入的id不同，5.7版插入id3（迷惑行為），其他表有關聯但沒設外鍵約束估計會出錯

另外，實測5.7版的MyISAM沒有這個問題。

2. Bug #25922 InnoDB crash recovery changes: make DDL in MySQL 'atomic'

新功能

CTE (Common Table Expressions)

根據文檔的解釋：「公用表表達式（CTE）是一個命名的臨時結果集，僅存在於單個SQL語句範圍內」

恭喜，這下終於可以在MySQL用遞迴了

。例如之前的購物車專案，商品用鄰接表(Adjacency List)紀錄階層

這時候要取出Breadcrumbs就很簡單(?)了

另外新功能還有Windows function、Instant Add Columns等等

重大更新

1. 放棄MyISAM

8.0以後的版本的系統資料庫（即mysql資料庫）完全不使用MyISAM引擎，改用InnoDB

幾種情況可以考慮繼續使用MyISAM，例如需要經常在不同的機器間copy資料庫，或是只插入不修改資料的資料庫。但是仍建議使用InnoDB而非MyISAM

2. 持久化系統變數

在8.0以前，要更改系統變數，用SET GLOBAL指令只是「暫時」的，因為只要重啟MySQL，系統變數又會變回原樣。

例如要把ONLY_FULL_GROUP_BY這個模式刪掉

重啟MySQL之後，會發現ONLY_FULL_GROUP_BY又跑回來了

要永久修改，必須修改/etc/mysql/my.cnf檔案(Windows為my.ini檔)

上例刪掉ONLY_FULL_GROUP_BY，就在my.ini檔加上紅框中的那幾行。重啟MySQL後會發現ONLY_FULL_GROUP_BY不會再跑回來了

但是這樣的修改方式對雲端伺服器不友好，因為不方便更改伺服器的配置

8.0版本有了新的解決方案，使用關鍵字”PERSIST”可以永久修改系統變數

依然是刪掉ONLY_FULL_GROUP_BY的例子

這時候會產生一個mysqld-auto檔（在Laragon的情況，檔案是"laragon\data\mysql-8\mysqld-auto.cnf"）

這是一個json格式的檔案，記錄了何時更動了哪些系統變數

{

  "Version":1,

  "mysql_server":{

     "sql_mode":{

        "Value":"STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION",

        "Metadata":{

           "Timestamp":1596296883902530,

           "User":"root",

           "Host":"localhost"

        }

      }

   }

}

之後重啟MySQL，會發現sql_mode不會變回原樣了！

也可以重置PERSIST，詳情請參考文檔