Post view

insoler社群網站的MySQL資料庫從MyISAM更換成InnoDB儲存引擎,以及utf8mb4編碼系統🍏

在2年前(其實是1年半前)的09/16/2017,我花了一些時間,將MySQL資料庫系統的編碼格式從有點老舊的「3位元組的utf8_general_ci」換成新的「4位元組的utf8mb4_general_ci」。這樣的改變,可以讓網站上的所有的文字都支援最新的Unicode編碼,可以使用Unicode的圖形字元以及表情符號,例如:🧨🌹🌸🚙🍏🍎、😁😂😡😰💢。請閱讀這篇文章:

insoler社群網站🍎升級到4位元組的Unicode多國語系utf8mb4編碼系統

我安裝目前最新的Joomla 3.9.5版本,想要了解一下經過多年以後,在Joomla 4.0版正式發表以前,Joomla這個僅次於WordPress的網站系統會進化到什麼程度?

我使用phpMyAdmin 4.8.5(目前的最新版本)發現剛剛安裝好的全新Joomla網站,預設值就已經是使用「4位元組的utf8mb4_general_ci」。甚至整個資料庫表格都是使用「InnoDB儲存引擎」!

但是在海豚的才剛剛發表的最新版本Dolphin 7.4.1,仍舊是使用「3位元組的utf8_general_ci」與「MyISAM儲存引擎」(MySQL Storage Engine)。相較於Joomla網站系統,好像有點過時。

雖然「MySQL儲存引擎」使用MyISAM格式好像有點過時。不過,MyISAM檔案格式比較簡單,處理速度也比較快,並不是能全面大勝InnoDB。這就像是Windows系統上常用的NTFS、FAT32、exFAT這3個檔案格式,雖然FAT32的一個檔案大小的最大限制是4GB。如果所有檔案都遠小於4GB的話,甚至沒有4MB以上的檔案,這3種檔案格式就沒有壓倒性優勢,而是各有特色,也各有優缺點。

同樣的,MySQL系統上常用的MyISAM、InnoDB也是各有特色,而且各有優缺點。在Google搜尋MyISAM、InnoDB就可以找到許多的說明與比較的文章。

Difference Between InnoDb and MyISAM

MyISAM Vs InnoDB

Chapter 14 The InnoDB Storage Engine

要提醒你的是,應該要注意那些文章是否使用MySQL 8的最新版本。至少也必須是在MySQL 5.6以後的版本。太過老舊的電腦資訊文章,只有考古價值,沒有太多的參考價值。

在09/16/2017的時候,我花了一些時間,手動轉換資料庫結構,從「3位元組的utf8_general_ci」換成新的「4位元組的utf8mb4_general_ci」,所以你可以在phpMyAdmin看到雖然儲存引擎的類型還是MyISAM,但已經把整個網站的資料庫都換成「4位元組的utf8mb4_general_ci」。

有些不在意區分英文字大小寫的資料表,我是使用「4位元組的utf8mb4_general_ci」。但是某些需要區分英文字大小寫(也同時會區別某些中文字元與日文字符號)我則是改成「4位元組的utf8mb4_bin」格式。

把phpMyAdmin畫面捲到最底下,雖然會看到MySQL預設的資料庫儲存引擎是InnoDB,不過整個insoler資料庫,包括Dolphin 7.4.1的資料庫仍舊是MyISAM。

雖然MyISAM的處理速度比較快(如果是使用在超大型網站,許多人同時讀寫資料時可能會比InnoDB慢很多)但是安裝一個全新的Joomla 3.9.5版網站,使用的儲存引擎已經改成InnoDB。我想這應該是有許多人、許多站長的研究結果,雖然InnoDB會比較慢,但可能其他優點會比MyISAM好。

所以我花了許多時間,把儲存引擎從MyISAM更換成InnoDB。事實上,這已經是失敗3次,最後一次才終於轉換成功。

這次只有更換成InnoDB儲存引擎,上次轉換「3位元組的utf8_general_ci」換成新的「4位元組的utf8mb4_general_ci」或是新的「4位元組的utf8mb4_bin」編碼格式就不再改變。

但是對於user來說,換成新的「4位元組的utf8mb4_general_ci」或是新的「4位元組的utf8mb4_bin」編碼格式會比較有「升級的感覺」。因為這樣就可以使用Unicode的圖形文字與表情符號。但其實從2017年到現在,只有我有使用,並沒有任何會員有使用這項功能,因此實際上應該是沒有人有「升級的感覺」!

這次把MyISAM更換成InnoDB儲存引擎,MySQL預設的也是InnoDB格式。全部統一成InnoDB格式,雖然失敗3次才終於轉換成功,但是從user的角度來看,絕對不可能看到某個網站的資料庫是使用哪一種格式,甚至是哪一種資料庫伺服器!因此這次的「升級感覺」絕對是只有我才會有感覺!

在phpMyAdmin的Demo網站上可以看到他們是使用有點舊的MySQL 8.0.3版。目前的最新版本是MySQL 8.0.15。我猜想MySQL 8.0.15在Linux應該可以完全穩定的正常執行。甚至在Windows 10上面也沒有太大的問題。

但是MySQL 8.0.15在目前最新版本macOS Mojave 10.14.4不但安裝好以後,完全無法正常啟動伺服器(MySQL Server會不斷嘗試自我重新啟動),更不可能用來匯入備份的資料庫,可說是完全無法使用的「磚塊級資料庫系統」!

更不用說,更老舊的MySQL 8.0.3問題更多,更無法安裝在macOS Mojave 10.14系統上!

從MyISAM更換InnoDB儲存引擎的轉換步驟

無論你想要做什麼實驗、測試、升級版本... 等任何動作以前,所有站長都必須要先做的「事前準備工作」就是「備份整個網站」!這包括:

  • 備份整個網站的PHP程式碼。
  • 匯出與備份整個MySQL資料庫。

「備份整個網站」的PHP程式碼,其實就只是複製檔案到另外一個地方,通常最好是另外一個磁碟機、USB外接硬碟、USB隨身碟等。

但是「匯出與備份整個MySQL資料庫」,特別是MySQL的話,就比較麻煩,甚至可說是非常麻煩,要是不小心做錯了,很可能會有備份的資料錯誤,導致無法重新匯入還原,導致網站無法復原的重大問題!

通常「匯出整個MySQL資料庫」可以透過這3種方式:

  • phpMyAdmin(PHP寫的資料庫管理網頁)
  • MySQL Workbench(MySQL官方提供的視窗模式管理程式)
  • mysqldump(MySQL官方提供的命令列匯出資料程式。要匯入資料必須改用另外一個mysql命令列指令)

對於個人網站、小型公司網站、學生用的練習測試網站來說,可能很多人都是使用phpMyAdmin來完成資料的匯出與匯入的動作。但是對於大型、超大型網站來說,絕對不會有人使用phpMyAdmin來備份資料庫!

因為就算是insoler這麼小的奈米網站,使用mysqldump只需要5秒就能匯出整個資料庫,使用phpMyAdmin至少需要3-5分鐘以上!光是每天製作的全網站Sitemap檔案,就需要3分鐘!只要5〜10秒就能完成的事情,沒人會想要使用phpMyAdmin!更不用說,大型網站很可能資料庫超過50GB、100GB以上,就算是使用mysqldump都需要花10幾分鐘,更絕對不可能使用phpMyAdmin來匯出資料庫。

排除不能用的phpMyAdmin。第二個最好的選擇當然是使用「視窗模式管理程式」的MySQL Workbench。不過,很不幸的,這個管理程式一直寫的非常差,也因此才會有很多人寧可使用phpMyAdmin。如果你使用MySQL Workbench來匯出、匯入資料庫的話,在「3位元組的utf8_general_ci」的情況,並不會遇到任何問題。

但如果已經是使用新的「4位元組的utf8mb4_general_ci」編碼格式的話,由於MySQL Workbench只支援「3位元組的utf8」,所以就算資料庫已經是使用「4位元組的utf8mb4」在匯出資料以後,會變成「3位元組的utf8」!而且沒有任何警告訊息!只會看到很正常的匯出完成!

如果站長沒有留意到MySQL Workbench不支援「4位元組的utf8mb4」,就刪除資料庫,或是重新匯入備份的資料庫,就會因為匯入的資料只有「3位元組的utf8」,匯入完成以後,原本使用的「4位元組的utf8mb4」的Unicode字元,全部都會變成「????」問號!例如原本是使用「🍏」字元,使用MySQL Workbench匯出整個資料庫以後,會變成「3位元組的utf8」,再重新匯入以後,文章裡只要有使用「🍏」字元的地方,就會變成「????」!

而且這個影響會影響到整個網站!因此不支援「4位元組的utf8mb4」的MySQL Workbench也絕對不能使用!

所以剩下唯一可以使用的資料庫備份、還原工具,就只剩「mysqldump」與「mysql」這兩個非常難用的命命列程式指令!

使用「mysqldump」與「mysql」匯出與匯入資料庫

通常你可以使用這樣的指令來備份、匯出整個資料庫:

/usr/local/mysql/bin/mysqldump --defaults-file=/etc/my.cnf -h localhost -Q --add-drop-table --databases insoler > insoler.sql

然後使用這樣的指令來匯入、還原資料庫:

/usr/local/mysql/bin/mysql --defaults-file=/etc/my.cnf -h localhost --comments insoler < insoler.sql

要提醒你的是,mysql指令裡面的「insoler」是用來指定要匯出、匯入的資料庫名稱。請換成你想要使用的資料庫名稱。例如Joomla等。

另外,在連接MySQL的時候要輸入帳號、密碼。為了避免輸入錯誤的帳號、密碼,我其實是把帳號密碼設定在my.cnf設定檔案裡面。你必須在my.cnf事先新增這樣的資料:

[mysql]

user = 連接MySQL的帳號名稱

password = 連接MySQL的密碼

default-character-set = utf8mb4

 

[mysqldump]

user = 連接MySQL的帳號名稱

password = 連接MySQL的密碼

default-character-set = utf8mb4

 

分別匯出「資料庫結構」與「資料庫的內容」

但是因為這次是需要變更儲存引擎,從MyISAM改成InnoDB(從utf8改成utf8mb4編碼的時候也一樣),就不可以備份整個資料庫!因為這樣會導致insoler.sql檔案太大!insoler目前是250MB左右,BNW的conference.sql則是2.5GB左右。

雖然匯出的「.sql」只是一個UTF8編碼的純文字檔案,但是我想,應該是沒有任何的「文字編輯程式」或是Word可以編輯高達2.5GB的純文字檔案!甚至有許多文字編輯程式,也很難編輯250MB的純文字檔。

因此,我們不能匯出整個資料庫到單一的insoelr.sql檔案,而必須分別匯出「資料庫結構」與「資料庫的內容」。這樣的話,「資料庫結構」檔案就會非常小,可以使用任何的「文字編輯程式」甚至是Windows的「記事本」也可以輕鬆編輯。

匯出「資料庫結構」:

/usr/local/mysql/bin/mysqldump --defaults-file=/etc/my.cnf -h localhost -Q --no-data --databases insoler > insoler-struct.sql

匯出「資料庫內容」:

/usr/local/mysql/bin/mysqldump --defaults-file=/etc/my.cnf -h localhost -Q --no-create-info=TRUE --skip-triggers --databases insoler > insoler-data.sql

只有使用這種方式匯出的資料庫內容,才會是支援「4位元組的utf8mb4」的UTF8純文字檔案,不會有匯入以後變成????亂碼的問題!

匯出的insoler-struct.sql資料庫結構檔案只有161KB,因此可以使用任何的文字編輯程式來修改資料庫結構。

從utf8_general_ci改成utf8mb4_general_ci

雖然「理論上」應該是可以同時執行「utf8改utf8mb4」以及「MyISAM改InnoDB」。不過最好還是分2次完成,這樣比較容易測試與除錯。以免誤以為轉換成功,幾天之後才發現出了很大的錯誤!要重新修正資料內容就會非常棘手。

這是Dolphin 7.4.1標準的bx_forum資料表的結構:

CREATE TABLE `bx_forum` (
`forum_id` int(10) UNSIGNED NOT NULL,
`forum_uri` varchar(255) NOT NULL DEFAULT '',
`cat_id` int(11) NOT NULL DEFAULT '0',
`forum_title` varchar(255) DEFAULT NULL,
`forum_desc` varchar(255) NOT NULL DEFAULT '',
`forum_posts` int(11) NOT NULL DEFAULT '0',
`forum_topics` int(11) NOT NULL DEFAULT '0',
`forum_last` int(11) NOT NULL DEFAULT '0',
`forum_type` enum('public','private') NOT NULL DEFAULT 'public',
`forum_order` int(11) NOT NULL DEFAULT '0'
PRIMARY KEY (`forum_id`),
KEY `cat_id` (`cat_id`),
KEY `forum_uri` (`forum_uri`);
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

當你想要進行「utf8改utf8mb4」的時候,就會遇到這個問題:

MySQL VARCHAR(255) UTF8 is too long for key, but max length is 1000 bytes

mysql> ALTER TABLE entry ADD UNIQUE INDEX (name(255));

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

這個錯誤訊息是指定的「索引Key」最長只能是1000位元組!

由於bx_forum資料表的「`forum_uri`」資料欄位是使用varchar(255)格式,因此在3位元組的UTF8格式的時候,索引鍵的Key長度是:

255 x 3 = 765 位元組

765 位元組的長度並沒有超過最長1000位元組的上限,所以並沒有任何問題。但是,一但想要改成「4位元組的utf8mb4」的話,就會變成:

255 x 4 = 1020 位元組

只有把資料結構最後的「DEFAULT CHARSET=utf8」改成「DEFAULT CHARSET=utf8mb4」再匯入資料庫結構的時候,就會看到上面的「Specified key was too long; max key length is 1000 bytes」錯誤訊息!

唯一的解決辦法就是確認資料欄位真的需要varchar(255),資料內容真的有達到255個字?如果沒有的話,可以改成這樣的格式:

  `forum_uri` varchar(250) NOT NULL DEFAULT '',

這樣的話,資料索引鍵的長度就會是:

250 x 4 = 1000 位元組

剛剛好可以在索引鍵的長度的上限,只要確定資料內容沒有任何一筆資料超過250個字,就可以縮短資料結構到「varchar(250)」。要注意的是,所謂「確認資料內容沒有任何一筆資料超過250個字」必須你自己手動確認。在使用mysql指令匯入資料時,萬一有某些資料超過250個字,多的字元會被自動捨棄、自動截斷,而且不會有任何的警告訊息!

CREATE TABLE `bx_forum` (
`forum_id` int(10) UNSIGNED NOT NULL,
`forum_uri` varchar(250) NOT NULL DEFAULT '',
`cat_id` int(11) NOT NULL DEFAULT '0',
`forum_title` varchar(255) DEFAULT NULL,
`forum_desc` varchar(255) NOT NULL DEFAULT '',
`forum_posts` int(11) NOT NULL DEFAULT '0',
`forum_topics` int(11) NOT NULL DEFAULT '0',
`forum_last` int(11) NOT NULL DEFAULT '0',
`forum_type` enum('public','private') NOT NULL DEFAULT 'public',
`forum_order` int(11) NOT NULL DEFAULT '0'
PRIMARY KEY (`forum_id`),
KEY `cat_id` (`cat_id`),
KEY `forum_uri` (`forum_uri`);
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

只有「索引鍵」會有1000字元長度的限制,其他資料欄位並沒有這樣的限制,所以改成這樣的資料結構,就可以把utf8改成utf8mb4。不過,堅持非要使用varchar(255)並沒有任何意義。255這個8位元的數字上限(從0〜255)只對「電腦有意義」,對人類來說,看到上限是「255個字」,無論是誰都會覺的有點奇怪!反而對於「上限是1000個字元」這樣的整數,反而會認為比較合理。

因此你可以全部都改成「varchar(250)」。當然前提是必須事先確認沒有任何一筆資料的長度會超過250個字!

從MyISAM更換成InnoDB儲存引擎

雖然全部都統一成「varchar(250)」會是比較好的做法,不過如果想要改變MySQL儲存引擎的話,你還會遇到新的問題!在前面完成的資料結構內容是:

CREATE TABLE `bx_forum` (
  `forum_id` int(10) UNSIGNED NOT NULL,
  `forum_uri` varchar(250) NOT NULL DEFAULT '',
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `forum_title` varchar(250) COLLATE utf8mb4_bin DEFAULT NULL,
  `forum_desc` varchar(250) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `forum_posts` int(11) NOT NULL DEFAULT '0',
  `forum_topics` int(11) NOT NULL DEFAULT '0',
  `forum_last` int(11) NOT NULL DEFAULT '0',
  `forum_type` enum('public','private') COLLATE utf8mb4_bin NOT NULL DEFAULT 'public',
  `forum_order` int(11) NOT NULL DEFAULT '0'
  PRIMARY KEY (`forum_id`),
  KEY `cat_id` (`cat_id`),
  KEY `forum_uri` (`forum_uri`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

想要從MyISAM改成InnoDB儲存引擎,只要全部搜尋取代把「ENGINE=MyISAM」自動取代成「ENGINE=InnoDB」即可。但是這樣的話,在匯入資料庫的時候,就會看到新的錯誤訊息!

MySQL: Specified key was too long; max key length is 767 bytes

Error: 1071 Specified key was too long; max key length is 767 bytes

在MyISAM格式,索引鍵可以達到1000位元組。但是在InnoDB格式,索引鍵只能達到767位元組!我想你一定也會認為上限是767是什麼奇怪的數字?

因此,相同的問題,索引鍵欄位不可以再使用「varchar(250)」!但是其他欄位可以繼續使用「varchar(250)」。因為:

767 / 4 = 191.75 位元組。

由於「位元組」是記憶體單位,最小的單位是1個,沒有小數點,無法切割。所以索引鍵欄位的上限是:

  • MyISAM格式+utf8mb4:varchar(250)
  • InnoDB格式+utf8mb4:varchar(191)

如果你認為上限是「191個字元」看起來有點奇怪,也可以改成「varchar(190)」。同樣的,你必須事先確認,轉換資料結構以前,沒有任何一筆資料內容的長度超過190個字!特別是索引鍵的forum_uri欄位內容都不可以超過190個字。

CREATE TABLE `bx_forum` (
  `forum_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `forum_uri` varchar(190) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `cat_id` int(11) NOT NULL DEFAULT '0',
  `forum_title` varchar(250) COLLATE utf8mb4_bin DEFAULT NULL,
  `forum_desc` varchar(250) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `forum_posts` int(11) NOT NULL DEFAULT '0',
  `forum_topics` int(11) NOT NULL DEFAULT '0',
  `forum_last` int(11) NOT NULL DEFAULT '0',
  `forum_type` enum('public','private') COLLATE utf8mb4_bin NOT NULL DEFAULT 'public',
  `forum_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`forum_id`),
  KEY `cat_id` (`cat_id`),
  KEY `forum_uri` (`forum_uri`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

把資料結構改成這樣,就可以完成「utf8改utf8mb4」以及「MyISAM改InnoDB」的任務。你只要使用前面的mysql指令匯入手動修正好的insoler-struct.sql資料結構,確認沒有任何錯誤訊息。就可以再匯入insoler-data.sql資料庫內容。

由於phpMyAdmin在MyISAM模式並沒有什麼問題。但是在InnoDB模式,反而會看到資料表的「資料筆數」竟然是「負數」的荒謬情況。一個資料表完全沒有任何資料內容的話,資料筆數是0。只要有新增資料,就會是1以上。永遠都不可能會有「負數」。

而且你打開bx_forum資料表,直接跳到最後一筆資料,你也會發現最後的資料竟然都被人間蒸發!完全無法顯示在phpMyAdmin!但卻可以正常地顯示在網站上!

3.11 The number of rows for InnoDB tables is not correct.

PHPMyAdmin issue: Is it advisable to assign a very large value to `$cfg['MaxExactCount']`?

要解決這個問題,你必須在phpMyAdmin的config.inc.php設定檔案的最後新增這樣的陣列變數:

$cfg['MaxExactCount'] = 1000000; //InnoDB

指定的數字可以是1000000或是9000000。我不知道這個參數的上限是多少?或許有人使用10000000000也沒有問題。但我認為不要設定在遠超過整個網站的資料筆數會比較好。比如你的網站資料筆數低於1萬筆,卻設定在10000000000。

蘇言霖 2019/04/17 2 153
Comments
Order by: 
Per page:
 
  •  ayaka: 
     

    最近相簿好像有點問題,不登錄反而可以看到小圖而登錄進去沒有辦法在相簿看到小圖,這樣也獲取不到下載位置了

     
     2019/04/211 replies1 replies 
    0 points
     
Rate
0 votes
Post info
蘇言霖
「超級懶貓級」社群網站站長
2019/04/17 (130 days ago)
Actions