更改已上線專案資料庫 charset/collation 開放特殊字元的輸入

要讓專案可以儲存特殊字元(例如: 🎂 等 emoji )有以下的需求:

1. Django 的設定 DATABASES 中 options 的設定需要設定 ‘charset’:‘utf8mb4’；這部分的設定已經在 Iuno 的自動設定中加上
2. 資料庫的 schema 需要符合 charset 為 utf8mb4、 collation 為 utf8mb4_unicode_ci，這部分需要以下步驟完成更改:

步驟:

1. 進入 mysql 的 container

2. 進入 mysql 指令 (mysql -u [專案資料庫的使用者] -p"[專案的使用者密碼]")

3. 指定目標專案的資料庫 (use [專案資料庫])

4. 執行

   SELECT CONCAT('ALTER TABLE ', tbl.TABLE_SCHEMA, '.', tbl.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') AS ExecuteTheString FROM information_schema.TABLES tbl WHERE tbl.TABLE_SCHEMA = 'nuwa_homepage';
   

5. 將結果複製至編輯器 (Notepad++ 即可)

6. 以空字串取代 |

7. 複製大概 50-80 行的合法指令，一部份一部份慢慢的在 mysql 的指令中執行

完成後就已經更改成功，可以隨意挑選一個 table 觀看 schema，應改 charset 為 utf8mb4 collate 為 utf8mb4_unicode_ci。 (show create table [隨意 Table])，結果應該如下:

+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| template_editor_version | CREATE TABLE `template_editor_version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dirName` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fileName` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL,
  `number` int(10) unsigned NOT NULL,
  `checksum` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `conflict` tinyint(1) NOT NULL,
  `nextVersion_id` int(11) DEFAULT NULL,
  `preVersion_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `template_editor_vers_nextVersion_id_9680228e_fk_template_` (`nextVersion_id`),
  KEY `template_editor_vers_preVersion_id_1893052b_fk_template_` (`preVersion_id`),
  CONSTRAINT `template_editor_vers_nextVersion_id_9680228e_fk_template_` FOREIGN KEY (`nextVersion_id`) REFERENCES `template_editor_version` (`id`),
  CONSTRAINT `template_editor_vers_preVersion_id_1893052b_fk_template_` FOREIGN KEY (`preVersion_id`) REFERENCES `template_editor_version` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1161 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

參考來源: https://pcwiki.pixnet.net/blog/post/105578519 https://stackoverflow.com/questions/10859966/how-to-convert-all-tables-in-database-to-one-collation http://georgepavlides.info/convert-all-tables-in-a-mysql-database-to-utf8_general_ci/