透過命令列管理 MySQL 資料庫是 DBA 基礎技能。本文記錄建立不同字元集資料庫、查看建庫語句、刪除資料庫的完整操作流程與輸出範例。
/usr/local/mysql/bin/mysql -uroot -p'admin123'
mysql> CREATE DATABASE byrd_default;
Query OK, 1 row affected (0.08 sec)
mysql> SHOW DATABASES LIKE 'byrd%';
+------------------+
| Database (byrd%) |
+------------------+
| byrd_default |
+------------------+
mysql> SHOW CREATE DATABASE byrd_default;
+--------------+-----------------------------------------------------------------------+
| Database | Create Database |
+--------------+-----------------------------------------------------------------------+
| byrd_default | CREATE DATABASE `byrd_default` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------------------------+
預設字元集為 utf8(編譯時指定)。
mysql> CREATE DATABASE gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW CREATE DATABASE gbk;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| gbk | CREATE DATABASE `gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
mysql> DROP DATABASE gbk1;
Query OK, 0 rows affected (0.19 sec)
mysql> DROP DATABASE IF EXISTS byrd_default; -- 不存在不報錯,只警告
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| byrd_default |
| gbk |
| mysql |
| performance_schema |
| test |
| ultrax |
+--------------------+
7 rows in set (0.00 sec)
| 操作 | 指令 |
|---|---|
| 建立資料庫 | CREATE DATABASE db_name [CHARACTER SET charset] [COLLATE collation]; |
| 查看建庫語句 | SHOW CREATE DATABASE db_name; |
| 列出資料庫 | SHOW DATABASES [LIKE 'pattern']; |
| 刪除資料庫 | DROP DATABASE [IF EXISTS] db_name; |
| 切換資料庫 | USE db_name; |
CHARACTER SET utf8mb4 和 COLLATE utf8mb4_unicode_ci,支援完整 Unicode(含 Emoji)RENAME DATABASE(MySQL 5.1+ 不支援,可改表前綴)IF EXISTS 避免腳本報錯中斷